MySQL深分页优化：千万数据offset分页为什么越翻越慢
大约 7 分钟
MySQL深分页优化：千万数据offset分页为什么越翻越慢
适读人群：Java后端开发、DBA、对SQL性能优化感兴趣的工程师 | 阅读时长：约22分钟
开篇故事
2022年，我们的运营同学跑来找我，说后台的订单列表翻到第500页后，每次翻页要等15秒，而且还在增长。
我看了一下他们的分页SQL：
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 9980; -- 第500页，每页20条orders表有1500万条记录。这条SQL在我的测试机上跑了13.7秒。
换到第1000页（OFFSET 19980）：27.4秒，直接超时了。
但奇怪的是，查第一页（OFFSET 0）只要0.02秒。为什么翻页越深越慢？
一、深分页慢的根因
1.1 OFFSET的执行原理
很多人以为 LIMIT 20 OFFSET 9980 是「直接跳到第9980条开始读」，但实际上MySQL的执行过程是：
1. 按 ORDER BY 的索引扫描
2. 扫描并跳过前 9980 条记录
3. 返回第 9981~10000 条记录
实际扫描行数 = offset + limit = 9980 + 20 = 10000行！如果你有 created_at 的索引，且需要回表（查询了非索引字段），那么：
扫描索引叶子节点：10000次
回表取完整行数据：10000次（每次都是随机IO！）
实际返回：20行
浪费了 9980 次无效的回表操作1.2 为什么越翻越慢
第1页: OFFSET 0, 扫描 20 行, 回表 20 次, 耗时 0.02s
第100页: OFFSET 1980, 扫描 2000 行, 回表 2000 次, 耗时 0.3s
第500页: OFFSET 9980, 扫描 10000 行, 回表 10000 次, 耗时 1.5s
第5000页: OFFSET 99980, 扫描 100000 行, 回表 100000 次, 耗时 13.7s线性增长。offset越大，无效扫描越多。
二、底层原理：B+树与回表的代价
索引扫描过程（idx_created_at）：
B+树叶子节点（按created_at排序）：
[2023-01-01, id=50000]→[2023-01-02, id=49999]→...→[2022-01-01, id=1]
OFFSET 9980：
步骤1：从最新的叶子节点开始，顺序读取
步骤2：跳过前9980个叶子节点（每个节点记录一个(created_at, id)对）
步骤3：对第9981~10000个叶子节点，用id回表查主键索引
回表代价：
每次回表 = 随机IO（从索引文件跳到数据文件的不同页面）
10000次随机IO >> 20次随机IO
这就是越翻越慢的本质三、完整解决方案与代码
3.1 方案一：游标分页（最推荐）
用 WHERE id > lastId 替代 OFFSET，每次记住上一页的最后一条记录的ID。
-- 传统深分页（慢）
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 9980;
-- 游标分页（快）
SELECT * FROM orders WHERE id < :lastId ORDER BY id DESC LIMIT 20;
-- lastId是上一页最后一条记录的idEXPLAIN对比：
-- 传统分页
+----+-------+------+-----+---------+---------+----------+
| id | type | key | ... | key_len | rows | Extra |
+----+-------+------+-----+---------+---------+----------+
| 1 | index | PRIMARY| ...| 8 | 10000 | NULL |
+----+-------+------+-----+---------+---------+----------+
-- 扫描10000行，耗时 13.7s
-- 游标分页
+----+-------+------+------+---------+------+-------+
| id | type | key | ... | key_len | rows | Extra |
+----+-------+------+------+---------+------+-------+
| 1 | range | PRIMARY| ...| 8 | 20 | NULL |
+----+-------+------+------+---------+------+-------+
-- 扫描20行，耗时 0.003s！Java实现：
@Service
public class OrderQueryService {
@Autowired
private OrderMapper orderMapper;
/**
* 游标分页查询订单
* @param lastId 上一页最后一条记录的ID（第一页传null或0）
* @param size 每页大小
*/
public CursorPage<Order> listOrders(Long lastId, int size) {
List<Order> orders;
if (lastId == null || lastId == 0) {
// 第一页
orders = orderMapper.selectFirstPage(size + 1); // 多查一条判断是否有下一页
} else {
// 后续页
orders = orderMapper.selectByIdLessThan(lastId, size + 1);
}
boolean hasNext = orders.size() > size;
if (hasNext) {
orders = orders.subList(0, size); // 去掉多查的那条
}
Long nextCursor = hasNext ? orders.get(orders.size() - 1).getId() : null;
return new CursorPage<>(orders, nextCursor, hasNext);
}
@Data
@AllArgsConstructor
public static class CursorPage<T> {
private List<T> data;
private Long nextCursor; // 下次请求传这个值作为lastId
private boolean hasNext;
}
}对应的Mapper SQL：
<select id="selectFirstPage" resultType="Order">
SELECT * FROM orders ORDER BY id DESC LIMIT #{size}
</select>
<select id="selectByIdLessThan" resultType="Order">
SELECT * FROM orders
WHERE id < #{lastId}
ORDER BY id DESC
LIMIT #{size}
</select>3.2 方案二：延迟关联（适合随机跳页）
当业务需要支持随机跳页（不只是上下翻），可以用延迟关联（Deferred Join）：
-- 传统深分页（慢，需要10000次回表）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 9980;
-- 延迟关联（快）
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 9980
) t ON o.id = t.id;为什么延迟关联更快？
子查询：SELECT id FROM orders ORDER BY created_at DESC LIMIT 9980, 20
→ 走 idx_created_at 覆盖索引扫描（只扫索引，不回表）
→ 只需读10000个索引叶子节点，返回20个id
外层JOIN：根据20个id回表（主键查询）
→ 20次主键查询，20次回表
总计：10000次索引扫描（顺序IO）+ 20次回表（随机IO）
比原始方案：10000次回表（随机IO）要快得多实际EXPLAIN：
EXPLAIN SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 9980
) t ON o.id = t.id;+----+-------------+--------+--------+---------------+---------------+---------+---------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+---------------+---------------+---------+---------+-------+-------------+
| 1 | PRIMARY | o | eq_ref | PRIMARY | PRIMARY | 8 | t.id | 1 | NULL |
| 2 | DERIVED | orders | index | NULL | idx_created_at| 5 | NULL | 10000 | Using index |
+----+-------------+--------+--------+---------------+---------------+---------+---------+-------+-------------+
-- 子查询：Using index（覆盖索引，无回表），外层：eq_ref（主键查询）
-- 耗时：0.4s（对比原始的13.7s）3.3 方案三：ID范围分页（适合数据导出）
适合大批量数据导出的场景：
/**
* 基于ID范围的分批导出（适合导出千万级数据）
*/
@Service
public class OrderExportService {
@Autowired
private OrderMapper orderMapper;
public void exportAllOrders(OutputStream output) throws Exception {
long minId = orderMapper.selectMinId();
long maxId = orderMapper.selectMaxId();
int batchSize = 1000;
long segmentSize = 10000; // 每个ID段
for (long fromId = minId; fromId <= maxId; fromId += segmentSize) {
long toId = fromId + segmentSize - 1;
// 在ID范围内分页（小范围内offset快）
int offset = 0;
while (true) {
List<Order> batch = orderMapper.selectByIdRange(
fromId, toId, offset, batchSize);
if (batch.isEmpty()) break;
writeToStream(batch, output);
offset += batchSize;
if (batch.size() < batchSize) break;
}
}
}
}对应SQL：
-- ID范围内的分页（范围小，offset代价小）
SELECT * FROM orders
WHERE id BETWEEN :fromId AND :toId
ORDER BY id ASC
LIMIT :batchSize OFFSET :offset;四、踩坑实录
坑1：游标分页在ORDER BY非主键时，结果不稳定
// 问题：按created_at排序时，created_at可能有重复值
SELECT * FROM orders WHERE created_at < :lastCreatedAt ORDER BY created_at DESC LIMIT 20;
// 如果多条记录的created_at相同，翻页时会漏数据或重复数据！正确做法：游标用组合字段（排序字段 + 主键）：
-- 组合游标：created_at + id 保证唯一性
SELECT * FROM orders
WHERE (created_at < :lastCreatedAt)
OR (created_at = :lastCreatedAt AND id < :lastId)
ORDER BY created_at DESC, id DESC
LIMIT 20;// 返回的游标包含两个字段
record Cursor(LocalDateTime createdAt, Long id) {}
public CursorPage<Order> listOrders(Cursor cursor, int size) {
List<Order> orders;
if (cursor == null) {
orders = orderMapper.selectFirstPage(size + 1);
} else {
orders = orderMapper.selectByCursor(
cursor.createdAt(), cursor.id(), size + 1);
}
// ...
}坑2：延迟关联对MySQL优化器不友好，有时退化
-- 在某些MySQL版本和参数配置下，延迟关联可能不走子查询覆盖索引
-- 可以用STRAIGHT_JOIN强制按照你指定的顺序JOIN
SELECT o.* FROM (
SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 9980
) t STRAIGHT_JOIN orders o ON o.id = t.id;
-- 或者改成子查询形式
SELECT * FROM orders WHERE id IN (
SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 9980
);
-- 注意：IN子查询在某些版本会有性能问题，需要EXPLAIN验证坑3：前端分页总数查询(COUNT(*))也慢
-- 分页需要返回总页数，COUNT(*)也是全表扫描
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 1500万行，耗时 2.3s解决方案：
// 方案1：缓存COUNT结果（接受近似值）
@Cacheable(key = "'orders:count:' + #status", expire = 60)
public long countOrders(int status) {
return orderMapper.count(status);
}
// 方案2：对于不需要精确总数的场景，用信息表估算
// information_schema.TABLES 的 TABLE_ROWS 字段（估算值，不精确但快）
public long estimateCount(String tableName) {
return jdbcTemplate.queryForObject(
"SELECT TABLE_ROWS FROM information_schema.TABLES " +
"WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?",
Long.class, tableName
);
}
// 方案3：游标分页不需要总页数（流式翻页）
// 只展示「还有更多」而不是「共xxx页」五、总结与延伸
深分页优化的核心思路：减少无效扫描和无效回表。
三种方案的适用场景：
| 方案 | 适用场景 | 不适用场景 |
|---|---|---|
| 游标分页 | 瀑布流、上下翻页 | 随机跳页、需要总页数 |
| 延迟关联 | 随机跳页、传统分页UI | 数据频繁变化（分页结果不稳定） |
| ID范围分页 | 大批量数据导出 | 普通用户翻页 |
一个原则：分页深度超过100页的需求，一定要和产品讨论是否真的需要。用户实际翻到第500页的概率趋近于零，与其优化深分页，不如改成搜索功能让用户直达目标数据。