Mysql資料庫索引初學者詳解-mysql教程-PHP中文網

核心概念

主鍵索引/輔助索引
聚集索引/非聚集索引
表查找/索引覆蓋
索引下推
複合索引/最左邊前綴匹配
前綴索引
解釋一下

1.【指標定義】

1.索引定義

除了資料本身之外，資料庫系統還維護滿足特定搜尋演算法的資料結構。這些結構以某種方式引用（指向）資料，允許在它們上實現高級搜尋演算法。 這些資料結構就是索引。

2.索引的資料結構

B-tree / B+ 樹（MySQL 的 InnoDB 引擎使用 B+ 樹作為預設索引結構）
哈希表
排序數組

3.為什麼選擇 B+ 樹而不是 B 樹

B樹結構：記錄儲存在樹節點中。

Mysql Database Index Explained for Beginners

B+樹結構：記錄僅儲存在樹的葉子節點中。

Mysql Database Index Explained for Beginners

假設資料大小為 1KB，索引大小為 16B，資料庫使用磁碟資料頁，預設磁碟頁大小為 16K，相同的 3 個 I/O 操作將會得到：

B-樹可以取得16*16*16=4096記錄。
B+樹可以取得1000*1000*1000=10億筆記錄。

2.【索引類型】

1.主鍵索引與輔助索引

主鍵索引：索引的葉子節點是資料行。
二級索引：索引的葉子節點是KEY欄位加上主鍵索引。因此，透過二級索引查詢時，首先找到主鍵值，然後InnoDB透過主鍵索引找到對應的資料塊。
在InnoDB中，主索引檔案直接儲存資料行，稱為聚集索引，而二級索引則指向主鍵引用。
在MyISAM中，主索引和二級索引都指向實體行（磁碟位置）。

Mysql Database Index Explained for Beginners

2.聚集索引和非聚集索引

聚集索引重新組織磁碟上的實際數據，以便按一個或多個指定的列值進行排序。特點是資料的儲存順序和索引順序一致。一般主鍵會預設建立聚集索引，一張表只允許有一個聚集索引（原因：資料只能以一種順序儲存）。如圖所示，InnoDB的主索引和輔助索引都是聚集索引。
與聚集索引的葉子節點是資料記錄相比，非聚集索引的葉子節點是指向資料記錄的指標。最大的差異是資料記錄的順序與索引順序不符

3.聚集索引的優點和缺點

優點：透過主鍵查詢條目時，不需要查表（資料在主鍵節點下）
缺點：不規則的資料插入會導致頻繁的分頁。

3.【擴展索引概念】

1.查表

查表的概念涉及到主鍵索引查詢和非主鍵索引查詢的區別。

如果查詢是select * from T where ID=500，主鍵查詢只需要找ID樹。
如果查詢是 select * from T where k=5，則非主鍵索引查詢需要先在k索引樹中查找ID值500，然後再在ID索引樹中查找。
從非主鍵索引移回主鍵索引的過程稱為查詢表。

基於非主鍵索引的查詢需要掃描額外的索引樹。 因此，我們應該在應用程式中盡量使用主鍵查詢。從儲存空間的角度來看，由於非主鍵索引樹的葉子節點儲存的是主鍵值，所以建議主鍵欄位盡可能短。這樣，非主鍵索引樹的葉子節點就更小，非主鍵索引佔用的空間也更少。一般情況下，建議建立自增主鍵，以盡量減少非主鍵索引所佔用的空間

2.索引覆蓋

首先透過非主鍵索引定位到主鍵索引（主鍵位於非主鍵索引搜尋樹的葉子節點） )，然後透過主鍵索引定位到查詢內容。在這個過程中，回到主鍵索引樹就稱為查表。
查詢內容是主鍵值時，我們可以直接提供查詢結果，無需查表。也就是說，本次查詢中非主鍵索引已經「覆蓋」了我們的查詢需求，因此稱為覆蓋索引。
覆蓋索引可以直接從輔助索引取得查詢結果，無需到主索引進行查表，從而減少搜尋次數（不需要從輔助索引樹移動到聚集索引樹）或減少IO操作（輔助索引樹可以一次從磁碟載入更多節點），從而提高效能。

3.綜合指數

複合索引是指將資料表的多個欄位進行索引。

場景1：

複合索引（a，b）

按a，b排序（先按a排序，如果a相同則按b排序）。因此，以下的語句可以直接使用複合索引來獲得結果（其實就是利用了最左前綴原則）：

以下語句不能使用複合查詢：

場景2：

對於複合索引（a,b,c），以下語句可以直接透過複合索引取得結果：

以下語句不能使用複合索引，需要進行檔案排序運算：

總結：

以複合索引（a,b,c）為例，建立這樣的索引相當於建立索引a,ab,abc。 用一個索引代替三個索引肯定是有好處的，因為每增加一個索引都會增加索引寫入作業的開銷和磁碟空間使用。

4.最左前綴原則

不只是索引的完整定義，只要滿足最左邊的前綴，就可以用來加速檢索。這個最左前綴可以是複合索引最左邊的N個字段，也可以是字串索引最左邊的M個字元。 利用索引的「最左前綴」原則來定位記錄，避免冗餘的索引定義。
當(a,b)上已經有索引時，一般不需要在a上單獨建立索引

5.索引下推

MySQL 5.6 引入了索引下推優化，

在索引遍歷時可以根據索引包含的欄位過濾掉不符合條件的記錄，減少查表次數。

創建表

雷雷

SELECT * from user where name like 'Chen%' 最左前綴原則，命中 idx_name_age 索引
從名稱如“Chen%”且年齡=20 的用戶中選擇 *
- 5.6版本之前，會先依照名稱索引來配對2筆記錄（此時忽略age=20的條件），找到對應的2個ID，進行查表，然後根據age=20進行篩選。
- 5.6版本之後，引入了索引下推。根據姓名配對2筆記錄後，在進行查表之前不會忽略age=20的條件，在查表之前根據年齡進行過濾。這種索引下推可以減少查表次數，提高查詢效能

6。前綴索引

當索引是一個長字元序列時，它會佔用大量記憶體並且速度很慢。在這種情況下，可以使用前綴索引。我們不是對整個值進行索引，而是對前幾個字元進行索引，以節省空間並獲得良好的效能。 前綴索引使用索引的前幾個字母。但是，為了降低索引重複率，我們必須評估前綴索引的唯一性。

首先計算目前字串欄位的唯一性比例：select 1.0*count(distinct name)/count(*) from test
然後，計算不同前綴的唯一性比率：
- 從測試中選擇 1.0*count(distinct left(name,1))/count(*) 作為名稱的第一個字元作為前綴索引
- 從測試中選擇 1.0*count(distinct left(name,2))/count(*) 作為名稱的前兩個字元作為前綴索引
- ...
當 left(str, n) 沒有顯著增加時，選擇 n 作為前綴索引截止值。
建立索引 alter table test add key(name(n));