深分页性能瓶颈与优化方案

举个常见的场景：一个商品列表页，后端接口采用分页查询。前几页加载飞快，用户几乎无感，但当翻到第500页时，接口响应时间从50毫秒飙升到3秒。打开慢查询日志一看，又是那条熟悉的SQL在作祟。

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

这就是深分页问题。数据表里有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一样要扫描、一样要比较，只是最终不返回而已。这就好比让你从一本厚厚的电话本里找到第1001个号码，但你必须从第一个开始一个一个数，尽管前1000个你完全不需要。

为什么 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 p
INNER 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 p
WHERE p.id >= (
    SELECT id FROM products ORDER BY id LIMIT 1 OFFSET 1000
)
ORDER BY p.id
LIMIT 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跳过那些不需要的记录，而不是扫描后再丢弃。