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的字段查询,因不影响问题分析,暂时就不列出来了。
第一反应是DISTINCT用的不合理,以我之前的了解DISTINCT更多的用于查询单个字段去重,或者接口count()函数使用,更多的时候去重使用的是group by。
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现在已经走主键索引查询了,虽说还是用到了临时表和文件排序,但是效率已经大大提升了。
在回头看下优化后的语句,虽然执行时间已经从7秒优化到了1秒多,但是针对当前表的数量总觉得这个查询效率还是比较差,进一步思考还有没有其他优化方案。
可以看出此时利用了索引排序,效果和去掉ORDER BY一样,并且查询也是只用了0.03秒。业务的排序要求是按照创建时间倒序排列,如果id是自增的话,按照id排序几乎可以等价于创建时间排序,至此也可以完成优化,但是我们的id用的是分布式雪花算法,虽说也是按照时间递增的,但是由于并发原因导致id排序和创建时间排序会有一些差异,所以不能采用这种方案。
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.idsql执行计划如下
id大的执行顺序排在前边,所以这条sql的执行顺序是先根据条件查询table_a并走索引排序取前10条,查询结果作为驱动表去和table_b做关联查询,由于table_a的结果集只有10条数据,所以后边的GROUP BY的排序去重哪怕用到临时表或文件排序效率也很高,整个sql执行下来用时0.04秒,并且排序也满足业务要求,最终采用这种优化方案。