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

我們再來分析一下具體的語句在引擎層的執(zhí)行步驟，CRUD的操作都跟索引相關(guān)，我們先了解一下索引

索引

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

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

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

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

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

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

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

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

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

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

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

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

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

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

InnoDB 的索引模型

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

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

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

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

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

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

索引維護(hù)

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

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

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

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

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

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

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

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

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

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

1.只有一個索引;

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

這就是典型的 KV 場景

覆蓋索引

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

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

索引下推

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

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

最左前綴原則

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

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

這里我們的評估標(biāo)準(zhǔn)是，索引的復(fù)用能力。因為可以支持最左前綴，所以當(dāng)已經(jīng)有了 (a,b) 這個聯(lián)合索引后，一般就不需要單獨在 a 上建立索引了。因此，第一原則是，如果通過調(diào)整順序，可以少維護(hù)一個索引，那么這個順序往往就是需要優(yōu)先考慮采用的

前綴索引

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

但，這同時帶來的損失是，可能會增加額外的記錄掃描次數(shù)，因為索引相同需要進(jìn)一步比較

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

可以通過統(tǒng)計索引上有多少個不同的值來判斷要使用多長的前綴，從而減少掃描次數(shù)

前綴索引對覆蓋索引的影響

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

倒序存儲和hash存儲

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

第一種方式是使用倒序存儲。如果你存儲身份證號的時候把它倒過來存

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

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