MySQL 大偏移量分頁效能優化：子查詢 + 主鍵索引 替代 LIMIT offset

問題場景

SELECT * FROM big_table LIMIT 980000, 10;  -- 掃描 98 萬行再丟棄，極慢

優化原理

• 主鍵索引 (聚簇索引) 葉子節點即資料，掃描索引極快
• 先用覆蓋索引查出目標 ID，再回表取完整欄位

優化寫法

-- 子查詢先取 ID (走主鍵索引，覆蓋查詢)
SELECT * FROM big_table
WHERE id >= (SELECT id FROM big_table ORDER BY id LIMIT 980000, 1)
ORDER BY id LIMIT 10;

進階版：避免子查詢相關子查詢優化器限制

-- 兩步驟 (應用層組合)
-- 1. 取得起始 ID
SELECT id FROM big_table ORDER BY id LIMIT 980000, 1;
-- 假設回傳 1234567

-- 2. 條件查詢
SELECT * FROM big_table WHERE id >= 1234567 ORDER BY id LIMIT 10;

適用條件與限制

• 表必須有自增/有序主鍵 (或唯一索引)
• 資料不應大量刪除導致 ID 不連續 (仍正確但可能少幾行)
• MySQL 5.6+ 優化器支援 LIMIT 在子查詢中，舊版可能需改寫
• 若無主鍵，可建立 INDEX idx_sort (sort_col, id) 並按 sort_col, id 分頁

對比測試建議

-- 原寫法
EXPLAIN SELECT * FROM big_table LIMIT 980000, 10;
-- 優化寫法
EXPLAIN SELECT * FROM big_table WHERE id >= (SELECT id FROM big_table ORDER BY id LIMIT 980000, 1) LIMIT 10;
-- 觀察 rows、type (ref/range vs ALL)、Extra (Using where vs Using index)