MySQL 查詢優化實戰技巧：EXPLAIN 深度解讀與索引優化指南

四、索引優化實戰技巧

1. 同類型比較： 字符串比字符串、數字比數字，隱式轉換導致索引失效 (WHERE phone = 13800000000 字符串欄位傳數字 → 全表掃描)。
2. 索引列參與運算失效： WHERE YEAR(create_time) = 2023 → 全表掃描。改寫 WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。
3. LIKE 百分號前置失效： WHERE name LIKE '%abc' 無法走索引。若必須模糊前綴，考慮全文索引或反轉存儲。
4. 數值運算優於字符串： 整數比較比字符串快 10 倍+，IPv4 用 INT UNSIGNED、狀態碼用 TINYINT。
5. 類型選小不選大： TINYINT 夠用別用 INT，VARCHAR(20) 夠用別用 VARCHAR(255)，減少 IO/記憶體/索引大小。
6. NOT NULL + DEFAULT： 省去 NULL 判斷、索引統計更準、查詢優化器決策更優。
7. ENUM 內部為整數： 處理速度快，但修改枚舉值需 DDL，慎用。
8. PROCEDURE ANALYSE()： SELECT * FROM t PROCEDURE ANALYSE(16,256); 建議 ENUM 化欄位 (基數≤16、長度≤256)。
9. OPTIMIZE TABLE： 碎片化嚴重表 (頻繁 UPDATE/DELETE 變長欄位) 定期整理，回收空間、重建索引。
10. 慎用 BLOB/TEXT 索引： 僅支援前綴索引 (INDEX idx_content(content(100)))，全文搜尋用 FULLTEXT。

五、強制索引語法

-- 強制使用
SELECT * FROM t FORCE INDEX (idx_name) WHERE name = 'abc';

-- 忽略索引
SELECT * FROM t IGNORE INDEX (idx_name) WHERE name = 'abc';

-- 建議使用
SELECT * FROM t USE INDEX (idx_name) WHERE name = 'abc';

六、避坑檢查清單

1. EXPLAIN 顯示 type=ALL 或 type=index → 必須優化
2. key 為 NULL 但 possible_keys 有值 → 索引未命中，檢查隱式轉換/運算/函數
3. Extra 含 Using temporary、Using filesort → 索引順序不符合 ORDER BY/GROUP BY，調整索引欄位順序
4. rows 極大 (百萬+) → 檢查 WHERE 條件是否下推、分區裁剪是否生效
5. JOIN 關聯欄位類型/長度/字符集不一致 → 隱式轉換導致 ref 退化為 ALL

熟練掌握 EXPLAIN 語言，像醫生讀片子一樣診斷 SQL 健康狀態，配合索引設計原則與 SQL 改寫技巧，解決 90% 的查詢效能問題。