拨开慢SQL迷雾｜连载01：一文吃透执行计划基础，开启SQL调优之路

1.1 导语

在数据库性能调优中，读懂执行计划是第一步。磐维数据库（PanWeiDB）兼容 Oracle/MySQL 语法，同时继承了 PostgreSQL 的执行计划体系。本文将系统介绍 EXPLAIN 家族命令的用法与差异，并重点剖析 Seq Scan（全表扫描）、Index Scan（索引扫描）、Index Only Scan（仅索引扫描） 三种数据访问方式的适用场景，通过真实案例展示何时该用、何时不该用。

1.2 EXPLAIN 家族命令详解

磐维数据库提供了一系列 EXPLAIN 命令，用于查看 SQL 的执行计划。它们的共同点是只生成计划、不修改数据（ANALYZE 选项除外），但输出的详细程度和是否真实执行有所不同。

1.2.1 EXPLAIN – 只看计划，不执行语句

最基础的命令，仅输出优化器估算的执行计划，不会真正执行 SQL。

EXPLAIN
SELECT *
FROM scott.dept
WHERE EXISTS (SELECT NULL FROM scott.emp WHERE dept.deptno = emp.deptno);

输出示例：

QUERY PLAN
------------------------------------------------------------
Nested Loop Semi Join  (cost=0.00..3054.19 rows=300 width=102)
   Join Filter: (dept.deptno = emp.deptno)
   ->  Seq Scan on dept  (cost=0.00..16.19 rows=619 width=102)
   ->  Materialize  (cost=0.00..14.50 rows=300 width=32)
         ->  Seq Scan on emp  (cost=0.00..13.00 rows=300 width=32)
(5 rows)

关键字段说明：

• cost=0.00..3054.19：启动成本…总成本（成本单位非时间，用于优化器比较）
• rows=300：优化器估算该节点返回的行数
• width=102：估算每行的平均字节数

1.2.2 EXPLAIN VERBOSE – 显示更详细的计划信息

在 EXPLAIN 基础上增加 VERBOSE 关键字，会额外输出每个节点的输出列名（Output）和表的全限定名。

EXPLAIN VERBOSE
SELECT *
FROM scott.dept
WHERE EXISTS (SELECT NULL FROM scott.emp WHERE dept.deptno = emp.deptno);

输出差异：

QUERY PLAN
--------------------------------------------------------------------
Nested Loop Semi Join  (cost=0.00..3054.19 rows=300 width=102)
   Output: dept.deptno, dept.dname, dept.loc
   Join Filter: (dept.deptno = emp.deptno)
   ->  Seq Scan on scott.dept
         Output: dept.deptno, dept.dname, dept.loc
   ->  Materialize
         Output: emp.deptno
         ->  Seq Scan on scott.emp
               Output: emp.deptno
(9 rows)

适用场景：需要确认优化器是否正确识别了列投影，或排查列解析异常时使用。

1.2.3 EXPLAIN ANALYZE – 真正执行并显示实际耗时

这是最常用的调优命令。它不仅显示计划，还会真正执行 SQL，并在计划中追加 actual time 和实际返回行数。

EXPLAIN ANALYZE
SELECT *
FROM scott.dept
WHERE EXISTS (SELECT NULL FROM scott.emp WHERE dept.deptno = emp.deptno);

输出示例：

QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop Semi Join  (cost=0.00..3054.19 rows=300 width=102)
                       (actual time=0.033..0.041 rows=3 loops=1)
   Join Filter: (dept.deptno = emp.deptno)
   Rows Removed by Join Filter: 21
   ->  Seq Scan on dept
         (actual time=0.008..0.008 rows=4 loops=1)
   ->  Materialize
         (actual time=0.009..0.014 rows=24 loops=4)
         ->  Seq Scan on emp
               (actual time=0.005..0.008 rows=14 loops=1)
Total runtime: 0.091 ms
(7 rows)

关键字段说明：

• actual time=0.033..0.041：首行返回时间…所有行返回时间（单位：毫秒）
• rows=3：该节点实际返回的行数
• loops=1：该节点被执行的次数
• Rows Removed by Filter：被过滤条件排除的行数（判断索引需求的重要指标）
• Total runtime：整个语句的实际执行时间

注意：EXPLAIN ANALYZE 会真实执行 DML 语句（INSERT/UPDATE/DELETE）。如果只想看计划而不想修改数据，可以将其包裹在事务中执行后回滚。

1.2.4 EXPLAIN PERFORMANCE – 深入分析 Buffer 与 CPU

PERFORMANCE 是磐维/openGauss 的扩展语法，在 ANALYZE 基础上增加了 Buffer 访问统计和 CPU 周期统计，是排查 IO 瓶颈的利器。

EXPLAIN PERFORMANCE
SELECT *
FROM scott.dept
WHERE EXISTS (SELECT NULL FROM scott.emp WHERE dept.deptno = emp.deptno);

关键输出片段：

->  Seq Scan on scott.dept
      (actual time=0.521..0.521 rows=4 loops=1)
      (Buffers: shared read=1)                        -- 物理读 1 次
      (CPU: ex c/r=14562900918967, ex row=4 ...)

Buffer 指标解读：

• Buffers: shared hit=1：缓存命中，逻辑读，从内存 Buffer 中获取
• Buffers: shared read=1：缓存未命中，物理读，从磁盘读取

使用场景：当 SQL 逻辑上应该很快，但实际很慢时，通过 shared read 的数量判断是否是 IO 瓶颈。

1.2.5 使用 PREPARE 查看带参数语句的 Plan

