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ì)問了，那我們建索引的時(shí)候都把其他字段都加進(jìn)去唄，雖然查詢用不到，但不用二次查詢原始記錄效率高。理論上這樣是可行的，但這里有個(gè)重要問題就是數(shù)據(jù)冗余太嚴(yán)重了，導(dǎo)致索引和原始數(shù)據(jù)一樣大，在海量數(shù)據(jù)存儲(chǔ)的數(shù)據(jù)庫里面磁盤消耗是個(gè)問題，所以如何選擇可能要做個(gè)平衡。

接下來我們把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，對比下這條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樹，前綴索引簡單來講，就是要想能使用這個(gè)索引，查詢條件必須滿足索引建立涉及到的字段，并且和查詢使用的順序一致。

我們回頭看剛才那個(gè)or的例子，對于查詢條件a=1，他能使用a_i2(a,b)這個(gè)索引，因?yàn)樗饕樞蛞彩莂開頭的。但or的例子里面還或上一個(gè)查詢條件b=1，對于這個(gè)查詢就沒有索引可以用了，因?yàn)闆]有b開頭的索引存在。a_i2(a,b)這個(gè)索引里面雖然有b，但b對于b=1這個(gè)查詢條件來說不是在前面，不滿足前綴索引原則。

而對于剛才那個(gè)and的例子，則能夠完全使用索引，因?yàn)榇嬖谒饕齛_i2(a,b)，可以想象成先按索引a過濾數(shù)據(jù)，剩下數(shù)據(jù)再用索引b過濾數(shù)據(jù)。對于and條件來說，索引里面字段的順序換一下也是沒有關(guān)系的，數(shù)據(jù)庫會(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=?)

我們再來看一個(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

對比這2個(gè)查詢，發(fā)現(xiàn)下面這個(gè)多了個(gè)USE TEMP B-TREE FOR ORDER BY。對于第一個(gè)查詢來說，我們可以看到排序也是同樣滿足前綴索引原則（先按索引a過濾數(shù)據(jù)，剩下數(shù)據(jù)用索引b排序）。對于第二個(gè)查詢來說，因?yàn)椴粷M足這個(gè)原則導(dǎo)致多了個(gè)臨時(shí)表來做排序。看到這里大家應(yīng)該理解前綴索引的意思了。

我們再看這個(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)樾枰劝此饕齜過濾數(shù)據(jù)，但b不是第一個(gè)。

常規(guī)的查詢語句大部分是and，or，order的組合使用，只需要掌握上面說的原則，一定能寫出高性能的數(shù)據(jù)庫查詢語句來。

而對于更高級(jí)的一些連表可以繼續(xù)翻閱官方文檔：

https://www.sqlite.org/eqp.html

https://www.sqlite.org/lang_e...