圖解 MySQL 索引，清晰易懂，寫得太好了！

作者：shuaibing90
來源：https://www.xysycx.cn/articles/2020/12/05/1607146183637.html

什么是索引？

索引是輔助存盤引擎高效獲取資料的一種資料結構，

很多人形象的說索引就是資料的目錄，便于存盤引擎快速的定位資料，

索引的分類

我們經常從以下幾個方面對索引進行分類

從 「資料結構的角度」 對索引進行分類

• B+tree
• Hash
• Full-texts 索引

從 「物理存盤的角度」 對索引進行分類

• 聚簇索引
• 二級索引(輔助索引)

從 「索引欄位特性角度」 分類

• 主鍵索引
• 唯一索引
• 普通索引
• 前綴索引

從 「組成索引的欄位個數角度」 分類

• 單列索引
• 聯合索引（復合索引）

資料結構角度看索引

下表是 MySQL 常見的存盤引擎 InnoDB，MyISAM 和 Memory 分別支持的索引型別

在實際使用中，InnoDB 作為 MySQL 建表時默認的存盤引擎

對上表進行橫向查看可以了解到，B+tree 是 MySQL 中被存盤引擎采用最多的索引型別，

這里淺嘗輒止的談一下 B+tree 與 Hash 和紅黑樹的區別，另外，MySQL 系列面試題和答案全部整理好了，微信搜索?Java技術堆疊，在后臺發送：面試，?可以在線閱讀，

B+tree 和 B-tree

1970 年，R.Bayer 和 E.Mccreight 提出了一種適用于外查找的平衡多叉樹——B-樹，磁盤管理系統中的目錄管理，以及資料庫系統中的索引組織多數采用 B-Tree 這種資料結構， --資料結構 C 語言版第二版 嚴蔚敏

B+tree 是 B-Tree 的一個變種，（哦，對了，B-tree 念 B 樹，它不叫 B 減樹，，，）

B+tree 只在葉子節點存盤資料，而 B-tree 非葉子節點也存盤資料，對此處有疑問的可以到下面的連接自己插入資料測驗一番，

• B-tree : https://www.cs.usfca.edu/~galles/visualization/BTree.html
• B+tree : https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

因此，B+tree 單個節點的數量更小，在相同的磁盤 IO 下能查詢更多的節點，

另外 B+tree 葉子節點采用單鏈表鏈接適合 MySQL 中常見的基于范圍的順序檢索場景，而 B-tree 無法做到這一點，

B+tree 和紅黑樹

對于有 N 個葉子節點的 B+tree，搜索復雜度為 「O(logdN) ,d 是指 degree 是指 B+tree 的度」，表示節點允許的最大子節點個數為 d 個，在實際的運用中 d 值是大于 100 的，即使資料達到千萬級別時候 B+tree 的高度依然維持在 3-4 左右，保證了 3-4 次磁盤 I/O 就能查到目標資料，

從上圖中可以看出紅黑樹是二叉樹，節點的子節點個數最多為 2 個，意味著其搜索復雜度為 「O(logN)」，比 B+ 樹高出不少，因此紅黑樹檢索到目標資料所需經理的磁盤 I/O 次數更多，

B+tree 索引與 Hash 表

范圍查詢是 MySQL 資料庫中常見的場景，而 Hash 表不適合做范圍查詢，Hash 表更適合做等值查詢，另外 Hash 表還存在 Hash 函式選擇和 Hash 值沖突等問題，

因為這些原因，B+tree 索引要比 Hash 表索引有更廣的適用場景，

物理存盤角度看索引

MySQL 中的兩種常用存盤引擎對索引的處理方式差別較大，

InnoDB 的索引

首先看一下 InnoDB 存盤引擎中的索引，InnoDB 表的索引按照葉子節點存盤的是否為完整表資料分為聚簇索引和二級索引，

全表資料就是存盤在聚簇索引中的，

聚簇索引以外的其它索引叫做二級索引，

下面結合實際的例子介紹下這兩類索引，

