前言

MySQL深度分頁（例如 LIMIT 1000000, 20）性能差的主要原因在于 OFFSET 需要掃描并跳過大量數(shù)據(jù)，即使這些數(shù)據(jù)最終并不返回。隨著 OFFSET 增大，性能會急劇下降。

以下是優(yōu)化深度分頁的常用策略，根據(jù)場景選擇最適合的方案：

1. 使用覆蓋索引 + 延遲關(guān)聯(lián) (最常用且有效)

• 核心思想:
  • 先利用覆蓋索引快速找到目標分頁行的主鍵（避免回表）。
  • 再根據(jù)這些主鍵回表關(guān)聯(lián)獲取完整的行數(shù)據(jù)。
• 優(yōu)化前 (性能差):

  SELECT * FROM your_table ORDER BY sort_column LIMIT 1000000, 20;
  

• 優(yōu)化后:

  SELECT t.*
  FROM your_table t
  JOIN (
      SELECT id -- 只選擇主鍵
      FROM your_table
      ORDER BY sort_column -- 確保有 (sort_column, id) 或類似索引
      LIMIT 1000000, 20
  ) AS tmp ON t.id = tmp.id; -- 通過主鍵關(guān)聯(lián)回原表
  

• 為什么有效:
  • 子查詢 SELECT id ... LIMIT 1000000, 20 利用了覆蓋索引（僅包含 sort_column 和 id 的索引）。數(shù)據(jù)庫引擎只需掃描索引結(jié)構(gòu)就能找到這 20 行的 ID，速度非?？欤ㄋ饕ǔ１缺頂?shù)據(jù)小得多，且在內(nèi)存中可能性高）。
  • 外層查詢 SELECT t.* ... 只需要精確地根據(jù)這 20 個 ID 回表查詢完整數(shù)據(jù)，效率極高。
• 關(guān)鍵:
  • 必須創(chuàng)建合適的索引: 通常是 (sort_column, id) 或 (sort_column, other_columns_in_where)。確保子查詢能夠使用覆蓋索引。如果 sort_column 本身是主鍵或唯一索引，直接用 (sort_column) 即可。
  • 適用于排序字段相對穩(wěn)定的情況。

2. 基于游標/連續(xù)分頁 (Cursor-based Pagination / Keyset Pagination)

• 核心思想: 放棄使用 OFFSET，改為記住上一頁最后一條記錄的排序字段值（或多個字段值），作為下一頁的起始點。
• 優(yōu)化前:

  -- Page 1
  SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;
  -- Page 2 (慢!)
  SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20;
  

• 優(yōu)化后:

  -- Page 1
  SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;
  -- 假設(shè)最后一條記錄: created_at = '2023-10-25 14:30:00', id = 12345
  -- Page 2 (快!)
  SELECT * FROM orders
  WHERE (created_at < '2023-10-25 14:30:00') OR
        (created_at = '2023-10-25 14:30:00' AND id < 12345)
  ORDER BY created_at DESC, id DESC
  LIMIT 20;
  

• 為什么有效:
  • 完全避免了 OFFSET 的掃描跳過操作。
  • 查詢利用了 (created_at DESC, id DESC) 索引進行高效的范圍查找，只掃描需要的行。
• 關(guān)鍵:
  • 需要一個唯一且穩(wěn)定的排序鍵: 通常使用時間戳（如 created_at）或自增主鍵（如 id），或者它們的組合（如上例，防止 created_at 重復時順序不確定）。
  • 適用于連續(xù)瀏覽場景: 如無限滾動、上一頁/下一頁導航。不支持直接跳轉(zhuǎn)到任意頁碼。
  • 需要客戶端存儲"游標"（即上一頁最后記錄的排序鍵值）。
  • 處理新增/刪除數(shù)據(jù)時順序變化相對穩(wěn)定（取決于排序鍵）。

3. 預(yù)先計算 & 物化視圖 (Precomputation & Materialized Views)

• 核心思想: 對于復雜查詢或聚合分頁，將結(jié)果預(yù)先計算并存儲在一個專門的分頁表或物化視圖中。
• 實現(xiàn):
  • 創(chuàng)建一個新表，包含原始表的主鍵、排序字段、以及其他分頁需要的聚合/計算字段。
  • 使用定時任務(wù)（Cron, Event Scheduler）或觸發(fā)器（謹慎使用，性能開銷大）或變更數(shù)據(jù)捕獲（CDC）來維護這個表。
  • 對這個新表進行分頁查詢（可以使用延遲關(guān)聯(lián)或游標）。
