拨开慢SQL迷雾｜连载05：一文搞定高级调优三板斧，分区裁剪、SQL Patch与向量引擎

磐维（PanWeiDB）SQL 优化实战系列：
《拨开慢SQL迷雾｜连载01：一文吃透执行计划基础，开启SQL调优之路》
《拨开慢SQL迷雾｜连载02：一文掌握索引优化精髓，何时建、如何建、怎么避坑》
《拨开慢SQL迷雾｜连载03：一文掌控多表JOIN策略，从选型到Hint强制绑定》
《拨开慢SQL迷雾｜连载04：一文破解子查询性能陷阱，从NOT IN到分析函数改写》
《拨开慢SQL迷雾｜连载05：一文搞定高级调优三板斧，分区裁剪、SQL Patch与向量引擎》

导语

当单表索引优化和连接方式调整到达瓶颈时，就需要动用"重型武器"。本文介绍磐维（PanWeiDB）中三种高级调优手段：

1. 分区表裁剪：通过分区架构减少数据扫描范围；
2. SQL Patch：在不修改业务代码的前提下，为特定 SQL 绑定 Hint 执行计划；
3. 向量化执行引擎：将行存查询转换为向量化计算，大幅提升 AP 场景性能。

本文结合 TPCH Q1等经典压测用例与生产场景案例，给出完整的优化前后对比。

一、分区表裁剪：减少扫描数据量

1.1 原理

对于数据量巨大的事实表（如订单、日志），建议按业务维度（通常是时间）进行分区。查询时，优化器会自动识别分区键上的过滤条件，仅扫描匹配的分区，跳过无关分区，从而大幅减少 IO。

1.2 案例：TPCH ORDERS 表按年分区

场景：orders 表有 3000 万行，查询 1994 年某时间段的数据。

优化前：普通表全表扫描

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE o_orderdate >= '1994-02-01'
  AND o_orderdate < '1994-12-01';

执行计划：

QUERY PLAN
--------------------------------------------------------------------------------
+ Seq Scan on orders  (cost=0.00..285867.26 rows=914586 width=111)
                    (actual time=0.064..1912.433 rows=944468 loops=1)
   Filter: ((o_orderdate >= '1994-02-01 00:00:00'::oradate) AND
            (o_orderdate < '1994-12-01 00:00:00'::oradate))
   Rows Removed by Filter: 6555532
Total runtime: 1960.277 ms

分析：全表顺序扫描，过滤掉 655 万行无效数据，仅返回 94 万行，耗时 1960 ms。

优化后：分区表裁剪

建表与数据迁移：

CREATE TABLE orders_part (
    O_ORDERKEY      INTEGER NOT NULL,
    O_CUSTKEY       INTEGER NOT NULL,
    O_ORDERSTATUS   CHAR(1) NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     DATE NOT NULL,
    O_ORDERPRIORITY CHAR(15) NOT NULL,
    O_CLERK         CHAR(15) NOT NULL,
    O_SHIPPRIORITY  INTEGER NOT NULL,
    O_COMMENT       VARCHAR(79) NOT NULL
) PARTITION BY RANGE(O_ORDERDATE) INTERVAL('1 year') (
    PARTITION p_orders_1991 VALUES LESS THAN(TO_DATE('1992-01-01','yyyy-mm-dd'))
);

INSERT INTO orders_part SELECT * FROM orders;

查询分区表：

EXPLAIN ANALYZE
SELECT * FROM orders_part
WHERE o_orderdate >= '1994-02-01'
  AND o_orderdate < '1994-12-01';

执行计划：

QUERY PLAN
--------------------------------------------------------------------------------
Partition Iterator  (cost=0.00..228875.39 rows=18601 width=350)
                    (actual time=0.028..401.410 rows=944468 loops=1)
   Iterations: 1
+   ->  Partitioned Seq Scan on orders_part
         (cost=0.00..228875.39 rows=18601 width=350)
         (actual time=0.025..335.103 rows=944468 loops=1)
         Filter: ((o_orderdate >= '1994-02-01 00:00:00'::oradate) AND
                  (o_orderdate < '1994-12-01 00:00:00'::oradate))
         Rows Removed by Filter: 193476
