Sqlite數據庫中索引的使用、索引的優(yōu)缺點

pptv網絡電視2017V4.0.2.0035 官方最新版

• 類型：網絡電視大�。�31.6M語言：中文 評分：9.2
• 標簽：

立即下載

要使用索引對數據庫的數據操作進行優(yōu)化，那必須明確幾個問題：
1.什么是索引
2.索引的原理
3.索引的優(yōu)缺點
4.什么時候需要使用索引，如何使用
圍繞這幾個問題，來探究索引在數據庫操作中所起到的作用。

1.數據庫索引簡介
回憶一下小時候查字典的步驟，索引和字典目錄的概念是一致的。字典目錄可以讓我們不用翻整本字典就找到我們需要的內容頁數，然后翻到那一頁就可以。索引也是一樣，索引是對記錄按照多個字段進行排序的一種展現。對表中的某個字段建立索引會創(chuàng)建另一種數據結構，其中保存著字段的值，每個值還包括指向與它相關記錄的指針。這樣，就不必要查詢整個數據庫，自然提升了查詢效率。同時，索引的數據結構是經過排序的，因而可以對其執(zhí)行二分查找，那就更快了。

2. B-樹與索引
大多數的數據庫都是以B-樹或者B+樹作為存儲結構的，B樹索引也是最常見的索引。先簡單介紹下B-樹，可以增強對索引的理解。
B-樹是為磁盤設計的一種多叉平衡樹，B樹的真正最準確的定義為：一棵含有t（t>=2）個關鍵字的平衡多路查找樹。一棵M階的B樹滿足以下條件：
1）每個結點至多有M個孩子；
2）除根結點和葉結點外，其它每個結點至少有M/2個孩子；
3）根結點至少有兩個孩子（除非該樹僅包含一個結點）；
4）所有葉結點在同一層，葉結點不包含任何關鍵字信息，可以看作一種外部節(jié)點；
5）有K個關鍵字的非葉結點恰好包含K+1個孩子；
 B樹中的每個結點根據實際情況可以包含大量的關鍵字信息和分支(當然是不能超過磁盤塊的大小，根據磁盤驅動(disk drives)的不同，一般塊的大小在1k~4k左右)；這樣樹的深度降低了，這就意味著查找一個元素只要很少結點從外存磁盤中讀入內存，很快訪問到要查找的數據。B-樹上操作的時間通常由存取磁盤的時間和CPU計算時間這兩部分構成。而相對于磁盤的io速度，cpu的計算時間可以忽略不計，所以B樹的意義就顯現出來了，樹的深度降低，而深度決定了io的讀寫次數。
B樹索引是一個典型的樹結構，其包含的組件主要是：
1）葉子節(jié)點（Leaf node）：包含條目直接指向表里的數據行。
2）分支節(jié)點（Branch node）：包含的條目指向索引里其他的分支節(jié)點或者是葉子節(jié)點。
3)  根節(jié)點（Root node）：一個B樹索引只有一個根節(jié)點，它實際就是位于樹的最頂端的分支節(jié)點。
如下圖所示：

 
每個索引都包含兩部分內容，一部分是索引本身的值，第二部分即指向數據頁或者另一個索引也的指針。每個節(jié)點即為一個索引頁，包含了多個索引。
當你為一個空表建立一個索引，數據庫會分配一個空的索引頁，這個索引頁即代表根節(jié)點，在你插入數據之前，這個索引頁都是空的。每當你插入數據，數據庫就會在根節(jié)點創(chuàng)建索引條目，。當根節(jié)點插滿的時候，再插入數據時，根節(jié)點就會分裂。舉個例子，根節(jié)點插入了如圖所示的數據。（超過4個就分裂），這時候插入H，就會分裂成2個節(jié)點，移動G到新的根節(jié)點，把H和N放在新的右孩子節(jié)點中。如圖所示：
    
　　　　根節(jié)點插滿4個節(jié)點
    
　　　　　　插入H，進行分裂。

大致的分裂步驟如下：
1）創(chuàng)建兩個兒子節(jié)點
2）將原節(jié)點中的數據近似分為兩半，寫入兩個新的孩子節(jié)點中。
3）在跟節(jié)點中放置指向頁節(jié)點的指針

當你不斷向表中插入數據，根節(jié)點中指向葉節(jié)點的指針也被插滿，當葉子還需要分裂的時候，根節(jié)點沒有空間再創(chuàng)建指向新的葉節(jié)點的指針。那么數據庫就會創(chuàng)建分支節(jié)點。隨著葉子節(jié)點的分裂，根節(jié)點中的指針都指向了這些分支節(jié)點。隨著數據的不斷插入，索引會增加更多的分支節(jié)點，使樹結構變成這樣的一個多級結構。

