MySQL索引原理

xy的技術圈發表於2019-07-25

什麼是索引?

“索引”是為了能夠更快地查詢資料。比如一本書的目錄,就是這本書的內容的索引,讀者可以通過在目錄中快速查詢自己想要的內容,然後根據頁碼去找到具體的章節。

資料庫也是一樣,如果查詢語句使用到了索引,會先去索引裡面查詢,取得資料所在行的實體地址,進而訪問資料。

索引的優缺點

優勢:以快速檢索,減少I/O次數,加快檢索速度;根據索引分組和排序,可以加快分組和排序;

劣勢:索引本身也是表,因此會佔用儲存空間。索引的維護和建立需要時間成本,這個成本隨著資料量增大而增大;構建索引會降低資料表的修改操作(刪除,新增,修改)的效率,因為在修改資料表的同時還需要修改索引表。

索引的分類

在MySQL中,常見的索引型別有:主鍵索引、唯一索引、普通索引、全文索引、組合索引。建立語法分別為:

建立索引.png

其中,組合索引又稱為多列索引,上述程式碼中最後一個例子就是建立了3列的索引。MySQL在根據索引查詢時,會遵循“最左匹配”原則,即先根據col1的條件查,再根據col2的條件查,然後再根據col3的條件去查。

如果跳過了一個列直接查後面的列,比如下面的語句,就不能使用上面建立的索引了:

語句.png

這裡有一個小技巧,如果你前面的列是一個簡單的列舉型別,比如性別等,可以用在where語句中加 col1 in(MALE, FEMALE) 來“跳過” col1 列,並使用上述索引。

對於某列如果是字串且比較長(比如UUID),推薦使用字首索引,即匹配前n個字元。具體這個n取值多少是根據你的資料來的,《高效能MySQL》裡提供了一個技巧:通過使用LEFT函式查詢,從1開始,不斷增加n的值,直到查詢結果的行數接近完整列的查詢結果的行數,就是合適的n的值。

字首索引.png

索引的實現原理

MySQL的索引是由儲存引擎來實現的。由於儲存引擎不同,所以具有不同的索引型別,如BTree索引,B+Tree索引,雜湊索引,全文索引等。這裡由於主要介紹BTree索引和B+Tree索引,我們平時使用最多的InnoDB引擎就是基於B+Tree索引的。

目前版本的MySQL InnoDB引擎已經支援全文索引,但不支援中文,可以通過使用ngram外掛開始支援中文。

從二叉搜尋樹聊起

瞭解過資料結構的朋友應該知道一種叫二叉樹的資料結構。二叉樹根據用途不同,衍生了不同的變種,比如堆,比如二叉搜尋樹。

而二叉搜尋樹中,為了防止極端情況樹的高度過大影響查詢效率,所以衍生出了一些平衡二叉查詢樹,最典型的就是AVL和紅黑樹。

但二叉樹在資料量較大時,深度過深,不太適合資料庫的查詢,所以資料庫使用了多叉樹。

BTree

BTree(又稱為B-Tree)是一個平衡搜尋多叉樹。BTree的結構如下圖:

BTree

設樹的度為2d(d>1),高度為h,那麼BTree有以下性質:

  • 每個葉子結點的高度一樣,等於h;
  • 每個非葉子結點由n-1個key和n個指標組成,key和指標相互隔離,結點兩端一定是key;
  • 葉子結點指標為null;
  • 非葉子結點的key都是[key,data]二元組,其中key表示作為索引的鍵,data為鍵值所在行的其它列的資料;

在BTree中,對索引列是順序儲存的,所以很適合查詢範圍資料和ORDER BY操作。

B+Tree

B+Tree是BTree的一種變種。B+Tree和BTree的不同主要在於:

  • B+Tree中的非葉子結點不儲存資料,只儲存鍵值;
  • B+Tree的葉子結點沒有指標,所有鍵值都會出現在葉子結點上,且key儲存的鍵值對應data資料的實體地址;
  • B+Tree的每個非葉子節點由n個鍵值key和n個指標point組成;

