深入解析 MySQL 索引原理與實戰應用指南

MySQL 索引核心概念與分類

在 MySQL 資料庫中，索引是提升查詢效能的關鍵機制。主要分為兩大類型：Hash 索引與B+ 樹索引（常稱 B-Tree 索引）。InnoDB 與 MyISAM 儲存引擎預設使用 B+ 樹索引，而 Memory 引擎支援 Hash 索引。理解兩者差異，能幫助你在不同場景下做出最佳選擇。

B+ 樹索引的適用場景

B+ 樹索引因其有序特性，支援多種查詢模式：

1. 全值匹配

如 order_id = "123"，可直接定位唯一記錄。

2. 最左前綴匹配

聯合索引 (user_id, date) 中，僅查詢 user_id 可用索引，但單獨查詢 date 則無法使用。

3. 列前綴查詢

order_sn LIKE '134%' 可利用索引，但 LIKE '%134' 則不行。

4. 範圍查詢

create_time > '2015-01-09' AND create_time < '2015-01-10' 可使用索引範圍掃描。

5. 精確匹配左列 + 範圍匹配右列

如 user_id = 1 AND create_time > '2016-09-18'，user_id 精確匹配，create_time 範圍匹配。

6. 覆蓋索引

查詢欄位全部包含在索引中，無需回表，大幅提升效能。

B+ 樹索引的限制

• 必須從最左列開始：聯合索引 (order_id, create_time)，單查 create_time 無法使用索引。
• 不能跳過索引列：索引 (date, name, phone)，查詢 date 和 phone 時，僅 date 生效。
• NOT IN、!= 操作無法使用索引。
• 範圍查詢右側列失效：user_id = 1 AND age > 20 AND name = 'Tom' 中，name 索引失效。

Hash 索引特性與限制

Hash 索引基於雜湊表實現，僅支援等值查詢（=、IN）。特點如下：

• 存儲雜湊碼，查找極快（O(1)）。
• 不支援排序、範圍查詢、部分匹配。
• 需兩次讀取：先讀雜湊碼定位行，再讀取行資料。
• 不適用於選擇性低的欄位（如性別），易產生大量雜湊衝突。

為什麼需要索引？

1. 減少掃描資料量：索引體積遠小於資料表。
2. 避免臨時表排序：索引天然有序，可直接滿足 ORDER BY。
3. 隨機 I/O 轉順序 I/O：提升磁碟讀取效率。

索引並非越多越好

• 增加寫入成本（INSERT/UPDATE/DELETE 需維護索引）。
• 過多索引延長優化器分析時間。

建立索引的最佳策略

1. 避免在索引列上使用函數或運算式

錯誤：WHERE TO_DAYS(out_date) - TO_DAYS(NOW()) <= 30
正確：WHERE out_date < DATE_ADD(NOW(), INTERVAL 30 DAY)

2. 控制索引長度

InnoDB 單列索引長度限制 767 字節（或 3072 字節，視版本而定），建議對 CHAR/VARCHAR 設定前綴長度（如 10-15 字元）。

3. 關注選擇性

選擇性 = COUNT(DISTINCT column) / COUNT(*)，越接近 1 越適合建索引。

4. 聯合索引列順序原則

1. 常用於 WHERE 條件的列優先。
2. 選擇性高的列優先。
3. 長度小的列優先（減少索引體積）。