標籤:簡單的 orange 返回 一個 指標 指定 提高 圖片 基本
引言
這篇文章,裡面講到對於一個41G大小、包含百萬條記錄的資料庫進行查詢操作,如果利用了索引,可以把操作耗時從37s降到0.2s。
那麼什麼是索引呢?利用索引可以加快資料庫查詢操作的原理是什麼呢?
索引的基本原理
資料庫提供了一種持久化的資料存放區方式,從資料庫中查詢資料庫是一個基本的操作,查詢操作的效率是很重要的。
對於查詢操作來說,如果被查詢的資料已某種方式組織起來,那麼查詢操作的效率會極大提高。
在資料庫中,一條記錄會有很多列。如果把這些記錄按照列Col1以某種資料結構組織起來,那麼列Col2一定是亂序的。
因此,資料庫在未經處理資料之外,維護了滿足特定尋找演算法的資料結構,指向未經處理資料,稱之為索引。
舉例來說,在下面的圖中,資料庫有兩列Col1、Col2。在儲存時,按照列Col1組織各行,比如Col1已二叉樹方式組織。如果尋找col1中的某一個值,利用二叉樹進行二分尋找,不需要遍曆整個資料庫。
這樣一來列Col2就是亂序的。為瞭解決這個問題,為Col2建立了索引,即把Col2也按照某種資料結構(這裡是二叉樹)組織起來。這樣子尋找列Col2時只需要進行二分尋找即可。
?
索引的實現
由於資料庫是儲存在磁碟上的,因此實現索引用的資料結構會儲存在磁碟上。磁碟的IO是需要注意的問題。
- 二叉樹
二叉樹是一種經典的資料結構,但是並不適合進行資料庫索引。
原因在於二叉樹中每一個節點的度只有2,樹的深度較高。在儲存時,一般一個節點需要一次磁碟IO,樹的深度較高,查詢一個資料需要的磁碟IO次數越高,尋找需要的時間越長。
B樹
B樹是二叉樹的變種,主要區別在於每一個節點的度可以大於2,即每一個節點可以分很多叉,大大降低了樹的深度。
?
- 每條資料表示為[key,data]
- 每個非葉子節點有(n-1)條資料n個指標組成
- 所有分葉節點具有相同的深度,等於樹高h
- 指標指向節點的key大於左邊的記錄小於右邊記錄
上面這些特點使得B+樹的深度大大降低,並且實現了對資料的有序組織。
B+樹
B+樹是對B樹的擴充,特點在於非葉子節點不儲存data,只儲存key。如果每一個節點的大小固定(如4k,正如在sqlite中那樣),那麼可以進一步提高內部節點的度,降低樹的深度。
?
- 非葉子節點只儲存key,葉子節點不儲存指標
- 每一個節點大小固定,需要一次讀磁碟操作(page)
順序訪問指標的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
根據索引尋找資料時,分兩步
- 根據索引找到rowid(第一次B+樹尋找)
- 根據rowid尋找其他列的資料(第二次B+樹尋找)
通過兩次B+樹尋找避免了一次全表掃描。
1. 對某一行或某幾行添加PRIMARY KEY或UNIQUE約束,那麼資料庫會自動為這些列建立索引2. 指定某一列為INTEGER PRIMARY KEY,那麼這一列和rowid被指定為同一列。即可以通過rowid來擷取,也可以通過列名來擷取。
一個例子
下面是一個資料庫中一個表的統計資訊,通過sqlite3_analyzer工具得到。
?
可以看到表中一共有3651條記錄,B樹的深度只有2,有33個葉子節點,1個非葉子節點。因此最多隻需要2次磁碟IO就可以根據rowid找到一行的資料。
利用索引提高尋找效率
比如我們有這麼一個表
?
benchmark
查詢語句如下
SELECT price FROM fruitsforsale WHERE fruit=‘Peach’
由於沒有索引,因此不得不做一次全表掃描。通過順序訪問指標遍曆各個記錄(record),比較fruit這一列和‘peatch’是否一致,如果一致,返回這一行的price列的值。
?
對‘fruit’列加索引
如下,運行同樣的語句,可以根據索引找到目標列對應的rowid為4,然後根據rowid找到對應行,從而選出price。通過兩次B+樹尋找避免了全表尋找。這也是最簡單的情況
?
多條索引命中
建立索引時,不要求索引是uique的,即索引表中的key可以是一樣的。
如,索引表中有orange
兩條記錄,找到第一條記錄時,根據順序訪問指標可以輕易找到下一條索引,避免另一次B+樹尋找。(rowid=1和rowid=23可能位於兩個不同的葉子節點中)
即這個尋找索引的過程,可以通過一次B+樹查和一次next操作完成,而next操作是很快的。
?
利用索引加快搜尋和排序
在大多情況下,我們需要同時進行尋找和排序操作,這時如果建立適當的索引,可以提高尋找效率。
比如下面表中對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的表來說,進行全表掃描的代價顯然是很大的。
參考連結
- 淺談演算法和資料結構: 十 平衡尋找樹之B樹
- MySQL索引背後的資料結構及演算法原理
- Query Planning(這篇是sqlite關於索引的文檔)
- EXPLAIN QUERY PLAN
- MySQL單表百萬資料記錄分頁效能最佳化
sqlite索引的原理