说明查询计划

1. EXPLAIN QUERY PLAN命令

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

警告：如上所述，EXPLAIN QUERY PLAN输出格式确实在3.24.0版本（2018-06-04）中发生了重大变化。3.36.0版中还有其他较小的更改（待定）。在后续发行版中可能会进行进一步的更改。

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

SQLite的工作原理入门。
有关查询优化器的注意事项。
索引如何工作。
在下一代查询规划。

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

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

sqlite> .eqp在

在自动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命令对SELECT语句进行操作，该SELECT语句通过对表t1执行全表扫描来实现：

sqlite>解释查询计划选择a，b从t1那里a = 1;
查询计划
`-扫描t1

上面的示例显示SQLite选择全表扫描将访问表中的所有行。如果查询能够使用索引，则SCAN / SEARCH记录将包括索引的名称，而对于SEARCH记录，将指示如何标识所访问的行的子集。例如：

sqlite>在t1（a）上创建索引i1；
sqlite>解释查询计划选择a，b从t1那里a = 1;
查询计划
`-使用索引i1搜索t1（a =？）

在前面的示例中，SQLite使用索引“ i1”来优化形式（a =？）的WHERE子句项-在这种情况下为“ a = 1”。前面的示例不能使用coverage索引，但是下面的示例可以使用，并且事实反映在输出中：

sqlite>在t1（a，b）上创建索引i2;
sqlite>解释查询计划选择a，b从t1那里a = 1; 
查询计划
`-使用覆盖指数i2搜索t1（a =？）

SQLite中的所有联接都是使用嵌套扫描实现的。当使用EXPLAIN QUERY PLAN分析具有联接的SELECT查询时，将为每个嵌套循环输出一个SCAN或SEARCH记录。例如：

sqlite>解释查询计划选择t1。*，t2。*从t1，t2，其中t1.a = 1和t1.b> 2;
查询计划
|-使用索引i2搜索t1（a =？AND b>？）
`-扫描t2

条目的顺序表示嵌套顺序。在这种情况下，使用索引i2对表t1的扫描是外循环（因为它首先出现），而对表t2的全表扫描是内循环（因为它最后出现）。在下面的示例中，SELECT的FROM子句中的t1和t2的位置相反。查询策略保持不变。EXPLAIN QUERY PLAN的输出显示查询的实际评估方式，而不是SQL语句中的指定方式。

sqlite>解释查询计划选择t1。*，t2。*从t2，t1，其中t1.a = 1和t1.b> 2;
查询计划
|-使用索引i2搜索t1（a =？AND b>？）
`-扫描t2

如果查询的WHERE子句包含OR表达式，则SQLite可能会使用“按联合进行OR”策略（也称为 OR优化）。在这种情况下，将有一个用于搜索的顶级记录，其中有两个子记录，每个索引一个：

sqlite>在t1（b）上创建索引i3；
sqlite>解释查询计划选择*从t1处，其中a = 1或b = 2;
查询计划
`--MULTI-INDEX OR
   |-使用覆盖指数i2（a =？）搜索t1
   `-使用索引i3搜索t1（b =？）

1.2。临时排序B树

如果SELECT查询包含ORDER BY，GROUP BY或DISTINCT子句，则SQLite可能需要使用临时的b树结构对输出行进行排序。或者，它可能使用index。使用索引几乎总是比执行排序更有效率。如果需要临时b树，则将记录添加到EXPLAIN QUERY PLAN输出中，并将“详细信息”字段设置为形式为“ USE TEMP B-TREE FOR xxx”的字符串值，其中xxx是“ ORDER”之一BY”，“ GROUP BY”或“ DISTINCT”。例如：

sqlite>解释查询计划选择c，d从t2 ORDER BY c;
查询计划
|-扫描t2
`-使用TEMP B-树来排序

在这种情况下，可以通过在t2（c）上创建索引来避免使用临时b树，如下所示：