+         Selected Partitions: 4
Total runtime: 444.784 ms

关键变化：

• 执行计划中出现 Partition Iterator 算子
• Rows Removed by Filter 从 655 万降至 19 万

效果对比：

额外收益：快速清理历史数据

分区表删除历史数据可直接使用 TRUNCATE PARTITION，无需扫描全表：

\timing on
ALTER TABLE orders_part TRUNCATE PARTITION FOR ('1994-02-01');

执行后该分区数据秒级清空，而普通表 DELETE 需要逐行标记并产生大量 WAL。

二、SQL Patch：不修改 SQL 绑定执行计划

2.1 原理

生产环境中，很多慢 SQL 来自封装好的应用程序，无法直接修改源码。磐维提供 SQL Patch 机制，通过 unique_query_id 为特定 SQL 绑定 Hint，实现不修改代码、不重启数据库的"热修复"。

2.2 案例：将 Hash Join 强制改为 NestLoop

业务 SQL：

SELECT *
FROM t_tables t
INNER JOIN t_objects o
ON o.owner = t.owner
AND o.object_name = t.table_name;

优化前：默认 Hash Join

开启语句追踪并执行：

SET track_stmt_stat_level = 'L1,OFF';
\o /dev/null
SELECT * FROM t_tables t INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;
\o

从 statement_history 查看执行计划：

SELECT query_plan FROM dbe_perf.statement_history
WHERE query LIKE '%t_tables%' AND is_slow_sql = 0;

原执行计划：

Datanode Name: dn_6001
+ Hash Join  (cost=202.47..3856.25 rows=864 width=544)
   Hash Cond: (((o.owner)::text = (t.owner)::text) AND
               ((o.object_name)::text = (t.table_name)::text))
-   ->  Seq Scan on t_objects o
   ->  Hash
         ->  Seq Scan on t_tables t

分析：优化器选择 Hash Join，两表均全表扫描。假设业务场景下 t_tables 很小且 t_objects 有索引，NestLoop 可能更优。

优化后：通过 SQL Patch 绑定 NestLoop

步骤 1：获取 SQL 的 unique_query_id

SELECT unique_query_id, query
FROM dbe_perf.statement_history
WHERE query LIKE '%t_tables%';
-- 假设 unique_query_id = 1913649014

步骤 2：创建 Patch

SELECT * FROM dbe_sql_util.create_hint_sql_patch(
    'patch1',           -- Patch 名称
    1913649014,         -- 归一化 SQL ID
    'nestloop(t o)'     -- Hint 内容
);

步骤 3：再次执行并验证

\o /dev/null
SELECT * FROM t_tables t INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;
\o

优化后执行计划：

SELECT query_plan FROM dbe_perf.statement_history
WHERE query LIKE '%t_tables%' AND is_slow_sql = 0;

新执行计划：

Datanode Name: dn_6001
+ Nested Loop  (cost=0.00..10576.44 rows=864 width=544)
   ->  Seq Scan on t_tables t  (cost=0.00..159.59 rows=2859 width=350)
-   ->  Index Scan using t_objects_n10 on t_objects o
         (cost=0.00..3.63 rows=1 width=194)
         Index Cond: ((object_name)::text = (t.table_name)::text)
         Filter: ((t.owner)::text = (owner)::text)

关键变化：

• 连接方式从 Hash Join 变为 Nested Loop
• t_objects 从 Seq Scan 变为 Index Scan using t_objects_n10

效果：在不修改应用代码的情况下，强制改变了执行路径。

Patch 生命周期管理

-- 查看现有 Patch
SELECT patch_name, unique_sql_id, enable, status, hint_string
FROM gs_sql_patch;

-- 查看指定 Patch 详情
SELECT * FROM dbe_sql_util.show_sql_patch('patch1');

-- 删除 Patch
SELECT * FROM dbe_sql_util.drop_sql_patch('patch1');

三、向量化执行引擎：AP 场景的大杀器

3.1 原理

磐维 默认对行存表使用行执行引擎（Row-oriented），逐行处理数据。对于类 TPCH 的复杂分析查询（AP 场景），可开启 向量化执行引擎（Vector-oriented），将数据按批（Batch）处理，利用 SIMD 指令和列式计算优势，实现数量级性能提升。

