索引
索引常見的幾種類型
索引常見的類型有哈希索引，有序數(shù)組索引，二叉樹索引，跳表等等。本文主要探討 MySQL 的默認(rèn)存儲引擎 InnoDB 的索引結(jié)構(gòu)。
InnoDB的索引結(jié)構(gòu)
在InnoDB中是通過一種多路搜索樹——B+樹實現(xiàn)索引結(jié)構(gòu)的。在B+樹中是只有葉子結(jié)點會存儲數(shù)據(jù)，而且所有葉子結(jié)點會形成一個鏈表。而在InnoDB中維護(hù)的是一個雙向鏈表。
你可能會有一個疑問，為什么使用 B+樹 而不使用二叉樹或者B樹？
首先，我們知道訪問磁盤需要訪問到指定塊中，而訪問指定塊是需要 盤片旋轉(zhuǎn) 和 磁臂移動 的，這是一個比較耗時的過程，如果增加樹高那么就意味著你需要進(jìn)行更多次的磁盤訪問，所以會采用n叉樹。而使用B+樹是因為如果使用B樹在進(jìn)行一個范圍查找的時候每次都會進(jìn)行重新檢索，而在B+樹中可以充分利用葉子結(jié)點的鏈表。
在建表的時候你可能會添加多個索引，而 InnDB 會為每個索引建立一個 B+樹 進(jìn)行存儲索引。
比如這個時候我們建立了一個簡單的測試表
create table test(
id int primary key,
a int not null,
name varchar,
index(a)
)engine = InnoDB;
復(fù)制代碼
這個時候 InnDB 就會為我們建立兩個 B+索引樹
一個是 主鍵 的 聚簇索引，另一個是 普通索引 的 輔助索引，這里我直接貼上 MySQL淺談（索引、鎖） 這篇文章上面的貼圖(因為我懶不想畫圖了。。。)
可以看到在輔助索引上面的葉子節(jié)點的值只是存了主鍵的值，而在主鍵的聚簇索引上的葉子節(jié)點才是存上了整條記錄的值。
回表
所以這里就會引申出一個概念叫回表，比如這個時候我們進(jìn)行一個查詢操作
select name from test where a = 30;
復(fù)制代碼
我們知道因為條件 MySQL 是會走 a 的索引的，但是 a 索引上并沒有存儲 name 的值，此時我們就需要拿到相應(yīng) a 上的主鍵值，然后通過這個主鍵值去走 聚簇索引 最終拿到其中的name值，這個過程就叫回表。
我們來總結(jié)一下回表是什么？MySQL在輔助索引上找到對應(yīng)的主鍵值并通過主鍵值在聚簇索引上查找所要的數(shù)據(jù)就叫回表。
索引維護(hù)
我們知道索引是需要占用空間的，索引雖能提升我們的查詢速度但是也是不能濫用。
比如我們在用戶表里用身份證號做主鍵，那么每個二級索引的葉子節(jié)點占用約20個字節(jié)，而如果用整型做主鍵，則只要4個字節(jié)，如果是長整型（bigint）則是8個字節(jié)。也就是說如果我用整型后面維護(hù)了4個g的索引列表，那么用身份證將會是20個g。
所以我們可以通過縮減索引的大小來減少索引所占空間。
當(dāng)然B+樹為了維護(hù)索引的有序性會在刪除，插入的時候進(jìn)行一些必要的維護(hù)(在InnoDB中刪除會將節(jié)點標(biāo)記為“可復(fù)用”以減少對結(jié)構(gòu)的變動)。
比如在增加一個節(jié)點的時候可能會遇到數(shù)據(jù)頁滿了的情況，這個時候就需要做頁的分裂，這是一個比較耗時的工作，而且頁的分裂還會導(dǎo)致數(shù)據(jù)頁的利用率變低，比如原來存放三個數(shù)據(jù)的數(shù)據(jù)頁再次添加一個數(shù)據(jù)的時候需要做頁分裂，這個時候就會將現(xiàn)有的四個數(shù)據(jù)分配到兩個數(shù)據(jù)頁中，這樣就減少了數(shù)據(jù)頁利用率。
覆蓋索引
上面提到了 回表，而有時候我們查輔助索引的時候就已經(jīng)滿足了我們需要查的數(shù)據(jù)，這個時候 InnoDB 就會進(jìn)行一個叫 覆蓋索引 的操作來提升效率，減少回表。
比如這個時候我們進(jìn)行一個 select 操作
select id from test where a = 1;
復(fù)制代碼
這個時候很明顯我們走了 a 的索引直接能獲取到 id 的值，這個時候就不需要進(jìn)行回表，我們這個時候就使用了 覆蓋索引。
簡單來說 覆蓋索引 就是當(dāng)我們走輔助索引的時候能獲取到我們所需要的數(shù)據(jù)的時候不需要再次進(jìn)行回表操作的操作。
聯(lián)合索引
這個時候我們新建一個學(xué)生表
CREATE TABLE `stu` (
`id` int(11) NOT NULL,
`class` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_name` (`class`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
復(fù)制代碼
我們使用 class(班級號) 和 name 做一個 聯(lián)合索引，你可能會問這個聯(lián)合索引有什么用呢？我們可以結(jié)合著上面的 覆蓋索引 去理解，比如這個時候我們有一個需求，我們需要通過班級號去找對應(yīng)的學(xué)生姓名 。
select name from stu where class = 102;
復(fù)制代碼
這個時候我們就可以直接在 輔助索引 上查找到學(xué)生姓名而不需要再次回表。
總的來說，設(shè)計好索引，充分利用覆蓋索引能很大提升檢索速度。
最左前綴原則
這個是以 聯(lián)合索引 作為基礎(chǔ)的，是一種聯(lián)合索引的匹配規(guī)則。
這個時候，我們將上面的需求稍微變動一下，這時我們有個學(xué)生遲到，但是他在門衛(wèi)記錄信息的時候只寫了自己的名字張三而沒有寫班級，所以我們需要通過學(xué)生姓名去查找相應(yīng)的班級號。
select class from stu where name = '張三';
復(fù)制代碼
這個時候我們就不會走我們的聯(lián)合索引了，而是進(jìn)行了全表掃描。
為什么？因為 最左匹配原則。我們可以畫一張簡單的圖來理解一下。
我們可以看到整個索引設(shè)計就是這么設(shè)計的，所以我們需要查找的時候也需要遵循著這個規(guī)則，如果我們直接使用name，那么InnoDB是不知道我們需要干什么的。
當(dāng)然最左匹配原則還有這些規(guī)則
- 全值匹配的時候優(yōu)化器會改變順序，也就是說你全值匹配時的順序和原先的聯(lián)合索引順序不一致沒有關(guān)系，優(yōu)化器會幫你調(diào)好。
- 索引匹配從最左邊的地方開始，如果沒有則會進(jìn)行全表掃描，比如你設(shè)計了一個(a,b,c)的聯(lián)合索引，然后你可以使用(a),(a,b),(a,b,c) 而你使用 (b),(b,c),(c)就用不到索引了。
- 遇到范圍匹配會取消索引。比如這個時候你進(jìn)行一個這樣的 select 操作
select * from stu where class > 100 and name = '張三';
復(fù)制代碼
這個時候 InnoDB 就會放棄索引而進(jìn)行全表掃描，因為這個時候 InnoDB 會不知道怎么進(jìn)行遍歷索引，所以進(jìn)行全表掃描。
索引下推
我給你挖了個坑。剛剛的操作在 MySQL5.6 版本以前是需要進(jìn)行回表的，但是5.6之后的版本做了一個叫 索引下推 的優(yōu)化。
select * from stu where class > 100 and name = '張三';
復(fù)制代碼
如何優(yōu)化的呢？因為剛剛的最左匹配原則我們放棄了索引，后面我們緊接著會通過回表進(jìn)行判斷 name，這個時候我們所要做的操作應(yīng)該是這樣的
但是有了索引下推之后就變成這樣了，此時 "李四" 和 "小明" 這兩個不會再進(jìn)行回表。
因為這里匹配了后面的name = 張三，也就是說，如果最左匹配原則因為范圍查詢終止了，InnoDB還是會索引下推來優(yōu)化性能。
一些最佳實踐
哪些情況需要創(chuàng)建索引？
- 頻繁作為查詢條件的字段應(yīng)創(chuàng)建索引。
- 多表關(guān)聯(lián)查詢的時候，關(guān)聯(lián)字段應(yīng)該創(chuàng)建索引。
- 查詢中的排序字段，應(yīng)該創(chuàng)建索引。
- 統(tǒng)計或者分組字段需要創(chuàng)建索引。
哪些情況不需要創(chuàng)建索引
- 表記錄少。
- 經(jīng)常增刪改查的表。
- 頻繁更新的字段。
- where 條件使用不高的字段。
- 字段很大的時候。
其他
- 盡量選擇區(qū)分度高的列作為索引。
- 不要對索引進(jìn)行一些函數(shù)操作，還應(yīng)注意隱式的類型轉(zhuǎn)換和字符編碼轉(zhuǎn)換。
- 盡可能的擴(kuò)展索引，不要新建立索引。比如表中已經(jīng)有了a的索引，現(xiàn)在要加（a,b）的索引，那么只需要修改原來的索引即可。
- 多考慮覆蓋索引，索引下推，最左匹配。
鎖
全局鎖
MySQL提供了一個加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當(dāng)你需要讓整個庫處于只讀狀態(tài)的時候，可以使用這個命令，之后其他線程的以下語句會被阻塞：數(shù)據(jù)更新語句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語句（包括建表、修改表結(jié)構(gòu)等）和更新類事務(wù)的提交語句。
一般會在進(jìn)行 全庫邏輯備份 的時候使用，這樣就能確保 其他線程不能對該數(shù)據(jù)庫做更新操作。
在 MVCC 中提供了獲取 一致性視圖 的操作使得備份變得非常簡單，如果想了解 MVCC 可以參考我的另一篇文章 你真的懂MVCC嗎？來手動實踐一下？ 。
表鎖
MDL(Meta Data Lock)元數(shù)據(jù)鎖
MDL鎖用來保證只有一個線程能對該表進(jìn)行表結(jié)構(gòu)更改。
怎么說呢？MDL分為 MDL寫鎖 和 MDL讀鎖，加鎖規(guī)則是這樣的
- 當(dāng)線程對一個表進(jìn)行 CRUD 操作的時候會加 MDL讀鎖
- 當(dāng)線程對一個表進(jìn)行 表結(jié)構(gòu)更改 操作的時候會加 MDL寫鎖
- 寫鎖和讀鎖，寫鎖和寫鎖互斥，讀鎖之間不互斥
lock tables xxx read/write;
這是給一個表設(shè)置讀鎖和寫鎖的命令，如果在某個線程A中執(zhí)行l(wèi)ock tables t1 read, t2 write; 這個語句，則其他線程寫t1、讀寫t2的語句都會被阻塞。同時，線程A在執(zhí)行unlock tables之前，也只能執(zhí)行讀t1、讀寫t2的操作。連寫t1都不允許，自然也不能訪問其他表。
這種表鎖是一種處理并發(fā)的方式，但是在InnoDB中常用的是行鎖。
行鎖
我們知道在5.5版本以前 MySQL 的默認(rèn)存儲引擎是 MyISAM，而 MyISAM 和 InnoDB 最大的區(qū)別就是兩個
- 事務(wù)
- 行鎖
其中行鎖是我們今天的主題，如果不了解事務(wù)可以去補(bǔ)習(xí)一下。
其實行鎖就是兩個鎖，你可以理解為 寫鎖(排他鎖 X鎖)和讀鎖(共享鎖 S鎖)
共享鎖（S鎖）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。 也叫做讀鎖：讀鎖是共享的，多個客戶可以同時讀取同一個資源，但不允許其他客戶修改。
排他鎖（X鎖)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。也叫做寫鎖：寫鎖是排他的，寫鎖會阻塞其他的寫鎖和讀鎖。
而行鎖還會引起一個一個很頭疼的問題，那就是死鎖。
如果事務(wù)A對行100加了寫鎖，事務(wù)B對行101加了寫鎖，此時事務(wù)A想要修改行101而事務(wù)B又想修改行100，這樣占有且等待就導(dǎo)致了死鎖問題，而面對死鎖問題就只有檢測和預(yù)防了。
next-key鎖
MVCC 和行鎖是無法解決 幻讀 問題的，這個時候 InnoDB 使用了 一個叫 GAP鎖(間隙鎖) 的東西，它配合 行鎖 形成了 next-key鎖，解決了幻讀的問題。
但是因為它的加鎖規(guī)則，又導(dǎo)致了擴(kuò)大了一些加鎖范圍從而減少數(shù)據(jù)庫并發(fā)能力。具體的加鎖規(guī)則如下：
- 加鎖的基本單位是next-key lock 就是行鎖和GAP鎖結(jié)合。
- 查找過程中訪問到的對象就會加鎖。
- 索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖。
- 索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock退化為間隙鎖。
- 唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
MVCC 解決幻讀的思路比較復(fù)雜，這里就不做過多的驗證。
總結(jié)
對于 MySQL 的索引來說，我給了很多最佳實踐，其實這些最佳實踐都是從原理來的，而 InnoDB 其實就是一個改進(jìn)版的 B+樹，還有存儲索引的結(jié)構(gòu)。弄懂了這些你就會得心應(yīng)手起來。
而對于 MySQL 的鎖，主要就是在行鎖方面，InnoDB 其實就是使用了 行鎖，MVCC還有next-key鎖來實現(xiàn)事務(wù)并發(fā)控制的。
而對于MySQL中最重要的其實就是 鎖和索引 了，因為內(nèi)容太多這篇文章僅僅做一些介紹和簡單的分析，如果想深入了解可以查看相應(yīng)的文章。
作者：FrancisQ
原文：https://juejin.im/post/5db19103e51d452a300b14c9
