千万级数据表深分页查询优化：从 5秒到 0.1秒-开发者社区

摘要：在海量数据的业务场景下，MySQL 的深度分页（Deep Pagination）是一个经典的性能杀手。

1. 事故现场：接口响应超时

上周五临下班，监控系统突然报警，某核心后台管理系统的“订单列表”页面加载超时。运营反馈：“我想翻到第 10000 页查看半年前的历史订单，结果页面一直在转圈，最后报错了。”

我去查了一下数据库日志，发现了一条慢 SQL，执行时间竟然高达5.23 秒。

-- 原始慢 SQL SELECT * FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 10;

看似很简单的查询，为什么会这么慢？

我们先看下表结构和数据量：

表名：t_order
数据量：2000 万行
索引：id (主键), idx_create_time_status (联合索引)

2. 深度分页为什么慢？

很多同学认为 LIMIT 100000, 10 的意思是：直接跳到第 100000 行，然后取出 10 行。

错！MySQL 并不是这么工作的。

LIMIT 100000, 10 的实际执行过程是：

MySQL 会根据索引扫描100010行数据。
如果不走覆盖索引（Select *），MySQL 还需要拿着这 100010 个主键 ID 去回表（回主键索引查全部字段）。
抛弃掉前100000行数据。
只返回最后10行给客户端。

这简直是资源浪费！

大量的回表操作（Random I/O）是导致性能崩塌的罪魁祸首。

3. 解决方案与代码实战

既然知道了瓶颈在于“回表”，那我们的优化思路就是：尽可能减少回表次数，或者干脆不回表。

方案一：延迟关联（覆盖索引优化）

这是最通用、改动最小的方案。

核心思路：先通过覆盖索引（Covering Index）只查出目标页的 10 个 ID。因为只查 ID，不需要回表，速度极快。然后再用这 10 个 ID 去关联原表查询完整数据。

优化后的 SQL

SELECT t1.* FROM t_order t1 INNER JOIN ( -- 子查询只查 ID，利用覆盖索引，不用回表 SELECT id FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 10 ) t2 ON t1.id = t2.id;

执行时间对比：从5.23s降低到了0.15s。

在 Java 中，我们不需要手动拼接 SQL，可以利用 MyBatis 的自定义 SQL 功能。

<select id="selectDeepPage" resultType="com.example.entity.Order"> SELECT t1.* FROM t_order t1 INNER JOIN ( SELECT id FROM t_order WHERE status = #{status} ORDER BY create_time DESC LIMIT #{offset}, #{size} ) t2 ON t1.id = t2.id </select>

public PageResult<Order> getOrderPage(int page, int size, int status) { int offset = (page - 1) * size; // 调用优化后的 SQL List<Order> list = orderMapper.selectDeepPage(status, offset, size); // ... 获取总条数 logic return new PageResult<>(list); }

方案二：游标法（Seek Method） —— 性能最佳

如果你的业务场景不需要“跳转到第 N 页”，只需要“下一页”（无限滚动），那么这个方案是性能天花板。

核心思路：记住上一页最后一条数据的排序字段值（比如 create_time 和 id），下一页直接从这个位置开始找。

优化后的 SQL

假设上一页最后一条数据的 create_time 是 '2023-01-01 12:00:00'，ID 是 500000。

SELECT * FROM t_order WHERE status = 1 AND (create_time < '2023-01-01 12:00:00' OR (create_time = '2023-01-01 12:00:00' AND id < 500000)) ORDER BY create_time DESC, id DESC LIMIT 10;

执行时间：稳定在0.01s级别，无论翻到多少页。

缺点：

不支持“跳转到第 1000 页”。
代码逻辑需要改造，前端需传回 last_id。

// cursorTime: 上一页最后一条的创建时间 // cursorId: 上一页最后一条的ID public List<Order> getNextPage(LocalDateTime cursorTime, Long cursorId, int size) { LambdaQueryWrapper<Order> wrapper = Wrappers.lambdaQuery(); wrapper.eq(Order::getStatus, 1); if (cursorTime != null && cursorId != null) { // 构造 (create_time < t) OR (create_time = t AND id < id) wrapper.and(w -> w .lt(Order::getCreateTime, cursorTime) .or(inner -> inner.eq(Order::getCreateTime, cursorTime).lt(Order::getId, cursorId)) ); } wrapper.orderByDesc(Order::getCreateTime, Order::getId); wrapper.last("LIMIT " + size); return orderMapper.selectList(wrapper); }

方案三：ID 范围查询（限制 ID）

如果是自增 ID 且连续（没有删除过数据）的理想情况，可以直接根据 ID 范围推算。

-- ID 连续，第 10000 页的 ID 起点大约是 10000 * 10 = 100000 SELECT * FROM t_order WHERE id <= 100000 ORDER BY id DESC LIMIT 10;

但这在现实中几乎不可用，因为订单表肯定有删除或 ID 不连续的情况。

一种变体是：先查出第 100000 条数据的 ID（利用覆盖索引快查），再 WHERE id < checked_id。

-- 第一步：极速定位起始 ID SELECT id FROM t_order WHERE status=1 ORDER BY create_time DESC LIMIT 100000, 1; -- 结果得到 id = 9527 -- 第二步：范围查询 SELECT * FROM t_order WHERE status=1 AND id <= 9527 ORDER BY create_time DESC LIMIT 10;

4. 性能压测对比

我们在开发环境模拟了 2000 万数据，对三种方案进行了压测（每组执行 10 次取平均值）：

页码深度	原始 SQL (秒)	延迟关联 (秒)	游标法 (秒)
第 10 页	0.005	0.006	0.003
第 1,000 页	0.120	0.030	0.003
第 10,000 页	0.850	0.060	0.004
第 100,000 页	5.230	0.150	0.005