MySQL 索引選擇黃金法則：從原理到實戰的效能優化指南

MySQL 索引選擇黃金法則：從原理到實戰的效能優化指南

索引是查詢效能的生命線，但濫建索引反而拖垮寫入效能。本文提煉 7 條索引選擇鐵律，結合 EXPLAIN 實戰分析，助你精準命中最優索引策略。

一、索引基礎設施限制

• MyISAM、BDB、InnoDB、MEMORY 單表至少支援 16 個索引；
• 建立/刪除語法：

CREATE INDEX idx_id ON emp (id);
DROP INDEX idx_id ON emp;

二、EXPLAIN：優化入口的核心工具

EXPLAIN SELECT * FROM emp WHERE year >= 2014;

重點關注 type（all/index/range/ref/eq_ref/const/system）、key（實際用到的索引）、rows（預估掃描行數）、Extra（Using where/Using index/Using filesort 等）。

三、索引選擇七大鐵律

1. 索引「條件列」，而非「選擇列」
   SELECT name FROM emp WHERE year >= 2014 → 索引應建在 year，而非 name。
2. 高基數欄位優先
   唯一性越強（基數越大），索引選擇性越高，回表越少。性別、狀態碼等低基數欄位建索引意義極小。
3. 最左前綴原則
   複合索引 (a,b,c) 僅能支援 a、a,b、a,b,c 的查詢，b、c、b,c 無法命中。
4. 覆蓋索引避免回表
   查詢欄位全包含於索引中，Extra 顯示 Using index，極大提升效能。
5. 範圍查詢後的欄位無法用索引
   WHERE a=1 AND b>2 AND c=3 中，c 即使在複合索引中也失效。
6. !=、<、>、IS NULL/IS NOT NULL 通常導致全表掃描
   除非配合覆蓋索引或強制提示。
7. B-Tree vs Hash 索引選型
   B-Tree 支援範圍/排序/分組；Hash 僅支援等值查詢（MEMORY 引擎預設），範圍查詢會退化全表掃描。

四、優化器自動選擇索引的邏輯

• 多個索引可用時，選擇預估掃描行數最少者；
• MIN()/MAX() 可直接走索引極值，無需掃描；
• ORDER BY/GROUP BY 若符合最左前綴，可直接利用索引有序性避免 filesort；
• 純索引欄位查詢（如 SELECT key_part3 FROM t WHERE key_part1=1）可直接在索引樹完成，不訪問資料頁。

五、實戰建議

1. 以 EXPLAIN 為準，而非經驗臆測；
2. 定期 ANALYZE TABLE 更新統計資訊，避免優化器誤判；
3. 大表新增索引建議使用 pt-online-schema-change 避免鎖表；
4. 定期清理未使用索引（sys.schema_unused_indexes 視圖輔助）。