MySQL 索引建立核心規則與優化實戰

MySQL 索引建立核心規則與優化實戰

索引是資料庫性能優化的關鍵，MySQL 採用 B+ 樹作為核心數據結構。掌握索引建立規則，能讓查詢速度提升數量級。以下整理五大核心原則：

1. 最左前綴原則與範圍查詢停止匹配

聯合索引 (a,b,c,d) 中，MySQL 會從最左列開始匹配，遇到範圍查詢（>、<、between、like）即停止。
範例：a=1 AND b=2 AND c>3 AND d=4，若索引為 (a,b,c,d)，d 用不到索引；調整為 (a,b,d,c) 則全用上。a,b,d 順序可任意，優化器會自動重排。

2. 等值與 IN 查詢順序不影響索引使用

a=1 AND b=2 AND c=3 建立 (a,b,c) 索引，列順序任意，優化器會自動識別最優路徑。

3. 選擇高區分度列作為索引

區分度公式：count(DISTINCT col) / count(*)。
- 唯一鍵區分度 = 1（最佳）
- 狀態、性別等低基數列區分度接近 0，大數據下索引失效
- 經驗值：JOIN 關聯欄位建議區分度 > 0.1（平均掃描 10 行命中 1 行）

4. 索引列保持「乾淨」，避免函數運算

錯誤： FROM_UNIXTIME(create_time) = '2014-05-29' — 全表掃描
正確： create_time = UNIX_TIMESTAMP('2014-05-29') — 走索引
原理：B+ 樹存儲原始值，函數運算導致無法利用索引有序性。

5. 擴展現有索引優於新建索引

表已有 a 索引，需加 (a,b) 索引時，直接 ALTER TABLE ... ADD INDEX idx_a_b (a,b) 擴展原索引，避免重複維護成本。

總結

索引設計需結合業務查詢模式，遵循「最左前綴、高區分度、列乾淨、擴展優於新建」四大原則，配合 EXPLAIN 分析執行計劃，持續優化慢查詢。