参考文章:MySQL 优化 GROUP BY-松散索引扫描与紧凑索引扫描

背景

最近压测的时候发现有一条sql语句在查询到几 w 条数据的时候,查询耗时达到了1s左右,达到了我们线上设置的timeout,需要优化一下。

sql 作用

sql是为了从订单表 (Order) 查询哪些店铺、商圈有待指派的订单,where条件中有 5 个字段来过滤

设计之初,因为group by操作会使用临时表来操作,增大查询耗时和内存使用,所以不建议用group by来操作,所以语句采用如下形式:

SELECT 商圈id FROM Order_x WHERE AND  A = a1 AND  B = b1 AND  (C & c1) = c1 AND   (D = d1  OR (D = d2 AND E < 1536227354));

查到商圈 id 后,在代码层面做去重操作,返回上层,告诉那些商圈是有待指派订单的。

优化前耗时

37735 rows in set (0.96 sec)

优化过程

慢的原因

首先用explain来看下sql使用的索引情况

从结果可以看出,可能扫描的数据行数达到了 8W 多行,使用的索引过滤效果不好。其实你要的数据确实也是 3w 多条,索引过滤后的数据太多,在 Mysql sendingdata 操作时耗时自然很大。

然后和 DBA 一起讨论,看看是否能加其他索引来优化这个查询。

但是我们where条件的几个字段,除了 E(期望送达时间)能起到很好的区分效果外(但这个区分度太高了),其他几个字段取值范围都非常小,就比如 A(订单状态)骑士就 7,8 种,B(调度类型)就 2 种,即使加上索引效果也不明显。因为查询出来的数据还是可能会多达几万条,无法解决这个问题。

我们尝试了给where语句中的字段加索引仍然无法解决问题后,陷入了僵局。问题不是出在了索引,而是数据量大,那如何减少查询的数据量呢?

改变思路

尝试加上distinct来直接返回有订单的商圈 id。sql语句变形如下:

SELECT distinct(商圈id) FROM Order_x WHERE AND  A = a1 AND  B = b1 AND  (C & c1) = c1 AND   (D = d1  OR (D = d2 AND E < 1536227354));

但是查询耗时仍然很大:

292 rows in set (0.98 sec)

explain显示,扫描行数不变,Extra信息变成了Using where; Using temporary,因为distinct会使用临时表,耗时反而增大了。然后一顿骚操作,把sql语句直接推到变成了

SELECT distinct(商圈id) FROM Order_x WHERE AND  A = a1;

意外的变快了,用explain一查原来用到group-by索引,这一发现让我觉得group by的操作不是我认知里面的就一定慢。

Using where; Using index for group-by

后来我搜索下这个Using index for group-by这个关键字找到了group byLoose Index Scan,原来我的语句击中了这个策略。然后我认真的看了相关知识,把 SQL 语句变为下面的方式,且新建了一个索引:

//新建了一个索引
create index A_B_C_D_index on Order_x (商圈id,A,B,C,D)
SELECT distinct(商圈id) FROM Order_x WHERE AND  A = a1 AND  B = b1 AND  (C & c1) = c1 AND   (D = d1  OR D = d2) group by 商圈id,A,B,C,D;
297 rows in set (0.02 sec)

查询效率提升到了 20ms,问题解决。

为何效率提升这么大?

因为用到了group by Loose Index Scan,但是要用这个东西,条件比较严苛,我刚开始在没理解明白的情况下,构造好几种SQL语句,都没有正确走到这个策略。

总结


↙↙↙阅读原文可查看相关链接,并与作者交流