
开篇
是一个弱智的操作产生了下面的分析,如果一会最后忍不住喷还是先行
ctrl+w
为上
在看运营提的需求池时,发现有一个列表接口检索过慢。但实际未使用任何条件检索,也就是说一个普通的百万级表,查询需要10s+,激起了我的好奇心
在使用Arthas
的Trace
分析后,发现竟然是真的,原本以为是数据后处理过多的判断逻辑导致。。
1 | trace com.demo.modules.mk.service.impl.QuestionnaireAnswerServiceImpl page |
显然,慢查询是罪魁祸首。接下来深入分析具体原因。
分析过程
SQL
下面是具体执行出的SQL
1 | SELECT |
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 | SELECT |
从执行时间上看,绝对是排序打乱了索引的有序性,被迫把数据拉到sort buffer
中重新计算了。换句话说,查询部分使用了索引,但排序部分没有走索引。
看下这个表的index6索引有什么东西
1 | CREATE TABLE `mk_questionnaire_answer` ( |
解决问题
好吧。。。索引竟然不是按照最左前缀去加的,把索引换成严格的最左前缀匹配
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 已优化掉,性能提升。 |
总结
为什么会慢?
简单理解就是要不要再次排序的问题,正常的asc
、desc
排序,从索引结构上用不着单独进行排序。发生上边的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,那么就会通过磁盘临时文件辅助进行排序,如果数据量比较小,则可以直接在内存中进行。
在内存中排序会使用快排算法
,而通过磁盘临时文件则会使用归并排序算法
。
排序步骤可以分为以下几步:
- 取出select的数据存入sort buffer。
- 在sort buffer中进行快排或者归并排序算法。
- 如果有limit按照limit取相应的结果集进行返回。