记一次慢SQL优化
问题
这是一个线上问题,从日志平台查询到的 SQL 执行情况,该 SQL 执行的时间为 11.146s,可以认定为是一个慢查询,美化后的 SQL 如下:
先找到这个表的定义以及索引情况如下:
可见,主要有两个联合索引: status, to_account_id 和 status, from_account_id 问题分析
我们先用 explain 查看执行计划:
先看看explain的含义吧。
id :没什么就是ID而已,如果没有子查询的话,通常就一行。
select_type :大致分为简单查询和复杂查询两类,复杂查询又分为简单子查询,派生表(from中的子查询)和union。一般我们看见simple比较多,代表不包含子查询和union,如果有复杂查询则会标记成primary。
table :表名
type :表示关联类型,决定Mysql通过什么方式查找行数据。这个一般就是我们看查询时候的关键信息点。比如ALL就是全表扫描;index代表使用索引;range代表有限制的扫描索引,回比直接扫描全部索引好一些;ref也是索引查找,会返回匹配具体某个值的行数据,这个还有一些其他类型,比如eq_ref只返回符合的一条记录,const会进行优化转换成常量。
possible_keys :显示可以使用的索引,但不一定用。
key :实际使用到的索引。
key_len :索引使用的字节数。
ref :代表上面key一列中使用索引查找用到的列或者常量值。
rows :为了找到符合条件的数据读取的行数。
filtered :表示查询符合条件的数据占表的行数百分比,rows*filtered可以大致得到关联的行数,Mysql5.1之后新增的字段。
Extra :额外信息,比如 using index 表示使用覆盖索引,using where 表示在存储引擎之后进行过滤,using temporary 表示使用临时表,using filesort 表示对结果进行外部排序。
基本上述的经验,我们看到索引和扫描行数其实都没啥问题,但是,我们发现执行计划中使用了 using filesort 。
综合执行 SQL 和表定义,基本断定问题出在 ORDER BY amount desc, create_time asc ,在生产线上数据记录较多,使用 order by 语句后引起 filesort ,导致出现了外部排序,从而降低了 SQL 的查询性能。
再来理解一下 order by 的工作原理,帮助我们更好的做 SQL 优化。
一般情况下,执行计划中如果出现 using filesort 就会走如上的执行流程,对于Mysql来说,数据量小则在内存中进行排序,数据量大则需要在磁盘中排序,这个过程统一都叫做filesort 。 首先根据索引找到对应的数据,然后把数据放入排序缓冲区中 如果要排序的数据实际大小没有超过缓冲区大小,就会使用内存排序,如快速排序,然后取出符合条件的数据返回 如果超过了缓冲区大小,就需要使用外部排序,算法一般使用多路归并排序,首先对数据分块,然后对每块数据进行排序,排序结果保存在磁盘中,最后将排序结果合并
除了知道排序的流程之外,排序使用的是字段的定义最大长度,而不是实际存储的长度,所以会花费更多的空间。
另外在5.6之前的版本,如果涉及到多表关联查询,排序字段来自不同表的话,会将关联结果保存到临时表中,这就是我们平时看到 using temporary;using filesort 的场景,如果这时候再使用limit ,limit 将会发生在排序之后,这样也可能导致排序的数据量非常大。
整个情况来看,缓冲区大小、排序字段的数据长度、查询数据条数等都会影响查询性能。
分析了整个排序过程,指导的优化思想就是尽量不使用 using filesort ,尤其是在排序的数据量比较大的时候,那么优化的方式就是尽量让查询出来的数据已经是排好序的,也就是合理使用联合索引以及覆盖索引 。 优化方向
优化1:调整索引结构
优化2:代码结构优化
另外,我们发现一处代码,在 for 循环中做操作,然后更新 DB 表中的状态,这样会导致 1500 次的 DB 更新,可以考虑将 DB 的更新做批量处理,减少 DB 写的次数,比如 100 条记录执行一次 DB 更新,这样会大大降低写 db 的次数。
这样每次 方法调用,就会将 3000 次的写操作,降低为 30 次的写操作,当然批量的大小可以调节。
这里我们仅仅针对 SQL 调优,代码问题就暂时不考虑了。 性能结果
测试环境数据量在30万数据 优化前查询在 1.5s 以上 优化后查询在 0.4s 左右
查询性能提升 3~4 倍。
从生产的从库上查询看到数据量大概有3KW+,符合 where 条件的数据大概在300万左右 优化前查询在 11s ~ 14s 优化后查询在 0.8s 左右
性能提升10倍以上。
虽然这个优化比较简单,但是还是需要我们平时有扎实的基础才能选择最合理的方式进行优化。
赵丽颖冯绍峰官宣离婚因聚少离多和平分手23日,赵丽颖工作室和冯绍峰工作室同时发布声明,称赵丽颖冯绍峰已决定和平分手结束婚姻关系,并已于近日办理相关手续,未来将共同抚养陪伴孩子成长。冯绍峰对于离婚也发文称日子很长,过去很
李思思,央视一姐不是她的目标最近李思思化身白娘子登上了舞台。被网友夸赞端庄大方,多才多艺,一点儿不输赵雅芝。而在之前知道要排练白蛇转的时候,李思思还曾经想着要反串一下许仙。白娘子的造型一出,的确是惊艳了众人。
范冰冰想复出估计想疯了,啥热度都要蹭一波自从前年范冰冰因为偷漏税风波之后,瞬间从象牙塔跌入深渊虽然范冰冰一直风评就极差,她是怎样从一个丫鬟变为女主的路径,想必所有人都不言而喻吧但一个戏子私生活再混乱,咱外人都没啥好评头论
利好灵活用工!人社部鼓励网上签约近日,人力资源社会保障部办公厅印发人力资源社会保障部办公厅关于订立电子劳动合同有关问题的函,文件明确用人单位与劳动者协商一致,可以采用电子形式订立书面劳动合同,电子合同将直接利好灵
鹿晗关晓彤互动,关晓彤这一举动惊呆了男友鹿晗鹿晗关晓彤这对小鲜肉cp虽然在一开始恋情曝光的时候并不受到大家的认可,反而有很多鹿晗的粉丝表现出一致的抵触,但是鹿晗却并没有因此改变自己的选择,反而对关晓彤十分的疼爱。应该是不想让
她比大熊猫还珍贵,却不断遭人驱逐后,这些人的举动暖爆了!你眼里的狗狗是什么样子?调皮?捣蛋?机灵?或者是,拆家小能手?你有见过这样的狗狗吗?从出生开始,它的命运就被人类所选择,日复一日地进行艰难的训练,要经历寄养培训服役退役四个阶段,它
韩雪近4000元白鞋被踩脏,下意识一个举动,诠释了教养2字!说起韩雪,大家一定对她一定不会太陌生,韩雪也是一直被人称为娱乐圈当中最看重的一位女的是,他出道多年以来也是0绯闻,还是有着不俗的家庭背景的,她在娱乐圈当中的言行举止,也是一直受到人
乔任梁女友徐璐拍摄婚纱照,疑似好事将近,网友5个月不止不知道大家还认不认识黄景瑜这位新生代的演员,原本一个默默无闻的小演员,在参加了红海行动这部电影之后,也是名声大噪,而他也是借此重新回归娱乐圈。因为在红海行动中,他的表现良好,可以说
肖战十月份首个好消息,品牌未官宣已有线下地广?喜欢一个人需要理由吗?真的没有理由。只是因为一个名字就足以让人心动,因为这两个字是肖战。也正因为我们喜欢的人足够优秀,足够吸引人,所以当伴随着光芒的同时也有舆论产生。流量带来热度的
郑爽回应代孕。11年后,她的流星雨终于成了一场暴风雨Sayings相信你仍在为这条热搜震惊郑爽和张恒代孕,并一度想弃养。编辑几个小时前,郑爽做了最新回应,开头就说,这是自己非常伤心和私密的事情。紧接着另一条热搜跟着上了,只有三个字看
郑爽如同苍蝇一般,扑棱几天无一人在意她前几天吴亦凡,因为私德败坏而频繁上热搜,不仅仅是受害者在网上控诉,也是引起众多网友和其合作的品牌商对他抵制和谴责。但这个时候还有个苍蝇也跑出来蹭热度了,郑爽因为代孕一事,遭到网友和