MySQL 什么是索引？-有解無憂

該文為《 MySQL 實戰 45 講》的學習筆記，感謝查看，如有錯誤，歡迎指正

一、索引簡介

索引就類似書本的目錄，作用就是方便我們更加快速的查找到想要的資料，

索引的實作方式比較多，常見的有哈希表，有序陣列，搜索樹，

1.1 哈希表

哈希表是將資料以key-value的形式存盤起來，簡單來說就是將key通過哈希函式換算成陣列中的一個確定的位置，將value存到這個位置去，當key比較多時，有可能換算出相同的位置，此時可以通過鏈表來解決，在查詢時先找到位置，再對該位置的多個value進行遍歷，

哈希表適合用于等值查詢，由于是無序的，不適合用來做區間查詢，

1.2 有序陣列

有序陣列在等值查詢和區間查詢上效率都很高，由于是有序的，可以通過二分法快速得到結果，也支持范圍查詢，但是也有一個缺點，如果要在中間插入一個資料，那么后面的所有記錄都要向后挪一位，成本太高了，

因此，有序陣列只適用于靜態存盤引擎，例如我們要保存2019年的出生人口資訊，就適合用有序陣列，

1.3 搜索樹

常見的搜索樹有二叉，也有多叉，

二叉樹的特點是：

每個節點的左兒子小于父節點，父節點又小于右兒子，

多叉樹的特點是：

每個節點有多個兒子，兒子之間的大小保證從左到右遞增，

由于索引不止存在記憶體中，還會寫到磁盤上，而讀磁盤越多，查詢效率越慢，要降低讀磁盤的次數的話，就要盡量訪問盡量少的資料塊，

假設資料塊大小是N，樹高為M，最多可以存的資料行數為 N^(M-1)（N 的 M-1 次方），最多訪問磁盤數為 M-1，

要使樹高比較小，訪問次數就少，N叉樹的樹高就小于二叉樹，以 InnoDB 的一個整數欄位索引為例，這個 N 差不多是 1200，這棵樹高是 4 的時候，就可以存 1200 的 3 次方個值，這已經 17 億行記錄了，一個 10 億行的表上一個整數欄位的索引，查找一個值最多只需要訪問 3 次磁盤，

資料庫底層存盤的核心就是基于這些資料模型的，每碰到一個新資料庫，我們需要先關注它的資料模型，這樣才能從理論上分析出這個資料庫的適用場景，

二、InnoDB 的索引模型

在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種存盤方式的表稱為索引組織表，
InnoDB 使用了 B+ 樹索引模型，所以資料都是存盤在 B+ 樹中的，

因此，每一個索引在 InnoDB 里面對應一棵 B+ 樹，

2.1 索引分類

根據欄位約束，分為主鍵索引和普通索引；根據欄位內容是否可重復，分為唯一索引和非唯一索引，

主鍵索引
主鍵是一種約束，一個表中只能有一個主鍵；
主鍵可以是多個列；
主鍵可以被其它表參考為外鍵使用；
主鍵索引可以理解為非空欄位+唯一索引；
主鍵索引的葉子節點存的是整行資料，
普通索引（二級索引）
一個表中可以有多個普通索引；索引可以有多列；
普通索引的葉子節點內容是主鍵的值；
唯一索引
欄位內容不能重復，但是可以為空；
一個表中可以有多個唯一索引；
不能做外鍵使用；
非唯一索引
欄位內容允許重復；

下面以表為例，建表陳述句：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分別為 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，兩棵樹的示例示意圖如下：
在這里插入圖片描述
id欄位為主鍵索引，主鍵索引的欄位是不會重復的，必定是唯一索引；
k欄位為普通索引，k的值允許重復，因此是非唯一索引，

2.2 回表操作

分析下面 2 條 SQL 陳述句：

select * from T where ID=500，此時用到的是主鍵索引，因此直接從索引中回傳了整行記錄，只需要搜索ID這棵 B+ 樹，
select * from T where k=5，此時用到的是普通索引，需要先搜索 k索引樹，得到ID = 500 ，再根據500到ID索引樹搜索一次，這種需要回傳主鍵索引樹搜索的程序，叫做回表，

以上兩條 SQL 陳述句回傳的結果是一樣的，但是效率卻不一樣，因為第 2 條 SQL 陳述句有一次回表操作，效率會慢很多，因此，要盡量避免回表操作，多使用主鍵查詢，

2.3 頁的分裂與合并

還是以上表為例，如果我們要插入一個資料，ID 值為 700，則只需要在 R5 后面新增加 1 條記錄即可，如果插入的值 ID 為 400，那就需要邏輯上挪動后面的資料，空出位置，

如果恰好 R5 所在的資料頁已經滿了，那么就需要申請一個新的資料頁，并且將 R5 挪過去，這個情況就叫做頁分裂，

