从小白到架构师利用 group by 的 Loose Index Scan 优化 sql

易寒 · 2018年10月17日 · 最后由 simple 回复于 2018年10月18日 · 2610 次阅读

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

背景

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

sql 作用

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

A 订单状态，用 A=a1 来判断是否是新订单。
B 调度类型，分商圈 b1 和店铺 b2 调度。
C 调度标志，用 C&c1=c1 来表示是否支持自动调度。
D 区分订单类型，主要有立即单 d1 和预约单 d2。
E 期望送达时间。预约单的时候，用户会设定期望何时送来，我们系统会根据这个值向前推 x 分钟来进行调度。

设计之初，因为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 by的Loose 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语句，都没有正确走到这个策略。

group by 后面跟的字段，必须在同一个索引中最前面的连续位置，比如你的索引是(A,B,C,D)，你如果是group by B,C,D，这种是不触发Loose Index Scan，因为你 A 是第一个，所以第一个字段必须是 A，
select后面的显示字段必须跟group by保持一致。
查询只能针对一个表。
group by 后面没有的字段，where条件里面该字段必须是常量，我理解的是必须是=号标识，要向进行or或者&运算，必须把改字段添加到group by后面。
建索引时必须把where条件后面所有的字段都要加到索引里面去。

总结

对 SQL 相关的知识比较缺乏，准备补补。
听别人，不如自己试验下，本次group by在常规来说，我们不应该采用，但实际调研结果反而比较好。
SELECT 商圈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;，删掉 distinct，代码层面操作去重，优化索引使用，不使用临时表。