MySQL 分頁效能深度探索：電梯 vs 扶梯、Limit 優化、Order By 陷阱

MySQL 分頁效能深度探索：電梯 vs 扶梯、Limit 優化、Order By 陷阱

分頁是業務高頻場景，但 LIMIT offset, size 隨著 offset 增大效能急劇下降。本文對比「電梯式」（精確跳頁）與「扶梯式」（游標/Keyset）分頁，深度剖析優化策略與 ORDER BY 索引陷阱。

一、兩種分頁模式對比

二、傳統電梯式分頁效能痛點

-- 兩條 SQL 模式
SELECT COUNT(*) FROM t WHERE x=y;                                    -- 總數
SELECT * FROM t WHERE x=y ORDER BY id LIMIT 100000, 10;              -- 第 10001 頁

• LIMIT 100000, 10 需讀取 100,010 行 → 棄讀 100,000 行 → 極慢。
• COUNT(*) 全表掃描或二級索引掃描，同樣昂貴。

三、優化策略工具箱

1. 扶梯式改造（首選）

-- 首頁
SELECT * FROM t WHERE x=y ORDER BY id LIMIT 10;

-- 翻頁：前端傳 last_id
SELECT * FROM t WHERE x=y AND id > 100000 ORDER BY id LIMIT 10;

• 利用覆蓋索引 (x, id) 直接定位，無回表、無 Filesort。
• 無法獲取總數/跳頁，需業務妥協（如「顯示前 1000 條」）。

2. 延遲關聯 / 子查詢優化（電梯式必備）

-- 原始：全字段回表排序
SELECT * FROM pa_dc_flow ORDER BY subject_code DESC LIMIT 100000, 5;

-- 優化：子查詢只取主鍵排序，再 JOIN 回表
SELECT t.* FROM pa_dc_flow t
INNER JOIN (
    SELECT id FROM pa_dc_flow ORDER BY subject_code DESC LIMIT 100000, 5
) tmp USING (id);

• 子查詢 SELECT id ... ORDER BY subject_code 可走 subject_code 索引（覆蓋索引），僅掃描索引葉子節點。
• 外層按主鍵 id 精準回表 5 行，極快。

3. ORDER BY 索引利用鐵律

核心：ORDER BY 欄位若要走索引排序，必須滿足：
① 出現在 SELECT 列表（覆蓋索引），或
② 出現在 WHERE 條件，或
③ 與 WHERE 條件欄位建立複合索引 (where_col, order_col) 滿足最左前綴。

4. COUNT(*) 近似優化

EXPLAIN SELECT COUNT(*) FROM t WHERE x=y;
-- rows 欄位為優化器估算行數，無需真實掃描，極快
-- 適用：不需精確總數、僅做分頁條顯示「約 N 條」

四、避坑總結

• ❌ 盲目用 LIMIT offset, size 處理百萬級資料深翻頁。
• ❌ ORDER BY 非索引欄位 + 大 Offset = 雙重災難。
• ✅ 業務允許 → 扶梯式；必須電梯式 → 延遲關聯 + 覆蓋索引。
• ✅ 複合索引設計遵循 (WHERE欄位, ORDER BY欄位, SELECT欄位) 覆蓋查詢。
• ✅ 定期 ANALYZE TABLE 更新統計，避免優化器誤判。