SQLite中文网
解释查询计划
▼ 目录

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) 中发生了其他细微变化。在后续版本中可能会进行进一步更改。

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

查询计划表示为树。在sqlite3_step()返回的原始形式中，树的每个节点都包含四个字段：一个整数节点 ID、一个整数父 ID、一个当前未使用的辅助整数字段以及节点的描述。因此，整个树是一个具有四列和零行或更多行的表。命令行外壳通常会拦截此表并将其呈现为 ASCII-art 图形，以便更方便地查看。要禁用 shell 自动图形呈现并以表格格式显示 EXPLAIN QUERY PLAN 输出，请运行命令“.explain off”以将“EXPLAIN formatting mode”设置为关闭。要恢复自动图形渲染，请运行“.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 的输出包括一条记录，其“详细信息”列中的值以“SCAN”或“SEARCH”开头。“SCAN”用于全表扫描，包括 SQLite 按照索引定义的顺序遍历表中所有记录的情况。“SEARCH”表示只访问了表行的一个子集。每条 SCAN 或 SEARCH 记录都包含以下信息：

  • 从中读取数据的表、视图或子查询的名称。
  • 是否使用索引或自动索引。
  • 覆盖索引优化 是否适用。
  • WHERE 子句的哪些术语用于索引。

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

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 输出，其中“详细信息”字段设置为“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 的每一行。然而，第二个子查询是“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

如果展平优化用于 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-tree结构来实现两个子查询结果的UNION。计算复合的另一种方法是将每个子查询作为协同例程运行，安排它们的输出按排序顺序出现，然后将结果合并在一起。当查询规划器选择后一种方法时，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