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

1.现象:

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

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

2.分析:

从现象来看

每天凌晨15分，可能是定时任务

sql耗时，很可能是慢查询。根据关键字定位到这条语句

<update id="initChance">
    UPDATE policychance SET chances=#{chances} WHERE pid=#{pid}
</update>

3.验证:

该语句的被调用在com.xx.xxxxx.Provider#initPolicies.
该方法是一个定时任务 @Scheduled(cron = "0 15 0/1 * * ?”)，分析该任务的作用是每天初始化抽奖机会，一旦初始化完成通过设置标志位TASK_STAT_COMPLETE截流，所以当且仅当凌晨15分左右会出现报警。
分析该语句，我们看到where条件是pid=#{pid}，pid字段只有一个组合索引，字段顺序（userid， pid），因为mysql最左前缀匹配的规则，该语句无法用到索引，而policychance表数据量庞大，约有1000W＋，因此执行性能差，监控显示执行时间约26s。

4.解决方案:

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

    USE xxxdb;
    CREATE index ix_pid_user_id ON policychance (pid，user_id) ;
    DROP INDEX ix_user_id_pid ON policychance;

5.然而:

too young！第二天凌晨依旧是报警，执行时间是有一点点的缩短（19s），但是为什么还是这么慢？我们忽略了使用索引最大的前提：索引列的数据离散度要足够的大！

SELECT count(DISTINCT pid) FROM policychance

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

6.最终的解决方案:

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