該文為《 MySQL 實戰(zhàn) 45 講》的學(xué)習(xí)筆記粥脚，感謝查看窃肠，如有錯誤包个，歡迎指正
一刷允、索引簡介
索引就類似書本的目錄冤留，作用就是方便我們更加快速的查找到想要的數(shù)據(jù)。
索引的實現(xiàn)方式比較多树灶，常見的有哈希表纤怒，有序數(shù)組，搜索樹天通。
1.1 哈希表
哈希表是將數(shù)據(jù)以key-value的形式存儲起來泊窘，簡單來說就是將key通過哈希函數(shù)換算成數(shù)組中的一個確定的位置，將value存到這個位置去像寒。當(dāng)key比較多時烘豹，有可能換算出相同的位置，此時可以通過鏈表來解決诺祸。在查詢時先找到位置携悯，再對該位置的多個value進(jìn)行遍歷。
哈希表適合用于等值查詢筷笨，由于是無序的憔鬼，不適合用來做區(qū)間查詢。
1.2 有序數(shù)組
有序數(shù)組在等值查詢和區(qū)間查詢上效率都很高胃夏。由于是有序的轴或，可以通過二分法快速得到結(jié)果。也支持范圍查詢仰禀。但是也有一個缺點照雁，如果要在中間插入一個數(shù)據(jù)，那么后面的所有記錄都要向后挪一位答恶，成本太高了囊榜。
因此，有序數(shù)組只適用于靜態(tài)存儲引擎亥宿。 例如我們要保存2019年的出生人口信息卸勺，就適合用有序數(shù)組。
1.3 搜索樹
常見的搜索樹有二叉烫扼，也有多叉曙求。
二叉樹的特點是：
- 每個節(jié)點的左兒子小于父節(jié)點，父節(jié)點又小于右兒子映企。
多叉樹的特點是：
- 每個節(jié)點有多個兒子悟狱，兒子之間的大小保證從左到右遞增。
由于索引不止存在內(nèi)存中堰氓，還會寫到磁盤上挤渐，而讀磁盤越多，查詢效率越慢双絮。要降低讀磁盤的次數(shù)的話浴麻，就要盡量訪問盡量少的數(shù)據(jù)塊得问。
假設(shè)數(shù)據(jù)塊大小是N，樹高為M软免，最多可以存的數(shù)據(jù)行數(shù)為 N^(M-1)（N 的 M-1 次方）宫纬。最多訪問磁盤數(shù)為 M-1。
要使樹高比較小膏萧，訪問次數(shù)就少漓骚，N叉樹的樹高就小于二叉樹。以 InnoDB 的一個整數(shù)字段索引為例榛泛，這個 N 差不多是 1200蝌蹂，這棵樹高是 4 的時候，就可以存 1200 的 3 次方個值曹锨，這已經(jīng) 17 億行記錄了叉信。一個 10 億行的表上一個整數(shù)字段的索引，查找一個值最多只需要訪問 3 次磁盤艘希。
數(shù)據(jù)庫底層存儲的核心就是基于這些數(shù)據(jù)模型的硼身。每碰到一個新數(shù)據(jù)庫，我們需要先關(guān)注它的數(shù)據(jù)模型覆享，這樣才能從理論上分析出這個數(shù)據(jù)庫的適用場景佳遂。
二、InnoDB 的索引模型
- 在 InnoDB 中撒顿，表都是根據(jù)主鍵順序以索引的形式存放的丑罪，這種存儲方式的表稱為
索引組織表。 - InnoDB 使用了 B+ 樹索引模型凤壁，所以數(shù)據(jù)都是存儲在 B+ 樹中的吩屹。
因此，每一個索引在 InnoDB 里面對應(yīng)一棵 B+ 樹拧抖。
2.1 索引分類
根據(jù)字段約束煤搜，分為主鍵索引和普通索引；根據(jù)字段內(nèi)容是否可重復(fù)唧席，分為唯一索引和非唯一索引擦盾。
主鍵索引
主鍵是一種約束，一個表中只能有一個主鍵淌哟；
主鍵可以是多個列迹卢；
主鍵可以被其它表引用為外鍵使用；
主鍵索引可以理解為非空字段+唯一索引徒仓；
主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)腐碱。普通索引（二級索引）
一個表中可以有多個普通索引；索引可以有多列掉弛；
普通索引的葉子節(jié)點內(nèi)容是主鍵的值症见；唯一索引
字段內(nèi)容不能重復(fù)喂走，但是可以為空；
一個表中可以有多個唯一索引筒饰；
不能做外鍵使用；非唯一索引
字段內(nèi)容允許重復(fù)壁晒；
下面以表為例瓷们，建表語句：
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字段為主鍵索引携取，主鍵索引的字段是不會重復(fù)的攒钳，必定是唯一索引；k字段為普通索引雷滋，k的值允許重復(fù)不撑，因此是非唯一索引。
2.2 回表操作
分析下面 2 條 SQL 語句：
-
select * from T where ID=500晤斩。此時用到的是主鍵索引焕檬，因此直接從索引中返回了整行記錄，只需要搜索ID這棵 B+ 樹澳泵。 -
select * from T where k=5实愚。此時用到的是普通索引，需要先搜索k索引樹兔辅，得到ID = 500腊敲，再根據(jù)500到ID索引樹搜索一次。這種需要返回主鍵索引樹搜索的過程维苔，叫做回表碰辅。
以上兩條 SQL 語句返回的結(jié)果是一樣的，但是效率卻不一樣介时，因為第 2 條 SQL 語句有一次回表操作乎赴，效率會慢很多，因此潮尝，要盡量避免回表操作榕吼，多使用主鍵查詢。
2.3 頁的分裂與合并
還是以上表為例勉失，如果我們要插入一個數(shù)據(jù)羹蚣，ID 值為 700，則只需要在 R5 后面新增加 1 條記錄即可乱凿。如果插入的值 ID 為 400顽素，那就需要邏輯上挪動后面的數(shù)據(jù)咽弦，空出位置。
如果恰好 R5 所在的數(shù)據(jù)頁已經(jīng)滿了胁出，那么就需要申請一個新的數(shù)據(jù)頁型型，并且將 R5 挪過去，這個情況就叫做頁分裂全蝶。
數(shù)據(jù)頁中并不是要利用率達(dá)到 100% 才會申請新的數(shù)據(jù)頁闹蒜。也不是說只要有數(shù)據(jù)刪除，那么后一頁的數(shù)據(jù)就會順補到前一頁抑淫，這樣太浪費性能了绷落。數(shù)據(jù)頁有一個利用率，假設(shè)分裂是80%始苇，合并是 50%砌烁。只要利用率達(dá)到了 80%，就會申請一個新的數(shù)據(jù)頁催式。如果刪除數(shù)據(jù)比較多函喉，利用率低于 50% 了，就會把后一頁的數(shù)據(jù)合并過來荣月。
如何避免頁分裂造成的性能消耗函似？常見做法是在表中，設(shè)置一個自增長的 id 主鍵喉童，這個字段不能和業(yè)務(wù)相關(guān)撇寞。自增主鍵的定義：NOT NULL PRIMARY KEY AUTO_INCREMENT。
這樣每次插入數(shù)據(jù)堂氯，如果不指定 id 值蔑担，就會自增長到最后，因為和業(yè)務(wù)無關(guān)咽白，所以沒必要去指定 id 值啤握。這樣可以避免出現(xiàn)頁分裂。
三晶框、索引的一些特點
3.1 覆蓋索引
還是以上表為例排抬，執(zhí)行以下 SQL 語句，分析執(zhí)行過程：
mysql> select * from T where k between 3 and 5;
- 在普通索引
k上遍歷授段，得到k=3對應(yīng)的ID值300蹲蒲； - 通過
ID=300去主鍵索引上取得整行記錄R3； - 繼續(xù)向后遍歷
k侵贵，得到k=5對應(yīng)的ID值500届搁； - 通過
ID=500去主鍵索引上取得整行記錄R5； - 繼續(xù)向后遍歷
k，發(fā)現(xiàn)k=6卡睦，不滿足between條件宴胧，循環(huán)結(jié)束。
可以看到表锻，這個過程讀了k索引樹的 3 條記錄（步驟 1恕齐，3，5）瞬逊， 回表了2次（步驟2显歧，4）。
如果我們換成以下 SQL 語句：
mysql> select ID from T where k between 3 and 5;
由于 ID已經(jīng)在k索引樹上了码耐，因此可以直接返回結(jié)果追迟，不用回表溶其。這種索引中已經(jīng)覆蓋了我們要查詢的數(shù)據(jù)骚腥，叫做覆蓋索引。
覆蓋索引可以減少樹的搜索次數(shù)（沒有回表過程）瓶逃，顯著提高查詢性能束铭。
3.2 關(guān)于掃描行數(shù)
MySQL 認(rèn)為上述操作掃描的行數(shù)是 2 行，因為在索引中查數(shù)據(jù)厢绝，是在引擎層的操作契沫。而 Server 層最后只拿到了 2 條記錄，因此 MySQL 認(rèn)為只掃描了 2 行昔汉。
那么如何看掃描函數(shù)呢懈万？有 2 種方法：
- 使用
explain查看預(yù)計掃描行數(shù)
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，預(yù)計掃描行數(shù)rows=1000靶病。
- 將慢日志記錄時間設(shè)置為 0 会通，直接在慢日志中查看掃描行數(shù)
# 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();
可以看到，掃描行數(shù)為Rows_examined: 95324
3.3 最左前綴原則
舉一個例子來理解最左前綴原則，假設(shè)有一個聯(lián)合索引（name，age）如下：
可以看到一罩，索引順序先按照第一個字段排序竣况，再按照第二個字段。
假設(shè)我們要查詢所有名為張三的數(shù)據(jù)涮较。可以快速定位到ID4，再依次向后遍歷端三。如果要查詢所有姓張的(where name like '張%')，也能用到索引鹃彻，先定位到ID3技肩，再依次向后遍歷，直到不滿足條件為止。
不只是索引的全部定義虚婿，只要滿足最左前綴旋奢，就可以利用索引來加速檢索。這個最左前綴可以是聯(lián)合索引的最左 N 個字段然痊，也可以是字符串索引的最左 M 個字符至朗。
在建立聯(lián)合索引時，如何確定字段的前后順序呢剧浸？
第一原則锹引，如果通過調(diào)整順序，可以少維護一個索引唆香，那么這個順序往往就是需要優(yōu)先考慮采用的嫌变。
比如，已經(jīng)有了一個(a, b)索引躬它，就不必再建立一個 a 索引了腾啥。考慮磁盤空間占用大小。
比如冯吓，(name, age) 索引加上 age 索引倘待，和 (age, name) 索引加上 name 索引。這兩種情況组贺，我們就要考慮占用空間了凸舵。選擇占用空間小的。
由于name 字段比 age 字段大失尖，因此我們選擇(name, age) 索引加上 age 索引啊奄。
3.4 索引下推
索引下推功能是在 MySQL 5.6 引入的，目的是減少回表次數(shù)掀潮。
還是以市民表的聯(lián)合索引（name, age）為例菇夸。如果現(xiàn)在有一個需求：檢索出表中“名字第一個字是張，而且年齡是 10 歲的所有男孩”胧辽。那么峻仇，SQL 語句是這么寫的：
mysql> select * from tuser where name like '張%' and age=10 and ismale=1;
- 沒有索引下推
先定位到ID3，然后回表到主鍵索引邑商，找出對應(yīng)的數(shù)據(jù)行摄咆，判斷是否符合and age=10 and ismale=1。最終要回表 4 次（ID3人断，ID4吭从，ID5，ID6）恶迈，返回的結(jié)果只有 ID4涩金，ID5谱醇。
在這里插入圖片描述 - 索引下推
在回表之前，會先判斷這個聯(lián)合索引上的后續(xù)字段是否滿足條件步做，不滿足則不進(jìn)行回表操作副渴。最終只用回表 2 次。
在這里插入圖片描述
感謝閱讀全度，有興趣的小伙伴可以關(guān)注我的公眾號DevOps探索之旅煮剧，大家一起學(xué)習(xí)進(jìn)步
