數(shù)據(jù)庫(kù)的性能優(yōu)化行業(yè)里面普遍偏少逻谦，今天這篇希望給大家?guī)?lái)點(diǎn)幫助
我們?cè)谑褂肧QLite進(jìn)行數(shù)據(jù)存儲(chǔ)查詢的時(shí)候掌实，要進(jìn)行查詢優(yōu)化，這里就會(huì)用到索引邦马，C端的數(shù)據(jù)量大部分情況下面雖然不是很大贱鼻，但良好的索引建立習(xí)慣往往會(huì)帶來(lái)不錯(cuò)的查詢性能提升宴卖，同時(shí)在未知的將來(lái)經(jīng)得住更大數(shù)據(jù)的考驗(yàn)，那如何優(yōu)化數(shù)據(jù)庫(kù)查詢呢邻悬，下面我們用例子一一演示下症昏。
y'h
先建個(gè)測(cè)試表table1,包含了三個(gè)索引：
sqlite> .schem
CREATE 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);
在常見(jiàn)的數(shù)據(jù)庫(kù)系統(tǒng)里面，進(jìn)行SQL查詢檢驗(yàn)都是用explain關(guān)鍵字父丰，比如：
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; table1
2? ? Rewind? ? ? ? 0? ? 9? ? 0? ? ? ? ? ? ? ? ? ? 00
3? ? ? Rowid? ? ? ? ? 0? ? 1? ? 0? ? ? ? ? ? ? ? ? ? 00? r[1]=rowid
4? ? ? Column? ? ? ? 0? ? 1? ? 2? ? ? ? ? ? ? ? ? ? 00? r[2]=table1.a
5? ? ? Column? ? ? ? 0? ? 2? ? 3? ? ? ? ? ? ? ? ? ? 00? r[3]=table1.b
6? ? ? Column? ? ? ? 0? ? 3? ? 4? ? ? ? ? ? ? ? ? ? 00? r[4]=table1.c
7? ? ? 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=0
11? ? Goto? ? ? ? ? 0? ? 1? ? 0? ? ? ? ? ? ? ? ? ? 00
立馬就會(huì)得到輸出肝谭，這些輸出表示SQLite執(zhí)行這條SQL用到的每句指令，這個(gè)其實(shí)不怎么直觀蛾扇，我們用到更多的是EXPLAIN QUERY PLAN攘烛，如下：
sqlite> explain QUERY PLAN select * from table1;
0|0|0|SCAN TABLE table1
這條SQL語(yǔ)句是查詢了整張表，所以結(jié)果關(guān)鍵字SCAN表示要完整遍歷镀首，這種效率是最低的坟漱，接下來(lái)我們?cè)囋嚰觽€(gè)查詢條件：
sqlite> explain QUERY PLAN select * from table1 where a=1;
0|0|0|SEARCH TABLE table1 USING INDEX a_i2 (a=?)
加上where a=1之后關(guān)鍵字變成了SEARCH，表示不再需要遍歷了更哄，而是使用了索引進(jìn)行了部分檢索芋齿，另外這條輸出還有更多信息，比如使用了索引a_i2成翩，而括號(hào)里面的a=?則表示是這個(gè)查詢條件引起的
我們稍微修改下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沟突，發(fā)現(xiàn)explain輸出有細(xì)微變化，從INDEX變成了COVERING INDEX捕传，CONVERING?
INDEX表示直接使用索引查詢就可以得到結(jié)果惠拭，不需要再次回查數(shù)據(jù)表，這樣效率更高庸论。而之前的查詢因?yàn)槭鞘褂?职辅，索引里面只有a記錄，所以必須要查詢?cè)加涗洸拍艿玫絙聂示，c字段域携。我們?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=?)
同意因?yàn)樗饕齛_i2已經(jīng)包含a和b了，所以也是使用CONVERING
INDEX鱼喉。那有同學(xué)可能會(huì)問(wèn)了秀鞭，那我們建索引的時(shí)候都把其他字段都加進(jìn)去唄，雖然查詢用不到扛禽，但不用二次查詢?cè)加涗浶矢叻姹摺＠碚撋线@樣是可行的，但這里有個(gè)重要問(wèn)題就是數(shù)據(jù)冗余太嚴(yán)重了编曼，導(dǎo)致索引和原始數(shù)據(jù)一樣大豆巨，在海量數(shù)據(jù)存儲(chǔ)的數(shù)據(jù)庫(kù)里面磁盤消耗是個(gè)問(wèn)題，所以如何選擇可能要做個(gè)平衡掐场。
接下來(lái)我們把a(bǔ)nd換成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
發(fā)現(xiàn)又變回SCAN了往扔，但仍然使用到了索引a_i2贩猎，對(duì)比下這條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=?)
多了個(gè)查詢條件b=1之后效率變差了，這是為什么呢萍膛？這里要引出我們創(chuàng)建索引使用的最關(guān)鍵的原則：前綴索引吭服。
索引一般是使用B樹，前綴索引簡(jiǎn)單來(lái)講蝗罗，就是要想能使用這個(gè)索引艇棕，查詢條件必須滿足索引建立涉及到的字段，并且和查詢使用的順序一致绿饵。
我們回頭看剛才那個(gè)or的例子欠肾，對(duì)于查詢條件a=1，他能使用a_i2(a,b)這個(gè)索引拟赊，因?yàn)樗饕樞蛞彩莂開頭的刺桃。但or的例子里面還或上一個(gè)查詢條件b=1，對(duì)于這個(gè)查詢就沒(méi)有索引可以用了吸祟，因?yàn)闆](méi)有b開頭的索引存在瑟慈。a_i2(a,b)這個(gè)索引里面雖然有b，但b對(duì)于b=1這個(gè)查詢條件來(lái)說(shuō)不是在前面屋匕，不滿足前綴索引原則葛碧。
而對(duì)于剛才那個(gè)and的例子，則能夠完全使用索引过吻，因?yàn)榇嬖谒饕齛_i2(a,b)进泼，可以想象成先按索引a過(guò)濾數(shù)據(jù)，剩下數(shù)據(jù)再用索引b過(guò)濾數(shù)據(jù)纤虽。對(duì)于and條件來(lái)說(shuō)乳绕，索引里面字段的順序換一下也是沒(méi)有關(guān)系的，數(shù)據(jù)庫(kù)會(huì)自動(dòng)優(yōu)化選擇逼纸，比如：
sqlite> .schem
CREATE 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個(gè)索引杰刽，如下：
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=?)
我們?cè)賮?lái)看一個(gè)進(jìn)階的菠发，加上一個(gè)排序：
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
對(duì)比這2個(gè)查詢，發(fā)現(xiàn)下面這個(gè)多了個(gè)USE
? TEMP B-TREE FOR ORDER?
BY贺嫂。對(duì)于第一個(gè)查詢來(lái)說(shuō)滓鸠，我們可以看到排序也是同樣滿足前綴索引原則（先按索引a過(guò)濾數(shù)據(jù)，剩下數(shù)據(jù)用索引b排序）涝婉。對(duì)于第二個(gè)查詢來(lái)說(shuō)哥力，因?yàn)椴粷M足這個(gè)原則導(dǎo)致多了個(gè)臨時(shí)表來(lái)做排序《胀洌看到這里大家應(yīng)該理解前綴索引的意思了吩跋。
我們?cè)倏催@個(gè)樣子，把查詢條件和排序換下：
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
顯然不滿足前綴索引原則了渔工，因?yàn)樾枰劝此饕齜過(guò)濾數(shù)據(jù)锌钮，但b不是第一個(gè)。
常規(guī)的查詢語(yǔ)句大部分是and引矩，or梁丘，order的組合使用，只需要掌握上面說(shuō)的原則旺韭，一定能寫出高性能的數(shù)據(jù)庫(kù)查詢語(yǔ)句來(lái)氛谜。
而對(duì)于更高級(jí)的一些連表可以繼續(xù)翻閱官方文檔：
https://www.sqlite.org/eqp.html
https://www.sqlite.org/lang_explain.html
更多文章請(qǐng)關(guān)注公眾號(hào)：安卓之美
