MySQL 索引設計與優化實戰：從最左前綴到慢查詢優化全流程

MySQL 索引設計與優化實戰：從最左前綴到慢查詢優化全流程

索引是查詢性能的核心杠桿。本文系統總結索引創建原則、失效場景、EXPLAIN 實戰分析，並給出慢查詢優化標準化步驟，附大量實測案例。

一、索引創建四大黃金原則

1. 最左前綴匹配：聯合索引 (a,b,c,d)，查詢 a=1 AND b=2 AND c>3 AND d=4 僅用到 (a,b,c)，d 失效；調整順序為 (a,b,d,c) 可全部命中
2. 高區分度優先：區分度 = COUNT(DISTINCT col)/COUNT(*)，唯一鍵=1，性別類字段接近 0。Join 字段建議 > 0.1（平均 10 行掃描 1 行）
3. 索引列保持「乾淨」：不參與計算/函數/類型轉換。
   ❌ FROM_UNIXTIME(create_time) = '2014-05-29'
   ✅ create_time = UNIX_TIMESTAMP('2014-05-29')
4. 擴展索引而非新建：已有 (a) 需 (a,b) 時，直接 ALTER TABLE ... ADD INDEX (a,b) 覆蓋舊索引

二、索引失效常見陷阱

三、EXPLAIN 實戰案例解析

以 rental 表聯合索引 idx_rental_date(rental_date, inventory_id, customer_id) 為例：

1. 全值匹配（等值可亂序）

EXPLAIN SELECT * FROM rental 
WHERE rental_date='2005-05-25' AND inventory_id=373 AND customer_id=343;
-- type=ref, key=idx_rental_date, rows=1 ✓

2. 唯一索引升級為 const

ALTER TABLE rental ADD UNIQUE INDEX idx_rental_date(...);
-- 同條件下 type=const, rows=1 ✓

3. 范圍查詢停止匹配

EXPLAIN SELECT * FROM rental WHERE customer_id >= 373 AND customer_id <= 400;
-- type=range, key=idx_fk_customer_id, rows=745

4. 覆蓋索引

EXPLAIN SELECT last_update FROM payment 
WHERE payment_date='2006-02-14' AND amount=3.98;
-- Extra=Using index，無需回表 ✓

5. 列前綴索引

CREATE INDEX idx_title_desc_part ON film_text(title(10), description(20));
EXPLAIN SELECT title FROM film_text WHERE title LIKE 'AFRICAN%';
-- key=idx_title_desc_part, type=range ✓

四、慢查詢優化標準化步驟 (SOP)

1. 基線測試：SET SESSION query_cache_type=OFF; SELECT SQL_NO_CACHE ... 確認真慢
2. 單表 WHERE 篩選：對驅動表各字段單獨查詢，找區分度最高者（返回行數最少）
3. EXPLAIN 驗證：檢查 type、key、rows、Extra 是否符合預期
4. ORDER BY LIMIT 優先：排序字段建索引，避免 filesort
5. 理解業務場景：區分 OLTP/OLAP、讀寫比例、基數分布
6. 按原則加索引：參考上述四大原則，優先覆蓋索引
7. 觀察效果：重跑 EXPLAIN 與實際耗時，未達標回第 1 步

五、關鍵狀態變量監控

SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key 高 = 索引命中好
-- Handler_read_rnd_next 高 = 大量全表掃描，索引失效或 SQL 問題