专栏首页艾小仙男人要慢,SQL要快:记一次慢SQL优化

男人要慢,SQL要快:记一次慢SQL优化

问题

这是一个线上问题,从日志平台查询到的 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倍以上。

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

本文分享自微信公众号 - 艾小仙(aixiaoxianren),作者:艾小仙

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

原始发表时间:2021-07-30

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 记一次神奇的SQL查询经历,group by慢查询优化

    简单来说,就是查询一定条件下,都有哪些用户的。很简单的sql,可以看到,查询耗时为37秒。

    数据和云
  • 记一次神奇的sql查询经历,group by慢查询优化

    现网出现慢查询,在500万数量级的情况下,单表查询速度在30多秒,需要对sql进行优化,sql如下:

    梁规晓
  • 记一次详细的的SQL查询经历,group by慢查询优化

    现网出现慢查询,在500万数量级的情况下,单表查询速度在30多秒,需要对sql进行优化,sql如下:

    数据和云
  • 同样是下班,凭什么你拿手机我背电脑?

    作为一个DBA什么时候让你最心塞? 也许那就是:无论你是下班、聚餐、逛街、旅行还是出差,只要你出门,不分场景和目的,必须随身带着又沉又大的电脑! 而且你永远不...

    腾讯云数据库 TencentDB
  • 零基础 SQL 数据库小白,从入门到精通的学习路线与书单

    我观察了 865 个 SQL 入门者,发现大家在学习 SQL 的时候,最大的问题不是 SQL 语法,而是对 SQL 原理的不熟悉。

    Lenis
  • MySQL 之视图、 触发器、事务、存储

    --------------------------------------------------------------------------------...

    py3study
  • 为什么我使用了索引,查询还是慢?

    首先SQL判断一个语句是不是慢查询语句,用的是语句的执行时间。他把语句执行时间跟long_query_time这个系统参数作比较,如果语句执行时间比它还大,就会...

    Java_老男孩
  • java后端开发面经_数据库相关

    你答:有这样一个故事,讲的是一个小男孩和一个小女孩,这个小男孩呢,用很多好玩石头,而这个小女孩呢,有好多好吃的糖果,有一天,他们相互约定:小男孩用所有的石头交互...

    用户6055494
  • 把MySql当作列存数据库使用时多条件and查询如何实现?

    可能是为了优化查询性能,也可能是做大数据的惯性思维,才会把MySql数据库当分析型数仓去使用吧。

    Java艺术
  • MySQL数据库优化二三事

    点击上方蓝字“ITester软件测试小栈“关注我,每周一、三、五早上 08:30准时推送,每月不定期赠送技术书籍。

    ITester软件测试小栈
  • ElasticSearch优化会员列表搜索

    ElasticSearch是一个基于Lucene的搜索服务器。它提供了一个分布式多用户能力的全文搜索引擎,基于RESTful web接口。Elasticsear...

    搜云库技术团队
  • 记一次对DM数据库的优化过程

    某年某月某日的一个下午,接收到监控服务器的一条告警短信:尊敬的运维工程师 XX,你好:“192.168.136.200”数据库服务器 CPU 异常,CPU 使用...

    JAVA日知录
  • MySQL 查询分析

    本文主要由一个案例引发对 MySQL 性能问题的思考,主要讲述 MySQL 慢查询和 explain 工具这两个定位 MySQL 性能瓶颈的方法。

    谢庆玲
  • MySQL 性能调优——SQL 查询优化

    如何设计最优的数据库表结构,如何建立最好的索引,以及如何扩展数据库的查询,这些对于高性能来说都是必不可少的。但是只有这些还不够,要获得良好的数据库性能,我们还要...

    烂猪皮
  • 如何找到垃圾SQL语句,你知道这个方式吗?

    我们前几篇文章介绍了什么是索引,索引分析explain语法的用法,以及索引如何优化等文章,如果大家对这些知识点不熟悉,可以在历史文章里找一下。

    公众号 IT老哥
  • 记录一次实际过程中的MySql数据库SQL优化

    之前开发项目的过程当中数据库存储的数据量都不是很大,在表的设计当中就只有一个主键索引。很少接触到数据库的索引,SQL 优化这些东西。公司目前的项目数据达到了百万...

    海加尔金鹰
  • 细说那些让公司网站瘫痪的SQL

    步骤 3:指定记录慢查询日志 SQL 执行时间的阈值(long_query_time 单位:秒,默认 10 秒)。

    AlbertZhang
  • 实战!聊聊如何解决MySQL深分页问题

    我们日常做分页需求时,一般会用limit实现,但是当偏移量特别大的时候,查询效率就变得低下。本文将分四个方案,讨论如何优化MySQL百万数据的深分页问题,并附上...

    捡田螺的小男孩
  • 实战!聊聊如何解决MySQL深分页问题

    我们日常做分页需求时,一般会用limit实现,但是当偏移量特别大的时候,查询效率就变得低下。本文将分4个方案,讨论如何优化MySQL百万数据的深分页问题,并附上...

    阿峰博客

扫码关注云+社区

领取腾讯云代金券