sqlite索引的原理

標籤：簡單的   orange   返回   一個   指標   指定   提高   圖片   基本   

引言

這篇文章，裡面講到對於一個41G大小、包含百萬條記錄的資料庫進行查詢操作，如果利用了索引，可以把操作耗時從37s降到0.2s。
那麼什麼是索引呢？利用索引可以加快資料庫查詢操作的原理是什麼呢？

索引的基本原理

資料庫提供了一種持久化的資料存放區方式，從資料庫中查詢資料庫是一個基本的操作，查詢操作的效率是很重要的。
對於查詢操作來說，如果被查詢的資料已某種方式組織起來，那麼查詢操作的效率會極大提高。
在資料庫中，一條記錄會有很多列。如果把這些記錄按照列Col1以某種資料結構組織起來，那麼列Col2一定是亂序的。
因此，資料庫在未經處理資料之外，維護了滿足特定尋找演算法的資料結構，指向未經處理資料，稱之為索引。
舉例來說，在下面的圖中，資料庫有兩列Col1、Col2。在儲存時，按照列Col1組織各行，比如Col1已二叉樹方式組織。如果尋找col1中的某一個值，利用二叉樹進行二分尋找，不需要遍曆整個資料庫。
這樣一來列Col2就是亂序的。為瞭解決這個問題，為Col2建立了索引，即把Col2也按照某種資料結構（這裡是二叉樹）組織起來。這樣子尋找列Col2時只需要進行二分尋找即可。
?

索引的實現

由於資料庫是儲存在磁碟上的，因此實現索引用的資料結構會儲存在磁碟上。磁碟的IO是需要注意的問題。

1. 二叉樹
   二叉樹是一種經典的資料結構，但是並不適合進行資料庫索引。
   原因在於二叉樹中每一個節點的度只有2，樹的深度較高。在儲存時，一般一個節點需要一次磁碟IO，樹的深度較高，查詢一個資料需要的磁碟IO次數越高，尋找需要的時間越長。

2. B樹
   B樹是二叉樹的變種，主要區別在於每一個節點的度可以大於2，即每一個節點可以分很多叉，大大降低了樹的深度。
   ?

   • 每條資料表示為[key,data]
   • 每個非葉子節點有(n-1)條資料n個指標組成
   • 所有分葉節點具有相同的深度，等於樹高h
   • 指標指向節點的key大於左邊的記錄小於右邊記錄

   上面這些特點使得B+樹的深度大大降低，並且實現了對資料的有序組織。

3. B+樹
   
   B+樹是對B樹的擴充，特點在於非葉子節點不儲存data，只儲存key。如果每一個節點的大小固定（如4k，正如在sqlite中那樣），那麼可以進一步提高內部節點的度，降低樹的深度。
   ?

   • 非葉子節點只儲存key，葉子節點不儲存指標
   • 每一個節點大小固定，需要一次讀磁碟操作（page）

4. 順序訪問指標的B+樹
   
   對B+樹做了一點改變，每一個葉子節點增加一個指向相鄰葉子節點的指標，這樣子可以提高區間訪問的效能。
   ?
   ，訪問key在15到30的data。

   • 如果沒有水平的指標
     B+樹尋找找到key=15的data，在同一個塊中找到key=18的data。然後進行第二次B+尋找，找到key=20的data，在同一個塊中找到key=30的data。
   • 有水平的指標
     B+樹尋找找到key=15的data，尋找同一個塊的內容，或沿著水平指標依次向右遍曆。

Sqlite中資料存放區方式

• 表(table)和索引(Index)都是帶順序訪問指標的B+樹
• table對應的B+樹中，key是rowid，data是這一行其他列資料（sqlite為每一行分配了一個rowid）
• index對應的B+樹種，key是需要索引的列，data是rowid

根據索引尋找資料時，分兩步

1. 根據索引找到rowid（第一次B+樹尋找）
2. 根據rowid尋找其他列的資料（第二次B+樹尋找）

通過兩次B+樹尋找避免了一次全表掃描。

1. 對某一行或某幾行添加PRIMARY KEY或UNIQUE約束，那麼資料庫會自動為這些列建立索引2. 指定某一列為INTEGER PRIMARY KEY，那麼這一列和rowid被指定為同一列。即可以通過rowid來擷取，也可以通過列名來擷取。

一個例子

下面是一個資料庫中一個表的統計資訊，通過sqlite3_analyzer工具得到。
?
可以看到表中一共有3651條記錄，B樹的深度只有2，有33個葉子節點，1個非葉子節點。因此最多隻需要2次磁碟IO就可以根據rowid找到一行的資料。

利用索引提高尋找效率

比如我們有這麼一個表
?

1. benchmark
   查詢語句如下

   SELECT price FROM fruitsforsale WHERE fruit=‘Peach’

   由於沒有索引，因此不得不做一次全表掃描。通過順序訪問指標遍曆各個記錄(record)，比較fruit這一列和‘peatch’是否一致，如果一致，返回這一行的price列的值。
   ?

2. 對‘fruit’列加索引
   如下，運行同樣的語句，可以根據索引找到目標列對應的rowid為4，然後根據rowid找到對應行，從而選出price。通過兩次B+樹尋找避免了全表尋找。這也是最簡單的情況 
   ?

3. 多條索引命中
   建立索引時，不要求索引是uique的，即索引表中的key可以是一樣的。
   如，索引表中有orange兩條記錄，找到第一條記錄時，根據順序訪問指標可以輕易找到下一條索引，避免另一次B+樹尋找。（rowid=1和rowid=23可能位於兩個不同的葉子節點中）
   即這個尋找索引的過程，可以通過一次B+樹查和一次next操作完成，而next操作是很快的。
   ?

4. 利用索引加快搜尋和排序
   在大多情況下，我們需要同時進行尋找和排序操作，這時如果建立適當的索引，可以提高尋找效率。
   比如下面表中對fruit和state兩列做了索引，運行下面的sql語句時，就不需要進行排序操作了，因為索引表是帶有順序的。

   SELECT price FROM fruitforsale WHERE fruit=‘Orange‘ ORDER BY state

   ?

解釋引言中問題

在sqlite中有一個命令叫做explain query plan，可以查看sqlite是如何執行尋找操作的。下面的資料庫語句不是引言中的查詢語句，原理一樣

• 37s的操作（沒有用索引）
  ?

• 0.2s的操作（用了索引）
  ?

注意detail列。不用索引時，使用的是“SCAN”這個詞，即全表掃描。使用索引時，使用的是“SEARCH”這個詞。
對於一個41G的表來說，進行全表掃描的代價顯然是很大的。

參考連結

1. 淺談演算法和資料結構: 十 平衡尋找樹之B樹
2. MySQL索引背後的資料結構及演算法原理
3. Query Planning(這篇是sqlite關於索引的文檔)
4. EXPLAIN QUERY PLAN
5. MySQL單表百萬資料記錄分頁效能最佳化

sqlite索引的原理