SQLite查詢最佳化效能要點

標籤：

Sqlite是輕量級的，在編譯之後很小，其中一個原因就是在查詢最佳化方面比較簡單，它只是運用索引機制來進行最佳化的，經過對SQLite的查詢最佳化的分析以及對原始碼的研究，我將SQLite的查詢優總結如下：

一、影響查詢效能的因素：

1． 對錶中行的檢索數目，越小越好

2． 排序與否。

3． 是否要對一個索引。

4． 查詢語句的形式

二、幾個查詢最佳化的轉換

1． 對於單個表的單個列而言，如果都有形如T.C=expr這樣的子句，並且都是用OR操作符串連起來，形如： x = expr1 OR expr2 = x OR x = expr3 此時由於對於OR，在SQLite中不能利用索引來最佳化，所以可以將它轉換成帶有IN操作符的子句：x IN(expr1,expr2,expr3)這樣就可以用索引進行最佳化，效果很明顯，但是如果在都沒有索引的情況下OR語句執行效率會稍優於IN語句的效率。

2． 如果一個子句的操作符是BETWEEN，在SQLite中同樣不能用索引進行最佳化，所以也要進行相應的等價轉換： 如：a BETWEEN b AND c可以轉換成：(a BETWEEN b AND c) AND (a>=b) AND (a<=c)。 在上面這個子句中， (a>=b) AND (a<=c)將被設為dynamic且是(a BETWEEN b AND c)的子句，那麼如果BETWEEN語句已經編碼，那麼子句就忽略不計，如果存在可利用的index使得子句已經滿足條件，那麼父句則被忽略。

3． 如果一個單元的操作符是LIKE，那麼將做下面的轉換：x LIKE ‘abc%’，轉換成：x>=‘abc’ AND x<‘abd’。因為在SQLite中的LIKE是不能用索引進行最佳化的，所以如果存在索引的話，則轉換後和不轉換相差很遠，因為對LIKE不起作用，但如果不存在索引，那麼LIKE在效率方面也還是比不上轉換後的效率的。

三、 幾種查詢語句的處理（複合查詢）
1．查詢語句為：<SelectA> <operator> <selectB> ORDER BY <orderbylist> ORDER BY 
     執行方法： is one of UNION ALL, UNION, EXCEPT, or INTERSECT. 這個語句的執行過程是先將selectA和selectB執行並且排序，再對兩個結果掃描處理，對上面四種操作是不同的，將執行過程分成七個子過程：

   outA: 將selectA的結果的一行放到最終結果集中

   outB: 將selectA的結果的一行放到最終結果集中(只有UNION操作和UNION ALL操作，其它操作都不放入最終結果集中)

    AltB: 當selectA的目前記錄小於selectB的目前記錄

    AeqB: 當selectA的目前記錄等於selectB的目前記錄

    AgtB: 當selectA的目前記錄大於selectB的目前記錄

    EofA: 當selectA的結果遍曆完

    EofB: 當selectB的結果遍曆完

         下面就是四種操作的執行過程：

 

執行順序	UNION ALL	UNION	EXCEPT	INTERSECT
AltB:	outA, nextA	outA, nextA	outA,nextA	nextA
AeqB:	outA, nextA	nextA	nextA	outA, nextA
AgtB:	outB, nextB	outB, nextB	nextB	nextB
EofA:	outB, nextB	outB, nextB	halt	halt
EofB:	outA, nextA	outA, nextA	outA,nextA	halt

     2． 如果可能的話，可以把一個用到GROUP BY查詢的語句轉換成DISTINCT語句來查詢，因為GROUP BY有時候可能會用到index，而對於DISTINCT都不會用到索引的 。

四、子查詢扁平化 

     例子：SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

     對這個SQL語句的執行一般預設的方法就是先執行內查詢，把結果放到一個暫存資料表中，再對這個表進行外部查詢，這就要對資料處理兩次，另外這個暫存資料表沒有索引，所以對外部查詢就不能進行最佳化了，如果對上面的SQL進行處理後可以得到如下SQL語句：SELECT x+y AS a FROM t1 WHERE z<100 AND a>5，這個結果顯然和上面的一樣，但此時只需要對

資料進行查詢一次就夠了，另外如果在表t1上有索引的話就避免了遍曆整個表。

運用flatten方法最佳化SQL的條件：

