千萬級大表索引失效實戰：MySQL 優化器選錯索引導致 IO 暴增的解決之道

案例背景與現象

• 表 program_Access_log：4,000 萬筆，約 30GB
• 主鍵 id，索引 program_id
• 伺服器記憶體 16GB，無法全表載入

原查詢：

SELECT * FROM program_Access_log 
WHERE program_id BETWEEN 1 AND 4000;

加上 id BETWEEN 1 AND 500000 後速度無提升，甚至更慢。

根因深度剖析

1. 優化器誤判：program_id 範圍小（4000），被選為驅動索引
2. 回表災難：透過 program_id 索引掃出的 ID 為離散分佈
3. 隨機 IO 風暴：每筆回表需磁盤定位，30GB 數據遠超 16GB 記憶體，Buffer Pool 命中率極低

反直覺的解決方案：誘導優化器走主鍵

-- 擴大 program_id 範圍，使其選擇度降低，優化器轉而選擇主鍵 id
SELECT * FROM program_Access_log 
WHERE id BETWEEN 1 AND 500000 
  AND program_id BETWEEN 1 AND 15000000;

結果：主鍵範圍掃描變為順序讀取，IO 從隨機變順序，速度提升數十倍。

系統性優化策略

核心心法

「索引不總是越多越好，關鍵在於訪問模式是否順序。」大表隨機回表是效能殺手，應優先考慮讓查詢走順序掃描，哪怕掃描行數略多。