結構圖:

B+Tree

B+Tree對比BTree的優點:

一般來說B+Tree比BTree更適合實現外存的索引結構,因為儲存引擎的設計專家巧妙的利用了外存(磁碟)的儲存結構。

磁碟的最小儲存單位是扇區(sector),而作業系統的塊(block)通常是整數倍的sector,作業系統以頁(page)為單位管理記憶體,一頁(page)通常預設為4K,資料庫的頁通常設定為作業系統頁的整數倍,因此索引結構的節點被設計為一個頁的大小,然後利用外存的“預讀取”原則,每次讀取的時候,把整個節點的資料讀取到記憶體中,然後在記憶體中查詢。

已知記憶體的讀取速度是外存讀取I/O速度的幾百倍,那麼提升查詢速度的關鍵就在於儘可能少的磁碟I/O,那麼可以知道,每個節點中的key個數越多,那麼樹的高度越小,需要I/O的次數越少,因此一般來說B+Tree比BTree更快,因為B+Tree的非葉節點中不儲存data,就可以儲存更多的key。

帶順序索引的B+Tree

一般在資料庫系統或檔案系統中使用的B+Tree結構都在經典B+Tree的基礎上進行了優化,增加了順序訪問指標。

帶順序索引的B+Tree

在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指標,就形成了帶有順序訪問指標的B+Tree。做這個優化的目的是為了提高區間訪問的效能,例如如果要查詢key為從18到49的所有資料記錄,當找到18後,只需順著節點和指標順序遍歷就可以一次性訪問到所有資料節點,不用從頭再查詢一次,極大提到了區間查詢效率。

聚簇索引和非聚簇索引

MySQL中最常見的兩種儲存引擎分別是MyISAM和InnoDB,分別實現了非聚簇索引和聚簇索引。

前段時間看到一個問題:“你知道為什麼InnoDB非主鍵索引普遍比主鍵索引要慢嗎?”答案是InnoDB使用了聚簇索引,主鍵索引主需要查詢一次,而非主鍵索引需要查詢兩次。

為什麼非主鍵索引需要查詢兩次呢?且看接下來的內容。

主索引與輔助索引

首先介紹一下基礎的概念。在索引的分類中,我們可以按照索引的鍵是否為主鍵來分為“主索引”和“輔助索引”,使用主鍵鍵值建立的索引稱為“主索引”,其它的稱為“輔助索引”。因此主索引只能有一個,輔助索引可以有很多個。

為什麼需要用到輔助索引?因為前面我們介紹了,查詢語句如果想要使用索引,是需要滿足最左匹配原則的。有時候我們的查詢並不會使用到主鍵列,所以需要在其它列建立索引,即輔助索引。

非聚簇索引

非聚簇索引的主索引和輔助索引幾乎是一樣的,只是主索引不允許重複,不允許空值,他們的葉子結點的key都儲存指向鍵值對應的資料的實體地址。

非聚簇索引的資料表和索引表是分開儲存的。非聚簇索引中的資料是根據資料的插入順序儲存。因此非聚簇索引更適合單個資料的查詢。插入順序不受鍵值影響。

聚簇索引

聚簇索引的主索引的葉子結點儲存的是鍵值對應的資料本身,輔助索引的葉子結點儲存的是鍵值對應的資料的主鍵鍵值。因此主鍵的值長度越小越好,型別越簡單越好。

聚簇索引的資料和主鍵索引儲存在一起。

聚簇索引的資料是根據主鍵的順序儲存。因此適合按主鍵索引的區間查詢,可以有更少的磁碟I/O,加快查詢速度。但是也是因為這個原因,聚簇索引的插入順序最好按照主鍵單調的順序插入,否則會頻繁的引起頁分裂(BTree插入時的一個操作),嚴重影響效能。

在InnoDB中,如果只需要查詢索引的列,就儘量不要加入其它的列,這樣會提高查詢效率。

一張圖說明聚簇索引與非聚簇索引的區別:

聚簇索引和非聚簇索引的區別

相關文章