MySQL 分頁查詢優化

在這里插入圖片描述

- 前言
- 1. LIMIT offset, count 的性能陷阱：為什么它慢？😩
- 2. 優化策略一：基于排序字段的“跳躍式”查詢 (Seek Method) 🚀
- 3. 優化策略二：利用子查詢優化 OFFSET 掃描 (ID Subquery)
- 4. 基礎優化：為 ORDER BY 列創建索引
- 5. 優化總頁數/總記錄數計算 (COUNT(*) 問題)
- 6. EXPLAIN 分析分頁查詢
- 7. 總結與選擇最佳策略

🌟我的其他文章也講解的比較有趣😁，如果喜歡博主的講解方式，可以多多支持一下，感謝🤗！

其他優質專欄：【🎇SpringBoot】【🎉多線程】【🎨Redis】【?設計模式專欄（已完結）】…等

如果喜歡作者的講解方式，可以點贊收藏加關注，你的支持就是我的動力
?更多文章請看個人主頁：碼熔burning

前言

你好呀，正在與大量數據打交道的開發者們！👋 分頁，這個再尋常不過的功能，背后卻隱藏著一個性能殺手——那就是 LIMIT offset, count 中不斷增大的 offset 值。隨著用戶翻頁越來越深，查詢速度卻越來越慢，這不僅影響用戶體驗，還可能給數據庫帶來巨大的壓力。

今天，我們就來揭露 LIMIT offset, count 的性能問題，并學習幾種有效的優化策略，讓你的分頁查詢在數據量和深度增加時依然保持高性能。

1. LIMIT offset, count 的性能陷阱：為什么它慢？😩

我們常用的分頁查詢語句通常長這樣：

SELECT * FROM articles ORDER BY publish_time DESC LIMIT 10000, 10; -- 查找第 10001 到 10010 條記錄

這條語句的意圖很明確：跳過前面 10000 條記錄，然后返回接下來的 10 條。

問題就出在這個 offset (10000) 上。

MySQL 在處理 LIMIT offset, count 時，如果不借助優化手段，它的基本行為是：

首先，找到所有符合 WHERE 條件（如果沒有 WHERE 就是全表）的記錄。
然后，對這些記錄進行 ORDER BY 排序（如果 ORDER BY 的列沒有合適的索引，這里還會發生 Using filesort，進一步加劇性能問題）。
接下來，跳過前 offset 條記錄！ MySQL 必須把這些記錄一條條地讀出來，然后再把它們拋棄掉。
最后，返回接下來的 count 條記錄。

想象一下，如果 offset 是 100萬，count 是 10。數據庫需要找到并排序至少 100萬零 10 條記錄，然后丟棄掉 100萬條，只返回最后的 10條。這前 100萬條記錄的讀取和處理，就是白白浪費的性能開銷！ 而且 offset 越大，浪費的開銷就越大，查詢就越慢。這就是深層分頁的痛點。

在 EXPLAIN 中，雖然 LIMIT 子句會體現在執行計劃中，但你無法直接從 rows 這一列看出 offset 帶來的巨大開銷。rows 顯示的是優化器預估需要掃描的行數，而不是實際跳過的行數。LIMIT 的影響體現在它強制優化器必須找到 offset + count 條記錄才能結束掃描。

2. 優化策略一：基于排序字段的“跳躍式”查詢 (Seek Method) 🚀

這是最常用且效果最好的優化方法，特別是對于只能一頁一頁往后翻（或往前翻）的場景。它的核心思想是：不再使用 OFFSET 跳過，而是記錄上一頁最后一條記錄的排序字段值，然后通過 WHERE 條件直接定位到下一頁的起始位置。

適用場景：

你希望實現“下一頁”、“上一頁”這樣的分頁導航，而不是直接跳轉到任意頁碼。
你的排序字段（或聯合排序字段）是有索引的。

原理：

假設你按照 id 升序排序，每頁顯示 10 條記錄。

第一頁： SELECT * FROM articles ORDER BY id ASC LIMIT 10;
- 獲取第一頁的 10 條記錄，并記住最后一條記錄的 id 值，比如是 10。
第二頁： 不再使用 LIMIT 10, 10。而是使用上一頁最后的 id 值作為起點：
- SELECT * FROM articles WHERE id > 10 ORDER BY id ASC LIMIT 10;
- MySQL 可以直接利用 id 索引，快速找到 id > 10 的第一條記錄，然后順序掃描索引和數據行，直到找到 10 條記錄為止。完全避免了掃描和丟棄前 10 條記錄的開銷。
第三頁： 記住第二頁最后一條記錄的 id 值，比如是 20。查詢：
- SELECT * FROM articles WHERE id > 20 ORDER BY id ASC LIMIT 10;

以此類推，無論翻到多深，每一頁的查詢都只基于上一頁的終點進行簡單的索引查找和順序掃描 count 條記錄，性能非常穩定，不受 offset 影響。

處理非唯一排序字段：

