得物面试:MySQL 深度分页如何优化?
JavaGuide 官方网站： javaguide.cn
JavaGuide 官方星球 ： JavaGuide知识星球
今天分享的是一位读者在去年秋招面试得物被提问的一个问题。
ps：这个数据库优化问题在面试中还是比较常见的， 阿里、腾讯、用友、京东、小红书等中大厂的面试都问过这个问题。
深度分页介绍
查询偏移量过大的场景我们称为深度分页，这会导致查询性能较低，例如：
# MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
深度分页优化建议
这里以 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 也没办法保证完全连续。
子查询
我们先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快一些。
阿里巴巴《Java 开发手册》中也有对应的描述：
利用延迟关联或者子查询优化超多分页场景。
# 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;
不过，子查询的结果会产生一张新表，会影响性能，应该尽量避免大量使用子查询。并且，这种方法只适用于 ID 是正序的。在复杂分页场景，往往需要通过过滤条件，筛选到符合条件的 ID，此时的 ID 是离散且不连续的。
当然，我们也可以利用子查询先去获取目标分页的 ID 集合，然后再根据 ID 集合获取内容，但这种写法非常繁琐，不如使用 INNER JOIN 延迟关联。
INNER JOIN 延迟关联
延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了 INNER JOIN 代替子查询。
SELECT t1.* FROM t_order t1
INNER JOIN (SELECT id FROM t_order limit 1000000, 1) t2
ON t1.id >= t2.id
LIMIT 10;
覆盖索引
索引中已经包含了所有需要获取的字段的查询方式称为覆盖索引。
覆盖索引的好处：
- 避免 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;
不过，当查询的结果集占表的总行数的很大一部分时，可能就不会走索引了，自动转换为全表扫描。当然了，也可以通过 FORCE INDEX 来强制查询优化器走索引，但这种提升效果一般不明显。
参考
- 聊聊如何解决 MySQL 深分页问题 - 捡田螺的小男孩：https://juejin.cn/post/7012016858379321358
- 数据库深分页介绍及优化方案 - 京东零售技术：https://mp.weixin.qq.com/s/ZEwGKvRCyvAgGlmeseAS7g
- MySQL 深分页优化 - 得物技术：https://juejin.cn/post/6985478936683610149
👉 MySQL 面试题推荐 ：
- 为什么要用索引？MySQL 索引底层数据结构是什么？
- 为什么 MySQL 不建议使用 NULL 作为列默认值？
- MySQl 索引失效的场景有哪些？
- MySQL 支持哪些存储引擎？MyISAM 和 InnoDB 的区别是什么？
- MySQL 中有哪些锁？表级锁和行级锁有什么区别？
- MySQL 事务的默认隔离级别是什么?可以解决幻读问题么？
👉 优质合集推荐 ：
点击下方卡片进入公众号
回复 「PDF」 即可领取原创PDF技术面试手册
回复 「学习路线 」 即可获取4w+字最新版Java学习路线
回复 「开源」 即可获取优质Java开源项目合集
免费分享无套路，有帮助点个赞就好！