MySQL 高效分頁三大方案深度對比

百萬級資料分頁：LIMIT 偏移量陷阱與三種破解方案

隨著偏移量增大，LIMIT 100000, 10 會劇烈變慢，因 MySQL 需掃描並丟棄前 10 萬行。

方案一：子查詢先取 ID 再回表

SELECT * FROM `content` 
WHERE id >= (
    SELECT id FROM `content` ORDER BY id DESC LIMIT 100000, 1
)
ORDER BY id DESC LIMIT 10;

• 子查詢只走主鍵索引（Using index），極快
• 主查詢按範圍回表，避免大偏移掃描
• 實測效率提升 14~20 倍

方案二：JOIN 延遲關聯（推薦）

SELECT * FROM `content` AS t1
JOIN (
    SELECT id FROM `content` ORDER BY id DESC LIMIT 100000, 1
) AS t2 ON t1.id = t2.id
LIMIT 10;

• 與子查詢效能持平，寫法更標準
• EXPLAIN 顯示 DERIVED 表走索引

方案三：SQL_CALC_FOUND_ROWS 取總數

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();  -- 取無 LIMIT 總行數

• 單次查詢同時獲得分頁資料與總數，省去 COUNT(*)
• 適用於需顯示總頁數的場景
• 注意：SQL_CALC_FOUND_ROWS 會掃描全表，大表慎用

效能對比總結

進階建議

• 游標分頁：記住上一頁最大 ID，WHERE id > :last_id LIMIT 10，無偏移量問題
• 覆蓋索引：SELECT 欄位全在索引中，避免回表
• 大偏移場景（如後台管理）可接受延遲，前台用戶翻頁建議游標式