MySQL中OFFSET越大越慢怎样解决

深分页问题

一个商品列表页，后端接口用的分页查询：

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

前几页加载很快，用户也没啥感觉。但当翻到第 500 页的时候，接口响应时间从 50ms 飙到了 3 秒。你打开慢查询日志一看，又是这条 SQL 在搞事。

这就是深分页问题，表里有 50 万条数据，id 是主键，按理说走索引应该很快。但 OFFSET 一大，性能就断崖式下跌。这不是个例，几乎所有用 LIMIT offset, count 做分页的系统，随着数据量的增加都会撞上这堵墙。

LIMIT offset, count 到底在干什么

先看一条最简单的分页 SQL：

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

这条语句的执行过程是这样的：

1. MySQL 从索引（主键）上从第一条开始，逐条往后扫

2. 扫到第 1 条时开始计数，跳过前 1000 条

3. 从第 1001 条开始，取 20 条返回

4. 对这 20 条记录，回表取完整行数据

关键在第 2 步。MySQL 必须逐条跳过前 1000 条记录，即使它不需要这些数据。 这些被跳过的记录，MySQL 一样要扫描、一样要比较，只是最终不返回而已。

跳过不等于不扫描。OFFSET 越大，跳过越多，扫描越多。

为什么 OFFSET 越大越慢

用 EXPLAIN 看一下这条查询的执行计划：

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 * 导致每条被跳过的记录都可能触发回表

方案一：延迟关联，先查 ID 再取数据

延迟关联的核心思路是：先用覆盖索引快速拿到需要的 ID，再用 ID 回表取完整数据。

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

这条 SQL 分两步执行：

第一步（子查询）：

    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 看一下执行计划：

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 是多少。所以它适用于无限滚动、加载更多这类场景，不适合有页码的分页器。

方案三：子查询优化，让 MySQL 先走索引

这个方案适合没有主键可用、或者排序字段不是主键的场景。

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

子查询只执行一次，拿到 OFFSET 位置的那条记录的 ID。外层查询从这个 ID 开始往后取 20 条。

和延迟关联的区别在于：延迟关联是"先查一批 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。

三种方案对比

选择建议：

• 有页码导航的需求（后台管理系统、商品搜索）：延迟关联或子查询优化
• 无限滚动、信息流（朋友圈、微博）：游标分页
• 数据量千万级：游标分页是唯一选择，其他方案在超大 OFFSET 下依然会退化

小结

深分页慢的根源：OFFSET 越大，MySQL 丢弃的数据越多，但扫描的成本一点没少。 延迟关联用覆盖索引减少了回表浪费，子查询优化用一个精确的起点取代了逐条跳过，游标分页则直接绕过了 OFFSET 的问题。三者本质都在做同一件事：让 MySQL 跳过那些不需要的记录，而不是扫描了再丢掉。