MySQL深度分页优化问题

这篇具有很好参考价值的文章主要介绍了MySQL深度分页优化问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

☆* 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开发手册》中也有明确的优化方式:

MySQL深度分页优化问题,MySQL,mysql

我们先来看子查询,子查询的思路是:先查询出 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 覆盖索引

索引中已经包含了所有需要获取的字段的查询方式称为覆盖索引。

# 覆盖索引
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模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL__深度分页问题

    😊 @ 作者:Lion J 💖 @ 主页: https://blog.csdn.net/weixin_69252724 🎉 @ 主题: MySQL__深度分页问题) ⏱️ @ 创作时间:2024年04月27日 ———————————————— 在我一次测试中, 100万条数据, 一次偶然的数据展示中, 发现响应数据特别慢, 从前端到后端的一个问题检查上, 最

    2024年04月28日
    浏览(26)
  • MySQL 深度分页

    MySQL 深度分页是指在分页查询数据量比较大的表时,需要访问表中的某一段数据,而这段数据的位置非常靠后,需要通过较大的 offset 来获取目标数据。 默认分页即通过 limit #{offset}, #{pageSize} 或 limit #{pageSize} offset #{offset} 来进行分页。二者本质上都是全表扫描,MySQL 会依次取

    2024年01月20日
    浏览(48)
  • Mysql——》优化limit分页

    推荐链接:     总结——》【Java】     总结——》【Mysql】     总结——》【Redis】     总结——》【Kafka】     总结——》【Spring】     总结——》【SpringBoot】     总结——》【MyBatis、MyBatis-Plus】     总结——》【Linux】     总结——》【MongoDB】    

    2024年02月13日
    浏览(40)
  • 73.MySQL 分页原理与优化(上)

    我们刷网站的时候,我们经常会遇到需要分页查询的场景。比如下图红框里的翻页功能。 我们很容易能联想到可以用 mysql 实现。假设我们的建表 sql 是这样的 建表 sql 大家也不用扣细节,只需要知道 id 是主键,并且在 user_name 建了个非主键索引就够了,其他都不重要。 为了

    2024年01月23日
    浏览(41)
  • MySQL大数据量分页查询方法及其优化

    ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N ---适应场景: 适用于数据量较少的情况(元组百/千级) ---原因/缺点: 全表扫描,速度会很慢 且 有的数据库 结果集返回不稳定 (如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从

    2024年02月15日
    浏览(36)
  • MySQL分页查询详解:优化大数据集的LIMIT和OFFSET

    最近在工作中,我们遇到了一个需求,甲方要求直接从数据库导出一个业务模块中所有使用中的工单信息。为了实现这一目标,我编写了一条SQL查询语句,并请求DBA协助导出数据。尽管工单数量并不多,只有3000多条,但每个工单都包含了大量的信息。DBA进行了多次导出操作,

    2024年02月10日
    浏览(52)
  • MySQL性能深度优化

    这里的深度优化是指,除了建索引、左匹配索引等等其他的优化手段。 文章涉及到操作系统连接数、IO、Mysql本身的某些参数设置,值得记录下来。 CPU:48C 内存:128G DISK:3.2TSSD innodb_thread_concurrency=32 表示SQL经过解析后,允许同时有32个线程去innodb引擎取数据,如果超过32个,

    2024年02月09日
    浏览(55)
  • Mysql Php 推送获取随机数据解决分页重复问题

    或许你已经看过很多博主写的文章,要不就是抄袭,要不就是给你一个下面的语句,随机是随机了,但是多来两页,你会发现前面出现的数据在第三页甚至第二页就出现了 这是因为rand()机制的问题,他每次都会打乱数据给你,然后你去取的时候0-10,11-20都有可能是同一个数据

    2024年02月06日
    浏览(47)
  • mybatis-plus技巧--动态表名-多语句-拼接sql--关于mybatis的mysql分页查询总数的优化思考

    传入tableName参数就可以了,不过只能用$不能用# 因为#会发生预编译,然后会在表名上加引号’\\\'。 新建一个表名拦截类实现TableNameHandler mybatisPlus添加插件 实例: 每天按统计 如果表名为count则加上今天的时间 每次设置 直接设置名字,然后就会改变的。 需要在配置文件中的

    2024年01月16日
    浏览(49)
  • 若依分离版——解决配置双数据源oracle,mysql分页错误问题

    1. 按照若依的手册配置双数据源mysql,oracle   2. 在service指定 数据源 @DataSource(value = DataSourceType.MASTER) 或者@DataSource(value = DataSourceType.SLAVE) 3. 发现出现使用分页的情况下报错,不使用分页时正常。 4.  最后找到解决办法,是application.yml文件的pagehelper分页配置有误,正确配置如

    2024年02月15日
    浏览(46)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包