性能文章>记一次慢SQL优化>

记一次慢SQL优化原创

5月前
3170013

问题

这是一个线上问题,从日志平台查询到的 SQL 执行情况,该 SQL 执行的时间为 11.146s,可以认定为是一个慢查询,美化后的 SQL 如下:

先找到这个表的定义以及索引情况如下:

可见,主要有两个联合索引:status, to_account_idstatus, 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

  1. 首先根据索引找到对应的数据,然后把数据放入排序缓冲区中
  2. 如果要排序的数据实际大小没有超过缓冲区大小,就会使用内存排序,如快速排序,然后取出符合条件的数据返回
  3. 如果超过了缓冲区大小,就需要使用外部排序,算法一般使用多路归并排序,首先对数据分块,然后对每块数据进行排序,排序结果保存在磁盘中,最后将排序结果合并

除了知道排序的流程之外,排序使用的是字段的定义最大长度,而不是实际存储的长度,所以会花费更多的空间。

另外在5.6之前的版本,如果涉及到多表关联查询,排序字段来自不同表的话,会将关联结果保存到临时表中,这就是我们平时看到using temporary;using filesort的场景,如果这时候再使用limitlimit将会发生在排序之后,这样也可能导致排序的数据量非常大。

整个情况来看,缓冲区大小、排序字段的数据长度、查询数据条数等都会影响查询性能。

分析了整个排序过程,指导的优化思想就是尽量不使用using filesort,尤其是在排序的数据量比较大的时候,那么优化的方式就是尽量让查询出来的数据已经是排好序的,也就是合理使用联合索引以及覆盖索引

优化方向

优化1:调整索引结构

优化2:代码结构优化

另外,我们发现一处代码,在 for 循环中做操作,然后更新 DB 表中的状态,这样会导致 1500 次的 DB 更新,可以考虑将 DB 的更新做批量处理,减少 DB 写的次数,比如 100 条记录执行一次 DB 更新,这样会大大降低写 db 的次数。

这样每次 方法调用,就会将 3000 次的写操作,降低为 30 次的写操作,当然批量的大小可以调节。

这里我们仅仅针对 SQL 调优,代码问题就暂时不考虑了。

性能结果

测试环境数据量在30万数据

  1. 优化前查询在 1.5s 以上
  2. 优化后查询在 0.4s 左右

查询性能提升 3~4 倍。

从生产的从库上查询看到数据量大概有3KW+,符合 where 条件的数据大概在300万左右

  1. 优化前查询在 11s ~ 14s

  2. 优化后查询在 0.8s 左右

性能提升10倍以上。

虽然这个优化比较简单,但是还是需要我们平时有扎实的基础才能选择最合理的方式进行优化。

 

分类:
标签:
请先登录,再评论

暂无回复,快来写下第一个回复吧~

为你推荐

Elasticsearch调优篇-慢查询分析笔记
前言- elasticsearch提供了非常灵活的搜索条件给我们使用,在使用复杂表达式的同时,如果使用不当,可能也会为我们带来了潜在的风险,因为影响查询性能的因素很多很多,这篇笔记主要记录一下慢查询可
从一次数据库问题排查实战
前言上周五,一同事在开发时遇到了一个问题,叫我帮忙看下.在描述这个同事遇到问题之前,我先简单做一些知识的铺垫,否则不好描述.这里面涉及到的知识点有Spring的事务传播机制、数据库的隔离级别等.本篇重
大招落地:MySQL 插入更新死锁源码分析
天再来分析一个死锁场景。下面开始真正的内容。建表语句:```javaCREATE TABLE `tenant_config` ( `id` bigint(21) NOT NULL AUTO_INCR
一次慢查询暴露的隐蔽的问题
最近解决了一个生产 SQL 慢查询的问题,排查问题之后发现一些比较隐匿且容易忽略的问题。 业务背景介绍最近业务上需要上线一个预警功能,需要查出一段时间内交易,求出当前交易成功率。当成功率低于设定阈值时
千万级数据表选错索引导致的线上慢查询事故
前言又和大家见面了!又两周过去了,我的云笔记里又多了几篇写了一半的文章草稿。有的是因为质量没有达到预期还准备再加点内容,有的则完全是一个灵感而已,内容完全木有。羡慕很多大佬们,一周能产出五六篇文章,给
记一次中间件导致的慢SQL排查过程
前言最近发现线上出现一个奇葩的问题,这问题让笔者定位了好长时间,期间排查问题的过程还是挺有意思的,正好博客也好久不更新了,就以此为素材写出了本篇文章。 Bug现场我们的分库分表中间件在经过一年的沉淀之
MySQL全面瓦解21(番外):一次深夜优化亿级数据分页的奇妙经历
作者:翁智华,转载已获授权原文链接:https://www.cnblogs.com/wzh2010/p/14316920.html 背景1月22号晚上10点半,下班后愉快的坐在在回家的地铁上,心里想着
隐藏了2年的Bug,终于连根拔起,悲观锁并没有那么简单
接手的新项目,接二连三的出现账不平的问题,作为程序员中比较执着的人,不解决誓不罢休。最终,经过两次,历时多日终于将其连根拔起。实属不易,特写篇文章记录一下。 文章中不仅会讲到使用悲观锁踩到的坑,以及本人是如何排查问题的,某些思路和方法或许能对大家有所帮助。