您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 延遲關聯優化：百萬級分頁查詢從秒級降至毫秒級實戰

編輯：MySQL 綜合教學

痛點：深分頁的效能崩塌

業務 SQL：

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

表 36 萬行，LIMIT 149420, 20 意味著 MySQL 必須掃描並丟棄前 149,420 行，**掃描行數隨 OFFSET 線性增長**，CPU 長期 30-40%。

優化思路：延遲關聯

核心思想：先用覆蓋索引只查主鍵 ID（極窄、極快），再按 ID 回表取完整列。將「大表排序+分頁」拆解為「窄索引排序+分頁」+「主鍵點查」。

優化前執行計劃

EXPLAIN SELECT ... LIMIT 149420, 20;
-- type=range, key=ind_endtime, rows=349622, Extra=Using where; Using filesort
-- 耗時：~1.2s

優化前耗時

優化後 SQL

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

優化後執行計劃

-- 衍生表 (Derived) 掃描主鍵索引，rows=733552，但無 filesort，無回表
-- 主查詢 eq_ref 主鍵點查 20 行
-- 耗時：~0.4s (提升 3 倍)

優化後耗時

為什麼有效？

階段	原查詢	延遲關聯
排序數據寬度	全行 (含 TEXT/BLOB 等大字段)	僅 8 字節 BIGINT ID
Sort Buffer 利用率	極低，易分段、用臨時表	極高，單 Buffer 可裝下所有排序鍵
回表次數	OFFSET + LIMIT 次 (149,440 次)	僅 LIMIT 次 (20 次)
IO 模式	大量隨機 IO	極少量點查 IO

適用場景與限制

✅ 大表、深分頁、ORDER BY 主鍵/唯一鍵
✅ WHERE 條件可走索引（哪怕是範圍掃描）
❌ ORDER BY 非索引列（仍需 filesort，收益有限）
❌ 複雜 JOIN 查詢（衍生表優化器處理較弱）

其他深分頁優化方案對比

方案	原理	適用性	維護成本
延遲關聯	窄索引排序+主鍵回表	通用、無業務改造	低
書籤/游標分頁	記錄上次最大 ID，`WHERE id > last_id LIMIT 20`	需前端配合、不支援跳頁	中
前端緩存	Redis 緩存熱門頁面	讀多寫少、資料準實時性低	中
搜尋引擎	Elasticsearch/Sphinx 替代	複雜檢索、全文搜尋	高

實戰提醒

MySQL 8.0+ CTE 寫法更優雅：WITH ids AS (...) SELECT a.* FROM relation a JOIN ids b ON a.id=b.id
確保子查詢走覆蓋索引（Using index），否則退化為全表掃描
監控 Handler_read_rnd_next 指標驗證優化效果