mysql优化

一个小小的改动，优化掉Using temporary; Using filesort

问题

项目开发过程中，发现某条sql出现了以下错误

Out of sort memory,  consider increasing server sort buffer size

错误原因是待排序的内容过大，导致 buffer 不够用

其实以前就出过一次问题，当时给排序的 create_time 字段加上索引就解决了

定位到问题sql如下

SELECT a.id
from  a
         LEFT JOIN  ac on a.column_id = ac.id
         LEFT JOIN  ic ON a.ic_id = ic.id
where not exists(select 1 from  gb where gb.type = 1 and gb.service_id = ac.id)
  and a.`level` = 1
  and a.state = 0
  and ac.state = 1
order by a.create_time desc
limit 1, 15;

排查

首先对sql进行 explain，结果如下

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	ac		ALL	PRIMARY				25	10	Using where; Using temporary; Using filesort
1	SIMPLE	gb		ref	goods_bind_service_id_type_index	goods_bind_service_id_type_index	14	zfapp_app.ac.id,const	1	100	Using where; Not exists; Using index
1	SIMPLE	a		ref	idx_column	idx_column	8	zfapp_app.ac.id	355	1	Using where
1	SIMPLE	ic		eq_ref	PRIMARY	PRIMARY	8	zfapp_app.a.ic_id	1	100

里面出现了非常可怕的 Using temporary; Using filesort

另外出现的顺序也有点奇怪，我是以a表为主表，但是出现在第一项的是ac表

解决

在一点点的测试之后，终于找到了问题所在。

将where之后的 and ac.state = 1 移到 join后面，最终sql如下

SELECT a.id
from  a
         LEFT JOIN  ac on a.column_id = ac.id  and ac.state = 1
         LEFT JOIN  ic ON a.ic_id = ic.id
where not exists(select 1 from  gb where gb.type = 1 and gb.service_id = ac.id)
  and a.`level` = 1
  and a.state = 0
order by a.create_time desc
limit 1| 15;

再 explain一下

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	a		ref	index	idx_create_time	8		12	1	Using where; Backward index scan
1	SIMPLE	ic		eq_ref	PRIMARY	PRIMARY	8	zfapp_app.a.ic_id	1	100
1	SIMPLE	ac		eq_ref	PRIMARY		8	zfapp_app.a.column_id	25	10	Using where
1	SIMPLE	gb		ref	goods_bind_service_id_type_index	goods_bind_service_id_type_index	14	zfapp_app.ac.id,const	1	100	Using where; Not exists; Using index

结果正常了

原理

先随便猜测一下，对表的判断写在join里面，就只是对该表做回表，写在外面就是将两个表合并排序，问题很大