☆* o(≧▽≦)o *☆嗨~我是小奥🍹
📄📄📄个人博客:小奥的博客
📄📄📄CSDN:个人CSDN
📙📙📙Github:传送门
📅📅📅面经分享(牛客主页):传送门
🍹文章作者技术和水平有限,如果文中出现错误,希望大家多多指正!
📜 如果觉得内容还不错,欢迎点赞收藏关注哟! ❤️
MySQL深度分页优化问题
一、Limit使用
limit子句可以用于强制select语句返回指定的记录数。
select 字段 from 表名 limit 参数1,参数2;
select 字段 from 表名 limit 参数2 offset 参数1;(为了与 PostgreSQL 兼容)
- 参数1:指定第一个返回记录行的偏移量,从0开始
- 参数2:指定返回记录行的最大数目
如果只给定一个参数,那么表示返回的最大记录行数目。
如果第二个参数为-1,表示第一个参数的偏移量之后的所有的数据。
二、深度分页优化
查询偏移量过大的场景我们称为深度分页,这会导致查询性能较低。比如下面的查询:
# 普通分页查询
test> select * from t_demo order by id limit 1000000, 10
[2024-01-18 20:26:41] 在 396 ms (execution: 346 ms, fetching: 50 ms) 内检索到从 1 开始的 10 行
这条SQL在执行的过程中,通过非聚簇索引去查询主键,然后拿到主键再通过聚簇索引进行回表查询,查询到满足条件的1000010条数据,丢弃前面的1000000条,返回最后10条。
优化的思路也非常明确:
- 减少回表的次数
- 尽量通过索引来查询
2.1 范围查询
当可以保证 ID 的连续性时,根据 ID 范围进行分页是比较好的解决方案:
# 连续id时,根据id范围进行分页
test> select * from t_demo where id > 1000000 and id <= 1000010 order by id
[2024-01-18 20:26:41] 在 69 ms (execution: 8 ms, fetching: 61 ms) 内检索到从 1 开始的 10 行
# 通过记录上次查询结果的最后一条记录的ID进行下一页的查询
test> select * from t_demo where id > 1000000 limit 10
[2024-01-18 20:26:41] 在 76 ms (execution: 6 ms, fetching: 70 ms) 内检索到从 1 开始的 10 行
但是一般来说,实际生产中很少会去使用数据库的自增ID,所以这种优化方式的局限性比较大,而且也不是很有必要。
2.2 子查询
阿里《Java开发手册》中也有明确的优化方式:
我们先来看子查询,子查询的思路是:先查询出 limit 第一个参数对应的主键值,再根据这个主键值再去过滤并 limit,这样效率会高一点。
# 子查询
test> select * from t_demo where id >= (select id from t_demo limit 1000000, 1) limit 10
[2024-01-18 20:26:41] 在 210 ms (execution: 177 ms, fetching: 33 ms) 内检索到从 1 开始的 10 行
不过,子查询的结果会先产生一张新的表,会影响数据库的性能,所以应该避免使用子查询,并且id>=(...)
也限制了ID必须是递增的,这同样不适用比较复杂的场景。
2.3 inner join 延迟关联
延迟关联的优化思路,跟子查询的优化思路其实是一样的:都是把条件转移到主键索引,然后减少回表。不同点是,延迟关联使用了 INNER JOIN 代替子查询。
# inner join 延迟关联
test> select t1.* from t_demo t1
inner join (select id from t_demo limit 1000000, 1) t2
on t1.id >= t2.id
limit 10
[2024-01-18 20:26:42] 在 211 ms (execution: 181 ms, fetching: 30 ms) 内检索到从 1 开始的 10 行
2.4 覆盖索引
索引中已经包含了所有需要获取的字段的查询方式称为覆盖索引。文章来源:https://www.toymoban.com/news/detail-803829.html
# 覆盖索引
test> select id, a, b from t_demo
order by a
limit 1000000, 10
[2024-01-18 20:26:42] 在 279 ms (execution: 248 ms, fetching: 31 ms) 内检索到从 1 开始的 10 行
覆盖索引的好处:文章来源地址https://www.toymoban.com/news/detail-803829.html
- 避免 InnoDB 表进行索引的二次查询,也就是回表操作: InnoDB 是以聚集索引的顺序来存储的,对于 InnoDB 来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了 IO 操作,提升了查询效率。
- 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的,对于 IO 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 IO 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
到了这里,关于MySQL深度分页优化问题的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!