背景
最近压测的时候发现有一条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,代码层面操作去重,优化索引使用,不使用临时表。