SQLite 學習筆記之五 - Explain Query Plan

※ 本文主要是 SQLite 官方網頁「EXPLAIN QUERY PLAN」的翻譯整理，希望能對大家有幫助！

我們可在 SQL 前加上「EXPLAIN QUERY PLAN」，以瞭解 SQLite 內部的實際運作流程與如何使用索引。除了 SELECT，也適用於：UPDATE、DELETE、INSERT INTO ... SELECT 等指令。更可用此指令來調校 SQL 與 DB schema，並進而改善效能。

一、輸出結果

此指令輸出的結果共有 4 個欄位：selectid、order、 from、detail ，其中以「detail」欄位提供的資訊最為有用。

欄位	說明
selectid	通常是 0（代表是最頂層的 SELECT）。只有當查詢中包含了「子查詢」（subquery，sub-select）時，此欄位才會出現非 0 的數值，代表這是第幾個子查詢。
order	因為 SQLite 的 join 動作都是使用「巢狀迴圈掃瞄」（nested scans），所以這個欄位代表此掃瞄動作是在第幾層迴圈，由 0 開始，0 代表最外層迴圈、1 代表第二層迴圈…依此類推。
from	代表此掃瞄/搜尋動作是發生在 FROM 子句中的第幾個 table，由 0 開始，0 代表第一個 table，1 則代表第二個 table…依此類推。
detail	開頭通常是「SCAN」或「SEARCH」。「SCAN」代表是 full-table scan，也包含了依據某 index 定義之順序對 table 內所有資料進行遍訪；「SEARCH」代表只有對 table 內的某子集合（subset）進行遍訪。而每一個「SCAN」或「SEARCH」還包含了以下資訊： 讀取資料的 table 名稱。 是否使用 index（或 automatic index）？ 是否使用「涵蓋式索引」（covering index）來最佳化？ WHERE 中的哪一個子句被用於 index？ SQLite 可能拜訪的資料列數（估計值）。

二、表格與索引掃瞄

1. 例一、表示 SQLite 會對 t1 進行全表掃瞄，並估計可能會巡訪 100,000 列的資料。若您覺得這個估計值不準，可以再執行「ANALYZE」指令以更新表格與索引的統計資訊：
   
   sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
   0|0|0|SCAN TABLE t1 (~100000 rows)
2. 例二、如果 SQLite 發現有索引可用，則 SCAN / SEARCH 就會顯示此計畫所使用的索引名稱。若是「SEARCH」的話，還會顯示 SQLite 是依據哪些限制條件來過濾的。例如下面這個例子就表示 SQLite 是利用索引「i1」來最佳化 WHERE 中的「a=?」子句，而且 SQLite 預估約有 10 列會符合「a=1」子句：
   
   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 (a=?) (~10 rows)
3. 例三、這個例子則表示 SQLite 利用了「涵蓋式索引」（covering index ）：
   
   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=?) (~10 rows)
4. 例四、這個例子說明第二個欄位（order）與第三個欄位（from）之意義。第一列 SEARCH TABLE 的「order=0」表示這是外層迴圈的掃瞄，第二列 SCAN TABLE 的「order=1」表示這是內層迴圈的掃瞄。至於第一列的「from=0」表示 t1 是在 FROM 子句中的第一個表格，而第二列的「from=1」表示 t2 是在 FROM 子句中的第二個表格。
   
   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>?) (~3 rows)
   0|1|1|SCAN TABLE t2 (~1000000 rows)
5. 例五、若對調上例 SQL「FROM」子句中的表格順序，則可發現 SQLite 仍使用相同的查詢策略，唯 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>?) (~3 rows)
   0|1|0|SCAN TABLE t2 (~1000000 rows) 
6. 例六、若 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=?) (~10 rows)
   0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)

三、暫時用來排序的 B-Tree

1. 如果在 SELECT 查詢中包含了 ORDER BY、GROUP BY 或 DISTINCT 子句，則 SQLite 可能會使用「暫時性的 B-Tree 結構」來對輸出資料列進行排序。或者利用索引，而使用索引會比使用 B-Tree 來得有效率。
2. 如果 SQLite 決定採用 B-Tree 排序的話，則在「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 (~1000000 rows)
   0|0|0|USE TEMP B-TREE FOR ORDER BY
3. 若希望 SQLite 避免採用「暫時性的 B-Tree 結構」來排序的話，可參考下例在要排序的欄位上建立一個索引：
   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 (~1000000 rows) 

四、子查詢

1. 在前面的例子中，第一個欄位（selectid）都是 0。但如果查詢中包含了子查詢（subquery、sub-select），不管是在 FROM 子句或者是 SQL 的一部份，那麼該欄位就會出現非 0 值。而最頂層的 SELECT 的 selectid 總是 0。
2. 下例中，我們看到有兩個子查詢的 selectid 分別是「1」和「2」。另外，還有一列 SCAN 以及兩列 EXECUTE。這兩個 EXECUTE 代表這兩個子查詢都是由最頂層的查詢所執行的。
   
   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 (~1000000 rows)
   0|0|0|EXECUTE SCALAR SUBQUERY 1
   1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
   0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
   2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
3. 而 EXECUTE 紀錄中的「CORRELATED」代表第二個子查詢與最頂層的查詢是相關的，也就是說從頂層找出一個資料列以後會再執行一次第二個子查詢。至於第一個子查詢中沒有出現此修飾詞，則代表此查詢只會執行一次，而且執行結果會被 cache。換句話說，因為第二個子查詢會執行多次，所以是提升效能的關鍵。
4. 除非是 SQLite 對子查詢採用了「扁平優化」（flattening optimization），否則在 FROM 子句裡的子查詢，其查詢結果會被先儲存在暫存表格之內，後續 SQLite 會再用這個暫存表格裡的資料來取代子查詢並提供給上層的父查詢（parent query）使用。而在 EXPLAIN QUERY PLAN 的結果中，我們就可以看到「SCAN TABLE」會變成「SCAN SUBQUERY」：
   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 (~1000000 rows)
   0|0|0|SCAN SUBQUERY 1 (~1000000 rows)
   0|0|0|USE TEMP B-TREE FOR GROUP BY
5. 如果 SQLite 對子查詢採用了「扁平優化」，就看不到「SCAN SUBQUERY」出現。反之，我們會看到 SQLite 對表格 t1 與 t2 執行了「巢狀迴圈 join」（nested loop join）：
   sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
   0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)
   0|1|1|SCAN TABLE t1 (~1000000 rows) 

五、複合查詢

1.  如果是 UNION、UNION ALL 、EXCEPT 或 INTERSECT 這類的「複合式查詢」（compound queries），則 SQLite 會個別指派一個專屬的 selectid 給每一個子查詢、然後報告其查詢計畫。至於父查詢（複合式查詢）的報告，則會獨立顯示在另外一行，如下例中的「COMPOUND SUBQUERIES……」：
   sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
   1|0|0|SCAN TABLE t1 (~1000000 rows)
   2|0|0|SCAN TABLE t2 (~1000000 rows)
   0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
2. 上例中的「USING TEMP B-TREE」還說明了 SQLite 使用「暫存的 B-Tree結構」來進行兩個子查詢的結果集的 UNION 操作。
3. 如果 SQLite 沒有使用到「暫存的 B-Tree結構」，則此訊息就不會出現，如下所示：
   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 (~1000000 rows)
   2|0|0|SCAN TABLE t2 (~1000000 rows)
   2|0|0|USE TEMP B-TREE FOR ORDER BY
   0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)