MySQL 查詢優化器是 SQL 執行效能的核心大腦，其決策品質直接決定查詢是「秒回」還是「跑不完」。理解優化器如何評估索引選擇性、何時放棄索引掃全表、如何決定連接順序、何種寫法會誤導優化器，是 DBA 與開發者進行 SQL 調優的必修課。本文系統解析優化器工作原理、EXPLAIN 實戰解讀、常見誤導優化器的寫法與修正、以及 STRAIGHT_JOIN、IGNORE INDEX 等干預手段。
單一目標：以最小代價找到符合條件的列 → 盡早、盡多地過濾掉不符合條件的列。
WHERE 1=0 → 直接回傳空結果，不存取表WHERE、JOIN ON、ORDER BY、GROUP BY 中可用的索引EXPLAIN SELECT * FROM orders
WHERE customer_id = 1001 AND status = 'paid'
ORDER BY created_at DESC LIMIT 10;| 欄位 | 含義 | 優化關注點 |
|---|---|---|
| id | 查詢序號（含子查詢/UNION） | 同 id 從上往下執行 |
| select_type | SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION... | DERIVED 衍生表常需物化，效能較差 |
| table | 存取的表名/別名 | 確認掃描順序 |
| partitions | 分區表命中的分區 | 分區裁剪是否生效 |
| type | 存取類型：system > const > eq_ref > ref > range > index > ALL | 避免 ALL（全表掃描），至少達 ref/range |
| possible_keys | 理論可用索引 | 為空表示無索引可用 |
| key | 實際選用索引 | 與 possible_keys 差異大需調查 |
| key_len | 實際使用的索引長度（位元組） | 複合索引前綴長度是否足夠 |
| ref | 比較參考：const/欄位/函數 | func 表示函數結果，可能失效 |
| rows | 預估掃描列數 | 越小越好，若遠大於實際回傳需優化 |
| filtered | 表層過濾後剩餘比例 | 低值表示索引選擇性差 |
| Extra | 額外資訊：Using index/Using where/Using temporary/Using filesort... | Using filesort/temporary 需優化 |
-- 原始：全表掃描
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type=ALL, rows=1000000
-- 優化：字面日期範圍，用到索引
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- type=range, key=idx_created_at, rows=50000| 誤導寫法 | 原因 | 修正寫法 |
|---|---|---|
WHERE YEAR(date_col) = 2024 | 索引欄包裹函數，逐列計算 | WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01' |
WHERE col * 2 > 100 | 索引欄參與運算 | WHERE col > 50 |
WHERE col LIKE '%keyword%' | 前導萬用字元，無法用索引 | 全文索引 MATCH(col) AGAINST('keyword') 或前綴 LIKE 'keyword%' |
WHERE col = 'string' COLLATE utf8_bin | 字元集/排序規則不匹配 | 統一表/欄位/連線字元集，或建立對應排序規則索引 |
JOIN 時 ON 兩側型別不同 | 隱含轉換導致索引失效 | CAST 統一型別或 ALTER TABLE 修正欄位型別 |
WHERE col IN (subquery) | 舊版本子查詢優化差 | 改寫為 JOIN 或 EXISTS（5.6+ 已優化） |
ANALYZE TABLE tbl_name：更新索引基數統計（INFORMATION_SCHEMA.STATISTICS）myisamchk --analyze / innodb_stats_persistent=ON（5.6+）持久化統計innodb_stats_auto_recalc=ON：自動重算（預設 10% 變更觸發）| 手段 | 語法 | 適用場景 | 風險 |
|---|---|---|---|
| 強制索引 | FORCE INDEX (idx_name) | 優化器錯選全表掃描，確知索引極佳 | 資料分布變化後可能反而變差 |
| 忽略索引 | IGNORE INDEX (idx_name) | 優化器錯選低選擇性索引 | 同左 |
| 指定連接順序 | STRAIGHT_JOIN 或 SELECT STRAIGHT_JOIN ... | 多表連接優化器順序錯誤 | 寫死順序，資料量變化失效 |
| 關鍵字提示 | SQL_SMALL_RESULT、SQL_BIG_RESULT、SQL_BUFFER_RESULT | 影響暫存表選擇/排序策略 | 版本相容性不一 |
鐵律：干預前必須有 EXPLAIN 證據、基準測試對比、回滾計畫。優化器版本迭代快，硬編碼 Hint 往往成為技術債。
DELETE FROM tbl; -- 瞬間完成，重建空檔案，AUTO_INCREMENT 歸零
DELETE FROM tbl WHERE 1>0; -- 逐列刪除，回傳實際影響列數，保留 AUTO_INCREMENT（MyISAM）-- 危險：更新索引欄位，新值仍符合 WHERE 條件
UPDATE t SET key_col = key_col + 1 WHERE key_col > 0;
-- 修正：讓優化器無法用索引
UPDATE t SET key_col = key_col + 1 WHERE key_col + 0 > 0;
-- 或升級 5.6+ 內建保護機制-- 錯誤：優化器視 RAND() 為常數，不真正隨機
SELECT * FROM t ORDER BY RAND();
-- 修正：引用表欄位欺騙優化器
SELECT * FROM t ORDER BY RAND() * id; -- id 為任意欄位Handler_read_key, Handler_read_rnd_next）而非單純看時間MySQL 優化器雖然日益智能，但統計資訊準確度、SQL 寫法是否友善、索引設計是否匹配查詢模式仍是決定執行計畫優劣的三大支柱。掌握 EXPLAIN 深度解讀、避開函數包裹索引欄/前導萬用字元/隱含型別轉換等「優化器殺手」，並在必要時以數據為憑慎用 Hint 干預，才能駕馭優化器穩定產出高效執行計畫。記住：最好的優化是寫出讓優化器「看得懂、估得準、選得對」的 SQL。
