mysql的一次sql耗时高引发报警的分析和处理

mysql的一次sql耗时高引发报警的分析和处理

现象

最近两天在每天的凌晨0:15~20分左右收到报警短息,报警内容:

JDBC-SQL请求最近三分钟内平均耗时时间过高的报警,监控类型:SQL…

分析

从现象来看

  • 每天凌晨15分,可能是定时任务
  • sql耗时,很可能是慢查询。根据关键字定位到这条语句
1
2
3
<update id="initChance">
UPDATE policychance SET chances=#{chances} WHERE pid=#{pid}
</update>

验证

  1. 该语句的被调用在com.xx.xxxxx.Provider#initPolicies.
    该方法是一个定时任务 @Scheduled(cron = "0 15 0/1 * * ?”),分析该任务的作用是每天初始化抽奖机会,一旦初始化完成通过设置标志位TASK_STAT_COMPLETE截流,所以当且仅当凌晨15分左右会出现报警。

  2. 分析该语句,我们看到where条件是pid=#{pid},pid字段只有一个组合索引,字段顺序(userid, pid),因为mysql最左前缀匹配的规则,该语句无法用到索引,而policychance表数据量庞大,约有1000W+,因此执行性能差,监控显示执行时间约26s。

解决方案

根据对policychance表的使用情况来看,将索引字段顺序调整更合理。脚本如下

1
2
3
USE xxxdb;
CREATE index ix_pid_user_id ON policychance (pid,user_id) ;
DROP INDEX ix_user_id_pid ON policychance;

然而

too young!第二天凌晨依旧是报警,执行时间是有一点点的缩短(19s),但是为什么还是这么慢。后来发现我们忽略了使用索引最大的前提:索引列的数据离散度要足够的大!

1
SELECT count(DISTINCT pid) FROM policychance

policychance表的pid在一张1000W+的行中只有13个,离散度太低。在这种情况下,mysql引擎甚至会不使用索引。我们知道innodb存储引擎的索引类型是B+树,并不适用于这种情况。适用于这种情况的索引类型是位图索引(Bitmap index),目前mysql的存储引擎暂时还没有支持位图索引。ORACLE的位图索引介绍见这里

最终的解决方案

问题终归是要解决的,只是不太优雅。分页查询满足条件的id,批量update。如果有好的处理方案,请留言告诉我,互相学习互相进步。

作者: wuzhaoyang(John)
出处: http://wuzhaoyang.me/
因为作者水平有限,无法保证每句话都是对的,但能保证不复制粘贴,每句话经过推敲。希望能表达自己对于技术的态度,做一名优秀的软件工程师。