深分頁問題

一個商品列表頁，后端接口用的分頁查詢：

SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;

前幾頁加載很快，用戶也沒啥感覺。但當翻到第 500 頁的時候，接口響應時間從 50ms 飆到了 3 秒。你打開慢查詢日志一看，又是這條 SQL 在搞事。

這就是深分頁問題，表里有 50 萬條數據，id 是主鍵，按理說走索引應該很快。但 OFFSET 一大，性能就斷崖式下跌。這不是個例，幾乎所有用 LIMIT offset, count 做分頁的系統(tǒng)，隨著數據量的增加都會撞上這堵墻。

LIMIT offset, count 到底在干什么

先看一條最簡單的分頁 SQL：

SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 1000;

這條語句的執(zhí)行過程是這樣的：

1. MySQL 從索引（主鍵）上從第一條開始，逐條往后掃
2. 掃到第 1 條時開始計數，跳過前 1000 條
3. 從第 1001 條開始，取 20 條返回
4. 對這 20 條記錄，回表取完整行數據

關鍵在第 2 步。MySQL 必須逐條跳過前 1000 條記錄，即使它不需要這些數據。 這些被跳過的記錄，MySQL 一樣要掃描、一樣要比較，只是最終不返回而已。

跳過不等于不掃描。OFFSET 越大，跳過越多，掃描越多。

為什么 OFFSET 越大越慢

用 EXPLAIN 看一下這條查詢的執(zhí)行計劃：

EXPLAIN SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 1000;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | products | NULL       | index| NULL          | PRIMARY | 8     | NULL | 1020 | 100.00   | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

注意 type = index 和 rows = 1020。type 為 index 說明走了全索引掃描（遍歷整棵索引樹），rows 為 1020 說明預估要掃描 1020 行。

OFFSET 越大，這個 rows 值就越大。掃到第 100 萬頁時，光跳過就得掃描 100 萬條記錄。即使每條記錄掃描只要 0.1 毫秒，100 萬條也要 100 秒。

更糟的是，這個查詢除了掃描索引，還要回表取 * 的所有字段。每一條被跳過的記錄，MySQL 可能都要做一次回表。 因為 SELECT * 取的是完整行數據，索引里存不下了，必須回表。

這就是深分頁慢的兩個根源：

1. 掃描浪費：OFFSET 越大，MySQL 丟棄的記錄越多，但掃描成本不變
2. 回表浪費：SELECT * 導致每條被跳過的記錄都可能觸發(fā)回表

方案一：延遲關聯，先查 ID 再取數據

延遲關聯的核心思路是：先用覆蓋索引快速拿到需要的 ID，再用 ID 回表取完整數據。

SELECT p.* FROM products p
INNER JOIN (
    SELECT id FROM products ORDER BY id LIMIT 20 OFFSET 1000
) t ON p.id = t.id;

這條 SQL 分兩步執(zhí)行：

第一步（子查詢）：
    SELECT id FROM products ORDER BY id LIMIT 20 OFFSET 1000
    → 只掃主鍵索引，不需要回表，快速拿到 20 個 ID

第二步（外層查詢）：
    SELECT p.* FROM products p WHERE p.id IN (...)
    → 用主鍵精確查 20 條，直接走聚簇索引，零回表

為什么這樣更快？對比一下：

子查詢用了覆蓋索引（只取 id），掃描階段的開銷大幅降低。外層查詢用主鍵精確查找，不用掃描、不用排序。

方案二：游標分頁，用上一頁的最后一條當起點

延遲關聯解決了回表浪費，但掃描浪費還在——OFFSET 1000 時還是要跳過 1000 條。游標分頁直接把 OFFSET 干掉了。

思路是：記住上一頁最后一條記錄的 ID，下一頁查詢時從這個 ID 之后開始取。

-- 第一頁
SELECT * FROM products ORDER BY id LIMIT 20;
-- 返回的最后一條 id = 1000

-- 第二頁：從 id = 1000 之后開始
SELECT * FROM products WHERE id > 1000 ORDER BY id LIMIT 20;

-- 第三頁：從上一頁最后一條 id = 1020 之后開始
SELECT * FROM products WHERE id > 1020 ORDER BY id LIMIT 20;

EXPLAIN 看一下執(zhí)行計劃：

EXPLAIN SELECT * FROM products WHERE id > 1000 ORDER BY id LIMIT 20;

+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | products | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |  20  | 100.00   | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+

type = range，rows = 20。MySQL 直接定位到 id > 1000 的位置，取 20 條就停了。不管翻到第幾頁，掃描行數永遠是 20。

但游標分頁有局限：只能"下一頁"，不能跳頁。 用戶點第 5 頁，你沒法直接算出對應的 ID 是多少。所以它適用于無限滾動、加載更多這類場景，不適合有頁碼的分頁器。

方案三：子查詢優(yōu)化，讓 MySQL 先走索引

這個方案適合沒有主鍵可用、或者排序字段不是主鍵的場景。

SELECT p.* FROM products p
WHERE p.id >= (
    SELECT id FROM products ORDER BY id LIMIT 1 OFFSET 1000
)
ORDER BY p.id
LIMIT 20;

子查詢只執(zhí)行一次，拿到 OFFSET 位置的那條記錄的 ID。外層查詢從這個 ID 開始往后取 20 條。

和延遲關聯的區(qū)別在于：延遲關聯是"先查一批 ID，再用 ID 取數據"；這個方案是"先找一個起點 ID，再從起點往后取"。子查詢只返回一條記錄，開銷極小。

用偽代碼理解：

// 子查詢：找起點
start_id = SELECT id FROM products ORDER BY id LIMIT 1 OFFSET 1000

// 外層：從起點取數據
SELECT * FROM products WHERE id >= start_id ORDER BY id LIMIT 20

外層查詢 id >= start_id 加上 ORDER BY id 和 LIMIT 20，MySQL 可以直接走主鍵范圍掃描，rows 只有 20。

三種方案對比

選擇建議：

• 有頁碼導航的需求（后臺管理系統(tǒng)、商品搜索）：延遲關聯或子查詢優(yōu)化
• 無限滾動、信息流（朋友圈、微博）：游標分頁
• 數據量千萬級：游標分頁是唯一選擇，其他方案在超大 OFFSET 下依然會退化

小結

深分頁慢的根源：OFFSET 越大，MySQL 丟棄的數據越多，但掃描的成本一點沒少。 延遲關聯用覆蓋索引減少了回表浪費，子查詢優(yōu)化用一個精確的起點取代了逐條跳過，游標分頁則直接繞過了 OFFSET 的問題。三者本質都在做同一件事：讓 MySQL 跳過那些不需要的記錄，而不是掃描了再丟掉。

到此這篇關于MySQL中OFFSET 越大越慢怎么解決的文章就介紹到這了,更多相關MySQL OFFSET 越大越慢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論