MySQL 索引深度解析：原理、設計原則、B-Tree 與 Hash 對比實戰

MySQL 索引深度解析：從原理到實戰優化

索引是資料庫效能的靈魂。本文系統梳理 MySQL 索引底層結構、設計六大黃金原則、B-Tree 與 Hash 索引適用邊界、叢集與二級索引架構，並給出避坑清單，助您從「會建索引」進階到「懂索引優化」。

索引概覽：類型與儲存引擎支援

• 所有列型別可建索引，但各引擎對表最大索引數、總長度有限制 (最少 16 個/256 bytes)。
• 前綴索引：CHAR/VARCHAR 可指定長度，BLOB/TEXT 必須指定長度。MyISAM/InnoDB 5.1+ 前綴上限 1000 bytes (注意多位元組字符集換算)。
• 全文索引：僅 MyISAM (5.6 前) 及 InnoDB 5.6+ 支援，針對 CHAR/VARCHAR/TEXT，不支援前綴。
• 空間索引：僅 MyISAM 支援 SPATIAL 類型，採用 R-Tree。
• Memory 引擎：預設 Hash 索引，可指定 USING BTREE。

索引設計六大黃金原則

1. 為 WHERE/JOIN/ORDER BY 列建索引，而非 SELECT 列

索引作用於過濾、關聯、排序階段；輸出列走不走索引由覆蓋索引決定。

2. 高選擇性優先：唯一值越多，索引效果越好

性別、狀態碼等低基數列建索引幾無收益；唯一鍵、身分證、手機號等高基數列效果最佳。

3. 短索引原則：定長足夠區分即可

CHAR(200) 列若前 20 字元已唯一，建 INDEX(name(20)) 可大幅縮小索引體積、提升緩存命中率、加快比較速度。

4. 最左前綴原則：複合索引 (a,b,c) 等於擁有 (a)、(a,b)、(a,b,c) 三個索引

查詢條件必須從索引最左列開始連續匹配；WHERE b=1 或 WHERE a=1 AND c=3 皆無法用到 (a,b,c) 索引。

5. 克制過度索引：每個索引皆有維護成本

• 寫入 (INSERT/UPDATE/DELETE) 需同步更新所有索引，索引越多寫入越慢。
• 優化器生成執行計畫時需評估所有索引，索引過多反而誤導優化器。
• 若新索引是現有複合索引的最左前綴，則屬冗餘，勿建。

6. 考慮比較運算子：索引支援 <, <=, =, >=, >, BETWEEN, IN, LIKE 'prefix%'

LIKE '%suffix'、STRCMP()、函數運算 (如 WHERE YEAR(date)=2023) 皆導致索引失效。

B-Tree vs Hash 索引：核心差異與選擇

InnoDB 叢集索引與二級索引架構

• 叢集索引：資料與主鍵索引同頁儲存 (B+Tree 葉子節點即資料頁)。主鍵 → 唯一非空索引 → 隱藏 6-byte RowID 依序選擇。
• 二級索引：葉子節點儲存主鍵值而非物理地址。查詢需「回表」：先走二級索引找主鍵，再回叢集索引取資料。
• 覆蓋索引優化：SELECT id, name FROM t WHERE name='x' 若有 INDEX(name, id)，二級索引葉子已包含所有需求列，免回表，效能提升數量級。

實戰避坑清單

1. 隱式轉換殺索引：WHERE phone=13800000000 (phone 為 VARCHAR) 導致全表掃描，必須 WHERE phone='13800000000'。
2. 函數/運算殺索引：WHERE DATE(create_time)='2023-01-01' → 改 WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02'。
3. OR 兩側索引不全：WHERE a=1 OR b=2 若 a、b 無複合索引，通常全表掃描；改 UNION ALL 或建 INDEX(a,b) 配合條件下推 (ICP)。
4. 分頁深度大時：LIMIT 1000000, 10 仍需掃描 100 萬行；改用延遲關聯或游標式分頁 (WHERE id > last_id LIMIT 10)。
5. 統計資訊過期：大量寫入後執行 ANALYZE TABLE 更新統計，避免優化器誤判。

總結

索引設計是「空間換時間」的藝術。掌握 B+Tree 結構特性、最左前綴、覆蓋索引、回表代價，結合 EXPLAIN 觀察 type、key、rows、Extra 四大指標，持續迭代索引策略，才能在業務增長中保持查詢效能線性穩定。