专栏首页IT大咖说如果谁再问你“如何优化mysql分页查询”,请把这篇文章甩给他

如果谁再问你“如何优化mysql分页查询”,请把这篇文章甩给他

在我们日常开发中,分页查询是必不可少的,可以说每个后端程序猿大部分时间都是CURD,所以分页的查询也接触的不少,你们都是怎么实现的呢?前不久的一段时间,我的一个同事突然找我寻求帮助,他说他写的sql查询太慢了,问我能不能帮他优化一下那条查询语句,经过一段时间的优化,我们成功的将原来8秒一条的sql成功优化到了不到一秒,然而想到知识应该学会分享,所以我今天打算写出这个优化过程,可以让更多的程序猿可以看到。

为什么想到了优化分页查询

同事:hi boy ,让我们一起来探讨一下理想如何?

握草,我一听就觉得事情不对,肯定没好事,走过去一看,同事对我说,他维护的这张日志表数据已经超过500w了,可能是测试的同事在做压力测试,导致了数据库的用户操作日志记录一下子就突破了几百万,现在,同事写的分页查询速度已经很慢很慢了,当查询的记录越靠后的时候,查询时间越久,果不其然,没过多久,我们就在禅道上看到了测试大哥提交的bug:查看1000页以后的日志返回速度极慢,这是被迫优化啊,好尴尬。

如何优化

问题是找到了,那是因为当数据库存放的记录过大的时候,查询也靠后的记录速度越慢,为什么查询越靠后的记录就越慢呢?

我这里主要介绍mysql的分页优化,sqlserver、Oracle可以参考思想,还是回归之前的那个问题,我们来看看问什么查询会慢?我们需要实现准备好数据表以及记录。

创建数据表

添加数据

我在数据表中添加了100w张三、100w李四、100w王五、200赵六的用户,所以表中一共500w数据,添加数据的方式采用的存储过程。

查询过慢的原因

数据表和记录都已经准备好了,现在我们就需要来排查为什么分页查询页码越靠后查询速度越慢,我们先来看一个普通的分页查询:

这是查询第十页的数据,我相信大部分的人在写分页查询的时候都是这么写的,其中100:偏移量,意思就是说从哪里之后就是我需要的数据据,10:表示需要查询多少条记录,这个就是mysql的分页查询语法,你能看出这条sql存在什么问题吗?乍一看好像没啥问题,真的是这样吗?我们来看几个例子。

1.偏移量=0

查询时间

2.偏移量=1000

查询时间

3.偏移量=10000

查询结果

就算偏移量达到了10000,我们的查询速度还是很快的,这说明这条sql就没有任何问题了吗?既然这样,我就让你们看一下当偏移量达到200w的时候,会发生什么事情?

4.偏移量= 400w

查询结果

也就是说偏移量越大,查询的时候就越久,这是问什么呢?明明都是从查询10条记录,为什么偏移量越大,查询时间越久呢?

limit分页的原理

为什么会慢?我们不妨先猜测一下,像函数、扫描记录过多等等都会影响查询的速度,很显然这里我们并没有使用到函数,所以这会不会是扫描的记录过多呢?

这个就和limit有关了,你们知道limit是如何实现分页的吗?我们使用wxplain关键字来分别打印一下偏移量=0、1000、10000、400w的查询详情。

1:偏移量=0

2.偏移量=1000

3.偏移量=10000

4.偏移量= 400w

我们先来解释一下这些字段分别是什么意思

我们对比一下上面的信息,会发现只有一个字段的值有着很大的区别,那就是rows:扫描的行数,当limit分页的偏移量越大的时候扫描的行数就越多,这就是为什么我们查询越靠后的数据越慢。

假如你现在要查询的偏移量为100w,那么limit会扫描1000010行,然后丢弃前100w行数据,留下最后10行,返回给我们,所以说我们只需要控制扫描的行数,查询的速度自然就快了,那如何控制扫描的行数呢?

1.最大id查询法

扫描意思呢?举个例子,我查询第一页的时候是limit 0,10 查询到的最后一条id是10,那么下一页的查询只需要查询id大于10的19条数据即可。

我们看到rows=1949780,这个表示可能扫描了这么多行,这个行数是因为扫描id>400w的记录,后面还有两百万,而这里表示可能扫描了1949780行,但是由于limit 10的存在,所以扫描了10行之后就停止扫描了,我们也可以对比一下使用这种方式和直接使用limit 4000000,10的效率,千差万别。但是这种比较局限,只能适用于自增组件,那个uuid生成的主键这种方式不适用。

2.BETWEEN … AND

这种方式也只能适用于自增主键,并且id没有断裂,否者不推荐这种方式,我们发现使用BETWEEN AND的时候查询出来11条记录,也就是说BETWEEN AND包含了两边的边间条件。使用的时候需要特别注意一下。

3.limit id

这种查询方式就是先扫描4000010行,但是只取出id,然后再查询id大于这个值的前10条,这样虽然也是扫描了400多w行记录,由于id是主键,拥有者主键索引,所以查询 查询一个id的limit速度会快很多,我们可以对比一下一开始的limit 4000000,10,效率相差了3倍多。

4.延迟关联(个人推荐)

什么叫延迟关联,他让mysql扫描尽可能少的记录,获取到需要访问的记录后再根据关联列回到远表查询需要的所有列,这样听起来是不是很拗口,我们用sql来实现一下。

