SQLite是個典型的嵌入式DBMS，它有很多優點，它是輕量級的，在編譯之後很小，其中一個原因就是在查詢優化方面比較簡單，它隻是運用索引機制來進行優化的，經過對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的查詢優化代碼中的具體實作，現在來看一下它的幾個執行個體：
1 #include "stdio.h"
2 #include "sqlite3.h"
3 #include <windows.h>
4 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql);
5
6 int main(int argc, char **argv)
7 {
8 sqlite3 *db;
9 char *zErr;
10 int rc;
11 char *sql;
12 sqlite3_stmt *stmt=0;
13 rc = sqlite3_open("memory.db", &db);
14 if(rc) {
15 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
16 sqlite3_close(db);
17 }
18
19 //下面是所建的各個表的結構
20 sql="CREATE TABLE t1 (num int,word TEXT NOT NULL)";
21 //sql="CREATE TABLE t4 (num INTEGER NOT NULL,word TEXT NOT NULL)";
22 //sql="CREATE TABLE t3 (num INTEGER NOT NULL,word TEXT NOT NULL)";
23 rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
24 if(rc != SQLITE_OK) {
25 if (zErr != NULL) {
26 fprintf(stderr, "SQL error: %s\n", zErr);
27 sqlite3_free(zErr);
28 }
29 }
30
31 //下面是對是以插入進行手動送出，這樣可以加快插入速度
32 //sqlite3_exec(db,"BEGIN",NULL,NULL,&zErr);
33 //插入1000000條記錄
34 //for (int i=0;i<1000000;i++)
35 //{
36 // sql = sqlite3_mprintf("insert into t1 values(%d,'%s')",i,"goodc");
37 // rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
38 //}
39 //sqlite3_exec(db,"COMMIT",NULL,NULL,&zErr);
40
41 sql="create index t1nwindex on t1(num)";
42 rc=sqlite3_exec(db, sql, NULL, NULL, &zErr);
43 if(rc != SQLITE_OK) {
44 if (zErr != NULL) {
45 fprintf(stderr, "SQL error: %s\n", zErr);
46 sqlite3_free(zErr);
47 }
48 }
49
50 //sql="drop index t1nwindex";
51 //sql="drop index t3index";
52 //sql="delete from t2";
53 rc=sqlite3_exec(db, sql, NULL, NULL, &zErr);
54 if(rc != SQLITE_OK) {
55 if (zErr != NULL) {
56 fprintf(stderr, "SQL error: %s\n", zErr);
57 sqlite3_free(zErr);
58 }
59 }
60
61 printf("查詢結果是：\n");
62 //sql="select * from t1 where num=3000 or num=2000";//有INTEGER PRIMARY KEY，快
63 //sql="select * from t2 where num=3000 or num=2000";//沒有索引，慢
64 //sql="select * from t3 where num=3000 or num=2000";//有索引，快
65
66 //這裡交換位置了，但是結果用的時間想差比較大的原因是，t1是用索引存儲的，但是它不是由create index
67 //而建立的，是以系統還不會把它作為索引處理，是以這兩個表就隻是無索引的表，在内部優化計算代價隻是對它
68 //進行估計，因為源代碼中沒有捕獲到下面的查詢條件，是以都是系統最大值（源代碼中有），是以就嵌套順序沒
69 //變，是以出現下面的差異。
70 //sql="SELECT count(*) FROM t3, t1 WHERE t1.num = t3.num";//比下面的快，由于内層少
71 //sql="SELECT count(*) FROM t1, t3 WHERE t1.num = t3.num";//比上面的慢，由于内層多
72
73 //下面這個已經内部實作優化，是以所用時間是相同的
74 //sql="SELECT * FROM t2, t3 WHERE t2.num = t3.num";//有索引，稍快
75 //sql="SELECT * FROM t3, t2 WHERE t2.num = t3.num";//同上，内部已經優化
76
77 //sql="select * from t3 where num=8000";//有索引，快
78 //sql="select * from t1 where num%2=0";//有索引,但不能用，很慢
79 //sql="select * from t1 where num=8000";//沒有索引，慢
80
81 //BETWEEN的轉換優化---内部已經實作優化,如果有索引的話快一點
82 //sql="select count(*) from t2 where word between 'goodl' and 'goodm'";//BETWEEN
83 //sql="select count(*) from t2 where word >='goodl' and word<'goodm'";//BETWEEN的轉換
84
85 //LIKE的轉換優化---内部已經實作優化
86 //sql="select count(*) from t2 where word like 'goodl%'";//有索引不起作用
87 //sql="select count(*) from t2 where word >='goodl' and word <'goodm'";//如果有索引會更快
88
89 //IN的轉換優化---内部沒有實作優化,但此時如果可以用索引的話就會很好
90 //如果不用索引則在這裡展現不出IN比OR優，而如果有索引則差别很明顯
91 //sql="select count(*) from t2 where word in('goodllll','goodkkkk','goodaaaa')";
92 //sql="select count(*) from t2 where word ='goodllll' or word ='goodkkkk' or word='goodaaaa'";
93
94 int start=GetTickCount();
95 query(db,stmt,sql);
96 printf("the time has pass:%dms\n",GetTickCount()-start);
97 sqlite3_close(db);
98 return 0;
99 }
100
101 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql){
102 int rc,ncols,i;
103 const char *tail;
104 rc = sqlite3_prepare(db, sql, -1, &stmt, &tail);
105 if(rc != SQLITE_OK) {
106 fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
107 }
108 rc = sqlite3_step(stmt);
109 ncols = sqlite3_column_count(stmt);
110 while(rc == SQLITE_ROW) {
111 for(i=0; i < ncols; i++) {
112 fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
113 }
114 fprintf(stderr, "\n");
115 rc = sqlite3_step(stmt);
116 }
117 }