1.子查詢和外查詢沒有都用集合函式

2.子查詢沒有用集合函式或者外查詢不是個表的串連

3.子查詢不是一個左外串連的右運算元

4.子查詢沒有用DISTINCT或者外查詢不是個表的串連

5.子查詢沒有用DISTINCT或者外查詢沒有用集合函式

6.子查詢沒有用集合函式或者外查詢沒有用關鍵字DISTINCT

7.子查詢有一個FROM語句

8.子查詢沒有用LIMIT或者外查詢不是表的串連

9.子查詢沒有用LIMIT或者外查詢沒有用集合函式

10.子查詢沒有用集合函式或者外查詢沒用LIMIT

11.子查詢和外查詢不是同時是ORDER BY子句

12.子查詢和外查詢沒有都用LIMIT

13.子查詢沒有用OFFSET

14.外查詢不是一個複合查詢的一部分或者子查詢沒有同時用關鍵字ORDER BY和LIMIT

15.外查詢沒有用集合函式子查詢不包含ORDER BY

16.複合子查詢的扁平化：子查詢不是一個複合查詢，或者他是一個UNION ALL複合查詢，但他是都由若干個非集合函式的查詢構成，他的父查詢不是一個複合查詢的子查詢，也沒有用集合函式或者是DISTINCT查詢，並且在FROM語句中沒有其它的表或者子查詢，父查詢和子查詢可能會包含WHERE語句，這些都會受到上面11、12、13條件的限制。

例：   SELECT a+1 FROM (

              SELECT x FROM tab

              UNION ALL

              SELECT y FROM tab

               UNION ALL

               SELECT abs(z*2) FROM tab2

           ) WHERE a!=5 ORDER BY 1

轉換為：

        SELECT x+1 FROM tab WHERE x+1!=5

           UNION ALL

           SELECT y+1 FROM tab WHERE y+1!=5

           UNION ALL

           SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5

           ORDER BY 1

17.如果子查詢是一個複合查詢，那麼父查詢的所有的ORDER BY語句必須是對子查詢的列的簡單引用

18.子查詢沒有用LIMIT或者外查詢不具有WHERE語句

子查詢扁平化是由專門一個函數實現的，函數為：

static int flattenSubquery(

 Parse *pParse,       /* Parsing context */

 Select *p,           /* The parent or outer SELECT statement */

 int iFrom,           /* Index in p->pSrc->a[] of the inner subquery */

 int isAgg,           /* True if outer SELECT uses aggregate functions */

 int subqueryIsAgg    /* True if the subquery uses aggregate functions */

)

       它是在Select.c檔案中實現的。顯然對於一個比較複雜的查詢，如果滿足上面的條件時對這個查詢語句進行扁平化處理後就可以實現對查詢的最佳化。如果正好存在索引的話效果會更好！

 

五、串連查詢

       在返回查詢結果之前，相關表的每行必須都已經串連起來，在SQLite中，這是用嵌套迴圈實現的，在早期版本中，最左邊的是最外層迴圈，最右邊的是最內層迴圈，串連兩個或者更多的表時，如果有索引則放到內層迴圈中，也就是放到FROM最後面，因為對於前面選中的每行，找後面與之對應的行時，如果有索引則會很快，如果沒有則要遍曆整個表，這樣效率就很低，但在新版本中，這個最佳化已經實現。

       最佳化的方法如下：

       對要查詢的每個表，統計這個表上的索引資訊，首先將代價賦值為SQLITE_BIG_DBL（一個系統已經定義的常量）：

1)    如果沒有索引，則找有沒有在這個表上對rowid的查詢條件：

1．如果有Rowid=EXPR，如果有的話則返回對這個表代價估計，代價計為零，查詢得到的記錄數為1，並完成對這個表的代價估計，

2．如果沒有Rowid=EXPR 但有rowid IN (...)，而IN是一個列表，那麼記錄返回記錄數為IN列表中元素的個數，估計代價為NlogN,

3．如果IN不是一個列表而是一個子查詢結果，那麼由於具體這個子查詢不能確定，所以只能估計一個值，返回記錄數為100，代價為200。

4．如果對rowid是範圍的查詢，那麼就估計所有合格記錄是總記錄的三分之一，總記錄估計為1000000，並且估計代價也為記錄數。

5．如果這個查詢還要求排序，則再另外加上排序的代價NlogN