我们可以看到这种查看的方式和第三种的效率差不多,但是当字段比较多,类型的长度比较长的时候,这种还是比较有优势的。

5.分表查询

mysql推荐一张表的存储不要超过500w数据,查询400w不到1秒对于一般的查询来说已经可以了,如果还要更快的话,我建议使用分表存储,分表又分两种情况,水平分表于垂直分表。

水平分表

假如一张表的原始数据有1000w条数据,我可分三张表存储,一张表300的万,这样查询的时候压力就会小很多,并且效率也很高很多,那问题来了,如何这个水平水表如何实现呢?像可以借助mycat之类的中间件,阿里云也提供了数据库的分表技术,当然,你也可以自己手写分表,但是自己手写分表的时候需要注意id重复以及如何定义搭配当前id在那张表中,算法推荐使用hash值。

垂直分表

假如张彪的记录有100w,按正常来说查询速度应该不会太慢,但是由于这张表的字段超多,而且还有很多text类型的字段,这个时候我们可以将占用空间比较小的字段分在一张表,占用空间比较大的字段分在另一张表,两张表一一关联,这样,查询的时候就会快很多了。

冷热表

这里我还有一种分表思想,可以借鉴一下,那就是冷热表。

什么时冷热表?大家都用银行的app吧,你们查询账单的时候会发现只能查询近几个月的数据,之前的数据需要去柜台获取在查询历史账单中查看,他这里就是冷热表的设计思想。

我们新建两张一莫一样的表,一张表存放近三个月的记录(时间随情况而定,不一定时三个月) a表,另一张表存放三个月之前的数据:b表,用户产生的新记录可以存放在a表中,可以在每天凌晨的时候定时扫描a表,只要记录已经在三个月之前了,我们就可以将记录迁移到b表中,对于用户来说,查询近三个月的数据时他们比较敏感的,三个月之前的饿数据他们查询的可能并不多,所以这样的设计完全是合理的。

索引

这一点相信大家都知道,添加索引可以提高查询效率,如何我们的分页查询牵扯到条件的话,我们可以给条件添加索引,数据库会维护一张对应的索引表,查询的时候会先查询索引表,根据索引表返回的记录直接查询记录表,这样也减少了扫描的行数,但是需要注意,只要发生一下几点,索引都有可能不会被触发,一定要注意。

总结

总而言之,查询优化的重点就在于如何能扫描最少的记录,返回查询的结果,看上去容易,但是真正做起来的时候会发现是那么的不容易,对于写后端的程序猿来说,sql是家常菜,也是必不可少的一道菜,因为sql写的好不好直接决定着你程序的抗压能力强不强,这个时候你可能会说我可以使用缓存来降低数据库的访问,这只是治标不治本,只有写出漂亮的sql才能让程序立于不败之地。至于文章开头说的8秒是因为同事的那种表比我的这张表复杂得多,并且还加上了查询总记录的时间,我这里并没有给出count()查询时间,500w的数据,count()差不多都要两秒,所以sount(*),查询总数也是需要优化的,这个优化就比limit的优化简单多了,这里就不多做说明了。

作者:卖托儿索的小火柴

来源:

https://blog.csdn.net/qq_33220089/java/article/details/105012663

本文分享自微信公众号 - IT大咖说(itdakashuo),作者:卖托儿索的小火柴

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 鲁迅说过:有bug不可怕,改bug才可怕

    不过,近日,据网友发现,“鲁迅说过的话”检索系统已经上线,并表示,以后恶搞鲁迅先生名言名句可得小心了,不然,检索系统会来‘打假’,哈哈哈...”看来下一届学生不...

    IT大咖说
  • MySQL高可用架构案例篇:UCloud最佳实践

    IT大咖说
  • 互联网金融风控中的数据科学

    摘要 随着互联网行业的高速发展,互联网金融应运而生。它是传统金融行业与互联网精神相结合的新兴领域。互联网"开放、平等、协作、分享"的精神往传统金融业态渗透,对人...

    IT大咖说
  • SQL 教程:如何编写更佳的查询

    结构化查询语言(SQL)是数据科学行业中一项不可或缺的技能,一般来说,学习这个技能是挺容易的。不过,很多人都忘记了写查询只是SQL的第一步。我们还得确保查询性能...

    疯狂的技术宅
  • 快速学习Oracle-子查询

    子查询:在一个查询的内部还包括另一个查询,则此查询称为子查询。 Sql的任何位置都可以加入子查询。

    cwl_java
  • 【一文打尽】SQL 数据分析常用语句.....收藏

    • 1 基础查询 • 2 字符串\数字\日期时间 • 3 聚合数据查询 • 4 子查询 • 5 联接\组合查询 • 6 高级查询 • 7 更新数据 阅读提醒:点...

    小莹莹
  • 认识九大经典sql模式

    小结果集,源表较少,查询条件直接针对源表 对于典型的OLTP应用,多为返回小结果集的查询。如果过滤条件直接针对源表,我们必须保证这些过滤条件高效,对于重要的字...

    java达人
  • Access查询设计界面

    大家好,上节介绍了Access查询的知识框架,其实Access数据库的查询的功能很类似于Excel表中的筛选功能,但是功能更为丰富和强大。

    无言之月
  • Access查询基础

    大家好,前面介绍了Access数据库表部分的内容,后面开始介绍Access数据库查询部分的内容。

    无言之月
  • 2018-06-04第11章 子查询

    用户1250179

扫码关注云+社区

领取腾讯云代金券