小工具      在线工具  汉语词典  css  js  c++  java

慢查询优化,filesort详细解析

Java,数据库,mysql,sql 额外说明

收录于:97天前

问题

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

首先找到这个表的定义和索引如下:

可见,主要有两个联合索引:status, to_account_id 和 status, from_account_id

问题分析

我们先用explain查看执行计划:

我们先来看看解释的意思。

id:除了 ID,什么都没有。如果没有子查询,通常只是一行。

select_type:大致分为两类:简单查询和复杂查询。复杂查询分为简单子查询、派生表(from中的子查询)和联合。一般来说,我们看到的比较简单,这意味着它不包含子查询和联合。如果有复杂的查询,它将被标记为主要的。

表:表名

type:表示关联类型,决定了Mysql如何搜索行数据。这通常是我们查看查询时的关键信息点。例如ALL表示全表扫描; index 表示使用索引; range表示对索引进行有限扫描,比直接扫描所有索引要好; ref 也是索引搜索,它会返回与特定值匹配的行数据,还有一些其他的。类型,例如eq_ref只返回一条匹配的记录,而const会被优化并转换为常量。

possible_keys:显示可以使用但不一定使用的索引。

key:实际使用的索引。

key_len:索引使用的字节数。

ref:表示上面键列中用于索引搜索的列或常量值。

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,尤其是在排序的数据量比较大的时候,那么优化的方式就是尽量让查询的数据排序,即合理使用联合索引和覆盖索引。

优化方向

优化一:调整指数结构

优化二:代码结构优化

另外,我们发现了一段代码,在for循环中执行操作,然后更新DB表中的状态。这将导致 1500 次数据库更新。我们可以考虑批量处理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倍以上。

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

. . .

相关推荐

额外说明

Centos下使用yum安装Mysql8(Mysql5.7)及常用配置和使用

记录一下在centos7.x下面使用yum方式安装mysql8(Mysql5.7)关系型数据库 安装之前一般需要先确定centos7.x服务器里是否已经安装,未安装或者刚初始化的centos7.x服务器最好安装,原来已经有的要升级的话一定要对系统原有my

额外说明

SpringBoot自动配置原理

三个重要的注解 在使用main()启动SpringBoot的时候,只有一个注解@SpringBootApplication 我们点进去@SpringBootApplication注解可以发现有三个注解是比较重要的: @SpringBootConfigur

额外说明

关于idea无法自动编译-maven模块编译顺序-相关依赖找不到-maven Cannot resolve org.springframework.cloud:xxx

文章目录 前言 一.第一种类型的问题 二.第二种类型问题bom类型 三.怎么知道谁是bom? 前言 找不到符号.... 程序包xxx.xxx.xxx不存在 有这些内容却报没有找到这个包或类? Project ‘org.springframework.bo

额外说明

渗透测试-JavaFX通用漏洞利用工具开发从无到无

JavaFX 渗透工具开发 目标展望 环境配置 vscode配置JavaFX JavaFX基本程序结构 创建项目 新建一个包 新建入口类(主类) 新建(FX)label标签 创建按钮 JavaFX应用的 Stage窗口 JavaFX应用的 Scene 场

额外说明

使用windows下的Eclipse或者IDEA远程连接Linux的Hadoop并运行wordcount

Windows使用Eclipse或IDEA连接Linux环境Hadoop运行wordcount   1 环境准备      linux 系统版本centos7 ,Hadoop版本2.7.6      (1)正确安装hadoop,具体安装步骤参考安装教程,

额外说明

myhadoop.sh启动停止脚本

#!/bin/bash if [ $# -lt 1 ] then echo "No Args Input..." exit ; fi case $1 in "start") echo " =================== 启动 hadoop

额外说明

彻底研究String

String是很常用的类型,但有的同学在使用过程中存在一些误区,导致效率低下,在此对其机制进行一个彻底的讨论,水平有限,如有不同的见解请留言讨论。 String [SerializableAttribute] [ComVisibleAttribute(

额外说明

Windows系统缺少ucrtbased.dll文件导致程序无法运行问题

其实很多用户玩单机游戏或者安装软件的时候就出现过这种问题,如果是新手第一时间会认为是软件或游戏出错了,其实并不是这样,其主要原因就是你电脑系统的该dll文件丢失了或没有安装一些系统软件平台所需要的动态链接库,这时你可以下载这个ucrtbased.dll文

额外说明

Node.js基础

Node.js简介 1、javascript可以在浏览器执行的原因: javascript可以在浏览器中执行时因为有javascript引擎支持,不同浏览器有不同的解析引擎:chrome时V8,safri是JSCore,IE是Chakra。 2、java

ads via 小工具