2013年7月27日 星期六

SQLite 學習筆記之五 - Explain Query Plan

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

我們可在 SQL 前加上「EXPLAIN QUERY PLAN」,以瞭解 SQLite 內部的實際運作流程與如何使用索引。除了 SELECT,也適用於:UPDATEDELETEINSERT 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 BYGROUP BYDISTINCT 子句,則 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 值。而最頂層的 SELECTselectid 總是 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 對表格 t1t2 執行了「巢狀迴圈 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.  如果是 UNIONUNION ALLEXCEPTINTERSECT 這類的「複合式查詢」(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)

沒有留言:

張貼留言