如果你的排序字段不是唯一的（比如按發布時間 publish_time 排序，可能同一時間發布多篇文章），僅僅使用 WHERE publish_time > last_time 是不夠的，可能會漏掉或重復記錄。這時需要增加一個次級排序字段作為“決勝”條件，通常是主鍵 ID：

假設按 publish_time 倒序，然后按 id 倒序（確保唯一性），每頁 10 條。

第一頁： SELECT * FROM articles ORDER BY publish_time DESC, id DESC LIMIT 10;
- 獲取第一頁數據，記住最后一條記錄的 publish_time 和 id，比如是 ('2024-05-07 10:00:00', 120)。
第二頁： WHERE 條件需要同時考慮這兩個字段：
- SELECT * FROM articles WHERE (publish_time < '2024-05-07 10:00:00') OR (publish_time = '2024-05-07 10:00:00' AND id < 120) ORDER BY publish_time DESC, id DESC LIMIT 10;
- 這個條件的意思是：找發布時間早于上一頁最后一條的記錄，或者發布時間與上一頁最后一條相同但 ID 更小的記錄。
- 為了讓這個 WHERE 條件高效，你需要一個覆蓋 (publish_time, id) 的聯合索引，且順序和方向與 ORDER BY 匹配。

優點：

性能極高，不受 offset 大小影響。
避免了掃描和丟棄大量記錄的開銷。
如果排序字段有索引，MySQL 可以高效定位起始點。

缺點：

無法直接跳轉到任意頁碼。 只能從第一頁開始，一頁一頁往后翻（或往前翻，但 WHERE 條件和 ORDER BY 方向需要反過來）。
需要客戶端或應用端記錄并傳遞上一頁最后一條記錄的關鍵字段值。
需要排序字段是索引列。

EXPLAIN 對比：

原始查詢 EXPLAIN SELECT * FROM articles ORDER BY id LIMIT 10000, 10;：rows 可能會很大（預估掃描 10010 條），Extra 可能有 Using filesort (如果 id 不是主鍵且沒索引)。
優化后查詢 EXPLAIN SELECT * FROM articles WHERE id > 10000 ORDER BY id LIMIT 10;：rows 會小很多（預估掃描 10 條，因為 WHERE 條件已經縮小了范圍），type 可能是 range，Extra 中沒有 Using filesort。

3. 優化策略二：利用子查詢優化 OFFSET 掃描 (ID Subquery)

如果你需要支持用戶直接跳轉到任意頁碼，或者排序字段沒有合適的索引（雖然強烈建議為排序字段建索引！），可以考慮這種方法。它的核心是先在一個子查詢中查出當前頁的行的主鍵 ID，然后在外層查詢中根據這些 ID 去獲取完整的行數據。

原理：

SELECT t1.*
FROM articles t1
JOIN (SELECT idFROM articlesORDER BY publish_time DESCLIMIT 10000, 10 -- 在子查詢中進行低成本的 OFFSET 掃描
) as t2 ON t1.id = t2.id
ORDER BY t2.publish_time DESC; -- 注意：如果外層需要保持排序，這里可能需要再次 ORDER BY-- 或者如果子查詢的 ORDER BY 列已經包含在 SELECT id 中，可以直接用 t2 的列排序

解釋：

子查詢 (SELECT id FROM articles ORDER BY publish_time DESC LIMIT 10000, 10)：
- 這個子查詢仍然使用了 LIMIT offset, count，會掃描并丟棄前 offset 條記錄。
- 但它只查詢了主鍵 ID。主鍵 ID 通常很小，且主鍵本身就是索引。相比于查詢整個寬行并進行排序，只查詢和排序 ID 列表的開銷要小得多。
- 如果 ORDER BY publish_time DESC 可以利用 publish_time 的索引，那么子查詢的速度會更快（避免 Filesort）。
外層查詢 SELECT t1.* FROM articles t1 JOIN ... ON t1.id = t2.id：
- 外層查詢根據子查詢返回的少數（10個）ID，通過主鍵索引 (id) 去 articles 表中精確地找到并獲取這 10 行完整的記錄。這是高效的等值 JOIN。
- 最后的 ORDER BY t2.publish_time DESC 確保結果集按照正確的順序返回。如果 publish_time 也在子查詢的 SELECT 列表中，可以直接用 t2.publish_time 排序。

優點：

支持跳轉到任意頁碼。
相比直接對全行進行 LIMIT offset, count，子查詢對更小的 ID 集合進行 OFFSET 掃描和排序，性能有所提升（尤其當原始表的行很寬，或者 ORDER BY 的列沒有索引時）。

缺點：

性能仍然會隨著 offset 增大而下降，只是下降的速度可能比直接 LIMIT offset, count 慢。
SQL 語句更復雜。
引入了 JOIN 的開銷。

EXPLAIN 分析：

EXPLAIN 這個子查詢 JOIN 語句，你會看到子查詢 (DERIVED) 的執行計劃，它會顯示 LIMIT 和可能的 Filesort (如果 publish_time 沒索引)。外層 JOIN 會顯示通過 id 進行 JOIN 的類型（通常是 eq_ref 或 ref）。

