MySQL 大數據量分頁優化：避免 OFFSET 深分頁性能崩潰

MySQL LIMIT offset, rows 在深分頁（大 offset）時需掃描並丟棄 offset 行，導致性能隨頁碼線性惡化。本文介紹基於覆蓋索引+延遲關聯、游標分頁、記憶上一頁最大 ID 三種優化方案。

一、問題現象

-- 第 10 萬頁，每頁 20 行
SELECT * FROM huge_table ORDER BY id LIMIT 2000000, 20;
-- 執行計劃：type=ALL / index，rows≈200 萬，需讀取並丟棄 200 萬行

二、方案對比

三、最佳實踐：延遲關聯 SQL 模板

-- 原始慢查詢
SELECT * FROM orders WHERE status=1 ORDER BY created_at DESC LIMIT 100000, 20;

-- 優化：覆蓋索引 (status, created_at, id) + 延遲關聯
SELECT o.*
FROM orders o
JOIN (
  SELECT id
  FROM orders
  WHERE status=1
  ORDER BY created_at DESC
  LIMIT 100000, 20
) tmp USING (id);

子查詢僅掃描索引（Using index），外層按主鍵精確回表 20 行。

四、游標分頁實戰（推薦用於 API/Feed）

第一頁

SELECT id, title, created_at
FROM articles
WHERE status=1
ORDER BY id DESC
LIMIT 20;

後續頁（前端傳遞 last_id）

SELECT id, title, created_at
FROM articles
WHERE status=1 AND id < 987654321  -- last_id
ORDER BY id DESC
LIMIT 20;

索引 (status, id) 即可完美支持，EXPLAIN 顯示 range、rows=20。

五、總數統計優化

大表 COUNT(*) 同樣昂貴，建議：

• 近似值：EXPLAIN SELECT COUNT(*) ... 讀取 rows 估算
• 緩存：Redis 緩存總數，異步更新（每小時/每天）
• 觸發器維護計數表：INSERT/DELETE 時 UPDATE stats SET cnt=cnt±1
• 業務允許時：不提供總頁數，僅「下一頁」按鈕（游標分頁天然適配）

六、避坑提醒

• 不要在 WHERE 對列使用函數（DATE(created_at)），導致索引失效
• 複雜 JOIN 分頁：先在駆動表分頁取主鍵，再關聯其它表
• ORDER BY 非索引列：會觸發 filesort，配合 LIMIT 仍需全量排序
• MySQL 8.0 支持 LIMIT ... OFFSET ... 語法糖，底層機制未變

深分頁優化核心思想：**減少掃描行數、利用索引順序、業務接受游標式分頁**。根據業務場景選擇方案，能帶來數量級性能提升。