Sqlite資料庫中索引的使用、索引的優缺點[轉]

標籤：

原文連結1   原文連結2

3. 索引的種類

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

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

4．索引與資料的查詢，插入與刪除

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

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

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

5. 索引的優缺點
其實通過前面的介紹，索引的優缺點已經一目瞭然。
先說優點：
    1）大大加快資料的檢索速度，這也是建立索引的最主要的原因
    2）加速表和表之間的串連，特別是在實現資料的參考完整性方面特別有意義。
    3）在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。

再說缺點：
  1）建立索引需要耗費一定的時間，但是問題不大，一般索引只要build一次
  2）索引需要佔用物理空間，特別是叢集索引，需要較大的空間
  3）當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，降低了資料的維護速度，這個是比較大的問題。

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

4）在經常需要對範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的，同樣，在經常需要排序的列上最好也建立索引。

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

對於以下的列我們不應該建立索引：
1）很少在查詢中使用的列
2）含有很少非重複資料值的列，比如只有0，1，這時候掃描整表通常會更有效
3）對於定義為TEXT，IMAGE的資料不應該建立索引。這些欄位長度不固定，或許很長，或許為空白。
當然，對於更新操作遠大於查詢操作時，不建立索引。也可以考慮在大規模的更新操作前drop索引，之後重新建立，不過這就需要把建立索引對資源的消耗考慮在內。總之，使用索引需要平衡投入與產出，找到一個產出最好的點。

7. 在sqlite中使用索引 

1）Sqlite不支援叢集索引，android預設需要一個_id欄位，這保證了你插入的資料會按“_id”的整數順序插入，這個integer類型的主鍵就會扮演和叢集索引一樣的角色。所以不要再在對於聲明為：INTEGER PRIMARY KEY的主鍵上建立索引。

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

用如下語句建立索引：

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‘

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

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

最後在android上使用sqlite寫一個簡單的例子，看下索引對資料庫操作的影響。
建立如下表和索引：
   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方法，執行完sql語句後返回一個cursor，其實並沒有完成一個查詢操作，我在rawquery之前和之後計算查詢時間，永遠是1ms...這讓我無比苦悶。看了下源碼，在對cursor調用moveToNext這些移動遊標方法時，都會最終先調用getCount方法，而getCount方法才會調用native方法調用真正的查詢操作。這種設計顯然更加合理。 

Sqlite資料庫中索引的使用、索引的優缺點[轉]