前言

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

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

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

• 核心思想:
  • 先利用覆蓋索引快速找到目標(biāo)分頁(yè)行的主鍵（避免回表）。
  • 再根據(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) 或類(lèi)似索引
      LIMIT 1000000, 20
  ) AS tmp ON t.id = tmp.id; -- 通過(guò)主鍵關(guān)聯(lián)回原表
  

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

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

• 核心思想: 放棄使用 OFFSET，改為記住上一頁(yè)最后一條記錄的排序字段值（或多個(gè)字段值），作為下一頁(yè)的起始點(diǎn)。
• 優(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 的掃描跳過(guò)操作。
  • 查詢(xún)利用了 (created_at DESC, id DESC) 索引進(jìn)行高效的范圍查找，只掃描需要的行。
• 關(guān)鍵:
  • 需要一個(gè)唯一且穩(wěn)定的排序鍵: 通常使用時(shí)間戳（如 created_at）或自增主鍵（如 id），或者它們的組合（如上例，防止 created_at 重復(fù)時(shí)順序不確定）。
  • 適用于連續(xù)瀏覽場(chǎng)景: 如無(wú)限滾動(dòng)、上一頁(yè)/下一頁(yè)導(dǎo)航。不支持直接跳轉(zhuǎn)到任意頁(yè)碼。
  • 需要客戶(hù)端存儲(chǔ)"游標(biāo)"（即上一頁(yè)最后記錄的排序鍵值）。
  • 處理新增/刪除數(shù)據(jù)時(shí)順序變化相對(duì)穩(wěn)定（取決于排序鍵）。

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

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

4. 分區(qū) (Partitioning)

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

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

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

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

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

總結(jié)建議

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

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

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