小巧。快速。可靠。
三选二。
查询计划解释
► 目录

1. EXPLAIN QUERY PLAN 命令

警告：EXPLAIN QUERY PLAN 命令返回的数据仅用于交互式调试。输出格式可能会在 SQLite 版本之间发生变化。应用程序不应依赖 EXPLAIN QUERY PLAN 命令的输出格式。

警报：如上所述，EXPLAIN QUERY PLAN 的输出格式在 3.24.0 版本（2018-06-04）中发生了重大变化。3.36.0 版本（2021-06-18）中也进行了一些较小的更改。后续版本可能会进行更多更改。

The EXPLAIN QUERY PLAN SQL 命令用于获取 SQLite 用于实现特定 SQL 查询的策略或计划的高级描述。最重要的是，EXPLAIN QUERY PLAN 报告了查询使用数据库索引的方式。本文档是了解和解释 EXPLAIN QUERY PLAN 输出的指南。背景信息可在单独的地方找到

查询计划表示为一棵树。以原始形式，如 sqlite3_step() 返回的那样，树的每个节点包含四个字段：一个整数节点 ID、一个整数父 ID、一个当前未使用的辅助整数字段以及节点的描述。因此，整棵树是一个包含四列和零行或多行的表。命令行 Shell 通常会拦截此表并将其呈现为 ASCII 艺术图，以便于查看。要禁用 Shell 的自动图形渲染并以表格格式显示 EXPLAIN QUERY PLAN 输出，请运行命令“.explain off”以将“EXPLAIN 格式化模式”设置为关闭。要恢复自动图形渲染，请运行“.explain auto”。您可以使用“.show”命令查看当前的“EXPLAIN 格式化模式”设置。

还可以使用“.eqp on”命令将 CLI 设置为自动 EXPLAIN QUERY PLAN 模式

sqlite> .eqp on

在自动 EXPLAIN QUERY PLAN 模式下，Shell 会自动为您输入的每个语句运行一个单独的 EXPLAIN QUERY PLAN 查询，并在实际运行查询之前显示结果。使用“.eqp off”命令关闭自动 EXPLAIN QUERY PLAN 模式。

EXPLAIN QUERY PLAN 对 SELECT 语句最有用，但也可以与其他从数据库表读取数据的语句一起出现（例如 UPDATE、DELETE、INSERT INTO ... SELECT）。

1.1. 表和索引扫描

在处理 SELECT（或其他）语句时，SQLite 可以通过多种方式从数据库表中检索数据。它可以扫描表中的所有记录（全表扫描），根据 rowid 索引扫描表中记录的连续子集，扫描数据库 索引 中条目的连续子集，或在一个扫描中使用上述策略的组合。SQLite 从表或索引中检索数据的各种方式在 此处 详细描述。

对于查询读取的每个表，EXPLAIN QUERY PLAN 的输出都包含一条记录，其中“detail”列中的值以“SCAN”或“SEARCH”开头。“SCAN”用于全表扫描，包括 SQLite 按索引定义的顺序迭代表中的所有记录的情况。“SEARCH”表示仅访问表的子集行。每个 SCAN 或 SEARCH 记录包含以下信息

例如，以下 EXPLAIN QUERY PLAN 命令对 SELECT 语句进行操作，该语句通过对表 t1 执行全表扫描来实现

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

上面的示例显示 SQLite 选择全表扫描将访问表中的所有行。如果查询能够使用索引，则 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 的位置相反。查询策略保持不变。EXPLAIN QUERY PLAN 的输出显示查询的实际评估方式，而不是其在 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 树，则会向 EXPLAIN QUERY PLAN 输出添加一条记录，其“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 的子节点。例如

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”子查询。子查询在某种意义上是 SCALAR，因为它们返回单个值 - 一个单行、单列的表。如果实际查询返回更多内容，则仅使用第一行的第一列。

上面的第一个子查询相对于外部查询是常量。可以计算一次第一个子查询的值，然后在外部 SELECT 的每一行中重复使用。但是，第二个子查询是“相关”的。第二个子查询的值会根据外部查询当前行中的值而变化。因此，必须对外部 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

如果在 SELECT 语句的 FROM 子句中对子查询使用了 扁平化优化，则会有效地将子查询合并到外部查询中。EXPLAIN QUERY PLAN 的输出反映了这一点，如以下示例所示

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 树结构来实现两个子选择的组合结果。计算组合的另一种方法是将每个子查询作为协程运行，安排它们的输出按排序顺序出现，并将结果合并在一起。当查询计划器选择这种后一种方法时，EXPLAIN QUERY PLAN 的输出如下所示

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

此页面上次修改于 2022-01-08 05:02:57 UTC