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

以下是優化深度分頁的常用策略，根據場景選擇最適合的方案：

🛠 1. 使用覆蓋索引 + 延遲關聯 (最常用且有效)

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

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

• 優化后:

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

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

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

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

  -- 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;
  

• 優化后:

  -- Page 1
  SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;
  -- 假設最后一條記錄: 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) 索引進行高效的范圍查找，只掃描需要的行。
• 關鍵:
  • 需要一個唯一且穩定的排序鍵: 通常使用時間戳（如 created_at）或自增主鍵（如 id），或者它們的組合（如上例，防止 created_at 重復時順序不確定）。
  • 適用于連續瀏覽場景: 如無限滾動、上一頁/下一頁導航。不支持直接跳轉到任意頁碼。
  • 需要客戶端存儲"游標"（即上一頁最后記錄的排序鍵值）。
  • 處理新增/刪除數據時順序變化相對穩定（取決于排序鍵）。

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

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

🔄 4. 分區 (Partitioning)

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

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

• 為什么有效:
  • 顯著減少單次查詢需要掃描的數據量（從全表掃描變為分區掃描）。
• 關鍵:
  • 分區鍵的選擇至關重要，必須與分頁查詢的 WHERE 條件或排序強相關才能有效剪裁。
  • 分區本身不能解決分區內深度分頁的 OFFSET 問題，分區內數據量過大時仍需結合延遲關聯或游標。
  • 分區管理和維護有額外開銷。

?? 5. 其他考慮與權衡

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

📌 總結建議

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

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