4. 基礎優化：為 ORDER BY 列創建索引

無論你選擇哪種分頁策略，為 ORDER BY 子句中使用的列創建索引都是至關重要的一步。

如果你的 ORDER BY 列沒有索引，MySQL 就會進行 Using filesort，這本身就是一個巨大的性能開銷。分頁只會加劇這個開銷，因為它需要對 offset + count 這么多行進行排序。
如果 ORDER BY 列有索引，MySQL 可以避免 Filesort，直接按照索引順序讀取數據。這使得上面的策略一成為可能，也使得策略二中的子查詢更快。

請回顧之前關于 ORDER BY 優化的部分，確保你的排序字段有合適的索引，特別是聯合索引的列順序和方向。

5. 優化總頁數/總記錄數計算 (COUNT(*) 問題)

分頁界面通常需要顯示總記錄數或總頁數。計算這個值通常需要執行一個獨立的 SELECT COUNT(*) 語句，而且這個語句需要和分頁查詢使用相同的 WHERE 條件。

SELECT COUNT(*) FROM articles WHERE status = 'published';

對于大表，COUNT(*) 也可能非常慢，因為它通常需要掃描大量數據（至少是索引）。頻繁執行這個查詢會給數據庫帶來壓力。

優化 COUNT(*) 的策略：

犧牲精確性，提供估算值： 告訴用戶“大約有xxx條記錄”，而不是精確數字。可以使用 EXPLAIN SELECT ... 的 rows 估算值（不準確），或者維護一個近似的計數器。
限制總頁數： 只顯示前幾十頁或前一百頁的頁碼，更深的頁只提供“下一頁”按鈕。這樣可以避免計算一個巨大的 COUNT(*) 值。
異步加載計數： 先加載數據，后在后臺異步計算總數。
緩存計數： 將計數結果緩存一段時間，而不是每次都實時查詢。
維護計數器表： 對于非常頻繁且需要精確計數的場景，可以考慮通過觸發器或業務邏輯維護一個獨立的計數器表。
SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() (謹慎使用): 這是一個 MySQL 特性，在執行完 LIMIT 查詢后，可以通過 SELECT FOUND_ROWS() 獲取如果沒有 LIMIT 的總行數。看起來很方便，但底層實現并不總是高效（尤其在 InnoDB 中），它可能還是需要掃描大量行來確定總數。在很多情況下，單獨執行一個優化好的 COUNT(*)（可能利用覆蓋索引）會比 SQL_CALC_FOUND_ROWS 更快。通常不推薦在新的設計中使用它。

選擇哪種策略取決于你的業務需求和對精確度的要求。 對于大多數用戶來說，知道有“下一頁”或者一個大致的數字就足夠了，不需要精確到個位的總數。

6. EXPLAIN 分析分頁查詢

在優化分頁查詢時，EXPLAIN 是必不可少的工具。

分析原始查詢 (LIMIT offset, count)： 查看 type, key, rows, Extra。特別關注 Extra 中的 Using filesort 和 rows 的大小。rows 會告訴你為了得到這 count 行，優化器預估掃描了多少行，這個數字通常接近 offset + count 或更多。
分析子查詢優化方案 (JOIN (SELECT ID ... LIMIT offset, count))： 分別 EXPLAIN 外層查詢和子查詢。查看子查詢的 type, key, rows, Extra，看看 ID 的掃描是否高效，是否有 Filesort。查看外層 JOIN 的類型和使用的索引。
分析跳躍式查詢方案 (WHERE sort_key > last_value LIMIT count)： 查看 type ( ideally range), key, rows (應該很小，接近 count), Extra (沒有 Using filesort，可能有 Using index condition 如果有額外 WHERE 條件)。

通過對比不同方案的 EXPLAIN 輸出，你可以直觀地看到哪種方法減少了掃描的行數，消除了 Filesort，從而提高了效率。

7. 總結與選擇最佳策略

首要任務： 永遠為你的 ORDER BY 列創建合適的索引。這是所有高效分頁的基礎。
首選方案（對于“下一頁/上一頁”導航）： 使用基于索引排序字段的跳躍式查詢（Seek Method）。實現方式是記錄上一頁最后一條記錄的排序字段值，下一頁查詢使用 WHERE sort_key > last_value ORDER BY sort_key LIMIT count。處理非唯一排序字段時，記得加上主鍵作為次級排序字段和 WHERE 條件的組合判斷。
備選方案（對于任意頁碼跳轉）： 如果必須支持任意頁碼跳轉，可以考慮子查詢方案，先在子查詢中利用索引（特別是主鍵或排序字段索引）快速獲取當前頁的 ID 列表，再 JOIN 回原表。性能優于直接 LIMIT offset, count，但仍會隨 offset 增加而性能下降。
優化總數： 重新評估是否需要精確的總記錄數。如果不需要，考慮使用估算、限制頁數或異步計算等方法，避免昂貴的 COUNT(*) 查詢。
實踐驗證： 任何優化都需要在實際環境和數據下用 EXPLAIN 和性能測試來驗證效果。