MySQL LIMIT 深度分頁優化：從「掃描百萬行」到「毫秒級定位」的三種實戰模式

前言：一條慢查詢引發的血案

SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480, 20;

單表 2GB+ MyISAM，LIMIT 1275480,20 導致 MySQL 掃描 127 萬行再丟棄前 127 萬，CPU 飆升、IO 暴漲、響應時間秒級甚至超時。本文系統拆解 LIMIT offset, rows 的執行機制，並給出三種業務級優化方案。

一、LIMIT 執行原理與性能陷阱

• LIMIT N, M 邏輯等價：先定位第 N+1 行，再取 M 行。
• MySQL 必須掃描並丟棄前 N 行，時間複雜度 O(N)；N 越大越慢。
• 若無覆蓋索引，還需回表，放大 IO。

二、核心優化思想：用「業務鍵」替代「偏移量」

利用上一頁最後一條記業的排序鍵值（如 postdate、id）作為「游標」，下一頁直接 WHERE sort_key < last_val 或 > last_val，配合 LIMIT M，實現 O(1) 定位。

三、三種業務分頁模式對比

模式 1：只有「上一頁 / 下一頁」（無跳頁）

-- 第 1 頁
SELECT * FROM pw_gbook WHERE uid=48 ORDER BY postdate DESC LIMIT 20;

-- 第 2 頁（假設第 1 頁最小 postdate = '2009-11-03')
SELECT * FROM pw_gbook WHERE uid=48 AND postdate < '2009-11-03'
ORDER BY postdate DESC LIMIT 20;

-- 第 3 頁（以第 2 頁最小值為準）...

✅ 簡單、無跳頁邏輯、性能穩定。❌ 無法直接跳轉到第 N 頁。

模式 2：顯示頁碼「1, 2, 3, 4, 5...」（支援跳頁）

-- 從第 2 頁跳到第 5 頁（每頁 20 行）
-- 第 2 頁最小 postdate = '2009-11-03'，跨越 3 頁 = 60 行
SELECT * FROM pw_gbook WHERE uid=48 AND postdate < '2009-11-03'
ORDER BY postdate DESC LIMIT 60, 20;

⚠️ 跨頁過大時 LIMIT offset 退化回原問題；大型站點常限制最大跳頁深度（如僅允許跳轉前 100 頁）。

模式 3：首頁/末頁/鄰近頁（如「首頁 ≪ 100 101 102 103 ≫ 末頁」）

• 末頁已知總頁數：用倒數第 2 頁最小值反向定位。
• 末頁未知：反向排序取前 20 行 ORDER BY postdate ASC LIMIT 20。
• 首頁：同第 1 頁正序取值。

四、通用改寫模板：將「偏移量」轉為「主鍵範圍」

-- 原始慢查詢
SELECT * FROM pw_gbook WHERE uid=48 ORDER BY postdate DESC LIMIT 1275480, 20;

-- 改寫 1：利用主鍵 id（假設 id 遞增與 postdate 正相關）
SELECT * FROM pw_gbook WHERE id > 1275480 AND uid=48 ORDER BY postdate DESC LIMIT 20;

-- 改寫 2：利用 BETWEEN 範圍（若業務允許）
SELECT * FROM pw_gbook WHERE uid=48 AND postdate BETWEEN '2009-10-01' AND '2009-10-31'
ORDER BY postdate DESC LIMIT 20;

五、工程落地清單

1. 前端協議變更：分頁接口從 page, size 改為 cursor, size 或 last_id, size。
2. 索引覆蓋：確保 (uid, postdate DESC) 或 (uid, id) 組合索引存在，實現 Using index。
3. 緩存首頁熱數據：第 1 頁高頻訪問，Redis 緩存 5-10 分鐘。
4. 監控慢查詢：long_query_time=1，定期審計 LIMIT 大偏移 SQL。

總結：LIMIT offset, rows 是分頁性能殺手，「游標/Seek 分頁」 是唯一正解。根據業務跳頁需求選擇三種模式之一，配合覆蓋索引，可將百萬偏移查詢從 10 秒級壓縮至 10 毫秒級。