SQLite EXPLAIN QUERY PLAN：读懂查询执行计划

1. EXPLAIN QUERY PLAN 命令

EXPLAIN QUERY PLAN SQL 命令用于获得 SQLite 执行特定 SQL 查询时采用的策略或计划的高层描述。最重要的是，它会报告查询使用数据库索引的方式。本文说明如何理解和解读它的输出。以下资料另行介绍背景知识：

• SQLite 工作原理入门。
• 查询优化器说明。
• 索引如何工作。
• 下一代查询规划器。

查询计划以一棵树表示。在 sqlite3_step() 返回的原始形式中，每个节点包含四个字段：整数节点 ID、整数父节点 ID、当前未使用的辅助整数字段，以及节点描述。因此，整棵树表现为一张四列、零行或多行的表。命令行 shell 通常会截取这张表，将它绘制成便于阅读的 ASCII 图。

要禁用 shell 的自动绘图，并以表格格式显示 EXPLAIN QUERY PLAN 输出，可运行 .explain off，将“EXPLAIN 格式化模式”关闭。运行 .explain auto 可恢复自动绘图，运行 .show 可查看当前的格式化模式。

也可以使用 .eqp on 命令，将 CLI 设置为自动 EXPLAIN QUERY PLAN 模式：

sqlite> .eqp on

在自动模式下，对输入的每条语句，shell 都会先单独执行一次 EXPLAIN QUERY PLAN 查询，显示结果后再真正运行该语句。使用 .eqp off 可关闭自动模式。

EXPLAIN QUERY PLAN 对 SELECT 语句最有用，但也可以用于其他从数据库表读取数据的语句，例如 UPDATE、DELETE 和 INSERT INTO … SELECT。

1.1. 表扫描和索引扫描

处理 SELECT 或其他语句时，SQLite 可以通过多种方式从数据库表获取数据：扫描表中全部记录（全表扫描），依据 rowid 索引扫描连续的一部分记录，扫描数据库索引中连续的一部分条目，或在一次扫描中组合这些策略。各种方式的详细说明见查询规划文档。

对于查询读取的每张表，EXPLAIN QUERY PLAN 输出都包含一条记录，其 detail 列以 SCAN 或 SEARCH 开头。SCAN 表示完整扫描，也包括按照索引定义的顺序遍历表中全部记录的情况；SEARCH 表示只访问部分表行。每条记录包含以下信息：

• 数据来源的表、视图或子查询名称。
• 是否使用索引或自动索引。
• 是否应用覆盖索引优化。
• WHERE 子句中的哪些条件用于索引查找。

例如，下面的 EXPLAIN QUERY PLAN 命令对应的 SELECT 通过全表扫描读取 t1：

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN t1

如果查询可以使用索引，SCAN/SEARCH 记录就会包含索引名称。若是 SEARCH 记录，还会说明如何确定要访问的行子集。例如：

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING INDEX i1 (a=?)

这个例子中，SQLite 使用索引 i1 优化形如 (a=?) 的 WHERE 条件，此处具体是 a=1。该例无法使用覆盖索引，但下面的例子可以，输出也反映了这一点：

sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX i2 (a=?)

SQLite 中所有连接都通过嵌套扫描实现。用 EXPLAIN QUERY PLAN 分析带连接的 SELECT 时，每层嵌套循环都会输出一条 SCAN 或 SEARCH 记录。例如：

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

条目的顺序表示循环嵌套顺序。这里，用索引 i2 扫描表 t1 的操作先出现，因此是外层循环；最后出现的 t2 全表扫描是内层循环。下面的例子交换了 SELECT 中 FROM 子句里的 t1、t2 顺序，查询策略仍然相同。输出展示的是查询实际如何执行，而不是 SQL 语句中如何书写：

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

如果 WHERE 子句包含 OR 表达式，SQLite 可能采用 “OR by union” 策略，也称 OR 优化。此时会有一条顶层搜索记录，下方有两条子记录，分别对应一个索引：

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
QUERY PLAN
`--MULTI-INDEX OR
   |--SEARCH t1 USING COVERING INDEX i2 (a=?)
   `--SEARCH t1 USING INDEX i3 (b=?)

