排序规则编码不一致引起的慢查询

  |   0 评论   |   34 浏览

背景

线上有个实时统计数据的功能,之前的效率还可以,查询都是毫秒级别的,但是最近业务改造,查询字段调整了下,导致查询很慢,基本上是查不出来,数据库timeout。
有问题的sql为:

select
count(m.id) as num,
count(ifnull(v1.account_number, v2.account_number)) as num2,
count(v1.account_number)/count(m.id) as rate,
avg(v1.total_watch_time) as aaa_avg,
avg(v2.total_watch_time) as bbb_avg
from a_member m
left join (select *, sum(watch_time) total_watch_time from xuexi_duration where type='aaa' and biz_id=1 group by biz_id,
account_number) v1 on m.account_number=v1.account_number and m.biz_id=v1.biz_id
left join (select *, sum(watch_time) total_watch_time from xuexi_duration where type='bbb' and biz_id=1 group by biz_id, 
account_number) v2 on m.account_number=v2.account_number and m.biz_id=v2.biz_id
WHERE m.biz_id= 1;

改造之前的sql:

select
count(m.id) as num,
count(ifnull(v1.user_id, v2.user_id)) as num2,
count(v1.user_id)/count(m.id) as rate,
avg(v1.total_watch_time) as aaa_avg,
avg(v2.total_watch_time) as bbb_avg
from a_member m
left join (select *, sum(watch_time) total_watch_time from xuexi_duration where type='aaa' and biz_id=1 group by biz_id,
user_id) v1 on m.user_id=v1.user_id and m.biz_id=v1.biz_id
left join (select *, sum(watch_time) total_watch_time from xuexi_duration where type='bbb' and biz_id=1 group by
 biz_id, 
user_id) v2 on m.user_id=v2.user_id and m.biz_id=v2.biz_id
WHERE m.biz_id= 1;

上面sql涉及到的表的数据量:
a_member表:1700000左右
xuexi_duration表:3100000左右

分析及解决

改造之后查不出数据,首先想到的是改了查询字段,那么是否创建了索引。查看a_member表的索引情况,确实没有为新的字段添加索引,这边直接给account_number加了索引。对于xuexi_duration表,也缺少biz_id,account_number的联合索引,之前的联合索引是加在biz_id,user_id上的,这边也直接加上biz_id,account_number的索引。

再次执行,发现没什么改进,依旧很慢。

使用EXPLAIN,对两个sql进行分析,发现两个sql的执行过程有点不一样
改造之前的sql执行情况:
image.png

改造之后的sql执行情况:
image.png

从两个执行结果来看,是<derived2>,<derived3>这边使用全表查询,改造之前的sql是用到了索引,为什么会这样呢?
带着疑问开始比较user_id、account_number字段有什么不一样,发现除了user_id是int,account_number是varchar,没看出什么不一样。在看排序规则的时候,发现了问题的所在
image.png
image.png
从上面的两个图可以看出,这两张关联的表的排序规则不一样,导致关联的时候没办法走到索引。
因为数据库默认使用的是utf8_general_ci,所以修改a_member表的排序规则为utf8_general_ci。
image.png
改完之后,再次执行分析,可以看到已经走索引了,查询速度跟之前的sql一样了。

原因分析

因为int类型没有排序规则,所以两个表的排序规则不一致时,没有出现问题。
数据库中每个字段都有字符集和排序规则,如果排序规则不一样那么会导致索引不生效。

也可以关注我的公众号:程序之声
图片
关注公众号,领取更多资源

本文为博主原创文章,未经博主允许不得转载。

评论

发表评论