MySQL 分頁優化實戰：六種方案優劣對比與最佳實踐

問題背景

MySQL LIMIT M, N 深分頁（大 M）性能極差：需掃描丟棄前 M 行。隨數據量增長，響應時間線性惡化。本文總結六種優化方案並點評優劣。

六種方案詳解

方案 1：原生 LIMIT M, N

SELECT * FROM table_name LIMIT M, N

• 適用：數據量極小（百/千級）。
• 缺點：全表掃描，深分頁極慢；無 ORDER BY 時結果集順序不穩定。

方案 2：主鍵/唯一索引範圍查詢

SELECT * FROM table_name WHERE id_pk > (pageNum * pageSize) LIMIT M

• 適用：大數據量（萬級以上），ID 連續或近似連續。
• 優點：索引範圍掃描，極快。
• 隱患：若 ID 不連續（刪除導致空洞），會漏數據；結果集順序依賴索引順序，若需業務排序則不可用。

方案 3：索引範圍 + 顯式排序（推薦基礎寫法）

SELECT * FROM table_name 
WHERE id_pk > (pageNum * pageSize) 
ORDER BY id_pk ASC 
LIMIT M

• 優點：ORDER BY 列為主鍵/唯一索引時，MySQL 可消除排序操作，結果集穩定。
• 限制：MySQL 目前 ORDER BY ... DESC 無法利用索引倒序掃描（8.0+ 已優化），僅支持 ASC 高效利用索引。

方案 4：Prepared Statement + 索引範圍

PREPARE stmt_name FROM 
'SELECT * FROM table_name WHERE id_pk > (?, ?) ORDER BY id_pk ASC LIMIT ?';
SET @pageNum = 1, @pageSize = 10, @limit = 10;
EXECUTE stmt_name USING @pageNum, @pageSize, @limit;

• 優點：預編譯減少解析優化開銷，適合高頻分頁查詢。

方案 5：存儲過程封裝（作者推薦）

• 將方案 3/4 邏輯封裝為存儲過程，減少網絡交互，邏輯下沉 Server 端，性能略優。
• 缺點：業務邏輯耦合 DB，維護性差，調試困難，現代架構不推薦。

方案 6：SQL_CALC_FOUND_ROWS（不推薦）

SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT M, N;
SELECT FOUND_ROWS();

• 缺點：仍需全表掃描計算總行數，性能差，無實質優化，勿使用。

現代最佳實踐建議

1. 游標分頁：前端傳遞上一頁最大 ID，WHERE id > last_id ORDER BY id LIMIT N，無深分頁問題，適合無限滾動/列表翻頁。
2. 覆蓋索引優化：SELECT id FROM table WHERE ... ORDER BY id LIMIT M, N 先取 ID，再 WHERE id IN (...) 回表查詳情，減少回表次數。
3. 延遲關聯：子查詢只取主鍵 SELECT id FROM table ORDER BY id LIMIT M, N，外層 JOIN 原表取字段。
4. 業務妥協：禁止跳頁、限制最大頁碼、提供搜索/篩選替代深分頁。

總結對比表