MySQL 延遲關聯優化：破解大偏移量分頁查詢性能難題

面對百萬級數據表的大偏移量分頁查詢（LIMIT 149420, 20），傳統 ORDER BY ... LIMIT offset, rows 會導致 MySQL 掃描並丟棄大量無用行，造成 CPU 飆升、查詢緩慢。本文通過實戰案例，詳細演示「延遲關聯」優化技巧，將查詢時間縮減至原來的 1/3。

一、問題場景：大偏移量分頁的性能陷阱

某業務表 relation 約 36 萬行，高頻 SQL：

SELECT id, cu_id, name, ... 
FROM relation 
WHERE biz_type='0' AND end_time >= '2014-05-29' 
ORDER BY id ASC LIMIT 149420, 20;

執行計劃顯示：type=range、key=ind_endtime、rows=349622、Extra=Using where; Using filesort。MySQL 必須先按 ind_endtime 篩選、排序、掃描前 149,420 行再丟棄，最後取 20 行，導致大量隨機 I/O 與 CPU 計算，執行時間達秒級。

二、優化原理：延遲關聯

核心思想：利用覆蓋索引只掃描主鍵 ID，避免回表開銷；再用主鍵關聯原表取回所需列。

1. 子查詢：SELECT id FROM relation WHERE ... ORDER BY id LIMIT 149420, 20 —— 只讀取索引列，覆蓋索引掃描極快。
2. 外層關聯：SELECT a.* FROM relation a JOIN (...) b ON a.id=b.id —— 僅 20 次主鍵精確查找，無排序、無 filesort。

三、實戰優化與效果對比

優化後 SQL：

SELECT a.* 
FROM relation a,
     (SELECT id FROM relation 
      WHERE biz_type='0' AND end_time >= '2014-05-29' 
      ORDER BY id ASC LIMIT 149420, 20) b
WHERE a.id = b.id;

執行計劃變化：

• 衍生表 b：type=index、key=PRIMARY、rows=733552、Extra=Using index（覆蓋索引全掃，但無排序、無回表）
• 主表 a：type=eq_ref、key=PRIMARY、rows=1（20 次主鍵點查）

結果：執行時間從秒級降至毫秒級，約為原查詢的 1/3，CPU 佔用顯著下降。

四、適用條件與注意事項

• 表需有合適的覆蓋索引（如 (biz_type, end_time, id) 或主鍵 id 為聚簇索引）。
• 偏移量極大（> 10 萬）時效果最明顯；小偏移量優化收益有限。
• MySQL 5.6+ 對衍生表優化較好；5.5 需注意 derived_merge 參數。
• 業務層若能改用「書簽式分頁」（記錄上次最大 ID，WHERE id > last_id LIMIT 20）性能更優，但需前端配合。