MySQL 異常查詢案例分析：為什麼 LIMIT 10 比 LIMIT 100 慢 10 倍？

MySQL 異常查詢案例分析：為什麼 LIMIT 10 比 LIMIT 100 慢 10 倍？

在日常運維中，我們常會遇到看似不可思議的性能現象。本文記錄一個經典案例：相同的 SQL 語句，僅 LIMIT 子句的行數不同，LIMIT 10 的執行時間卻比 LIMIT 100 慢約 10 倍。我們將通過執行計劃、優化器成本模型與索引選擇邏輯，深入剖析根因並給出解決方案。

問題現場

用戶反饋：以下兩條語句僅 LIMIT 值不同，性能卻天壤之別：

SELECT f1, SUM(`f2`) `CNT` 
FROM T 
WHERE f1 IS NOT NULL AND f3 = '2014-05-12' 
GROUP BY f1 
ORDER BY `CNT` DESC 
LIMIT 10;

執行時間：3 分 3.65 秒

SELECT f1, SUM(`f2`) `CNT` 
FROM T 
WHERE f1 IS NOT NULL AND f3 = '2014-05-12' 
GROUP BY f1 
ORDER BY `CNT` DESC 
LIMIT 100;

執行時間：1.24 秒

性能差距高達 10 倍以上，極不尋常。

執行計劃揭示的關鍵線索

通過 EXPLAIN 觀察，發現當 LIMIT 從 67 變為 68 時，執行計劃發生劇烈變化：

-- LIMIT 67
id: 1
select_type: SIMPLE
table: a
type: range
possible_keys: A,B,C
key: B
key_len: 387
ref: NULL
rows: 2555192
Extra: Using where; Using temporary; Using filesort

-- LIMIT 68
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: A,B,C
key: A
key_len: 3
ref: const
rows: 67586
Extra: Using where; Using temporary; Using filesort

關鍵差異在於 key 欄位：LIMIT 67 使用索引 B，掃描行數估算 255 萬；LIMIT 68 使用索引 A，掃描行數估算僅 6.7 萬。優化器選擇了錯誤的索引。

索引定義與前綴索引陷阱

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

查詢條件為 f3 = '2014-05-12'，索引 A 可用前綴 f3；索引 B 僅能用前綴 f1（因 f1 IS NOT NULL 非等值條件）。MySQL 5.1/5.5 僅支援前綴索引，因此實際可用的索引列不同。

優化器為何誤判？

MySQL 優化器根據「估計掃描行數」計算查詢成本。在 test_if_cheaper_ordering 階段比較不同索引的排序成本時，存在一個 Bug：計算索引區分度時未考慮前綴索引限制。

具體而言，索引 B 實際只能使用 f1 做前綴，但優化器在估算單 key 平均行數時，卻使用了完整鍵 (f1,f2,f3) 的區分度，導致估算成本偏小，誤以為索引 B 更優。

為什麼 LIMIT 值大反而選對了？

在計算索引 B 的查詢成本時，LIMIT 返回行數也參與乘積運算。當 LIMIT 較大（如 100）時，乘積結果超過索引 A 的成本，優化器才會正確選擇索引 A。而 LIMIT 10 時，乘積較小，錯誤地勝出。

語句特性放大了 Bug 影響

該語句包含 GROUP BY 與 ORDER BY，需建立臨時表並排序。每增加一個 LIMIT 行，實際掃描行數會按「表總行數 / 不同 f2 值數」倍增，使得錯誤計劃的代價被極大放大。

解決方案

1. 強制索引：使用 FORCE INDEX(A) 繞過優化器誤判。
2. 升級版本：MySQL 5.6+ 引入了更完善的成本模型與直方圖統計，可緩解此類問題。
3. 調整索引設計：針對高頻查詢建立覆蓋索引 (f3, f1, f2)，避免前綴索引局限。
4. 修改優化器邏輯（內核層面）：在 test_if_cheaper_ordering 中改用實際可用前綴列的區分度計算成本。

總結

此案例揭示了 MySQL 早期版本優化器在處理前綴索引與 LIMIT 交互時的缺陷。理解優化器成本模型、善用 EXPLAIN 與 FORCE INDEX，是 DBA 解決疑難性能問題的核心技能。