create table workers
 (
     id    int(11)     not null auto_increment comment '員工工號',
     name  varchar(16) not null comment '員工名字',
     sales int(11) default null comment '員工銷售業績',
     primary key (id)
 ) engine InnoDB
   AUTO_INCREMENT = 10
   default charset = utf8;

 insert into workers(id, name, sales)
 values (1, '江南', 12744);
 insert into workers(id, name, sales)
 values (3, '今何在', 14082);
 insert into workers(id, name, sales)
 values (7, '路明非', 14738);
 insert into workers(id, name, sales)
 values (8, '呂歸塵', 7087);
 insert into workers(id, name, sales)
 values (11, '姬野', 8565);
 insert into workers(id, name, sales)
 values (15, '凱撒', 8501);
 insert into workers(id, name, sales)
 values (20, '繪梨衣', 7890);

我們現在自己的測驗資料庫中創建一個包含銷售員資訊的測驗表 workers

包含 id(主鍵),name,sales 三個欄位，指定表的存盤引擎為 InnoDB，

然后插入 8 條資料

這個例子當中，workers 表的聚簇索引建立在欄位 id 上

為了準確模擬，我們先把主鍵 id 插入 b+tree 得到下圖

然后在此圖基礎上，我畫出了高清版，

從圖中可以看到，聚簇索引的每個葉子節點存盤了一行完整的表資料，葉子節點間采用單向鏈表按 id 列遞增連接，可以方便的進行順序檢索，

InnoDB 表要求必須有聚簇索引，默認在主鍵欄位上建立聚簇索引，在沒有主鍵欄位的情況下，表的第一個 NOT NULL 的唯一索引將被建立為聚簇索引，在前兩者都沒有的情況下，InnoDB 將自動生成一個隱式自增 id 列并在此列上創建聚簇索引，

接著來看二級索引，

還以剛才的 workers 表為例

我們在 name 欄位上添加二級索引 index_name

alter table workers add index index_name(name);

同樣我們畫出了二級索引 index_name 的 B+tree 示意圖

圖中可以看出二級索引的葉子節點并不存盤一行完整的表資料，而是存盤了聚簇索引所在列的值，也就是workers 表中的 id 列的值，

這兩張示意圖中 B+tree 的度設定為了 3 ，這也主要是為了方便演示，

實際的 B+tree 索引中，樹的度通常會大于 100，

說了聚簇索引和二級索引 肯定要提到「回表查詢」，

由于二級索引的葉子節點不存盤完整的表資料，所以當通過二級索引查詢到聚簇索引的列值后，還需要回到局促索引也就是表資料本身進一步獲取資料，

比如說我們要在 workers 表中查詢 名叫呂歸塵的人

select * from workers where name='呂歸塵';

這條 SQL 通過 name='呂歸塵'的條件

在二級索引 index_name 中查詢到主鍵 id=8 ，接著帶著 id=8 這個條件

進一步回到聚簇索引查詢以后才能獲取到完整的資料，很顯然回表需要額外的 B+tree 搜索程序，必然增大查詢耗時，

需要注意的是通過二級索引查詢時，回表不是必須的程序，當 Query 的所有欄位在二級索引中就能找到時，就不需要回表，MySQL 稱此時的二級索引為覆寫索引或稱觸發了 「索引覆寫」，

select id,name from workers where name='呂歸塵';

這句 SQL 只查詢了 id，和 name，二級索引就已經包含了 Query 所以需要的所有欄位，就無需回表查詢，

explain select id,name from workers where name='呂歸塵';

使用 explain 查看此條 SQL 的執行計劃執行計劃的 Extra 欄位中出現了 Using where;Using index 表明查詢觸發了索引 index_name 的索引覆寫，且對索引做了 where 篩選，這里不需要回表，

下面做對比，查詢一下沒有索引的

explain select id,name,sales from workers where name='呂歸塵';

Extra 為 Using Index Condition 表示會先條件過濾索引，過濾完索引后找到所有符合索引條件的資料行，隨后用 WHERE 子句中的其他條件去過濾這些資料行，Index Condition Pushdown (ICP)是 MySQL 5.6 以上版本中的新特性，是一種在存盤引擎層使用索引過濾資料的一種優化方式，ICP 開啟時的執行計劃含有 Using index condition 標示 ，表示優化器使用了 ICP 對資料訪問進行優化，

如果你對此感興趣去查閱對應的官方檔案和技術博客，

這次我們簡化來理解，不考慮 ICP 對資料訪問的優化，當關閉 ICP 時，Index 僅僅是 data access 的一種訪問方式，存盤引擎通過索引回表獲取的資料會傳遞到 MySQL Server 層進行 WHERE 條件過濾，

