2.3.5. 索引選擇

MySQL是如何選擇索引的？

優化器決定了具體某一索引的選擇，也就是常說的執行計劃。而優化器的選擇是基于成本（cost），哪個索引的成本越低，優先使用哪個索引。

SQL 優化器會分析所有可能的執行計劃，選擇成本最低的執行，這種優化器稱之為：CBO（Cost-based Optimizer，基于成本的優化器）。

Cost  = Server Cost + Engine Cost= CPU Cost + IO Cost

CPU Cost 表示計算的開銷，比如索引鍵值的比較、記錄值的比較、結果集的排序……這些操作都在 Server 層完成；

IO Cost 表示引擎層 IO 的開銷，MySQL 8.0 可以通過區分一張表的數據是否在內存中，分別計算讀取內存 IO 開銷以及讀取磁盤 IO 的開銷。

優化器認為一條 SQL 需要創建基于磁盤的臨時表，這時的成本是最大的，索引鍵值的比較、記錄之間的比較，其實開銷是非常低的，但如果要比較的記錄數非常多，則成本會變得非常大。

MySQL索引出錯案例分析

索引創建在有限狀態上：

B+ 樹索引通常要建立在高選擇性的字段或字段組合上，如性別、訂單 ID、日期等，因為這樣每個字段值大多并不相同。像性別這種字段只有男女兩種，是低選擇性的字段，因此無須在性別字段上創建索引。

在有些低選擇性的列上，是有必要創建索引的。比如電商的核心業務表。

在電商業務中會有一個這樣的邏輯：會定期掃描支付狀態為支付中的訂單，然后強制讓其關閉，從而釋放庫存，給其他有需求的買家進行購買。一般僅為已完成、支付中、超時已關閉這幾種。絕大部分都是已完成，只有絕少部分因為系統故障原因，會在 15 分鐘后還沒有完成訂單，因此訂單狀態是存在數據傾斜的。

例如支付狀態只有已完成、支付中、超時已關閉三種，有一百萬條數據，優化器會認為每個狀態占用三分之一數據，使用全表掃描，避免二級索引回表效率會更高。

然而，由于數據傾斜，訂單狀態為支付中的數據非常少（例如有1萬條），這時根據索引的查詢效率會更高。

這時可以利用 MySQL 8.0 的直方圖功能，創建一個直方圖，讓優化器知道數據的分布，從而更好地選擇執行計劃。

建立索引時要注意的事：

• 經常頻繁用作查詢條件的字段應酌情考慮為其創建索引。
• 表的主外鍵或連表字段，必須建立索引，因為能很大程度提升連表查詢的性能。
• 建立索引的字段，一般值的區分性要足夠高，這樣才能提高索引的檢索效率。
• 建立索引的字段，值不應該過長，如果較長的字段要建立索引，可以選擇前綴索引。
• 建立聯合索引，應當遵循最左前綴原則，將多個字段之間按優先級順序組合。
• 經常根據范圍取值、排序、分組的字段應建立索引，因為索引有序，能加快排序時間。
• 對于唯一索引，如果確認不會利用該字段排序，那可以將結構改為Hash結構。
• 盡量使用聯合索引代替單值索引，聯合索引比多個單值索引查詢效率要高。

同時，還需有些注意點：

• 值經常會增刪改的字段，不合適建立索引，因為每次改變后需維護索引結構。
• 一個字段存在大量的重復值時，不適合建立索引，比如之前舉例的性別字段。
• 索引不能參與計算，因此經常帶函數查詢的字段，并不適合建立索引。
• 建立聯合索引時，一定要考慮優先級，查詢頻率最高的字段應當放首位。
• 當表的數據較少，不應當建立索引，因為數據量不大時，維護索引反而開銷更大。