上文《關(guān)于 mysql 執(zhí)行流程的解析》中我們主要介紹了sql語(yǔ)句在server層的執(zhí)行過(guò)程

我們?cè)賮?lái)分析一下具體的語(yǔ)句在引擎層的執(zhí)行步驟，CRUD的操作都跟索引相關(guān)，我們先了解一下索引

索引

索引的出現(xiàn)其實(shí)就是為了提高數(shù)據(jù)查詢的效率，就像書的目錄

數(shù)據(jù)結(jié)構(gòu)

常見(jiàn)的數(shù)據(jù)結(jié)構(gòu)有 哈希表、有序數(shù)組和搜索樹

哈希表是一種以鍵 – 值(key-value)存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，我們只要輸入待查找的值即 key， 就可以找到其對(duì)應(yīng)的值即 Value。哈希的思路很簡(jiǎn)單，把值放在數(shù)組里，用一個(gè)哈希函數(shù) 把 key 換算成一個(gè)位置，然后把 value 放在數(shù)組的對(duì)應(yīng)位置

不可避免地，多個(gè) key 值經(jīng)過(guò)哈希函數(shù)的換算，會(huì)出現(xiàn)同一個(gè)值的情況。處理這種情況的 一種方法是，拉出一個(gè)鏈表

哈希表這種結(jié)構(gòu)適用于只有等值查詢的場(chǎng)景

有序數(shù)組在等值查詢和范圍查詢場(chǎng)景中的性能就都非常優(yōu)秀

如果僅僅看查詢效率，有序數(shù)組就很好。但是，在需要更新數(shù)據(jù)的時(shí)候就 麻煩了，你往中間插入一個(gè)記錄就必須得挪動(dòng)后面所有的記錄，成本太高

有序數(shù)組索引只適用于靜態(tài)存儲(chǔ)引擎

二叉搜索樹的特點(diǎn)是:每個(gè)節(jié)點(diǎn)的左兒子小于父節(jié)點(diǎn)，父節(jié)點(diǎn)又小于右兒子

當(dāng)然為了維持 O(log(N)) 的查詢復(fù)雜度，你就需要保持這棵樹是平衡二叉樹。為了做這個(gè) 保證，更新的時(shí)間復(fù)雜度也是 O(log(N))

二叉樹是搜索效率最高的，但是實(shí)際上大多數(shù)的數(shù)據(jù)庫(kù)存儲(chǔ)卻并不使用二叉樹。 其原因是，索引不止存在內(nèi)存中，還要寫到磁盤上

為了讓一個(gè)查詢盡量少地讀磁盤，就必須讓查詢過(guò)程訪問(wèn)盡量少的數(shù)據(jù)塊。那么，我們就不應(yīng)該使用二叉樹，而是要使用“N 叉”樹。這里，“N 叉”樹中的“N”取決于數(shù)據(jù)塊的大小

N 叉樹由于在讀寫上的性能優(yōu)點(diǎn)，以及適配磁盤的訪問(wèn)模式，已經(jīng)被廣泛應(yīng)用在數(shù)據(jù)庫(kù)引擎中了

InnoDB 的索引模型

在 InnoDB 中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲(chǔ)方式的表稱為索引組織表。 InnoDB 使用了 B+ 樹索引模型，所以數(shù)據(jù)都是存儲(chǔ)在 B+ 樹中的

每一個(gè)索引在 InnoDB 里面對(duì)應(yīng)一棵 B+ 樹

根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類型分為主鍵索引和非主鍵索引

主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引

非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級(jí)索引

基于非主鍵索引的查詢需要多掃描一棵索引樹(回表)。因此，我們?cè)趹?yīng)用中應(yīng)該盡量 使用主鍵查詢

索引維護(hù)

B+ 樹為了維護(hù)索引有序性，在插入新值的時(shí)候需要做必要的維護(hù)

如果新插入的 ID 值比原來(lái)的小，就相對(duì)麻煩了，需要邏輯上挪動(dòng)后面的數(shù)據(jù)，空出位置

而更糟的情況是，如果所在的數(shù)據(jù)頁(yè)已經(jīng)滿了，根據(jù) B+ 樹的算法，這時(shí)候需要申請(qǐng) 一個(gè)新的數(shù)據(jù)頁(yè)，然后挪動(dòng)部分?jǐn)?shù)據(jù)過(guò)去。這個(gè)過(guò)程稱為頁(yè)分裂。在這種情況下，性能自然會(huì)受影響。

