您好,登錄后才能下訂單哦!
前言
數(shù)據(jù)庫的性能優(yōu)化行業(yè)里面普遍偏少,今天這篇希望給大家?guī)睃c(diǎn)幫助
SQLite是個(gè)典型的嵌入式DBMS,它有很多優(yōu)點(diǎn),它是輕量級(jí)的,在編譯之后很小,其中一個(gè)原因就是在查詢優(yōu)化方面比較簡(jiǎn)單
我們?cè)谑褂肧QLite進(jìn)行數(shù)據(jù)存儲(chǔ)查詢的時(shí)候,要進(jìn)行查詢優(yōu)化,這里就會(huì)用到索引,C端的數(shù)據(jù)量大部分情況下面雖然不是很大,但良好的索引建立習(xí)慣往往會(huì)帶來不錯(cuò)的查詢性能提升,同時(shí)在未知的將來經(jīng)得住更大數(shù)據(jù)的考驗(yàn),那如何優(yōu)化數(shù)據(jù)庫查詢呢,下面我們用例子一一演示下。
先建個(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);
在常見的數(shù)據(jù)庫系統(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語句是查詢了整張表,所以結(jié)果關(guān)鍵字SCAN表示要完整遍歷,這種效率是最低的,接下來我們?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ì)問了,那我們建索引的時(shí)候都把其他字段都加進(jìn)去唄,雖然查詢用不到,但不用二次查詢?cè)加涗浶矢摺@碚撋线@樣是可行的,但這里有個(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,對(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)單來講,就是要想能使用這個(gè)索引,查詢條件必須滿足索引建立涉及到的字段,并且和查詢使用的順序一致。
我們回頭看剛才那個(gè)or的例子,對(duì)于查詢條件a=1,他能使用a_i2(a,b)這個(gè)索引,因?yàn)樗饕樞蛞彩莂開頭的。但or的例子里面還或上一個(gè)查詢條件b=1,對(duì)于這個(gè)查詢就沒有索引可以用了,因?yàn)闆]有b開頭的索引存在。a_i2(a,b)這個(gè)索引里面雖然有b,但b對(duì)于b=1這個(gè)查詢條件來說不是在前面,不滿足前綴索引原則。
而對(duì)于剛才那個(gè)and的例子,則能夠完全使用索引,因?yàn)榇嬖谒饕齛_i2(a,b),可以想象成先按索引a過濾數(shù)據(jù),剩下數(shù)據(jù)再用索引b過濾數(shù)據(jù)。對(duì)于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=?)
我們?cè)賮砜匆粋€(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è)查詢來說,我們可以看到排序也是同樣滿足前綴索引原則(先按索引a過濾數(shù)據(jù),剩下數(shù)據(jù)用索引b排序)。對(duì)于第二個(gè)查詢來說,因?yàn)椴粷M足這個(gè)原則導(dǎo)致多了個(gè)臨時(shí)表來做排序。看到這里大家應(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)樾枰劝此饕齜過濾數(shù)據(jù),但b不是第一個(gè)。
常規(guī)的查詢語句大部分是and,or,order的組合使用,只需要掌握上面說的原則,一定能寫出高性能的數(shù)據(jù)庫查詢語句來。
而對(duì)于更高級(jí)的一些連表可以繼續(xù)翻閱官方文檔:
https://www.sqlite.org/eqp.html
https://www.sqlite.org/lang_e...
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問大家可以留言交流,謝謝大家對(duì)億速云的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。