业务中大量 SQL 使用参数（$1, $2），直接 EXPLAIN 无法分析。此时需要先 PREPARE，再 EXPLAIN ANALYZE EXECUTE。

-- 1. 定义预处理语句
PREPARE ps(numeric, varchar) AS
SELECT * FROM scott.emp WHERE empno = $1 AND ename = $2;

-- 2. 查看带参数的实际执行计划
EXPLAIN ANALYZE EXECUTE ps(7788, 'SCOTT');

输出示例：

QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using pk_emp on emp  (cost=0.00..8.27 rows=1 width=146)
                                 (actual time=2.256..2.257 rows=1 loops=1)
   Index Cond: (empno = $1)
   Filter: ((ename)::text = ($2)::text)
Total runtime: 2.301 ms
(4 rows)

1.3 三种扫描方式详解与适用场景

数据库访问表数据主要有三种方式。理解它们各自的适用场景，是避免"该走索引却全表扫描"或"不该走索引却硬走索引"的关键。

1.3.1 案例一：Seq Scan (全表扫描) – 小表或大数据量场景

适用场景：表数据量很小，或者查询需要返回表中绝大部分数据时，全表扫描往往比索引更高效。

示例：在 Oracle 示例库 scott.emp 表中查询 deptno = 10 的员工。

EXPLAIN ANALYZE SELECT * FROM emp WHERE deptno = 10;

执行计划：

                       QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on emp  (cost=0.00..13.75 rows=2 width=242)
                 (actual time=0.019..0.021 rows=3 loops=1)
   Filter: (deptno = 10::numeric)
+   Rows Removed by Filter: 11
Total runtime: 0.065 ms
(4 rows)

分析：

• emp 表仅有 14 行数据，全表扫描只需读取极少的页面。
• 虽然 Rows Removed by Filter: 11 看起来过滤了很多行，但因为表本身极小，建立索引后走索引再回表的开销反而可能比顺序扫描更高。
• 结论：对于小表，优化器选择 Seq Scan 是正确且高效的。此时不必强求索引。

反面教材：如果在大表上发生本不该发生的 Seq Scan，性能就会灾难性下降。例如 TPCH 的 customer 表（75 万行），若强制全表扫描查单条记录，耗时会从 0.043 ms 恶化到 359 ms（见案例二）。

1.3.2 案例二：Index Scan (索引扫描) – 精准定位少量数据

适用场景：在大表中，通过高选择性的条件返回少量数据。索引能快速定位到数据所在位置，避免扫描全表。

示例：TPCH customer 表（75 万行）按主键查询单条记录。

优化前（强制全表扫描，模拟无索引场景）：

EXPLAIN ANALYZE SELECT /*+ tablescan(customer) */ * 
FROM customer WHERE c_custkey = 701;

执行计划：

                       QUERY PLAN
--------------------------------------------------------------------------------
+ Seq Scan on customer  (cost=0.00..31928.00 rows=1 width=159)
                      (actual time=0.009..359.863 rows=1 loops=1)
   Filter: (c_custkey = 701)
   Rows Removed by Filter: 749999
Total runtime: 359.903 ms
(4 rows)

优化后（正常索引扫描）：

EXPLAIN ANALYZE SELECT * FROM customer WHERE c_custkey = 701;

执行计划：

                   QUERY PLAN
--------------------------------------------------------------------------------
+ Index Scan using customer_pkey on customer
  (cost=0.00..8.27 rows=1 width=159)
  (actual time=0.011..0.012 rows=1 loops=1)
   Index Cond: (c_custkey = 701)
Total runtime: 0.043 ms
(3 rows)

分析：

• 大表（75 万行）中仅返回 1 行，全表扫描需要遍历几乎所有行（Rows Removed by Filter: 749999）。
• 通过主键索引 customer_pkey，优化器直接定位到目标行。
• 性能提升约 8000 倍。
• 结论：大表返回少量数据时，索引扫描是最佳选择。若执行计划显示 Seq Scan 且 Rows Removed by Filter 极大，通常意味着缺少必要索引。

1.3.3 案例三：Index Only Scan (仅索引扫描) – 覆盖索引不回表

适用场景：查询的所有列（包括 SELECT 和 WHERE 中的条件列）都包含在索引中，数据库无需回表读取堆数据，直接从索引即可返回结果。

示例：TPCH lineitem 表通过索引查询 l_orderkey。

EXPLAIN ANALYZE SELECT l_orderkey FROM lineitem WHERE l_orderkey = 1;

执行计划：

                    QUERY PLAN
--------------------------------------------------------------------------------
+ Index Only Scan using lineitem_n2 on lineitem
  (cost=0.00..4.34 rows=5 width=4)
  (actual time=5.840..5.844 rows=6 loops=1)
   Index Cond: (l_orderkey = 1)
   Heap Fetches: 0
Total runtime: 5.882 ms
(4 rows)

分析：

• Heap Fetches: 0 是关键指标，表示完全没有回表操作。
• 因为查询列 l_orderkey 和条件列 l_orderkey 都在 lineitem_n2 索引中，数据库只需读取索引页即可返回结果。
• 相比 Index Scan 还需要根据索引指针回表取其他列，Index Only Scan 减少了随机 IO。
• 结论：设计索引时，若某些查询高频且只涉及少量列，可建立覆盖索引（包含 SELECT 和 WHERE 的所有列），让查询走到 Index Only Scan。

1.4 小结

• 小表全表扫描是合理选择，大表务必通过索引减少扫描范围。
• 设计索引时考虑覆盖索引，让高频查询走到 Index Only Scan。