关键参数：

SET try_vector_engine_strategy = off;    -- 默认，行引擎
SET try_vector_engine_strategy = force;  -- 强制向量化
SET try_vector_engine_strategy = optimal; -- 优化器自动选择

3.2 案例：TPCH Q1 行引擎 vs 向量化引擎

TPCH Q1 是典型的全表扫描 + 复杂聚合查询，对引擎效率极为敏感。

优化前：行引擎（默认）

SET try_vector_engine_strategy = off;

EXPLAIN ANALYZE
SELECT
    l_returnflag,
    l_linestatus,
    SUM(l_quantity) AS sum_qty,
    SUM(l_extendedprice) AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity) AS avg_qty,
    AVG(l_extendedprice) AS avg_price,
    AVG(l_discount) AS avg_disc,
    COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

执行计划：

QUERY PLAN
--------------------------------------------------------------------------------
Sort  (cost=8721040.14..8721040.16 rows=6 width=257)
      (actual time=489186.754..489186.754 rows=4 loops=1)
   Sort Key: l_returnflag, l_linestatus
   Sort Method: quicksort Memory: 25kB
+   ->  HashAggregate  (cost=8721039.96..8721040.07 rows=6 width=257)
                      (actual time=489186.720..489186.726 rows=4 loops=1)
         Group By Key: l_returnflag, l_linestatus
         ->  Seq Scan on lineitem
               (cost=0.00..3991584.60 rows=118236384 width=25)
               (actual time=0.019..53493.463 rows=118306082 loops=1)
               Filter: (l_shipdate <= '1998-09-02 00:00:00'::timestamp without time zone)
               Rows Removed by Filter: 1688526
Total runtime: 489186.931 ms

分析：行引擎下 HashAggregate 逐行聚合 1.18 亿行数据，耗时 489 秒（约 8 分钟）。

优化后：向量化引擎

EXPLAIN ANALYZE
SELECT /*+ SET(try_vector_engine_strategy force) */
    l_returnflag,
    l_linestatus,
    SUM(l_quantity) AS sum_qty,
    SUM(l_extendedprice) AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity) AS avg_qty,
    AVG(l_extendedprice) AS avg_price,
    AVG(l_discount) AS avg_disc,
    COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

执行计划：

QUERY PLAN
--------------------------------------------------------------------------------
Row Adapter  (cost=2338446.67..2338446.67 rows=6 width=257)
             (actual time=73465.856..73465.857 rows=4 loops=1)
+   ->  Vector Sort  (cost=2338446.66..2338446.67 rows=6 width=257)
                    (actual time=73465.851..73465.851 rows=4 loops=1)
         Sort Key: l_returnflag, l_linestatus
         Sort Method: quicksort Memory: 4kB
         ->  Vector Sonic Hash Aggregate
               (cost=2338446.48..2338446.58 rows=6 width=257)
               (actual time=73465.183..73465.184 rows=4 loops=1)
               Group By Key: l_returnflag, l_linestatus
               ->  Vector Adapter(type: BATCH MODE)
                     (cost=1156362.48..1156362.48 rows=29552100 width=25)
                     (actual time=2.694..42852.767 rows=29577564 loops=1)
                     Filter: (l_shipdate <= '1998-09-02 00:00:00'::oradate)
                     Rows Removed by Filter: 422231
                     ->  Seq Scan on lineitem
                           (cost=0.00..1156362.48 rows=29552100 width=25)
                           (actual time=0.517..35257.735 rows=29999795 loops=1)
Total runtime: 73466.921 ms

关键变化：

• 出现 Vector Sort、Vector Sonic Hash Aggregate、Vector Adapter 等向量化算子
• Vector Adapter(type: BATCH MODE) 表明数据按批处理，而非逐行
• 聚合计算从行引擎的 HashAggregate 切换为 Vector Sonic Hash Aggregate

效果对比：

四、总结

最佳实践组合：

• 数据量大的表先分区，从架构上减少扫描范围；
• 对无法改代码的慢 SQL，用 SQL Patch 应急绑定 Hint；
• 对复杂分析查询，开启向量化引擎，释放批处理性能红利。