1.2. 用于排序的临时 B 树

如果 SELECT 包含 ORDER BY、GROUP BY 或 DISTINCT 子句，SQLite 可能需要临时 B 树来对输出行排序，也可能使用索引。使用索引几乎总是比执行排序高效得多。

需要临时 B 树时，输出会增加一条记录，detail 字段形式为 USE TEMP B-TREE FOR xxx，其中 xxx 为 ORDER BY、GROUP BY 或 DISTINCT。例如：

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
|--SCAN t2
`--USE TEMP B-TREE FOR ORDER BY

本例可以通过在 t2(c) 上创建索引，避免使用临时 B 树：

sqlite> CREATE INDEX i4 ON t2(c);
sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
QUERY PLAN
`--SCAN t2 USING INDEX i4

1.3. 子查询

前面的例子都只有一条 SELECT。如果查询包含子 SELECT，它们会显示为外层 SELECT 的子节点。例如：

sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
|--SCAN TABLE t2 USING COVERING INDEX i4
|--SCALAR SUBQUERY
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH t1 USING INDEX i3 (b=?)

上例包含两个 SCALAR 子查询。“标量”的含义是返回单个值，即一行一列的表。如果实际结果超过这个范围，只会使用第一行的第一列。

第一个子查询相对于外层查询是常量，因此可以只计算一次，然后在外层 SELECT 的每一行中复用。第二个则是 CORRELATED（相关子查询）：它的值取决于外层查询当前行中的值，因此必须为外层 SELECT 的每个输出行执行一次。

如果子查询出现在 SELECT 的 FROM 子句中，除非应用了扁平化优化，SQLite 可以执行子查询并将结果存入临时表，也可以让子查询以协程运行。下面是后一种情况：外层查询每次需要子查询提供下一行输入时都会阻塞，控制权交给协程；协程生成所需输出行后，再将控制权交回主例程继续处理。

sqlite> EXPLAIN QUERY PLAN SELECT count(*)
      > FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq
      > GROUP BY x;
QUERY PLAN
|--CO-ROUTINE qqq
|  `--SCAN t1 USING COVERING INDEX i2
|--SCAN qqqq
`--USE TEMP B-TREE FOR GROUP BY

若对 FROM 子句中的子查询应用了扁平化优化，实际上就是将子查询合并到外层查询中。输出会反映这一点：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1) AS t3, t1;
QUERY PLAN
|--SEARCH t2 USING INDEX i4 (c=?)
`--SCAN t1

如果子查询的内容可能被访问多次，就不适合使用协程，否则协程必须重复计算这些数据。如果子查询又无法被扁平化，就必须将结果物化到一个临时表中：

sqlite> SELECT * FROM
      >   (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x,
      >   (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y;
QUERY PLAN
|--MATERIALIZE x
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
|--MATERIALIZE y
|  |--SEARCH t2 USING INDEX i4 (c=?)
|  `--USE TEMP B-TREE FOR ORDER BY
|--SCAN x
`--SCAN y

1.4. 复合查询

复合查询（UNION、UNION ALL、EXCEPT 或 INTERSECT）中的每个组成查询都会单独计算，并在 EXPLAIN QUERY PLAN 输出中占据各自的条目。

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  `--SCAN t1 USING COVERING INDEX i1
   `--UNION USING TEMP B-TREE
      `--SCAN t2 USING COVERING INDEX i4

上面的 USING TEMP B-TREE 表示使用临时 B 树，计算两个子 SELECT 结果的 UNION。另一种计算方式是让每个子查询作为协程运行，按排序后的顺序提供输出，再合并结果。查询规划器选择后一种方式时，输出如下：

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
QUERY PLAN
`--MERGE (EXCEPT)
   |--LEFT
   |  `--SCAN t1 USING COVERING INDEX i1
   `--RIGHT
      |--SCAN t2
      `--USE TEMP B-TREE FOR ORDER BY