MySQL 異常查詢案例：LIMIT 值影響索引選擇的優化器 Bug 分析

現象描述

相同 SQL，LIMIT 10 耗時 3 分 3 秒，LIMIT 100 耗時 1.24 秒。LIMIT 67 與 LIMIT 68 執行計劃截然不同。

執行計劃對比

-- LIMIT 67 (慢)
id: 1, table: a, type: range, key: B (f1,f2,f3), rows: 2,555,192, Extra: Using where; Using temporary; Using filesort

-- LIMIT 68 (快)
id: 1, table: a, type: ref, key: A (f3,f4,f1,f2,f5), key_len: 3, ref: const, rows: 67,586, Extra: Using where; Using temporary; Using filesort

索引定義

• 索引 A：(f3, f4, f1, f2, f5)
• 索引 B：(f1, f2, f3)

Bug 根因深度剖析

1. 優化器計算各索引「查詢代價」，選擇最小者
2. 代價模型核心：估算掃描行數
3. MySQL 5.1/5.5 僅支援前綴索引：索引 A 可用 f3，索引 B 可用 f1
4. Bug 細節：評估索引 B 區分度時，錯誤使用完整鍵 (f1,f2,f3) 而非前綴 f1
   • 假設：表 50 萬行，f1 1000 個值 → 平均 500 行/值
   • 錯誤計算：(f1,f2,f3) 50 萬值 → 平均 1 行/值
   • 導致索引 B 代價被嚴重低估
5. LIMIT 值參與代價乘積：LIMIT 較大時，索引 B 代價超過 A，優化器才正確選 A
6. GROUP BY 放大效應：每增加 1 個 LIMIT，實際需掃描 N 行（N = 總行數 / 不同 f2 值）

驗證：FORCE INDEX 強制使用 A

EXPLAIN SELECT ... FORCE INDEX(A) ... LIMIT 67;
-- rows: 67,586，證明索引 A 確實更優

修復方案

修改優化器源碼 test_if_cheaper_ordering 邏輯，計算索引區分度時使用實際可用前綴長度而非完整索引鍵。

臨時規避措施

• FORCE INDEX(A) 強制走正確索引
• 調整索引設計：將高選擇性欄位前置
• 升級 MySQL 5.6+（優化器重構，部分場景自動修正）