除了性能外，頁(yè)分裂操作還影響數(shù)據(jù)頁(yè)的利用率。原本放在一個(gè)頁(yè)的數(shù)據(jù)，現(xiàn)在分到兩個(gè)頁(yè)中，整體空間利用率降低大約 50%。

當(dāng)然有分裂就有合并。當(dāng)相鄰兩個(gè)頁(yè)由于刪除了數(shù)據(jù)，利用率很低之后，會(huì)將數(shù)據(jù)頁(yè)做合 并。合并的過(guò)程，可以認(rèn)為是分裂過(guò)程的逆過(guò)程

自增主鍵的插入數(shù)據(jù)模式，正符合了我們前面提到的遞增插入的場(chǎng)景。每次插 入一條新記錄，都是追加操作，都不涉及到挪動(dòng)其他記錄，也不會(huì)觸發(fā)葉子節(jié)點(diǎn)的分裂。

而有業(yè)務(wù)邏輯的字段做主鍵，則往往不容易保證有序插入，這樣寫數(shù)據(jù)成本相對(duì)較高

主鍵長(zhǎng)度越小，普通索引的葉子節(jié)點(diǎn)就越小，普通索引占用的空間也就越小

所以，從性能和存儲(chǔ)空間方面考量，自增主鍵往往是更合理的選擇

有沒(méi)有什么場(chǎng)景適合用業(yè)務(wù)字段直接做主鍵的呢?還是有的。比如，有些業(yè)務(wù)的場(chǎng)景需求 是這樣的:

1.只有一個(gè)索引;

2.該索引必須是唯一索引。

這就是典型的 KV 場(chǎng)景

覆蓋索引

如果執(zhí)行的語(yǔ)句是 select ID from t ，這時(shí)只需要查 ID 的 值，而 ID 的值已經(jīng)在 k 索引樹上了，因此可以直接提供查詢結(jié)果，不需要回表。也就是說(shuō)，在這個(gè)查詢里面，索引 k 已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引

由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段

索引下推

滿足最左前綴原則的時(shí)候，最左前綴可以用于在索引中定位記錄。這時(shí)，你可能要問(wèn)，那些不符合最左前綴的部分，會(huì)怎么樣呢？

MySQL 5.6 引入的索引下推優(yōu)化， 可以在索引遍歷過(guò) 程中，對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿足條件的記錄，減少回表次數(shù)

最左前綴原則

不只是索引的全部定義，只要滿足最左前綴，就可以利用索引來(lái)加速檢索

在建立聯(lián)合索引的時(shí)候，如何安排索引內(nèi)的字段順序?

這里我們的評(píng)估標(biāo)準(zhǔn)是，索引的復(fù)用能力。因?yàn)榭梢灾С肿钭笄熬Y，所以當(dāng)已經(jīng)有了 (a,b) 這個(gè)聯(lián)合索引后，一般就不需要單獨(dú)在 a 上建立索引了。因此，第一原則是，如果通過(guò)調(diào)整順序，可以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的

前綴索引

利用最左前綴原則可以定義字符串的一部分作為索引。默認(rèn)地，如果你創(chuàng)建索引的語(yǔ)句不指定前綴長(zhǎng)度，那么索引就會(huì)包含整個(gè)字符串

但，這同時(shí)帶來(lái)的損失是，可能會(huì)增加額外的記錄掃描次數(shù)，因?yàn)樗饕嗤枰M(jìn)一步比較

使用前綴索引，定義好長(zhǎng)度，就可以做到既節(jié)省空間，又不用額外增加太多的查 詢成本

可以通過(guò)統(tǒng)計(jì)索引上有多少個(gè)不同的值來(lái)判斷要使用多長(zhǎng)的前綴，從而減少掃描次數(shù)

前綴索引對(duì)覆蓋索引的影響

使用前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時(shí)需要考慮的一個(gè)因素

倒序存儲(chǔ)和hash存儲(chǔ)

對(duì)于類似于郵箱這樣的字段來(lái)說(shuō)，使用前綴索引的效果可能還不錯(cuò)。但是，遇到前綴的區(qū) 分度不夠好的情況時(shí)，我們要怎么辦呢?

第一種方式是使用倒序存儲(chǔ)。如果你存儲(chǔ)身份證號(hào)的時(shí)候把它倒過(guò)來(lái)存

第二種方式是使用 hash 字段。你可以在表上再創(chuàng)建一個(gè)整數(shù)字段，來(lái)保存身份證的校驗(yàn)碼，同時(shí)在這個(gè)字段上創(chuàng)建索引

免費(fèi)學(xué)習(xí)視頻教程推薦：mysql視頻教程