记录一次对order by、goup by和distinct的sql优化

问题描述

起初是因为一个B端的商品评价列表查询很慢，一次查询大概需要7秒左右的时间。相关sql是几个表的关联查询，主表数据大概只有十几万，最多的表数据也不超过50万，第一反应就是sql有问题或者没有建索引。

原始sql大致如下

SELECT DISTINCT
	table_a.id,
	table_a.a,
	table_a.b,
	table_a.c,
	table_b.a,
	table_b.b,
	table_b.c
FROM
	table_a 
LEFT JOIN table_b  ON (table_b.a_id = table_a.id)
WHERE
	( table_a.gcflag = 0)
ORDER BY

	table_a.create_time DESC
LIMIT 10

实际sql比这要复杂，提取影响效率的关键部分精简后大致如上，就是两个表的join查询，根据查询条件 WHERE后边还可能会有table_b的字段查询，因不影响问题分析，暂时就不列出来了。

table_a和table_b是1对多的关系，用到了DISTINCT关键字去重，t1的create_time有索引，gcflag是删除标记，值只有0或1所以没有建索引。

第一反应是DISTINCT用的不合理，以我之前的了解DISTINCT更多的用于查询单个字段去重，或者接口count()函数使用，更多的时候去重使用的是group by。

sql优化

先来看下sql分析计划

可以看到table_a是全表扫描，并且从extra中可以看出该sql用了临时表，并且用了文件排序，效率很差，所以sql才会很慢。

我们来看下mysql官网对DISTINCT的优化描述

首先说到DISTINCT结合ORDER BY使用在很多情况下是会使用临时表的，并且大多数情况下DISTINCT是等价于GROUP BY的，我们这条sql使用DISTINCT等价于GROUP BY table_a.id,table_a.a,table_a.b,table_a.c,table_b.a,table_b.b,table_b.c，也就是说会对所有查询条件进行排序去重。

实际上我们只需要对table_a.id去重就可以了，所以先尝试用group by简化下sql看下效果，简化后sql如下

SELECT
	table_a.id,
	table_a.a,
	table_a.b,
	table_a.c,
	table_b.a,
	table_b.b,
	table_b.c
FROM
	table_a
LEFT JOIN table_b ON (table_b.a_id = table_a.id)
WHERE
	(table_a.gcflag = 0)
GROUP BY
	table_a.id
ORDER BY
	table_a.create_time DESC
LIMIT 10

现在这条sql执行已经降到1秒多，从执行计划来看的话table_a现在已经走主键索引查询了，虽说还是用到了临时表和文件排序，但是效率已经大大提升了。

针对官方提到的GROUP BY和ORDER BY一起使用大多数情况下都会用到临时表做下验证，把优化后的语句去掉ORDER BY后看下执行计划如下：

果然已经没有了Using temporary; Using filesort信息，并且查询时间已经降到了0.03秒了。但是业务有排序的要求，ORDER BY不能贸然拿掉。

在回头看下优化后的语句，虽然执行时间已经从7秒优化到了1秒多，但是针对当前表的数量总觉得这个查询效率还是比较差，进一步思考还有没有其他优化方案。

在官方对ORDER BY索引失效场景描述中有一项说的是，ORDER BY 和GROUP BY具有不同的表达式。

也就是说只有ORDER BY 和GROUP BY的条件相同时才可以用索引排序，否则将会建立临时表，可能会用到文件排序。

基于此尝试将ORDER BY table_a.create_time DESC改成 ORDER BY table_a.id DESC试一下，改动后的执行计划如下：

可以看出此时利用了索引排序，效果和去掉ORDER BY一样，并且查询也是只用了0.03秒。业务的排序要求是按照创建时间倒序排列，如果id是自增的话，按照id排序几乎可以等价于创建时间排序，至此也可以完成优化，但是我们的id用的是分布式雪花算法，虽说也是按照时间递增的，但是由于并发原因导致id排序和创建时间排序会有一些差异，所以不能采用这种方案。

既然排序需要先全表扫描或者扫表全部索引，建立临时表后在进行排序，那么可不可以通过子查询语句现将table_a数据查出来，在基于查出来的数据在进行排序去重，这样可以避免大量数据扫描。

基于这个思路优化后的sql如下：

SELECT
	table_a.id,
	table_a.a,
	table_a.b,
	table_a.c,
	table_b.a,
	table_b.b,
	table_b.c
FROM
	(
		SELECT
			table_a.id,
			tablea.a,
			table_a.b,
			table_a.c
		FROM
			table_a
		WHERE
			gcflag = 0
		ORDER BY
			create_time DESC
		LIMIT 10
	) table_a
LEFT JOIN table_b ON (table_b.a_id = table_a.id)
GROUP BY
	table_a.id

sql执行计划如下

id大的执行顺序排在前边，所以这条sql的执行顺序是先根据条件查询table_a并走索引排序取前10条，查询结果作为驱动表去和table_b做关联查询，由于table_a的结果集只有10条数据，所以后边的GROUP BY的排序去重哪怕用到临时表或文件排序效率也很高，整个sql执行下来用时0.04秒，并且排序也满足业务要求，最终采用这种优化方案。

后边有时间在补充下mysql在同时具有order by和group by的条件时的执行顺序及逻辑。