3c28da8047
In the section on deep pagination optimization recommendations, even with deferred joins and subqueries, the internal SQL statements will still result in deep pagination. These queries need to be rewritten to use the primary key index (id). Otherwise, there is no difference from the original SQL, and performance might even be worse.
8.1 KiB
| title | category | head | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 深度分页介绍及优化建议 | 高性能 |
|
深度分页介绍
查询偏移量过大的场景我们称为深度分页，这会导致查询性能较低，例如：
# MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
深度分页问题的原因
当查询偏移量过大时，MySQL 的查询优化器可能会选择全表扫描而不是利用索引来优化查询。这是因为扫描索引和跳过大量记录可能比直接全表扫描更耗费资源。
不同机器上这个查询偏移量过大的临界点可能不同，取决于多个因素，包括硬件配置（如 CPU 性能、磁盘速度）、表的大小、索引的类型和统计信息等。
MySQL 的查询优化器采用基于成本的策略来选择最优的查询执行计划。它会根据 CPU 和 I/O 的成本来决定是否使用索引扫描或全表扫描。如果优化器认为全表扫描的成本更低，它就会放弃使用索引。不过，即使偏移量很大，如果查询中使用了覆盖索引（covering index），MySQL 仍然可能会使用索引，避免回表操作。
深度分页优化建议
这里以 MySQL 数据库为例介绍一下如何优化深度分页。
范围查询
当可以保证 ID 的连续性时，根据 ID 范围进行分页是比较好的解决方案：
# 查询指定 ID 范围的数据
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10
这种基于 ID 范围的深度分页优化方式存在很大限制：
- ID 连续性要求高: 实际项目中，数据库自增 ID 往往因为各种原因（例如删除数据、事务回滚等）导致 ID 不连续，难以保证连续性。
- 排序问题: 如果查询需要按照其他字段（例如创建时间、更新时间等）排序，而不是按照 ID 排序，那么这种方法就不再适用。
- 并发场景: 在高并发场景下，单纯依赖记录上次查询的最后一条记录的 ID 进行分页，容易出现数据重复或遗漏的问题。
子查询
我们先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快一些。
阿里巴巴《Java 开发手册》中也有对应的描述：
利用延迟关联或者子查询优化超多分页场景。
# 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order where id > 1000000 limit 1) LIMIT 10;
工作原理:
- 子查询
(SELECT id FROM t_order where id > 1000000 limit 1)会利用主键索引快速定位到第 1000001 条记录，并返回其 ID 值。 - 主查询
SELECT * FROM t_order WHERE id >= ... LIMIT 10将子查询返回的起始 ID 作为过滤条件，使用id >=获取从该 ID 开始的后续 10 条记录。
不过，子查询的结果会产生一张新表，会影响性能，应该尽量避免大量使用子查询。并且，这种方法只适用于 ID 是正序的。在复杂分页场景，往往需要通过过滤条件，筛选到符合条件的 ID，此时的 ID 是离散且不连续的。
当然，我们也可以利用子查询先去获取目标分页的 ID 集合，然后再根据 ID 集合获取内容，但这种写法非常繁琐，不如使用 INNER JOIN 延迟关联。
延迟关联
延迟关联与子查询的优化思路类似，都是通过将 LIMIT 操作转移到主键索引树上，减少回表次数。相比直接使用子查询，延迟关联通过 INNER JOIN 将子查询结果集成到主查询中，避免了子查询可能产生的临时表。在执行 INNER JOIN 时，MySQL 优化器能够利用索引进行高效的连接操作（如索引扫描或其他优化策略），因此在深度分页场景下，性能通常优于直接使用子查询。
-- 使用 INNER JOIN 进行延迟关联
SELECT t1.*
FROM t_order t1
INNER JOIN (SELECT id FROM t_order where id > 1000000 LIMIT 10) t2 ON t1.id = t2.id;
工作原理:
- 子查询
(SELECT id FROM t_order where id > 1000000 LIMIT 10)利用主键索引快速定位目标分页的 10 条记录的 ID。 - 通过
INNER JOIN将子查询结果与主表t_order关联，获取完整的记录数据。
除了使用 INNER JOIN 之外，还可以使用逗号连接子查询。
-- 使用逗号进行延迟关联
SELECT t1.* FROM t_order t1,
(SELECT id FROM t_order where id > 1000000 LIMIT 10) t2
WHERE t1.id = t2.id;
注意: 虽然逗号连接子查询也能实现类似的效果，但为了代码可读性和可维护性，建议使用更规范的 INNER JOIN 语法。
覆盖索引
索引中已经包含了所有需要获取的字段的查询方式称为覆盖索引。
覆盖索引的好处：
- 避免 InnoDB 表进行索引的二次查询，也就是回表操作: InnoDB 是以聚集索引的顺序来存储的，对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询（回表），减少了 IO 操作，提升了查询效率。
- 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
# 如果只需要查询 id, code, type 这三列，可建立 code 和 type 的覆盖索引
SELECT id, code, type FROM t_order
ORDER BY code
LIMIT 1000000, 10;
⚠️注意:
- 当查询的结果集占表的总行数的很大一部分时，MySQL 查询优化器可能选择放弃使用索引，自动转换为全表扫描。
- 虽然可以使用
FORCE INDEX强制查询优化器走索引，但这种方式可能会导致查询优化器无法选择更优的执行计划，效果并不总是理想。
总结
本文总结了几种常见的深度分页优化方案:
- 范围查询: 基于 ID 连续性进行分页，通过记录上一页最后一条记录的 ID 来获取下一页数据。适合 ID 连续且按 ID 查询的场景，但在 ID 不连续或需要按其他字段排序时存在局限。
- 子查询: 先通过子查询获取分页的起始主键值，再根据主键进行筛选分页。利用主键索引提高效率，但子查询会生成临时表，复杂场景下性能不佳。
- 延迟关联 (INNER JOIN): 使用
INNER JOIN将分页操作转移到主键索引上，减少回表次数。相比子查询，延迟关联的性能更优，适合大数据量的分页查询。 - 覆盖索引: 通过索引直接获取所需字段，避免回表操作，减少 IO 开销，适合查询特定字段的场景。但当结果集较大时，MySQL 可能会选择全表扫描。
参考
- 聊聊如何解决 MySQL 深分页问题 - 捡田螺的小男孩：https://juejin.cn/post/7012016858379321358
- 数据库深分页介绍及优化方案 - 京东零售技术：https://mp.weixin.qq.com/s/ZEwGKvRCyvAgGlmeseAS7g
- MySQL 深分页优化 - 得物技术：https://juejin.cn/post/6985478936683610149