EXPLAIN QUERY PLAN

1. EXPLAIN QUERY PLAN命令

1.1.表和索引扫描

1.2.临时排序B树

1.3.子查询

1.4.复合查询

2.示例代码

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

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

• 关于查询优化器的说明。

• 索引如何工作。

• 下一代查询计划器。

EXPLAIN QUERY PLAN命令每个返回零或多行四列。列名是“selectid”，“order”，“from”，“detail”。前三列包含一个整数值。最后一列“详细信息”包含一个文本值，其中包含大部分有用的信息。

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

1.1.表和索引扫描

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

对于查询读取的每个表，EXPLAIN QUERY PLAN的输出包括一个记录，“详细信息”列中的值以“SCAN”或“SEARCH”开头。“扫描”用于全表扫描，包括SQLite按照索引定义的顺序遍历表中所有记录的情况。“SEARCH”表示只有一部分表格行被访问。每个SCAN或SEARCH记录都包含以下信息：

• 表格数据的名称是从中读取的。

• 是否使用索引或自动索引。

• 覆盖指数优化是否适用。

• WHERE子句中的哪些条款用于建立索引。

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

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
0|0|0|SCAN TABLE t1

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

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
0|0|0|SEARCH TABLE t1 USING INDEX i1

前面的例子中，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; 
0|0|0|SEARCH TABLE 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;
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
0|1|1|SCAN TABLE t2

输出的第二列（“order”列）指示嵌套顺序。在这种情况下，使用索引i2扫描表t1是外部循环（顺序= 0），而表t2（order = 1）的全部表扫描是内部循环。第三列（from“”）表示SELECT语句的FROM子句中与每次扫描相关的表的位置。在上面的例子中，表t1占据了FROM子句中的第一个位置，所以值列的“from”在第一条记录中为0。表t2位于第二个位置，因此相应SCAN记录的“from”列设置为1.在以下示例中，SELECT的FROM子句中的t1和t2的位置相反。查询策略保持不变，但“from”

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

如果查询的WHERE子句包含OR表达式，那么SQLite可能会使用“OR by union”策略（这里也有描述）。在这种情况下，将有两个SEARCH记录，每个索引一个，在“order”和“from”列中都有相同的值。例如：

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
0|0|0|SEARCH TABLE 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; 
0|0|0|SCAN TABLE t2
0|0|0|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; 
0|0|0|SCAN TABLE t2 USING INDEX i4

1.3.子查询

在上面的所有示例中，第一列（列“selectid”）始终设置为0.如果查询包含子选择（作为FROM子句的一部分或作为SQL表达式的一部分），则EXPLAIN QUERY PLAN还包括每个子选择的报告。每个子选择被分配一个不同的，非零的“选择”值。顶级SELECT语句始终分配有选项ID值0.例如：

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

上面的示例包含一对分配了精选值1和2的标量子查询。除了SCAN记录外，还有2个与顶级子查询（selectid 0）关联的“EXECUTE”记录，表示子查询1和2被执行通过标量上下文中的顶级查询。与标量子查询2相关联的EXECUTE记录中的CORRELATED限定符指示查询必须针对顶级查询访问的每一行单独运行。它在与子查询1相关的记录中缺失意味着子查询仅运行一次并且结果被缓存。换句话说，子查询2可能更关键，因为它可能运行很多次，而子查询1只运行一次。

除非应用展平优化，否则，如果子查询出现在SELECT语句的FROM子句中，SQLite将执行子查询并将结果存储在临时表中。然后它使用临时表的内容代替子查询来执行父查询。这在EXPLAIN QUERY PLAN的输出中显示，通过将“SCAN SUBQUERY”记录替换为FROM子句中通常出现的每个元素的“SCAN TABLE”记录。例如：

sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR GROUP BY

如果在SELECT语句的FROM子句中的子查询上使用了展平优化，则EXPLAIN QUERY PLAN的输出反映了这一点。例如，即使在顶层SELECT的FROM子句中存在子查询，下面也没有“SCAN SUBQUERY”记录。相反，由于flattening优化在这种情况下适用，EXPLAIN QUERY PLAN报告显示顶级查询是使用表t1和t2的嵌套循环连接实现的。

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

1.4.复合查询

复合查询的每个组件查询（UNION，UNION ALL，EXCEPT或INTERSECT）都被分配了自己的选择标识并单独报告。输出用于标识操作的父级（复合查询）的单个记录，以及是否使用临时B树来实现它。例如：

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
1|0|0|SCAN TABLE t1
2|0|0|SCAN TABLE t2
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

上述输出中的“USING TEMP B-TREE”子句表示临时b树结构用于实现两个子选择结果的UNION。如果不需要临时B树，如下例所示，该子句不存在。

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
2|0|0|SCAN TABLE t2
2|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)

有时，在大型应用程序中，修改代码以生成EXPLAIN QUERY PLAN命令可能不便于调查的SELECT查询。从交互式调试会话中，可能几乎不可能。在这些情况下，类似于以下的函数可能会有用。此特定函数将SQLite语句句柄作为参数传递，并将相应的EXPLAIN QUERY PLAN报告输出到标准输出。应用程序特定版本可能会将报告输出到应用程序日志或类似文件

/*
** Argument pStmt is a prepared SQL statement. This function compiles
** an EXPLAIN QUERY PLAN command to report on the prepared statement,
** and prints the report to stdout using printf().
*/
int printExplainQueryPlan(sqlite3_stmt *pStmt){
  const char *zSql;               /* Input SQL */
  char *zExplain;                 /* SQL with EXPLAIN QUERY PLAN prepended */
  sqlite3_stmt *pExplain;         /* Compiled EXPLAIN QUERY PLAN command */
  int rc;                         /* Return code from sqlite3_prepare_v2() */

  zSql = sqlite3_sql(pStmt);
  if( zSql==0 ) return SQLITE_ERROR;

  zExplain = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zSql);
  if( zExplain==0 ) return SQLITE_NOMEM;

  rc = sqlite3_prepare_v2(sqlite3_db_handle(pStmt), zExplain, -1, &pExplain, 0);
  sqlite3_free(zExplain);
  if( rc!=SQLITE_OK ) return rc;

  while( SQLITE_ROW==sqlite3_step(pExplain) ){
    int iSelectid = sqlite3_column_int(pExplain, 0);
    int iOrder = sqlite3_column_int(pExplain, 1);
    int iFrom = sqlite3_column_int(pExplain, 2);
    const char *zDetail = (const char *)sqlite3_column_text(pExplain, 3);

    printf("%d %d %d %s\n", iSelectid, iOrder, iFrom, zDetail);
  }

  return sqlite3_finalize(pExplain);
}

 SQLite在公共领域。