在mysql的实际使用中，当表的数据量达到百万甚至千万级别时，传统的分页查询方式很容易出现性能问题，尤其是当查询的偏移量较大时，查询响应时间会明显变长，甚至出现卡顿。这是因为传统分页的逻辑需要扫描从起始位置到偏移量位置的所有数据，偏移量越大，扫描的无用数据就越多，性能损耗也就越高。
传统分页的性能瓶颈
我们常用的分页SQL语句一般是这样的形式：
SELECT * FROM user_table ORDER BY id LIMIT 1000000, 10;
这条语句的含义是从user_table表中按照id排序，跳过前1000000条数据，取后面的10条数据。mysql在执行这条语句时，需要先扫描前1000010条数据，然后丢弃前1000000条，只返回最后10条，当表的数据量很大，偏移量达到百万级别时，这个扫描过程会消耗大量的IO和CPU资源，导致查询变慢。
我们可以通过EXPLAIN命令查看这条语句的执行计划，会发现扫描的行数rows会非常大，基本等于偏移量加上要取的数据条数，这就是性能问题的核心原因。
延迟关联优化原理
延迟关联优化的核心思路是：先通过子查询或者覆盖索引，只获取需要返回数据的对应主键ID，因为主键索引的叶子节点存储的是完整数据，而普通索引叶子节点存储的是主键值，所以先查主键的扫描成本会低很多，拿到主键ID之后，再通过主键关联原表获取完整的行数据，这样就能大幅减少扫描的数据量。
整个流程可以分为两步：
- 第一步：通过索引查询获取目标数据对应的主键ID，这一步只扫描索引，不需要回表，效率很高
- 第二步：用获取到的主键ID作为条件，关联原表查询完整的行数据，因为主键查询是直接定位到对应数据页，速度非常快
延迟关联优化实现示例
还是以上面的user_table表为例，假设表结构如下：
CREATE TABLE user_table (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
age INT NOT NULL,
create_time DATETIME NOT NULL,
INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
如果我们需要按照create_time排序，查询偏移1000000条之后的10条数据，传统写法是：
SELECT * FROM user_table ORDER BY create_time LIMIT 1000000, 10;
使用延迟关联优化后的写法如下：
SELECT t.* FROM user_table t
INNER JOIN (
SELECT id FROM user_table ORDER BY create_time LIMIT 1000000, 10
) tmp ON t.id = tmp.id;
这里的子查询SELECT id FROM user_table ORDER BY create_time LIMIT 1000000, 10会走idx_create_time索引，因为索引本身是有序的，而且只需要查询id字段，属于覆盖索引，不需要回表查询完整数据，所以扫描效率很高。拿到10个id之后，再通过主键关联原表，每个id直接定位到对应的数据行，只需要10次回表操作，相比传统方式扫描1000010行数据，性能提升非常明显。
延迟关联的适用场景和注意事项
适用场景
- 表的数据量较大，通常超过10万行，分页偏移量较大的场景
- 排序字段有对应的索引，或者查询可以使用覆盖索引的场景
- 分页查询需要返回表的多个字段，无法只通过索引覆盖返回所有需要的字段的场景
注意事项
- 子查询中的排序字段必须有对应的索引，否则子查询本身还是会全表扫描，无法达到优化效果
- 如果查询的字段都可以通过覆盖索引返回，那么不需要使用延迟关联，直接使用覆盖索引查询效率更高
- 延迟关联会增加一次表关联操作，如果数据量很小，偏移量也很小，可能优化效果不明显，甚至因为多一次关联反而变慢，需要根据实际场景选择
性能对比测试
我们可以做一个简单的测试，在1000万数据的user_table表中，分别执行传统分页和优化后的延迟关联分页，查询偏移1000000条后的10条数据：
| 查询方式 | 扫描行数 | 执行时间 |
|---|---|---|
| 传统LIMIT分页 | 1000010 | 约2.3秒 |
| 延迟关联优化分页 | 10 | 约0.05秒 |
从测试结果可以明显看到，延迟关联优化后的查询扫描行数大幅减少，执行时间也缩短了几十倍，优化效果非常显著。
其他分页优化方案补充
除了延迟关联之外，还有一些其他的分页优化方案，可以根据场景选择：
- 使用游标分页：如果业务场景允许，不使用偏移量分页，而是使用上一页最后一条数据的主键作为条件，比如
WHERE id > 上一页最后id LIMIT 10，这种方式不管翻到多少页，性能都稳定，但是不支持跳页 - 限制最大偏移量：业务层面限制用户最多能翻到多少页，超过之后不允许继续翻页，避免超大偏移量的查询
- 使用搜索引擎：如果分页查询的场景非常复杂，可以考虑把数据同步到Elasticsearch等搜索引擎中，由搜索引擎承担分页查询的压力
延迟关联是mysql处理大数据量分页卡顿问题非常实用的优化技巧，理解它的原理和适用场景，能够帮助我们在实际开发中快速解决分页性能问题，提升系统的响应速度。