6．如果此時得到的代價小於總代價，那麼就更新總代價，否則不更新。

2)    如果WHERE子句中存在OR操作符，那麼要把這些OR串連的所有子句分開再進行分析。

1． 如果有子句是由AND串連符構成，那麼再把由AND串連的子句再分別分析。

2． 如果串連的子句的形式是X<op><expr>，那麼就再分析這個子句。

3． 接下來就是把整個對OR操作的總代價計算出來。

4． 如果這個查詢要求排序，則再在上面總代價上再乘上排序代價NlogN

5． 如果此時得到的代價小於總代價，那麼就更新總代價，否則不更新。

3)    如果有索引，則統計每個表的索引資訊，對於每個索引：

1． 先找到這個索引對應的列號，再找到對應的能用到（操作符必須為=或者是IN（…））這個索引的WHERE子句，如果沒有找到，則退出對每個索引的迴圈，如果找到，則判斷這個子句的操作符是什麼，如果是=，那麼沒有附加的代價，如果是IN（sub-select），那麼估計它附加代價inMultiplier為25，如果是IN（list），那麼附加代價就是N（N為list的列數）。

2． 再計算總的代價和總的查詢結果記錄數和代價。

3． nRow = pProbe->aiRowEst[i] * inMultiplier;/*計算行數*/

4． cost = nRow * estLog(inMultiplier);/*統計代價*/

5． 如果找不到操作符為=或者是IN（…）的子句，而是範圍的查詢，那麼同樣只好估計查詢結果記錄數為nRow/3，估計代價為cost/3。

6． 同樣，如果此查詢要求排序的話，再在上面的總代價上加上NlogN

7． 如果此時得到的代價小於總代價，那麼就更新總代價，否則不更新。

4)    通過上面的最佳化過程，可以得到對一個表查詢的總代價（就是上面各個代價的總和），再對第二個表進行同樣的操作，這樣如此直到把FROM子句中所有的表都計算出各自的代價，最後取最小的，這將作為嵌套迴圈的最內層，依次可以得到整個嵌套迴圈的嵌套順序，此時正是最優的，達到了最佳化的目的。

5)    所以迴圈的嵌套順序不一定是與FROM子句中的順序一致，因為在執行過程中會用索引最佳化來重新排列順序。

六、索引

   在SQLite中，有以下幾種索引：

1)    單列索引

2)    多列索引

3)    唯一性索引

4)    對於聲明為：INTEGER PRIMARY KEY的主鍵來說，這列會按預設排序，所以雖然在資料字典中沒有對它產生索引，但它的功能就像個索引。所以如果在這個主鍵上在單獨建立索引的話，這樣既浪費空間也沒有任何好處。

運用索引的注意事項：

1)    對於一個很小的表來說沒必要建立索引

2)    在一個表上如果經常做的是插入更新操作，那麼就要節制使用索引

3)    也不要在一個表上建立太多的索引，如果建立太多的話那麼在查詢的時候SQLite可能不會選擇最好的來執行查詢，一個解決辦法就是建立聚蔟索引

索引的運用時機：

1)    操作符：=、>、<、IN等

2)    操作符BETWEEN、LIKE、OR不能用索引，

       如BETWEEN：SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;

       這時就應該將其轉換成：

       SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;

       此時如果在myfield上有索引的話就可以用了，大大提高速度

 

       再如LIKE：SELECT * FROM mytable WHERE myfield LIKE ‘sql%‘;

       此時應該將它轉換成：

       SELECT * FROM mytable WHERE myfield >= ‘sql‘ AND myfield < ‘sqm‘;

       此時如果在myfield上有索引的話就可以用了，大大提高速度

       再如OR：SELECT * FROM mytable WHERE myfield = ‘abc‘ OR myfield = ‘xyz‘;

       此時應該將它轉換成：

       SELECT * FROM mytable WHERE myfield IN (‘abc‘, ‘xyz‘);

       此時如果在myfield上有索引的話就可以用了，大大提高速度

3)    有些時候索引都是不能用的，這時就應該遍曆全表（程式示範）

       SELECT * FROM mytable WHERE myfield % 2 = 1;

       SELECT * FROM mytable WHERE substr(myfield, 0, 1) = ‘w‘;

       SELECT * FROM mytable WHERE length(myfield) < 5;

SQLite查詢最佳化效能要點