MySQL USE INDEX 強制指定索引實戰：優化器誤判時的精準干預與百倍效能提升

MySQL 優化器並非全知全能，當 ORDER BY 干擾索引選擇導致掃描行數從 757 暴增至 22 萬時，USE INDEX 成為精準干預的手術刀。本文通過真實線上案例，演示「EXPLAIN 發現誤判 → USE INDEX 強制修正 → 效能驗證」的完整優化閉環。

案例背景：arena_match_index 表結構

CREATE TABLE arena_match_index (
    tid INT UNSIGNED NOT NULL DEFAULT 0,
    mid INT UNSIGNED NOT NULL DEFAULT 0,
    `group` INT UNSIGNED NOT NULL DEFAULT 0,
    round TINYINT UNSIGNED NOT NULL DEFAULT 0,
    day DATE NOT NULL DEFAULT '0000-00-00',
    begintime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    UNIQUE KEY tm (tid, mid),
    KEY mid (mid),
    KEY begintime (begintime),
    KEY dg (day, `group`),      -- 聯合索引：天 + 組
    KEY td (tid, day)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

問題 SQL：ORDER BY 導致優化器選錯索引

SELECT round FROM arena_match_index 
WHERE day='2010-12-31' AND `group`=18 AND begintime<'2010-12-31 12:14:28' 
ORDER BY begintime LIMIT 1;

優化器選擇：begintime 索引 → 掃描 226,480 行 → 30 秒+

EXPLAIN ... 
type: range, key: begintime, rows: 226480, Extra: Using where

原因：ORDER BY begintime 讓優化器誤以為走 begintime 索引可避免 filesort，實際上 WHERE 條件中 day 和 group 是高選擇性等值條件，應優先走 dg 聯合索引。

強制指定索引：USE INDEX (dg)

EXPLAIN SELECT round FROM arena_match_index USE INDEX (dg)
WHERE day='2010-12-31' AND `group`=18 AND begintime<'2010-12-31 12:14:28' 
ORDER BY begintime LIMIT 1;

優化後：dg 索引 → 掃描 757 行 → 毫秒級返回

type: ref, key: dg, key_len: 7, rows: 757, Extra: Using where; Using filesort

效能提升：掃描行數減少 99.7%，耗時從 30 秒降至毫秒級（百倍+提升）。

驗證：去掉 ORDER BY 後優化器自動選對

EXPLAIN SELECT round FROM arena_match_index 
WHERE day='2010-12-31' AND `group`=18 AND begintime<'2010-12-31 12:14:28' LIMIT 1;
type: ref, key: dg, rows: 717, Extra: Using where

證明：ORDER BY 是干擾優化器決策的關鍵因素。

USE INDEX / FORCE INDEX / IGNORE INDEX 三兄弟

最佳實踐與避坑指南

1. 先 EXPLAIN 再干預——盲目 FORCE INDEX 是釀成事故的捷徑。
2. 優先優化索引設計——調整列順序、增加覆蓋索引，讓優化器自然選對，優於 Hint。
3. 業務代碼中慎用 Hint——表結構變更時 Hint 可能失效或反作用，建議封裝在存儲過程或 DAO 層並加註釋。
4. 定期巡檢 Hint 失效：SELECT * FROM information_schema.OPTIMIZER_TRACE 或審計日誌發現。
5. 5.6+ 優化器增強（ICP、MRR、Index Condition Pushdown）已大幅減少誤判，升級版本往往比加 Hint 更徹底。

優化器是智能助手，Hint 是緊急制動器。懂得何時踩剎車、何時信任助手，是 SQL 調優從入門到精通的分水嶺。