最近压测的时候发现有一条sql
语句在查询到几 w 条数据的时候,查询耗时达到了1s
左右,达到了我们线上设置的timeout
,需要优化一下。
该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 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
条件后面所有的字段都要加到索引里面去。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,代码层面操作去重,优化索引使用,不使用临时表。