MySQL 分頁查詢最佳實踐：LIMIT 子句詳解與大偏移量優化策略

前言：分頁查詢是 Web 應用的核心需求

MySQL 透過 SELECT 語句的 LIMIT 子句原生支援分頁，語法簡潔卻暗藏效能陷阱。本文系統梳理 LIMIT 完整語法、基本用法、深分頁效能瓶頸，並給出生產級優化方案。

LIMIT 完整語法

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
       select_expression, ...
       [INTO OUTFILE 'file_name' export_options]
       [FROM table_references
       [WHERE where_definition]
       [GROUP BY col_name, ...]
       [HAVING where_definition]
       [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
       [LIMIT [offset,] rows]
       [PROCEDURE procedure_name]]

基本用法速查

關鍵點：offset 從 0 開始計數，非 1。

深分頁效能災難：為什麼 LIMIT 1000000, 10 會很慢？

MySQL 執行 LIMIT offset, rows 時，需先掃描並丟棄前 offset 行，再取 rows 行。偏移量越大，丟棄成本越高，IO 與 CPU 呈線性增長。

四大優化策略

1. 索引覆蓋 + 延遲關聯（Delayed Join）

-- 原始慢查詢
SELECT * FROM huge_table ORDER BY id LIMIT 1000000, 10;

-- 優化：子查詢只掃描索引取 id，再回表
SELECT ht.* FROM huge_table ht
INNER JOIN (
  SELECT id FROM huge_table ORDER BY id LIMIT 1000000, 10
) tmp USING (id);

原理：覆蓋索引極窄，掃描丟棄 100 萬行極快；回表僅 10 次。

2. 游標式分頁

-- 首頁
SELECT * FROM huge_table WHERE id > 0 ORDER BY id LIMIT 10;

-- 翻頁：記住上一頁最大 id
SELECT * FROM huge_table WHERE id > 1000010 ORDER BY id LIMIT 10;

適用於「下一頁」流式瀏覽，不支援跳頁，但效能恆定 O(1)。

3. 利用主鍵範圍估算

若 ID 連續且單調遞增，可估算 offset 對應的 ID 範圍，直接 WHERE id BETWEEN ... 定位，再 LIMIT 微調。

4. 物化視圖 / 緩存層

熱門列表（如首頁推薦）預計算物化至 Redis 或摘要表，分頁直接讀緩存，DB 僅負責增量刷新。

實戰建議清單

• 小偏移量（< 10 萬）：直寫 LIMIT 即可
• 大偏移量：強制使用延遲關聯或游標式分頁
• 避免 OFFSET 關鍵字：統一用 LIMIT offset, rows 兼容舊版本
• 配合 ORDER BY 主鍵/唯一索引：確保結果確定性，避免重複/遺漏
• 監控 Handler_read_rnd_next：該計數器飆升即為深分頁特徵

結語

LIMIT 雖簡單，大規模資料下的分頁藝術卻大有學問。掌握「索引覆蓋 + 延遲關聯」與「游標式分頁」兩大核心模式，即可應對 99% 的分頁效能挑戰。