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

star2017 1年前 ⋅ 2008 阅读

背景

线上有个实时统计数据的功能，之前的效率还可以，查询都是毫秒级别的，但是最近业务改造，查询字段调整了下，导致查询很慢，基本上是查不出来，数据库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执行情况：

改造之后的sql执行情况：

从两个执行结果来看，是<derived2>,<derived3>这边使用全表查询，改造之前的sql是用到了索引，为什么会这样呢？
带着疑问开始比较user_id、account_number字段有什么不一样，发现除了user_id是int，account_number是varchar，没看出什么不一样。在看排序规则的时候，发现了问题的所在

从上面的两个图可以看出，这两张关联的表的排序规则不一样，导致关联的时候没办法走到索引。
因为数据库默认使用的是utf8_general_ci，所以修改a_member表的排序规则为utf8_general_ci。

改完之后，再次执行分析，可以看到已经走索引了，查询速度跟之前的sql一样了。