sqlite>在t2（c）上创建索引i4；
sqlite>解释查询计划选择c，d从t2 ORDER BY c; 
查询计划
`-使用INDEX i4扫描t2

1.3。子查询

在以上所有示例中，只有一个SELECT语句。如果查询包含子选择，则这些子选择将显示为外部SELECT的子选择。例如：

sqlite>解释查询计划选择（从t1的b中选择b，其中a = 0），（从t1的b中选择a，其中b = t2.c）从t2中进行选择；
|-使用覆盖索引i4扫描表t2
|-标量子查询
| `-使用覆盖指数i2搜索t1（a =？）
`-相关的标量子查询
   `-使用索引i3搜索t1（b =？）

上面的示例包含两个“ SCALAR”子查询。子查询在某种意义上是标量的，因为它们返回一个值-一个单行单列的表。如果实际查询返回的结果更多，则仅使用第一行的第一列。

上面的第一个子查询相对于外部查询是恒定的。可以计算一次第一个子查询的值，然后将其重新用于外部SELECT的每一行。但是，第二个子查询是“ CORRELATED”。第二个子查询的值根据外部查询的当前行中的值而变化。因此，第二个子查询必须为外部SELECT中的每个输出行运行一次。

除非应用扁平化优化，否则如果子查询出现在SELECT语句的FROM子句中，则SQLite可以运行子查询并将结果存储在临时表中，也可以将子查询作为协同例程运行。以下查询是后者的示例。子查询由协同例程运行。每当外部查询需要子查询的另一行输入时，外部查询就会阻塞。控制权切换到产生所需输出行的协同程序，然后控制权切换回到继续处理的主程序。

sqlite>解释查询计划选择计数（*）
      > FROM（选择max（b）AS x FROM t1 GROUP BY a）AS qqq
      > GROUP BY x;
查询计划
| --CO-例程qqq
| `-使用覆盖指数i2扫描t1
|-扫描qqqq
`-使用TEMP B树进行分组

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

sqlite>解释查询计划选择*从（选择*从t2，其中c = 1）as t3，t1;
查询计划
|-使用索引i4搜索t2（c =？）
`-扫描t1

如果子查询的内容可能需要访问不止一次，则不希望使用协例程，因为协例程将不得不多次计算数据。而且，如果子查询不能被展平，则意味着子查询必须表现在一个临时表中。

sqlite>选择*从
      >（SELECT * FROM t1 a = 1 ORDER BY b LIMIT 2）作为x，
      >（SELECT * FROM t2 c = 1 ORDER BY d LIMIT 2）as y;
查询计划
|-材料x
| `-使用覆盖指数i2搜索t1（a =？）
|-材料y
| |-使用索引i4搜索t2（c =？）
| `-使用TEMP B-树来排序
|-扫描x
`-扫描

1.4。复合查询

复合查询的每个组件查询（UNION，UNION ALL，EXCEPT或INTERSECT）均被分配为单独计算，并在EXPLAIN QUERY PLAN输出中赋予其自己的行。

sqlite>解释查询计划从t1选择一个UNION从t2选择SELECT；
查询计划
`-复合查询
   |-左最子子查询
   | `-使用覆盖指数i1扫描t1
   `-使用TEMP B-树的联盟
      `-使用覆盖指数i4扫描t2

上面输出中的“ USING TEMP B-TREE”子句指示使用临时b树结构来实现两个子选择的结果的UNION。计算复合值的另一种方法是将每个子查询作为一个协同例程运行，安排它们的输出以排序的顺序出现，然后将结果合并在一起。当查询计划者选择后一种方法时，EXPLAIN QUERY PLAN输出看起来像这样：

sqlite>解释查询计划，从t1选择a，但从t2选择d，ORDER BY 1；
查询计划
`-合并（除）
   |-左
   | `-使用覆盖指数i1扫描t1
   `-对
      |-扫描t2
      `-使用TEMP B-树来排序