站長資訊網
上文《關于 mysql 執行流程的解析》中我們主要介紹了sql語句在server層的執行過程
我們再來分析一下具體的語句在引擎層的執行步驟，CRUD的操作都跟索引相關，我們先了解一下索引
索引
索引的出現其實就是為了提高數據查詢的效率，就像書的目錄
數據結構
常見的數據結構有 哈希表、有序數組和搜索樹
哈希表是一種以鍵 – 值(key-value)存儲數據的結構，我們只要輸入待查找的值即 key， 就可以找到其對應的值即 Value。哈希的思路很簡單，把值放在數組里，用一個哈希函數 把 key 換算成一個位置，然后把 value 放在數組的對應位置
不可避免地，多個 key 值經過哈希函數的換算，會出現同一個值的情況。處理這種情況的 一種方法是，拉出一個鏈表
哈希表這種結構適用于只有等值查詢的場景
有序數組在等值查詢和范圍查詢場景中的性能就都非常優秀
如果僅僅看查詢效率，有序數組就很好。但是，在需要更新數據的時候就 麻煩了，你往中間插入一個記錄就必須得挪動后面所有的記錄，成本太高
有序數組索引只適用于靜態存儲引擎
二叉搜索樹的特點是:每個節點的左兒子小于父節點，父節點又小于右兒子
當然為了維持 O(log(N)) 的查詢復雜度，你就需要保持這棵樹是平衡二叉樹。為了做這個 保證，更新的時間復雜度也是 O(log(N))
二叉樹是搜索效率最高的，但是實際上大多數的數據庫存儲卻并不使用二叉樹。 其原因是，索引不止存在內存中，還要寫到磁盤上
為了讓一個查詢盡量少地讀磁盤，就必須讓查詢過程訪問盡量少的數據塊。那么，我們就不應該使用二叉樹，而是要使用“N 叉”樹。這里，“N 叉”樹中的“N”取決于數據塊的大小
N 叉樹由于在讀寫上的性能優點，以及適配磁盤的訪問模式，已經被廣泛應用在數據庫引擎中了
InnoDB 的索引模型
在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表。 InnoDB 使用了 B+ 樹索引模型，所以數據都是存儲在 B+ 樹中的
每一個索引在 InnoDB 里面對應一棵 B+ 樹
根據葉子節點的內容，索引類型分為主鍵索引和非主鍵索引
主鍵索引的葉子節點存的是整行數據。在 InnoDB 里，主鍵索引也被稱為聚簇索引
非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級索引
基于非主鍵索引的查詢需要多掃描一棵索引樹(回表)。因此，我們在應用中應該盡量 使用主鍵查詢
索引維護
B+ 樹為了維護索引有序性，在插入新值的時候需要做必要的維護
如果新插入的 ID 值比原來的小，就相對麻煩了，需要邏輯上挪動后面的數據，空出位置
而更糟的情況是，如果所在的數據頁已經滿了，根據 B+ 樹的算法，這時候需要申請 一個新的數據頁，然后挪動部分數據過去。這個過程稱為頁分裂。在這種情況下，性能自然會受影響。
除了性能外，頁分裂操作還影響數據頁的利用率。原本放在一個頁的數據，現在分到兩個頁中，整體空間利用率降低大約 50%。
當然有分裂就有合并。當相鄰兩個頁由于刪除了數據，利用率很低之后，會將數據頁做合 并。合并的過程，可以認為是分裂過程的逆過程
自增主鍵的插入數據模式，正符合了我們前面提到的遞增插入的場景。每次插 入一條新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂。
而有業務邏輯的字段做主鍵，則往往不容易保證有序插入，這樣寫數據成本相對較高
主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小
所以，從性能和存儲空間方面考量，自增主鍵往往是更合理的選擇
有沒有什么場景適合用業務字段直接做主鍵的呢?還是有的。比如，有些業務的場景需求 是這樣的:
1.只有一個索引;
2.該索引必須是唯一索引。
這就是典型的 KV 場景
覆蓋索引
如果執行的語句是 select ID from t ，這時只需要查 ID 的 值，而 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢里面，索引 k 已經“覆蓋了”我們的查詢需求，我們稱為覆蓋索引
由于覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段
索引下推
滿足最左前綴原則的時候，最左前綴可以用于在索引中定位記錄。這時，你可能要問，那些不符合最左前綴的部分，會怎么樣呢？
MySQL 5.6 引入的索引下推優化， 可以在索引遍歷過 程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數
最左前綴原則
不只是索引的全部定義，只要滿足最左前綴，就可以利用索引來加速檢索
在建立聯合索引的時候，如何安排索引內的字段順序?
這里我們的評估標準是，索引的復用能力。因為可以支持最左前綴，所以當已經有了 (a,b) 這個聯合索引后，一般就不需要單獨在 a 上建立索引了。因此，第一原則是，如果通過調整順序，可以少維護一個索引，那么這個順序往往就是需要優先考慮采用的
前綴索引
利用最左前綴原則可以定義字符串的一部分作為索引。默認地，如果你創建索引的語句不指定前綴長度，那么索引就會包含整個字符串
但，這同時帶來的損失是，可能會增加額外的記錄掃描次數，因為索引相同需要進一步比較
使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查 詢成本
可以通過統計索引上有多少個不同的值來判斷要使用多長的前綴，從而減少掃描次數
前綴索引對覆蓋索引的影響
使用前綴索引就用不上覆蓋索引對查詢性能的優化了，這也是你在選擇是否使用前綴索引時需要考慮的一個因素
倒序存儲和hash存儲
對于類似于郵箱這樣的字段來說，使用前綴索引的效果可能還不錯。但是，遇到前綴的區 分度不夠好的情況時，我們要怎么辦呢?
第一種方式是使用倒序存儲。如果你存儲身份證號的時候把它倒過來存
第二種方式是使用 hash 字段。你可以在表上再創建一個整數字段，來保存身份證的校驗碼，同時在這個字段上創建索引
免費學習視頻教程推薦：mysql視頻教程