3. 索引的種類

1）聚集索引：表中行的物理順序與鍵值的邏輯（索引）順序相同。因為數據的物理順序只能有一種，所以一張表只能有一個聚集索引。如果一張表沒有聚集索引，那么這張表就沒有順序的概念，所有的新行都會插入到表的末尾。對于聚集索引，葉節(jié)點即存儲了數據行，不再有單獨的數據頁。就比如說我小時候查字典從來不看目錄，我覺得字典本身就是一個目錄，比如查裴字，只需要翻到p字母開頭的，再按順序找到e。通過這個方法我每次都能最快的查到老師說的那個字，得到老師的表揚。

2）非聚集索引：表中行的物理順序與索引順序無關。對于非聚集索引，葉節(jié)點存儲了索引字段值以及指向相應數據頁的指針。葉節(jié)點緊鄰在數據之上，對數據頁的每一行都有相應的索引行與之對應。有時候查字典，我并不知道這個字讀什么，那我就不得不通過字典目錄的“部首”來查找了。這時候我會發(fā)現，目錄中的排序和實際正文的排序是不一樣的，這對我來說很苦惱，因為我不能比別人快了，我需要先再目錄中找到這個字，再根據頁數去找到正文中的字。 

4．索引與數據的查詢，插入與刪除

1）查詢。查詢操作就和查字典是一樣的。當我們去查找指定記錄時，數據庫會先查找根節(jié)點，將待查數據與根節(jié)點的數據進行比較，再通過根節(jié)點的指針查詢下一個記錄，直到找到這個記錄。這是一個簡單的平衡樹的二分搜索的過程，我就不贅述了。在聚集索引中，找到頁節(jié)點即找到了數據行，而在非聚集索引中，我們還需要再去讀取數據頁。

2）插入。聚集索引的插入操作比較復雜，最簡單的情況，插入操作會找到對于的數據頁，然后為新數據騰出空間，執(zhí)行插入操作。如果該數據頁已經沒有空間，那就需要拆分數據頁，這是一個非常耗費資源的操作。對于僅有非聚集索引的表，插入只需在表的末尾插入即可。如果也包含了聚集索引，那么也會執(zhí)行聚集索引需要的插入操作。

3）刪除。刪除行后下方的數據會向上移動以填補空缺。如果刪除的數據是該數據頁的最后一行，那么這個數據頁會被回收，它的前后一頁的指針會被改變，被回收的數據頁也會在特定的情況被重新使用。與此同時，對于聚集索引，如果索引頁只剩一條記錄，那么該記錄可能會移動到鄰近的索引表中，原來的索引頁也會被回收。而非聚集索引沒辦法做到這一點，這就會導致出現多個數據頁都只有少量數據的情況。

5. 索引的優(yōu)缺點
其實通過前面的介紹，索引的優(yōu)缺點已經一目了然。
先說優(yōu)點：
    1）大大加快數據的檢索速度，這也是創(chuàng)建索引的最主要的原因
    2）加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

    3）在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

   再說缺點：
  1）創(chuàng)建索引需要耗費一定的時間，但是問題不大，一般索引只要build一次
  2）索引需要占用物理空間，特別是聚集索引，需要較大的空間

  3）當對表中的數據進行增加、刪除和修改的時候，索引也要動態(tài)的維護，降低了數據的維護速度，這個是比較大的問題。

6.索引的使用
       根據上文的分析，我們大致對什么時候使用索引有了自己的想法（如果你沒有，回頭再看一遍。。。）。一般我們需要在這些列上建立索引：
1）在經常需要搜索的列上，這是毋庸置疑的； 
2）經常同時對多列進行查詢，且每列都含有重復值可以建立組合索引，組合索引盡量要使常用查詢形成索引覆蓋（查詢中包含的所需字段皆包含于一個索引中，我們只需要搜索索引頁即可完成查詢）。 同時，該組合索引的前導列一定要是使用最頻繁的列。對于前導列的問題，在后面sqlite的索引使用介紹中還會做討論。
3）在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度，連接條件要充分考慮帶有索引的表。； 

4）在經常需要對范圍進行搜索的列上創(chuàng)建索引，因為索引已經排序，其指定的范圍是連續(xù)的，同樣，在經常需要排序的列上最好也創(chuàng)建索引。

6）在經常放到where子句中的列上面創(chuàng)建索引，加快條件的判斷速度。要注意的是where字句中對列的任何操作（如計算表達式，函數）都需要對表進行整表搜索，而沒有使用該列的索引。所以查詢時盡量把操作移到等號右邊。

