mysql索引詳解（總結）

上文《關於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视频教程

以上是mysql索引詳解（總結）的詳細內容。更多資訊請關注PHP中文網其他相關文章！