MySQL LIMIT 分頁效能優化深度解析：從百萬偏移到游標式分頁

單表 2G+ 的 MyISAM 表執行 LIMIT 1275480, 20 導致查詢極慢，本文深入剖析 LIMIT 掃描機制，提出三種分頁優化策略與程式碼改寫實例。

LIMIT 執行機制與效能陷阱

LIMIT N, M 實際執行邏輯：先掃描 N+M 行，再捨棄前 N 行，取後 M 行。
當 N 達百萬級，等同全表掃描，I/O 與 CPU 開銷極大。

優化策略一：游標式分頁（適用「上一頁/下一頁」）

利用上一頁最後一筆的排序欄位值（如 postdate）作為下一頁的篩選條件：

第一頁

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

第二頁（假設第一頁最小 postdate = '2009-11-3'）

SELECT * FROM pw_gbook WHERE uid='48' AND postdate < '2009-11-3' ORDER BY postdate DESC LIMIT 20;

上一頁（假設第一頁最大 postdate = '2009-11-1'）

SELECT * FROM pw_gbook WHERE uid='48' AND postdate > '2009-11-1' ORDER BY postdate DESC LIMIT 20;

優點：N 永遠為 0，索引範圍掃描極快。
限制：不支援任意跳頁。

優化策略二：帶跳頁的游標式分頁（適用「1,2,3,4,5...」）

從第 2 頁跳到第 5 頁（差 3 頁，每頁 20 筆）：

SELECT * FROM pw_gbook WHERE uid='48' AND postdate < '2009-11-3' ORDER BY postdate DESC LIMIT 60, 20;

仍比原始 LIMIT 1275480, 20 好，但跳頁越遠 N 越大，效能遞減。大型網站因此限制跳頁範圍或不提供直接跳頁輸入框。

優化策略三：邏輯轉換法（利用主鍵 id 替代偏移）

原查詢：

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

改寫為：

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

前提：id 為自增主鍵，且 id > 1275480 能精準定位到對應區間。此法將 OFFSET 掃描轉為索引範圍查找，效能提升數量級。

末頁與首頁特殊處理

• 末頁（知道總頁數）：用倒數第二頁的最小值反向查詢。
• 末頁（不知總頁數）：SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate ASC LIMIT 20; 直接取最舊 20 筆。
• 首頁：同第一頁邏輯。

替代方案：BETWEEN 區間分頁

若業務允許，用 WHERE postdate BETWEEN '2009-11-01' AND '2009-11-30' 替代 LIMIT 分頁，配合索引效能極佳。

實作建議

1. 前端 UI 採用「上一頁/下一頁」或「載入更多」模式，避免大偏移。
2. 後端儲存上一頁的游標值（排序欄位 + 主鍵），下一頁直接帶入。
3. 必要時用 Redis 緩存熱門分頁結果。
4. 大表務必在排序欄位（+ WHERE 條件欄位）建立複合索引。