對于以下的列我們不應該創(chuàng)建索引：
1）很少在查詢中使用的列
2）含有很少非重復數據值的列，比如只有0，1，這時候掃描整表通常會更有效
3）對于定義為TEXT，IMAGE的數據不應該創(chuàng)建索引。這些字段長度不固定，或許很長，或許為空。
當然，對于更新操作遠大于查詢操作時，不建立索引。也可以考慮在大規(guī)模的更新操作前drop索引，之后重新創(chuàng)建，不過這就需要把創(chuàng)建索引對資源的消耗考慮在內�？傊�，使用索引需要平衡投入與產出，找到一個產出最好的點。

7. 在sqlite中使用索引 

1）Sqlite不支持聚集索引，android默認需要一個_id字段，這保證了你插入的數據會按“_id”的整數順序插入，這個integer類型的主鍵就會扮演和聚集索引一樣的角色。所以不要再在對于聲明為：INTEGER PRIMARY KEY的主鍵上創(chuàng)建索引。

2）很多對索引不熟悉的朋友在表中創(chuàng)建了索引，卻發(fā)現沒有生效，其實這大多數和我接下來講的有關。對于where子句中出現的列要想索引生效，會有一些限制，這就和前導列有關。所謂前導列，就是在創(chuàng)建復合索引語句的第一列或者連續(xù)的多列。比如通過：CREATE INDEX comp_ind ON table1(x, y, z)創(chuàng)建索引，那么x,xy,xyz都是前導列，而yz，y，z這樣的就不是。下面講的這些，對于其他數據庫或許會有一些小的差別，這里以sqlite為標準。在where子句中，前導列必須使用等于或者in操作，最右邊的列可以使用不等式，這樣索引才可以完全生效。同時，where子句中的列不需要全建立了索引，但是必須保證建立索引的列之間沒有間隙。舉幾個例子來看吧：

用如下語句創(chuàng)建索引：
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
這里是一個查詢語句：
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'
這顯然對于abcd四列都是有效的，因為只有等于和in操作，并且是前導列。
再看一個查詢語句：
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'
那這里只有a，b和c的索引會是有效的，d列的索引會失效，因為它在c列的右邊，而c列使用了不等式，根據使用不等式的限制，c列已經屬于最右邊。
最后再看一條：
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'

索引將不會被使用，因為沒有使用前導列，這個查詢會是一個全表查詢。

3）對于between，or，like，都無法使用索引。
如 ...WHERE myfield BETWEEN 10 and 20;
這時就應該將其轉換成：
    ...WHERE myfield >= 10 AND myfield <= 20;
再如LIKE：...mytable WHERE myfield LIKE 'sql%';;
此時應該將它轉換成：
...WHERE myfield >= 'sql' AND myfield < 'sqm';
    再如OR：...WHERE myfield = 'abc' OR myfield = 'xyz';
此時應該將它轉換成：
...WHERE myfield IN ('abc', 'xyz');

其實除了索引，對查詢性能的影響因素還有很多，比如表的連接，是否排序等。影響數據庫操作的整體性能就需要考慮更多因素，使用更對的技巧，不得不說這是一個很大的學問。

最后在android上使用sqlite寫一個簡單的例子，看下索引對數據庫操作的影響。
創(chuàng)建如下表和索引：
   db.execSQL("create table if not exists t1(a,b)");        
   db.execSQL("create index if not exists ia on t1(a,b)");
插入10萬條數據，分別對表進行如下操作：
select * from t1 where a='90012'
插入：insert into t1(a,b) values('10008','name1.6982235534984673')
更新：update t1 set b='name1.999999' where a = '887'

刪除：delete from t1 where a = '1010'

數據如下（5次不同的操作取平均值）：
操作   無索引    有索引
查詢   170ms  5ms
插入   65ms   75ms
更新   240ms  52ms
刪除   234ms  78ms

        可以看到顯著提升了查詢的速度，稍稍減慢了插入速度，還稍稍提升了更新數據和刪除數據的速度。如果把更新和刪除中的where子句中的列換成b，速度就和沒有索引一樣了，因為索引失效。所以索引能大幅度提升查詢速度，對于刪除和更新操作，如果where子句中的列使用了索引，即使需要重新build索引，有可能速度還是比不使用索引要快的。對與插入操作，索引顯然是個負擔。同時，索引讓db的大小增加了2倍多。

       還有個要吐槽的是，android中的rawQurey方法，執(zhí)行完sql語句后返回一個cursor，其實并沒有完成一個查詢操作，我在rawquery之前和之后計算查詢時間，永遠是1ms...這讓我無比苦悶�？戳讼略创a，在對cursor調用moveToNext這些移動游標方法時，都會最終先調用getCount方法，而getCount方法才會調用native方法調用真正的查詢操作。這種設計顯然更加合理。