Using filesort 还是Using index condition?
小轲

开篇

是一个弱智的操作产生了下面的分析,如果一会最后忍不住喷还是先行ctrl+w为上

在看运营提的需求池时,发现有一个列表接口检索过慢。但实际未使用任何条件检索,也就是说一个普通的百万级表,查询需要10s+,激起了我的好奇心

在使用ArthasTrace分析后,发现竟然是真的,原本以为是数据后处理过多的判断逻辑导致。。

1
2
3
4
5
6
7
8
$ trace com.demo.modules.mk.service.impl.QuestionnaireAnswerServiceImpl page

Press Ctrl+C to abort.
Affect(class-cnt:1 , method-cnt:1) cost in 90 ms, listenerId: 1

`---ts=2025-03-19 16:08:45;thread_name=main-thread;id=1;is_daemon=false;priority=5;TCCL=sun.misc.Launcher$AppClassLoader@18b4aac2
`---[99960ms] com.demo.modules.mk.service.impl.QuestionnaireAnswerServiceImpl#page()
`---[91101ms] com.demo.modules.mk.service.dao.BaseDao#selectPage()

显然,慢查询是罪魁祸首。接下来深入分析具体原因。

分析过程

SQL

下面是具体执行出的SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
id,
#........此处省略10+字段
is_deleted
FROM
mk_questionnaire_answer
WHERE
is_deleted = 0
AND (
approve_status not IN (10,11,12,13))
ORDER BY
approve_status ASC,
commit_date ASC
LIMIT 10

Explan执行计划分析

列名 备注或说明
id 1 查询的序列号,简单查询通常为 1。
select_type SIMPLE 查询类型,表示这是一个简单查询,没有子查询或联合查询。
table mk_questionnaire_answer 目标查询的表名。
type range 访问类型,range
表示基于索引的范围扫描。
possible_keys index_6 查询可能用到的索引名称。
key index_6 实际使用的索引名称。
key_len 2 表示索引中实际参与查询的字段长度,单位为字节。
rows 294439 估算需要扫描的行数,行数较高表明查询范围较大。
filtered 10.00 查询条件的过滤率,表示约 10% 的行符合查询条件。
Extra Using index condition; Using MRR; Using filesort Using index condition:使用了索引下推优化。
Using MRR(多范围读取):优化范围查询的 I/O。
Using filesort:使用文件排序(可能带来性能影响)。

看这个执行计划,是走了索引的。仅是排序时使用了 Using filesort这个内存大户,所以看看能不能使用索引优化即可,先去掉order by看看。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
id,
#........此处省略10+字段
is_deleted
FROM
mk_questionnaire_answer
WHERE
is_deleted = 0
AND (
approve_status not IN (10,11,12,13))
LIMIT 10
#执行时间:0.3s

从执行时间上看,绝对是排序打乱了索引的有序性,被迫把数据拉到sort buffer中重新计算了。换句话说,查询部分使用了索引,但排序部分没有走索引。

看下这个表的index6索引有什么东西

1
2
3
4
5
6
7
CREATE TABLE `mk_questionnaire_answer`  (
`id` bigint NOT NULL AUTO_INCREMENT,
//忽略部分创建表信息
PRIMARY KEY (`id`) USING BTREE,
//这里就是关键的index6
INDEX `index_6`(`approve_status`, `is_deleted`, `type`, `gmt_create`, `commit_date`) USING BTREE
)

解决问题

好吧。。。索引竟然不是按照最左前缀去加的,把索引换成严格的最左前缀匹配

1
INDEX `index_6`(`approve_status`, `commit_date`, `is_deleted`, `type`, `gmt_create`) USING BTREE

再执行这个SQL看看,最后仅需要0.73s。优化 改BUG成功,是的 就是这么的丝滑

再Explan执行计划分析

最后再看下这个优化后的执行计划,Extra字段中提示不再使用sort buffer了,速度也快了起来

列名 备注或说明
id 1 查询的序列号,简单查询通常为 1。
select_type SIMPLE 查询类型,表示这是一个简单查询,没有子查询或联合查询。
table mk_questionnaire_answer 目标查询的表名。
type range 访问类型,range
表示基于索引的范围扫描。
possible_keys index_6 查询可能用到的索引名称。
key index_6 实际使用的索引名称。
key_len 2 表示索引中实际参与查询的字段长度,单位为字节。
rows 294423 估算需要扫描的行数,行数略微减少,可能因查询条件优化所致。
filtered 10.00 查询条件的过滤率,表示约 10% 的行符合查询条件。
Extra Using index condition Using index condition:使用了索引下推优化。
文件排序 filesort 已优化掉,性能提升。

总结

为什么会慢?

简单理解就是要不要再次排序的问题,正常的ascdesc 排序,从索引结构上用不着单独进行排序。发生上边的filesort后,会出现索引检索数据几乎无意义的情况,数据会慢很多。

上面情况是使用了两个字段同时进行参照排序,所以需要使用复合索引才能做到使用索引排列,前面index6虽然把字段都加上了,因顺序不同,所以MySQL无法按照最左原则去匹配,这时候我们只需要调换下索引顺序,等MySQL去重建索引就好了。

小声BB:大家之后建索引还是要看一下顺序~~

什么是sort buffer?

mysql的排序用到了sort buffer,sort buffer是一个内存块。

mysql会先取出需要排序的数据,然后把数据放入sort buffer,当所有数据都放入sort buffer或者sort buffer满了就开始排序,然后将排序好的结果返回给客户端。

参数sort_buffer_size显示的就是sort buffer的大小。

如果数据量超过sort buffer,那么就会通过磁盘临时文件辅助进行排序,如果数据量比较小,则可以直接在内存中进行。

在内存中排序会使用快排算法,而通过磁盘临时文件则会使用归并排序算法

排序步骤可以分为以下几步:

  1. 取出select的数据存入sort buffer。
  2. 在sort buffer中进行快排或者归并排序算法。
  3. 如果有limit按照limit取相应的结果集进行返回。

参考文章

  1. MySQL 查询性能优化:处理“Using index condition; Using temporary; Using filesort”-CSDN博客
  2. https://juejin.cn/post/7144911491454468103
 评论
评论插件加载失败
正在加载评论插件