您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 分頁查詢優化：8 種方案深度對比與最佳實踐

編輯：MySQL 資料庫相關知識

分頁性能殺手：OFFSET 深分頁

SELECT * FROM t ORDER BY id LIMIT 1000000, 20;

MySQL 必須掃描前 1,000,020 行，丟棄前 1,000,000 行，**IO 與 CPU 雙重浪費**。深分頁 (OFFSET > 10 萬) 性能呈線性下降。

8 種優化方案對比

方案	核心 SQL	適用場景	優點	缺點	推薦指數
1. 直接 LIMIT	`LIMIT M, N`	數據量 < 10 萬、淺分頁	簡單通用	深分頁極慢、結果集不穩定	⭐⭐
2. 主鍵游標 (ID > 值)	`WHERE id > ? ORDER BY id LIMIT N`	ID 連續/單調遞增、無刪除	索引範圍掃描極快	ID 不連續會漏數據、不支援任意頁跳轉	⭐⭐⭐⭐
3. 主鍵游標 + 排序	`WHERE id > ? ORDER BY id ASC LIMIT N`	ID 非連續、需嚴格順序	結果穩定、走索引	同方案 2，需記住上頁最大 ID	⭐⭐⭐⭐
4. PrepareStatement 游標	`PREPARE stmt FROM 'SELECT ... WHERE id > ? ORDER BY id LIMIT ?'`	高併發同分頁邏輯	減少解析開銷、防注入	邏輯同方案 3	⭐⭐⭐⭐
5. 索引下推 + LIMIT	`WHERE pk >= 1000 ORDER BY pk ASC LIMIT 0, 20`	已知起始主鍵值	極快定位、避免全掃	需業務知曉具體主鍵範圍	⭐⭐⭐⭐⭐
6. 子查詢/Join 定位	`SELECT * FROM t1 JOIN (SELECT id FROM t ORDER BY id DESC LIMIT (page-1)*size, 1) t2 ON t1.id <= t2.id ORDER BY t1.id DESC LIMIT size`	深分頁、需任意頁跳轉	子查詢僅掃描索引、回表少	SQL 複雜、Derived 表物化開銷	⭐⭐⭐
7. 存儲過程封裝	上述邏輯封裝為 SP	極高併發、邏輯固化	網絡交互少、服務端優化	維護成本高、調試難、業務耦合	⭐⭐
8. SQL_CALC_FOUND_ROWS	`SELECT SQL_CALC_FOUND_ROWS * FROM t LIMIT ...; SELECT FOUND_ROWS();`	不推薦	無	全表掃描計總數、8.0 棄用、性能極差	❌

深度解析：方案 6 (子查詢/Join 定位) 為何有效？

-- 子查詢版
SELECT * FROM t 
WHERE id <= (SELECT id FROM t ORDER BY id DESC LIMIT (page-1)*pageSize, 1)
ORDER BY id DESC LIMIT pageSize;

-- Join 版 (通常更優)
SELECT t1.* FROM t t1
JOIN (SELECT id FROM t ORDER BY id DESC LIMIT (page-1)*pageSize, 1) t2
  ON t1.id <= t2.id
ORDER BY t1.id DESC LIMIT pageSize;

原理：內層子查詢 ORDER BY id DESC LIMIT offset, 1 僅需掃描主鍵索引定位到「頁邊界 ID」，外層 id <= boundary_id 配合 ORDER BY id DESC LIMIT pageSize 直接範圍掃描，**避免了大 OFFSET 掃描丟棄**。

現代最佳實踐：Keyset Pagination (游標分頁)

前端協議

// 首頁
GET /api/list?limit=20

// 下一頁 (攜帶上頁最後一條記錄的排序鍵)
GET /api/list?limit=20&after_id=100500&after_created=1699900000

後端 SQL

SELECT * FROM t 
WHERE (created_at, id) < (?, ?)  -- 行構造器比較，支援複合排序
ORDER BY created_at DESC, id DESC
LIMIT ?;

優勢：

無 OFFSET，性能恆定 O(log N + Limit)。
天然支援高併發寫入不重複/不遺漏。
無需記住頁碼，適配無限滾動/瀑布流。

限制：不支援「跳轉到第 N 頁」，需 UI 配合 (搜索/篩選代替翻頁)。

總數統計優化

方案	適用性	備註
`COUNT(*)` 實時查	小表/低頻	InnoDB 全掃描極慢
Redis 緩存總數	高併發讀多寫少	定時任務/寫入時異步更新
統計表/物化視圖	數據倉庫/報表	ETL 時預聚合
EXPLAIN Rows 估算	近似值可接受	不準確，僅供參考
不提供總數	無限滾動/瀑布流	前端只有「加載更多」

避坑指南

禁止 LIMIT 1000000, 20：上線前 Code Review 必查。
複合索引順序：WHERE a=1 ORDER BY b LIMIT 10 需索引 (a, b)。
避免 ORDER BY RAND()：全表掃描 + 文件排序，改用應用層隨機主鍵。
覆蓋索引：SELECT id, name FROM t WHERE status=1 ORDER BY id LIMIT 20 索引 (status, id, name) 避免回表。