MySQL LIMIT 查詢優化經典案例：延遲關聯法實測數據對比

問題背景

MySQL LIMIT offset, size 在大偏移量時效能急劇下降。本文複現經典測試數據，驗證「延遲關聯」優化效果。

測試環境

• OS：Windows Server 2003
• CPU：P4 雙核 3.0GHz
• RAM：4GB
• 表：yanxue8_visit，主鍵 vid，百萬級資料

測試對比

Case 1：小偏移

-- 直接 LIMIT
SELECT * FROM yanxue8_visit LIMIT 10, 10;  -- 0.0004~0.0005s

-- 延遲關聯
SELECT * FROM yanxue8_visit 
WHERE vid >= (SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10, 1) 
LIMIT 10;  -- 0.0005~0.0006s

結論：小偏移直接 LIMIT 更快，子查詢額外開銷無優勢。

Case 2：大偏移

-- 直接 LIMIT
SELECT * FROM yanxue8_visit LIMIT 10000, 10;  -- 0.0187s

-- 延遲關聯
SELECT * FROM yanxue8_visit 
WHERE vid >= (SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 1) 
LIMIT 10;  -- 0.0061s (快 3 倍)

結論：偏移量越大，延遲關聯優勢越明顯。原理：子查詢僅掃描主鍵索引（覆蓋索引），極快定位起始 ID，外層主鍵範圍查詢避免回表掃描無用行。

優化模板

-- 通用模板：假設主鍵為 id，排序欄位同主鍵
SELECT * FROM `table` 
WHERE id >= (SELECT id FROM `table` ORDER BY id LIMIT #{offset}, 1) 
LIMIT #{size};

避坑指南

• 排序欄位非主鍵：子查詢需 ORDER BY sort_col, id 保證確定性，外層 WHERE sort_col > val OR (sort_col = val AND id >= sub_id) 較複雜，建議改用游標分頁。
• 主鍵非自增/有空洞：id >= sub_id 可能取不到足夠行，需改用 ROW_NUMBER() (MySQL 8.0)。
• 小偏移場景：直接用 LIMIT，簡單高效。

總結

深分頁優化核心是「先定位主鍵，再回表取數」。延遲關聯法在 MySQL 5.x 時代是經典解法，MySQL 8.0 建議改用窗口函數 ROW_NUMBER() 或應用層游標分頁，徹底消除 Offset 掃描開銷。