MySQL 分頁查詢優化：百萬級數據下 LIMIT 深分頁性能解決方案

MySQL LIMIT offset, rows 在淺分頁（offset 小）表現良好，但百萬級數據深分頁（如 LIMIT 500000, 10）會導致全表掃描前 50 萬行再丟棄，性能急劇下降。本文對比多種優化方案。

基礎語法

SELECT * FROM tbl LIMIT offset, rows;
-- 等價於
SELECT * FROM tbl LIMIT rows OFFSET offset;

深分頁性能痛點

LIMIT 490000, 10 需掃描 49 萬行索引/數據頁，丟棄前 49 萬行，僅返回 10 行。隨 offset 增大，耗時線性增長。

優化方案一：子查詢 + 索引定位（推薦）

SELECT * FROM news 
WHERE id >= (SELECT id FROM news ORDER BY id LIMIT 490000, 1) 
ORDER BY id LIMIT 10;

原理：子查詢僅遍歷主鍵索引定位到目標 ID（Using index），外層查詢直接範圍掃描 10 行，避免大量回表與丟棄。實測 0.18s vs 0.22s，優勢明顯。

優化方案二：記錄上一頁最大 ID（游標式分頁）

適用於「下一頁」場景，前端傳遞上一頁最後一條 id：

SELECT * FROM news WHERE id > last_seen_id ORDER BY id LIMIT 10;

無 offset，極快。但不支持隨機跳頁。

優化方案三：覆蓋索引 + 延遲關聯

SELECT t.* FROM tbl t
JOIN (SELECT id FROM tbl ORDER BY id LIMIT 490000, 10) tmp USING (id);

子查詢僅掃描索引（覆蓋索引），外層通過主鍵回表 10 行，減少回表次數。

方案對比表

避坑指南

• 確保 ORDER BY 列有索引（最好是主鍵）。
• 避免 SELECT *，只取所需列，增加覆蓋索引命中率。
• 極大數據量（億級）建議引入 ElasticSearch 或分表分庫。

核心思想：用索引定位起點，而非掃描丟棄。