Sqlite 简明教程

SQLite - EXPLAIN

SQLite 语句可以在之前加上“EXPLAIN”关键字或用于描述表格详细信息的“EXPLAIN QUERY PLAN”短语。

SQLite statement can be preceded by the keyword "EXPLAIN" or by the phrase "EXPLAIN QUERY PLAN" used for describing the details of a table.

这两种修改都会使 SQLite 语句表现为查询，并返回有关在省略 EXPLAIN 关键字或短语的情况下、SQLite 语句如何操作的信息。

Either modification causes the SQLite statement to behave as a query and to return information about how the SQLite statement would have operated if the EXPLAIN keyword or phrase had been omitted.

  1. The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only.

  2. The details of the output format are subject to change from one release of SQLite to the next.

  3. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented.

Syntax

EXPLAIN 的语法如下−

syntax for EXPLAIN is as follows −

EXPLAIN [SQLite Query]

EXPLAIN QUERY PLAN 的语法如下−

syntax for EXPLAIN QUERY PLAN is as follows −

EXPLAIN  QUERY PLAN [SQLite Query]

Example

考虑下面记录的 COMPANY 表格 −

Consider COMPANY table with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

现在，让我们使用 SELECT 语句检查以下子查询−

Now, let us check the following sub-query with SELECT statement −

sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;

这将产生以下结果。

This will produce the following result.

addr        opcode      p1          p2          p3
----------  ----------  ----------  ----------  ----------
0           Goto        0           19
1           Integer     0           0
2           OpenRead    0           8
3           SetNumColu  0           5
4           Rewind      0           17
5           Column      0           4
6           RealAffini  0           0
7           Integer     20000       0
8           Lt          357         16          collseq(BI
9           Rowid       0           0
10          Column      0           1
11          Column      0           2
12          Column      0           3
13          Column      0           4
14          RealAffini  0           0
15          Callback    5           0
16          Next        0           5
17          Close       0           0
18          Halt        0           0
19          Transactio  0           0
20          VerifyCook  0           38
21          Goto        0           1
22          Noop        0           0

现在，让我们使用 SELECT 语句检查以下 *Explain Query Plan *−

Now, let us check the following *Explain Query Plan * with SELECT statement −

SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000;

order       from        detail
----------  ----------  -------------
0           0           TABLE COMPANY