萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 深分頁優化神技:延遲關聯實戰將查詢提速 3 倍

痛點:深分頁的效能崩塌

業務 SQL:

SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,
       start_time, end_time, market_type, back_leaf_category,
       item_status, picture_url
FROM relation
WHERE biz_type='0' AND end_time>='2014-05-29'
ORDER BY id ASC LIMIT 149420, 20;

表量 36 萬,LIMIT 149420, 20 導致 MySQL 掃描 14.9 萬行再丟棄,CPU 長期 30-40%,執行時間 1.2 秒+。

延遲關聯核心思想

覆蓋索引先取主鍵 → 再回表取完整列。
子查詢僅掃描索引(極窄),獲取目標 20 個 ID,外層按主鍵精準回表,避免大量無效回表與排序開銷。

改寫前後對比

優化前

EXPLAIN: type=range, key=ind_endtime, rows=349622, Extra=Using where; Using filesort
執行時間:~1.2 秒

優化後

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;
EXPLAIN:
id=1, table=, type=ALL, rows=20
id=1, table=a, type=eq_ref, key=PRIMARY, rows=1
id=2, table=relation, type=index, key=PRIMARY, rows=733552
執行時間:~0.4 秒(提速 3 倍)

適用場景與注意事項

  • ✅ 大表深分頁(OFFSET 極大)。
  • ✅ 存在可覆蓋查詢的索引(如主鍵、或含 WHERE/ORDER BY 列的複合索引)。
  • ⚠️ 衍生表 () 會物化到臨時表,若子查詢結果集極大(如 LIMIT 1000000, 20)反而拖慢,需結合書籤/游標分頁或業務層面限制翻頁深度。
  • ⚠️ MySQL 8.0+ CTE 寫法更優雅:WITH cte AS (...) SELECT a.* FROM relation a JOIN cte b ON a.id=b.id;

同類優化對比

方案原理優點缺點
前端快取減少落庫查詢最省資源資料實時性妥協
書籤/游標分頁記錄上次最大 ID,向後取無 OFFSET 掃描不支援隨機跳頁
Sphinx/ES 搜尋倒排索引天然支援分頁功能豐富架構複雜度高
延遲關聯覆蓋索引取 ID + 主鍵回表改動最小、相容性強極深分頁仍有物化開銷
copyright © 萬盛學電腦網 all rights reserved