小巧、快速、可靠。
選擇其中三項。
說明查詢計畫 (EXPLAIN QUERY PLAN)
► 目錄

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) 中也發生了一些細微的變化。後續版本中可能會有更多變化。

SQL 指令 EXPLAIN QUERY PLAN 用於取得 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 樹結構來實現兩個子選擇結果的 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

本頁面最後修改時間：世界協調時間 2022-01-08 05:02:57