深分頁(yè)問題

一個(gè)商品列表頁(yè)，后端接口用的分頁(yè)查詢：

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

前幾頁(yè)加載很快，用戶也沒啥感覺。但當(dāng)翻到第 500 頁(yè)的時(shí)候，接口響應(yīng)時(shí)間從 50ms 飆到了 3 秒。你打開慢查詢?nèi)罩疽豢?，又是這條 SQL 在搞事。

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

LIMIT offset, count 到底在干什么

先看一條最簡(jiǎn)單的分頁(yè) SQL：

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

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

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

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

跳過(guò)不等于不掃描。OFFSET 越大，跳過(guò)越多，掃描越多。

為什么 OFFSET 越大越慢

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

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 說(shuō)明走了全索引掃描（遍歷整棵索引樹），rows 為 1020 說(shuō)明預(yù)估要掃描 1020 行。

OFFSET 越大，這個(gè) rows 值就越大。掃到第 100 萬(wàn)頁(yè)時(shí)，光跳過(guò)就得掃描 100 萬(wàn)條記錄。即使每條記錄掃描只要 0.1 毫秒，100 萬(wàn)條也要 100 秒。

更糟的是，這個(gè)查詢除了掃描索引，還要回表取 * 的所有字段。每一條被跳過(guò)的記錄，MySQL 可能都要做一次回表。 因?yàn)?SELECT * 取的是完整行數(shù)據(jù)，索引里存不下了，必須回表。

這就是深分頁(yè)慢的兩個(gè)根源：

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

方案一：延遲關(guān)聯(lián)，先查 ID 再取數(shù)據(jù)

延遲關(guān)聯(lián)的核心思路是：先用覆蓋索引快速拿到需要的 ID，再用 ID 回表取完整數(shù)據(jù)。

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 個(gè) ID

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

為什么這樣更快？對(duì)比一下：

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

方案二：游標(biāo)分頁(yè)，用上一頁(yè)的最后一條當(dāng)起點(diǎn)

延遲關(guān)聯(lián)解決了回表浪費(fèi)，但掃描浪費(fèi)還在——OFFSET 1000 時(shí)還是要跳過(guò) 1000 條。游標(biāo)分頁(yè)直接把 OFFSET 干掉了。

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

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

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

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

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

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 條就停了。不管翻到第幾頁(yè)，掃描行數(shù)永遠(yuǎn)是 20。

但游標(biāo)分頁(yè)有局限：只能"下一頁(yè)"，不能跳頁(yè)。 用戶點(diǎn)第 5 頁(yè)，你沒法直接算出對(duì)應(yīng)的 ID 是多少。所以它適用于無(wú)限滾動(dòng)、加載更多這類場(chǎng)景，不適合有頁(yè)碼的分頁(yè)器。

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

這個(gè)方案適合沒有主鍵可用、或者排序字段不是主鍵的場(chǎng)景。

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。外層查詢從這個(gè) ID 開始往后取 20 條。

和延遲關(guān)聯(lián)的區(qū)別在于：延遲關(guān)聯(lián)是"先查一批 ID，再用 ID 取數(shù)據(jù)"；這個(gè)方案是"先找一個(gè)起點(diǎn) ID，再?gòu)钠瘘c(diǎn)往后取"。子查詢只返回一條記錄，開銷極小。

用偽代碼理解：

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

// 外層：從起點(diǎn)取數(shù)據(jù)
SELECT * FROM products WHERE id >= start_id ORDER BY id LIMIT 20

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

三種方案對(duì)比

選擇建議：

• 有頁(yè)碼導(dǎo)航的需求（后臺(tái)管理系統(tǒng)、商品搜索）：延遲關(guān)聯(lián)或子查詢優(yōu)化
• 無(wú)限滾動(dòng)、信息流（朋友圈、微博）：游標(biāo)分頁(yè)
• 數(shù)據(jù)量千萬(wàn)級(jí)：游標(biāo)分頁(yè)是唯一選擇，其他方案在超大 OFFSET 下依然會(huì)退化

小結(jié)

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

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

最新評(píng)論