前言
數據庫的性能優化行業里面普遍偏少，今天這篇希望給大家帶來點幫助
SQLite是個典型的嵌入式DBMS，它有很多優點，它是輕量級的，在編譯之后很小，其中一個原因就是在查詢優化方面比較簡單
我們在使用SQLite進行數據存儲查詢的時候，要進行查詢優化，這里就會用到索引，C端的數據量大部分情況下面雖然不是很大，但良好的索引建立習慣往往會帶來不錯的查詢性能提升，同時在未知的將來經得住更大數據的考驗，那如何優化數據庫查詢呢，下面我們用例子一一演示下。
先建個測試表table1,包含了三個索引：
sqlite> .schemCREATE TABLE table1(id integer primary key not null default 0,a integer,b integer, c integer);CREATE INDEX a_i on table1 (a);CREATE INDEX a_i2 on table1 (a,b);CREATE INDEX a_i3 on table1 (c);
在常見的數據庫系統里面，進行SQL查詢檢驗都是用explain關鍵字，比如：
sqlite> explain select * from table1;addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- -------------0 Init 0 10 0 00 Start at 10 1 OpenRead 0 2 0 4 00 root=2 iDb=0; table12 Rewind 0 9 0 00 3 Rowid 0 1 0 00 r[1]=rowid 4 Column 0 1 2 00 r[2]=table1.a5 Column 0 2 3 00 r[3]=table1.b6 Column 0 3 4 00 r[4]=table1.c7 ResultRow 1 4 0 00 output=r[1..4]8 Next 0 3 0 01 9 Halt 0 0 0 00 10 Transaction 0 0 4 0 01 usesStmtJournal=011 Goto 0 1 0 00
立馬就會得到輸出，這些輸出表示SQLite執行這條SQL用到的每句指令，這個其實不怎么直觀，我們用到更多的是EXPLAIN QUERY PLAN，如下：
sqlite> explain QUERY PLAN select * from table1;0|0|0|SCAN TABLE table1
這條SQL語句是查詢了整張表，所以結果關鍵字SCAN表示要完整遍歷，這種效率是最低的，接下來我們試試加個查詢條件：
sqlite> explain QUERY PLAN select * from table1 where a=1;0|0|0|SEARCH TABLE table1 USING INDEX a_i2 (a=?)
加上where a=1之后關鍵字變成了SEARCH，表示不再需要遍歷了，而是使用了索引進行了部分檢索，另外這條輸出還有更多信息，比如使用了索引a_i2，而括號里面的a=?則表示是這個查詢條件引起的
我們稍微修改下SQL：
sqlite> explain QUERY PLAN select a from table1 where a=1;0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i (a=?)
把select 變成了select a，發現explain輸出有細微變化，從INDEX變成了COVERING INDEX，CONVERING INDEX表示直接使用索引查詢就可以得到結果，不需要再次回查數據表，這樣效率更高。而之前的查詢因為是使用，索引里面只有a記錄，所以必須要查詢原始記錄才能得到b，c字段。我們再試下這條SQL：
sqlite> explain QUERY PLAN select a,b from table1 where a=1 and b=1;0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i2 (a=? AND b=?)
同意因為索引a_i2已經包含a和b了，所以也是使用CONVERING INDEX。那有同學可能會問了，那我們建索引的時候都把其他字段都加進去唄，雖然查詢用不到，但不用二次查詢原始記錄效率高。理論上這樣是可行的，但這里有個重要問題就是數據冗余太嚴重了，導致索引和原始數據一樣大，在海量數據存儲的數據庫里面磁盤消耗是個問題，所以如何選擇可能要做個平衡。
接下來我們把and換成or：
sqlite> explain QUERY PLAN select a,b from table1 where a=1 or b=1;0|0|0|SCAN TABLE table1 USING COVERING INDEX a_i2
發現又變回SCAN了，但仍然使用到了索引a_i2，對比下這條SQL：
sqlite> explain QUERY PLAN select a,b from table1 where a=1;0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i2 (a=?)
多了個查詢條件b=1之后效率變差了，這是為什么呢？這里要引出我們創建索引使用的最關鍵的原則：前綴索引。
索引一般是使用B樹，前綴索引簡單來講，就是要想能使用這個索引，查詢條件必須滿足索引建立涉及到的字段，并且和查詢使用的順序一致。
我們回頭看剛才那個or的例子，對于查詢條件a=1，他能使用a_i2(a,b)這個索引，因為索引順序也是a開頭的。但or的例子里面還或上一個查詢條件b=1，對于這個查詢就沒有索引可以用了，因為沒有b開頭的索引存在。a_i2(a,b)這個索引里面雖然有b，但b對于b=1這個查詢條件來說不是在前面，不滿足前綴索引原則。
而對于剛才那個and的例子，則能夠完全使用索引，因為存在索引a_i2(a,b)，可以想象成先按索引a過濾數據，剩下數據再用索引b過濾數據。對于and條件來說，索引里面字段的順序換一下也是沒有關系的，數據庫會自動優化選擇，比如：
sqlite> .schemCREATE INDEX a_i22 on table2 (b,a);sqlite> explain QUERY PLAN select a,b from table2 where a=1 and b=1;0|0|0|SEARCH TABLE table2 USING COVERING INDEX a_i22 (b=? AND a=?)
如果or查詢也要充分使用索引，聰明的讀者一定想到了，那就是要建2個索引，如下：
CREATE TABLE table3(id integer primary key not null default 0,a integer,b integer, c integer);CREATE INDEX a_i222 on table3(a);CREATE INDEX a_i2222 on table3(b);sqlite> explain QUERY PLAN select a,b from table3 where a=1 or b=1;0|0|0|SEARCH TABLE table3 USING INDEX a_i222 (a=?)0|0|0|SEARCH TABLE table3 USING INDEX a_i2222 (b=?)
我們再來看一個進階的，加上一個排序：
CREATE TABLE table1(id integer primary key not null default 0,a integer,b integer, c integer);CREATE INDEX a_i2 on table1 (a,b);sqlite> explain QUERY PLAN select a,b from table1 where a=1 order by b;0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i2 (a=?)CREATE TABLE table3(id integer primary key not null default 0,a integer,b integer, c integer);CREATE INDEX a_i222 on table3(a);CREATE INDEX a_i2222 on table3(b);sqlite> explain QUERY PLAN select a,b from table3 where a=1 order by b;0|0|0|SEARCH TABLE table3 USING INDEX a_i222 (a=?)0|0|0|USE TEMP B-TREE FOR ORDER BY
對比這2個查詢，發現下面這個多了個USE TEMP B-TREE FOR ORDER BY。對于第一個查詢來說，我們可以看到排序也是同樣滿足前綴索引原則（先按索引a過濾數據，剩下數據用索引b排序）。對于第二個查詢來說，因為不滿足這個原則導致多了個臨時表來做排序。看到這里大家應該理解前綴索引的意思了。
我們再看這個樣子，把查詢條件和排序換下：
sqlite> explain QUERY PLAN select a,b from table1 where b=1 order by a;0|0|0|SCAN TABLE table1 USING COVERING INDEX a_i2
顯然不滿足前綴索引原則了，因為需要先按索引b過濾數據，但b不是第一個。
常規的查詢語句大部分是and，or，order的組合使用，只需要掌握上面說的原則，一定能寫出高性能的數據庫查詢語句來。
而對于更高級的一些連表可以繼續翻閱官方文檔：
https://www.sqlite.org/eqp.html
https://www.sqlite.org/lang_e...
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對VEVB武林網的支持。
新聞熱點
疑難解答