Extra 為 Using where 只是提醒我們 MySQL 將用 where 子句來過濾結果集，這個一般發生在 MySQL 服務器，而不是存盤引擎層，一般發生在不能走索引掃描的情況下或者走索引掃描，但是有些查詢條件不在索引當中的情況下，

這里表明沒有觸發索引覆寫，進行回表查詢，

MyISAM 的索引

說完了 InnoDB 的索引，接下來我們來看 MyISAM 的索引

以 MyISAM 存盤引擎存盤的表不存在聚簇索引，

MyISAM 表中的主鍵索引和非主鍵索引的結構是一樣的，從上圖中我們可以看到

他們的葉子節點是不存盤表資料的，節點中存放的是表資料的地址，所以 MyISAM 表可以沒有主鍵，

MyISAM 表的資料和索引是分開的，是單獨存放的，

MyISAM 表中的主鍵索引和非主鍵索引的區別僅在于主鍵索引 B+tree 上的 key 必須符合主鍵的限制，

非主鍵索引 B+tree 上的 key 只要符合相應欄位的特性就可以了，

索引欄位特性角度看索引

「主鍵索引」

• 建立在主鍵欄位上的索引
• 一張表最多只有一個主鍵索引
• 索引列值不允許為 null
• 通常在創建表的時候一起創建

「唯一索引」

• 建立在 UNIQUE 欄位上的索引就是唯一索引
• 一張表可以有多個唯一索引，索引列值允許為 null

我們演示創建索引

create table persons
 (
     id   int(11) not null auto_increment comment '主鍵id',
     eno  int(11) comment '工號',
     eid  int(11) comment '身份證號',
     veid int(11) comment '虛擬身份證號',
     name varchar(16) comment '名字',
     primary key (id) comment '主鍵索引',
     UNIQUE key (eno) comment 'eno唯一索引',
     UNIQUE key (eid) comment 'eid唯一索引'
 ) engine = InnoDB
   auto_increment = 1000
   default charset = utf8;
 alter table persons
     add unique index index_veid (veid) comment 'veid唯一索引';

通過 show index from persons;命令我們看到已經成功創建了三個唯一索引，

普通索引

主鍵索引和唯一索引對欄位的要求是要求欄位為主鍵或 unique 欄位，

而那些建立在普通欄位上的索引叫做普通索引，既不要求欄位為主鍵也不要求欄位為 unique，

前綴索引

前綴索引是指對字符型別欄位的前幾個字符或對二進制型別欄位的前幾個 bytes 建立的索引，而不是在整個欄位上建索引，

例如，可以對 persons 表中的 name(varchar(16))欄位 中 name 的前 5 個字符建立索引，

create index index_name on persons (name(5)) comment '前綴索引';
show index from persons;

前綴索引可以建立在型別為

• char
• varchar
• binary
• varbinary

的列上，可以大大減少索引占用的存盤空間，也能提升索引的查詢效率，

索引列的個數角度看索引

• 建立在單個列上的索引為單列索引

• • 上文演示的都是單列索引

• 建立在多列上的稱為聯合索引（復合索引）

演示一下聯合索引create index index_id_name on workers(id,name) comment '組合索引';這條陳述句在我們演示表 workers 中建立 id，name 這兩個欄位的聯合索引，借助 show index 命令查看索引的詳細資訊 操作后結果如下：

雖然詳細資訊當中列出了兩條關于聯合索引的條目，但并不表示聯合索引是建立了多個索引，聯合索引是一個索引結構，這兩個條目表示的是組合索引中欄位的具體資訊，按建立索引時的書寫順序排序，

同樣我們來看下聯合索引的 B+tree 示意圖

從圖中看到組合索引的非葉子節點保存了兩個欄位的值作為 B+tree 的 key 值，當 B+tree 上插入資料時，先按欄位 id 比較，在 id 相同的情況下按 name 欄位比較，

近期熱文推薦：

1.1,000+ 道 Java面試題及答案整理(2021最新版)

2.別在再滿屏的 if/ else 了，試試策略模式，真香！！

3.臥槽！Java 中的 xx ≠ null 是什么新語法？

4.Spring Boot 2.5 重磅發布，黑暗模式太炸了！

5.《Java開發手冊（嵩山版）》最新發布，速速下載！

覺得不錯，別忘了隨手點贊+轉發哦！

標籤：Java

上一篇：如何以角度向后端發送正確的json

下一篇：database-sync適配openGauss使用指導書