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

慢查询优化,filesort详细解析

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

收录于:43天前

问题

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

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

. . .

相关推荐

额外说明

Vue 2.0 - 首次引入组件

文章目录 前言 定义一个简单的组件 定义可传参数的组件 前言 在VUE 2.X的 官方文档 中,针对组件有下面一段描述内容: 组件系统是 Vue 的另一个重要概念,因为它是一种抽象,允许我们使用小型、独立和通常可复用的组件构建大型应用。 通俗的说法就是:

额外说明

MVVM项目开发(商品管理系统1)

MVVM项目开发(商品管理系统一) 注:下面使用 Microsoft Visual Studio2022,部分知识点引自导师教学,我的资源中有完整的系统及SQL数据库和数据库物理模型免费下载。 一、MVVM页面搭建 1.(1)、首先要搭建一个主页面,在项

额外说明

网页中宽度和高度的分类介绍

一、页面的实际宽度和高度读取 计算页面实际宽度和高度的代码如下所所示: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1

额外说明

MySQL的增删查改,你真的会了么?一文掌握MySQL不是梦

前情提要 鉴于篇幅,文中会有大量图片展示,可以双击放大图片查看。已将本文整理成PDF文档《MySQL大全》,需要的可以评论区留言哈。 Mysql数据库的安装和基本配置 建议去官方下载最新版MySQL,很多旧版官方都不支持,注意操作系统的差距。 因为MyS

额外说明

零基础学Python之数据类型的转换(手把手带你做牛客网python代码练习题)

文章目录 一、前言 二、转换数据类型的作用 三、转换数据类型的函数 四、代码举例 五、牛客网练习题 1、小数化整数 2、为整数增加小数点 -作者简介:在校大学生一枚,Java领域新星创作者,Java、Python正在学习中。 -日常学习网站:牛客网,可以

额外说明

ElasticSearch的客户端操作

ElasticSearch的客户端操作 1、客户端介绍 官方文档地址: https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html 实际开发中,有多种方式操作El

额外说明

【数据库】MySQL数据库约束(六大约束)

目录 1.数据库约束 1.1约束类型  1.2 非空约束(NOT NULL ) 1.3 唯一约束(UNIQUE)  1.4默认值约束(DEFAULT )  1.5主键约束(PRIMARY KEY)  1.6外键约束(FOREIGN KEY ) 写在前面

额外说明

python列表中 for循环操作

代码: print([x*x for x in range(1,11)]) print([x*x for x in range(1,11) if x%2==0]) print([m+n for m in 'ABC' for n in'abc'])

额外说明

K 近邻算法解析: 从原理到实践的机器学习指南

机器学习 第三课 k 近邻 概述 机器学习简介 K 近邻算法 K 近邻中的距离 欧氏距离 曼哈顿距离 余弦相似度 选择合适的 K 值 奇数 vs 偶数 通过交叉验证选择 k 值 实战 分类问题 回归问题 K 近邻算法的优缺点 优点 缺点 手把手实现 k

额外说明

Spring高手之路5——彻底掌握Bean的生命周期

文章目录 1. 理解Bean的生命周期 1.1 生命周期的各个阶段 2. 理解init-method和destroy-method 2.1 从XML配置创建Bean看生命周期 2.2 从配置类注解配置创建Bean看生命周期 2.3 初始化和销毁方法的特性

ads via 小工具