資料頁中并不是要利用率達到 100% 才會申請新的資料頁，也不是說只要有資料洗掉，那么后一頁的資料就會順補到前一頁，這樣太浪費性能了，資料頁有一個利用率，假設分裂是80%，合并是 50%，只要利用率達到了 80%，就會申請一個新的資料頁，如果洗掉資料比較多，利用率低于 50% 了，就會把后一頁的資料合并過來，

如何避免頁分裂造成的性能消耗？常見做法是在表中，設定一個自增長的 id 主鍵，這個欄位不能和業務相關，自增主鍵的定義：NOT NULL PRIMARY KEY AUTO_INCREMENT，

這樣每次插入資料，如果不指定 id 值，就會自增長到最后，因為和業務無關，所以沒必要去指定 id 值，這樣可以避免出現頁分裂，

三、索引的一些特點

3.1 覆寫索引

還是以上表為例，執行以下 SQL 陳述句，分析執行程序：

mysql> select * from T where k between 3 and 5;

在普通索引k上遍歷，得到k=3對應的 ID 值 300；
通過 ID=300 去主鍵索引上取得整行記錄R3；
繼續向后遍歷k，得到k=5對應的 ID 值 500；
通過 ID=500 去主鍵索引上取得整行記錄R5；
繼續向后遍歷k，發現k=6，不滿足between條件，回圈結束，

可以看到，這個程序讀了k索引樹的 3 條記錄（步驟 1，3，5），回表了2次（步驟2，4），

如果我們換成以下 SQL 陳述句：

mysql> select ID from T where k between 3 and 5;

由于 ID已經在k索引樹上了，因此可以直接回傳結果，不用回表，這種索引中已經覆寫了我們要查詢的資料，叫做覆寫索引，

覆寫索引可以減少樹的搜索次數（沒有回表程序），顯著提高查詢性能，

3.2 關于掃描行數

MySQL 認為上述操作掃描的行數是 2 行，因為在索引中查資料，是在引擎層的操作，而 Server 層最后只拿到了 2 條記錄，因此 MySQL 認為只掃描了 2 行，

那么如何看掃描函式呢？有 2 種方法：

使用explain查看預計掃描行數

mysql> explain select * from t where a between 1000 and 2000;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a             | a    | 5       | NULL | 1000 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

mysql>

可以看到使用了索引 key=a，預計掃描行數rows=1000，

將慢日志記錄時間設定為 0 ，直接在慢日志中查看掃描行數

# Time: 191228 13:03:16
# User@Host: federated[federated] @  [60.191.76.22]  Id:   177
# Query_time: 31.211439  Lock_time: 0.000059 Rows_sent: 0  Rows_examined: 95324
SET timestamp=1577509396;
CALL Z10004();

可以看到，掃描行數為Rows_examined: 95324

3.3 最左前綴原則

舉一個例子來理解最左前綴原則，假設有一個聯合索引（name，age）如下：
在這里插入圖片描述
可以看到，索引順序先按照第一個欄位排序，再按照第二個欄位，

假設我們要查詢所有名為張三的資料，可以快速定位到ID4，再依次向后遍歷，如果要查詢所有姓張的(where name like '張%')，也能用到索引，先定位到ID3，再依次向后遍歷，直到不滿足條件為止，

不只是索引的全部定義，只要滿足最左前綴，就可以利用索引來加速檢索，這個最左前綴可以是聯合索引的最左 N 個欄位，也可以是字串索引的最左 M 個字符，

在建立聯合索引時，如何確定欄位的前后順序呢？

第一原則，如果通過調整順序，可以少維護一個索引，那么這個順序往往就是需要優先考慮采用的，
比如，已經有了一個(a, b)索引，就不必再建立一個 a 索引了，
考慮磁盤空間占用大小，
比如，(name, age) 索引加上 age 索引，和 (age, name) 索引加上 name 索引，這兩種情況，我們就要考慮占用空間了，選擇占用空間小的，
由于name 欄位比 age 欄位大，因此我們選擇(name, age) 索引加上 age 索引，

3.4 索引下推

索引下推功能是在 MySQL 5.6 引入的，目的是減少回表次數，

還是以市民表的聯合索引（name, age）為例，如果現在有一個需求：檢索出表中“名字第一個字是張，而且年齡是 10 歲的所有男孩”，那么，SQL 陳述句是這么寫的：

mysql> select * from tuser where name like '張%' and age=10 and ismale=1;

沒有索引下推
先定位到ID3，然后回表到主鍵索引，找出對應的資料行，判斷是否符合and age=10 and ismale=1，最終要回表 4 次（ID3，ID4，ID5，ID6），回傳的結果只有 ID4，ID5，
索引下推
在回表之前，會先判斷這個聯合索引上的后續欄位是否滿足條件，不滿足則不進行回表操作，最終只用回表 2 次，

感謝閱讀，有興趣的小伙伴可以關注我的微信公眾號DevOps探索之旅，大家一起學習進步

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/80283.html

標籤：MySQL

上一篇：關于mongodb的分片問題想請教

下一篇：面試官：你用過mysql哪些存盤引擎，請分別展開介紹一下