MySQL LIMIT 深度分頁效能陷阱與優化實戰

LIMIT offset, rows 在大偏移量時效能線性下降，因 MySQL 需掃描並丟棄 offset 行。本文深度解析原理、優化方案與實戰測試數據。

問題現象

-- 表：千萬級資料，有索引 index_col
SELECT * FROM mytable WHERE index_col = xxx LIMIT 800000, 20;
-- 前幾萬行毫秒級，80萬偏移需 4 秒+，CPU 100%

根因分析

• EXPLAIN 顯示 type=ALL (全表掃描)
• MySQL 必須讀取並丟棄前 80 萬行才能取得後 20 行
• 隨著 offset 增大，掃描成本線性增長

優化方案對比

方案一：延遲關聯 / 子查詢定位主鍵 (推薦、通用)

-- 先用覆蓋索引快速定位主鍵，再回表
SELECT * FROM mytable 
WHERE id >= (
    SELECT id FROM mytable WHERE index_col = xxx ORDER BY id LIMIT 800000, 1
)
AND index_col = xxx
LIMIT 20;

原理：子查詢只掃描索引 (Covering Index)，極快；外層按主鍵範圍回表，避免大偏移掃描。

方案二：記住上一頁最大 ID (游標式分頁，效能最佳)

-- 第一頁
SELECT * FROM mytable WHERE index_col = xxx ORDER BY id LIMIT 20;
-- 記住最後一筆 id = 1000

-- 下一頁
SELECT * FROM mytable WHERE index_col = xxx AND id > 1000 ORDER BY id LIMIT 20;

• 優點：無偏移掃描、極快、適合無限滾動/API 分頁
• 缺點：不支援跳頁、需前端配合傳遞 last_id

方案三：覆蓋索引 + LIMIT (僅需索引欄位)

SELECT id, index_col FROM mytable WHERE index_col = xxx ORDER BY id LIMIT 800000, 20;
-- 只查索引欄位，無需回表，速度可接受

方案四：MySQL 8.0+ 窗口函數 (不推薦分頁用)

-- 窗口函數同樣需全掃描排序，分頁無優勢
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM mytable WHERE index_col = xxx
) t WHERE rn BETWEEN 800001 AND 800020;

實戰測試數據 (100 萬測試表)

查詢方式	偏移 80 萬	偏移 90 萬	說明
SELECT * LIMIT 800000,20	4.32 秒	4.8 秒	全表掃描+回表
SELECT id LIMIT 800000,20	0.27 秒	0.3 秒	覆蓋索引，無回表
延遲關聯 (子查詢定位 ID)	0.25 秒	0.28 秒	**推薦通用方案**
游標式 (id > last_id)	0.002 秒	0.002 秒	**極致效能**，需業務配合

LIMIT 語法完整說明

-- 基本語法
LIMIT [offset,] row_count
LIMIT row_count OFFSET offset

-- 範例
LIMIT 10           -- 前 10 筆 (相當於 LIMIT 0,10)
LIMIT 5,10         -- 跳過 5 筆，取 10 筆 (第 6-15 筆)
LIMIT 95, -1       -- 從第 96 筆到結束 (MySQL 特有，-1 表無上限)
LIMIT 10 OFFSET 5  -- PostgreSQL/標準語法

LIMIT 優化特性 (官方文檔)

• LIMIT n + ORDER BY：找到前 n 行即停止排序
• LIMIT n + DISTINCT：找到 n 個不同值即停止
• LIMIT n + GROUP BY：不計算不必要的分組
• LIMIT 0：快速回傳空結果集，可用於取得欄位資訊
• 臨時表大小受 LIMIT 影響

模糊查詢 + LIMIT 實戰

-- 百萬級模糊查詢 0.03 秒
SELECT id, name FROM user 
WHERE name LIKE '%83%' OR `key` LIKE '%83%' 
LIMIT 0, 25;
-- 前綴索引無效，但 LIMIT 0,25 讓全表掃描早停

分頁架構建議

場景	推薦方案
後台管理/報表 (需跳頁)	延遲關聯 + 覆蓋索引
前端無限滾動/APP 列表	游標式 (last_id)
搜尋結果首頁	LIMIT 0,20 直接查
深度分頁 > 100 頁	禁止深度跳頁，改搜尋/篩選