MySQL LIMIT 分頁優化：從偏移量陷阱到延遲關聯實戰

MySQL LIMIT 分頁優化：四種寫法、大偏移量效能崩塌、三大破解方案

LIMIT 寫法辨析

易混淆點：LIMIT offset, count 中 offset 先、count 後；LIMIT count OFFSET offset 關鍵字明確。

大偏移量效能崩塌原因

SELECT * FROM yanxue8_visit LIMIT 10000, 10;  -- 掃描 10010 行，丟棄 10000 行
SELECT * FROM yanxue8_visit LIMIT 0, 10;      -- 僅掃描 10 行

偏移量越大，被丟棄的行越多，IO 與 CPU 浪費指數級增長。

優化方案對比實測

方案一：子查詢先取 ID（延遲關聯雛形）

SELECT * FROM yanxue8_visit 
WHERE vid >= (SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 1)
LIMIT 10;

• 子查詢只走主鍵索引 Using index
• 主查詢範圍回表

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

SELECT * FROM yanxue8_visit AS t1
JOIN (SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 1) AS t2
ON t1.vid = t2.vid
LIMIT 10;

實測數據（Win2003、P4 雙核 3GHz、4G、MySQL 5.0.19）

結論：小偏移原生更優；大偏移延遲關聯碾壓，且偏移越大優勢越明顯。

進階：游標分頁（無偏移量）

-- 首頁
SELECT * FROM t ORDER BY id DESC LIMIT 20;
-- 記住最後一行 id = 1000
-- 下一頁
SELECT * FROM t WHERE id < 1000 ORDER BY id DESC LIMIT 20;

• 無偏移量、無大表掃描、效能恆定
• 適用：無限滾動、API 分頁、不需跳頁碼

SQL_CALC_FOUND_ROWS 取總數

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

• 單查詢得分頁數據 + 總數
• 但會全表掃描，**大表慎用**

總結建議

1. 小偏移（<1000）：原生 LIMIT
2. 大偏移：JOIN 延遲關聯
3. 無限滾動/APP：游標分頁
4. 需總頁數且表不大：SQL_CALC_FOUND_ROWS