千萬級大表索引失效排查：為何加了索引反而慢？

案例現場

• 表：program_access_log，4000 萬行，~30GB
• 主鍵：id，索引：program_id
• 記憶體：16GB（無法全表載入）

慢查詢現象

-- 慢
SELECT * FROM program_access_log WHERE program_id BETWEEN 1 AND 4000;

-- 加 id 限制仍慢
SELECT * FROM program_access_log 
WHERE id BETWEEN 1 AND 500000 
  AND program_id BETWEEN 1 AND 4000;

根因剖析

1. 優化器選錯索引：program_id 範圍小（選擇性高），優化器選它做驅動索引。
2. 回表隨機 I/O 暴增：索引掃描得到大量離散 id，需逐個回主鍵聚簇索引讀取完整行。
3. 緩衝池命中率極低：30GB 表 >> 16GB 記憶體，隨機讀取導致大量磁碟 I/O，效能崩塌。

解決方案對比

實施：反向利用優化器

-- 擴大 program_id 範圍遠超 id 範圍
SELECT * FROM program_access_log 
WHERE id BETWEEN 1 AND 500000 
  AND program_id BETWEEN 1 AND 15000000;

• 優化器判斷 id 範圍更小（選擇性更高），選主鍵索引
• 主鍵聚簇索引掃描為順序讀取，預讀效率高，磁碟 I/O 降 1-2 數量級
• 50 萬行順序掃描 ~ 秒級完成

深度優化建議

1. 覆蓋索引：ALTER TABLE ADD INDEX idx_pid_id (program_id, id); 查詢只需 id, program_id 時可避免回表。
2. 增加記憶體：innodb_buffer_pool_size 設至實體記憶體 70-80%，盡量缓住熱數據。
3. 分區/分表：升級 5.6+ 使用原生分區，或業務層按時間/租戶分表。
4. 歸檔冷數據：定期將歷史日誌移至歸檔表/庫，主表保持熱數據小體量。
5. 避免 SELECT *：只選需要欄位，減少傳輸與緩衝壓力。

核心心法

「索引不是銀彈。優化器基於統計資訊選擇索引，統計失真或極端數據分佈會導致錯誤選擇。大表隨機 I/O 是效能殺手，**順序掃描優於隨機回表**。」

工具箱：EXPLAIN、ANALYZE TABLE、OPTIMIZE TABLE、SHOW ENGINE INNODB STATUS、perf_schema、sys.schema_table_statistics。