• 為什么有效:
  • 將復雜查詢的開銷分攤到預(yù)計算階段。
  • 分頁查詢的目標表更小、結(jié)構(gòu)更簡單、索引更優(yōu)化。
• 適用場景:
  • 報表分頁、需要復雜聚合的分頁、數(shù)據(jù)相對靜態(tài)或可以接受一定延遲的場景。
  • 不適合需要實時最新數(shù)據(jù)的場景。

4. 分區(qū) (Partitioning)

• 核心思想: 將大表物理分割成更小的、更易管理的片段（分區(qū)）。分頁查詢可以限定在特定分區(qū)內(nèi)進行。
• 實現(xiàn):
  • 按范圍（如 created_at 年份、月份）或列表（如 region）分區(qū)。
  • 在查詢中顯式指定分區(qū)或利用分區(qū)剪裁（WHERE 條件匹配分區(qū)鍵）。

  -- 假設(shè)按年份分區(qū)
  SELECT * FROM your_table PARTITION (p2023)
  ORDER BY sort_column
  LIMIT 1000000, 20; -- 即使有 OFFSET, 但掃描的數(shù)據(jù)量僅限 2023 分區(qū)
  

• 為什么有效:
  • 顯著減少單次查詢需要掃描的數(shù)據(jù)量（從全表掃描變?yōu)榉謪^(qū)掃描）。
• 關(guān)鍵:
  • 分區(qū)鍵的選擇至關(guān)重要，必須與分頁查詢的 WHERE 條件或排序強相關(guān)才能有效剪裁。
  • 分區(qū)本身不能解決分區(qū)內(nèi)深度分頁的 OFFSET 問題，分區(qū)內(nèi)數(shù)據(jù)量過大時仍需結(jié)合延遲關(guān)聯(lián)或游標。
  • 分區(qū)管理和維護有額外開銷。

5. 其他考慮與權(quán)衡

• 避免 SELECT *: 只查詢需要的列，減少數(shù)據(jù)傳輸和內(nèi)存占用。
• 優(yōu)化 WHERE 條件: 盡可能縮小初始數(shù)據(jù)集。有效的 WHERE 條件是所有優(yōu)化的基礎(chǔ)。
• 前端/產(chǎn)品設(shè)計:
  • 限制可訪問的頁數(shù)（例如，只允許訪問前 100 頁）。
  • 鼓勵使用搜索/過濾縮小結(jié)果集，而不是無限制翻頁。
  • 對于"跳轉(zhuǎn)到最后一頁"這種需求，考慮顯示總條目數(shù)并提供輸入框跳轉(zhuǎn)，但實現(xiàn)時可能需要估算或緩存總數(shù)。
• 分庫分表 (Sharding): 終極方案，當單機容量和性能達到極限時。將數(shù)據(jù)分散到多個物理數(shù)據(jù)庫/表中。分頁查詢會變得非常復雜，通常需要中間件或應(yīng)用層聚合。
• 緩存: 對特定查詢模式（如熱門的前幾頁）進行結(jié)果緩存。

總結(jié)建議

1. 首選嘗試延遲關(guān)聯(lián) (覆蓋索引): 適用于大多數(shù)場景，對應(yīng)用層改動較小，效果顯著。關(guān)鍵是創(chuàng)建正確的覆蓋索引。
2. 對于連續(xù)瀏覽場景 (無限滾動/上下一頁): 強烈推薦游標分頁: 性能最優(yōu)，無 OFFSET 瓶頸。需要應(yīng)用層配合存儲游標。
3. 復雜聚合/報表分頁: 考慮預(yù)計算/物化視圖: 將計算壓力轉(zhuǎn)移到后臺。
4. 海量數(shù)據(jù)且訪問模式可分區(qū): 結(jié)合分區(qū) + 上述技巧 (延遲關(guān)聯(lián)/游標): 減少單次掃描范圍。
5. 審視需求: 是否真的需要深度隨機跳頁？優(yōu)化產(chǎn)品設(shè)計往往是性價比最高的方案。
6. 監(jiān)控與分析: 使用 EXPLAIN 分析查詢執(zhí)行計劃，確認是否使用了預(yù)期的索引。

選擇哪種方案取決于你的具體數(shù)據(jù)量、訪問模式、排序需求、實時性要求以及對應(yīng)用層改動的接受程度。通常 延遲關(guān)聯(lián) 和 游標分頁 是解決深度分頁性能問題最直接有效的武器??。

到此這篇關(guān)于MySQL深度分頁優(yōu)化常用策略的文章就介紹到這了,更多相關(guān)MySQL深度分頁優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論