MySQL LIMIT 優化機制：早停策略、分頁效能與大偏移量解決方案

LIMIT 基本優化行為

MySQL 在特定情況下對 LIMIT row_count 有專門優化 (不含 HAVING)：

1. 索引選擇優化

僅需少量行時，全表掃描可能改用索引掃描。

2. ORDER BY + LIMIT 早停排序

• 有索引：直接讀取前 N 行，極快
• 無索引 (filesort)：需先選出所有匹配行並大部分排序，確定前 N 行後停止

3. DISTINCT + LIMIT 早停去重

找到 N 個唯一值即停止掃描。

4. GROUP BY + LIMIT 早停分組

按索引順序讀取時，不計算多餘分組值。

5. 客戶端傳送完畢即放棄

除非使用 SQL_CALC_FOUND_ROWS。

6. LIMIT 0 快速驗證

回傳空結果集，可檢查查詢語法、取得欄位類型 (API 中)。

7. 臨時表空間預估

使用臨時表時，LIMIT 決定所需空間大小。

分頁查詢效能陷阱

問題：大偏移量極慢

-- 第 100001 頁，每頁 10 筆
SELECT * FROM large_table ORDER BY id LIMIT 1000000, 10;

MySQL 必須掃描並捨棄前 1,000,000 行，再取 10 行。資料越大越慢。

大偏移量解決方案

方案一：延遲關聯 (Deferred Join) - 推薦

-- 只用主鍵索引快速定位偏移位置，再回表取欄位
SELECT t.* 
FROM large_table t
INNER JOIN (
    SELECT id FROM large_table ORDER BY id LIMIT 1000000, 10
) tmp ON t.id = tmp.id;

子查詢只掃描索引 (覆蓋索引)，極快；外層只回表 10 次。

方案二：游標分頁 / Keyset Pagination (最佳)

-- 第一頁
SELECT * FROM large_table ORDER BY id LIMIT 10;

-- 後續頁：記住上一頁最後一筆的 id
SELECT * FROM large_table WHERE id > 1000010 ORDER BY id LIMIT 10;

優點：無偏移量掃描、效能恆定、適合無限滾動。
缺點：無法直接跳到指定頁碼、需唯一排序鍵。

方案三：覆蓋索引

-- 只查索引欄位
SELECT id, name FROM large_table ORDER BY id LIMIT 1000000, 10;

避免回表，但仍需掃描偏移量行。

方案四：緩存/預計算

• 熱門分頁結果存 Redis
• 定時任務預計算熱門頁面
• 搜尋結果分頁用 Elasticsearch

LIMIT 參數完整語法

LIMIT [offset,] row_count

-- 等價寫法
LIMIT 5          -- offset=0, row_count=5
LIMIT 0, 5       -- 明確指定
LIMIT 10 OFFSET 5 -- 標準 SQL 語法 (MySQL 支援)

實務建議對照表

相關系統變數