在mysql的业务查询场景中，分页查询是非常常见的需求，通常使用LIMIT关键字实现分页。但当偏移量不断增大形成深分页时，查询性能会出现明显下降，这时候就需要用到延迟关联等优化技巧来提升查询效率。
LIMIT深分页的性能问题
我们先来看一个普通的分页查询语句，假设有一张用户表user，包含id、name、age、create_time等字段，其中id是主键，create_time有普通索引。
普通的深分页查询语句如下：
-- 查询第100001到100010条数据，偏移量为100000 SELECT id, name, age, create_time FROM user ORDER BY create_time DESC LIMIT 100000, 10;
这条语句的执行逻辑是：mysql会先按照create_time的索引排序，然后扫描前100010条记录，最后丢弃前100000条，只返回后面的10条。当偏移量很大时，需要扫描的数据量非常多，即使create_time有索引，也可能需要回表查询大量数据，导致性能低下。
延迟关联优化原理
延迟关联的核心思路是：先通过覆盖索引查询到需要的主键id，减少扫描的数据量，再用这些主键id去关联原表查询需要的字段，避免大量无效的数据扫描和回表操作。
因为覆盖索引只需要扫描索引树，不需要回表，速度会比全表扫描或者大量回表快很多。拿到主键id之后，再通过这些id去原表查询数据，这时候的查询是精准的主键查询，效率非常高。
延迟关联优化实现示例
还是以上面的用户表为例，我们用延迟关联优化上面的深分页查询：
-- 延迟关联优化后的查询语句
SELECT u.id, u.name, u.age, u.create_time
FROM (
-- 子查询先通过覆盖索引拿到需要的主键id
SELECT id
FROM user
ORDER BY create_time DESC
LIMIT 100000, 10
) AS tmp
-- 关联原表查询需要的字段
INNER JOIN user AS u ON tmp.id = u.id
ORDER BY u.create_time DESC;
我们拆解一下这个查询的执行步骤：
- 第一步执行子查询，因为
id和create_time都在索引中，属于覆盖索引查询，只需要扫描索引树拿到100000到100010对应的10个id，不需要回表，扫描量大大减少。 - 第二步用这10个
id和原表做关联查询，这时候是原表的主键查询，每个id只需要一次回表操作，总共只需要10次回表，性能提升非常明显。
延迟关联的适用场景
延迟关联并不是所有分页场景都适用，它更适合以下场景：
- 查询的偏移量比较大，普通LIMIT分页性能已经出现明显下降。
- 排序的字段有索引，且子查询可以使用覆盖索引拿到主键id。
- 查询的字段比较多，普通分页需要大量回表查询这些字段。
如果查询的字段本身就在排序索引中，属于覆盖索引查询，那么普通的分页查询性能已经足够，不需要使用延迟关联。
其他深分页优化方式
除了延迟关联，还有两种常见的深分页优化方式：
基于主键游标分页
如果主键是连续自增的，可以使用主键作为游标，避免偏移量查询：
-- 假设上一页最后一条数据的id是100000，查询下一页10条数据 SELECT id, name, age, create_time FROM user WHERE id < 100000 ORDER BY id DESC LIMIT 10;
这种方式不需要扫描偏移量之前的数据，性能非常稳定，但是要求主键连续，且不能支持跳页查询。
使用标签记录位置
如果排序字段有唯一值，可以记录上一页最后一条数据的排序字段值，作为下一页查询的条件：
-- 假设上一页最后一条数据的create_time是2024-05-01 12:00:00，id是100000 SELECT id, name, age, create_time FROM user WHERE create_time < '2024-05-01 12:00:00' OR (create_time = '2024-05-01 12:00:00' AND id < 100000) ORDER BY create_time DESC, id DESC LIMIT 10;
这种方式也可以避免大偏移量扫描，同样不支持跳页查询，适合连续翻页的场景。
优化方案对比
我们把几种常见的深分页优化方案做一个对比：
| 优化方案 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 延迟关联 | 大偏移量分页，支持跳页 | 支持跳页，性能提升明显 | 需要排序字段有索引，子查询能覆盖索引 |
| 主键游标分页 | 主键连续，连续翻页 | 性能最优，无扫描损耗 | 不支持跳页，主键需要连续自增 |
| 标签记录位置 | 连续翻页，排序字段唯一 | 性能稳定，无大偏移量扫描 | 不支持跳页，需要记录上一页最后一条数据 |
在实际业务中，可以根据具体的分页需求选择合适的优化方案，延迟关联是支持跳页场景下的最优选择，也是最常用的深分页优化技巧。