现象:

  1. 程序大量SQL运行超时;
  2. 数据库中大量SQL长时间处于Updating阶段;

初步检查:

  1. 查看系统状态,CPU有24个核,只有一个核user利用率100%,其余核的idle基本上100%;
  2. 内存、IO.util都正常;
  3. 查看processlist,所有UPDATE语句长时间处于Updating阶段,其它类型的SQL没有Block或者运行缓慢现象;

进一步排查:

  1. 分析运行缓慢的SQL,发现运行缓慢的Update语句操作的都是同一张表;
  2. 执行缓慢的Update语句总共有两类:一类是通过主键更新一行数据;另一类是根据状态字段批量更新数据;Schema和SQL示例如下:

基本可以推测是根据状态字段更新数据的SQL导致的问题。进一步查看INNODB STATUS,发现如下记录:

这下可以解释为什么只有一个CPU核在运行了。第二条SQL通过索引加扫表的方式,寻找符合条件的数据,这条SQL消耗了一个CPU核。查找数据的同时,此SQL加了gap锁,导致其它更新同一张表的SQL一直在等待锁的释放,因此其它CPU核基本处于空闲状态。

解决

找到问题的根源,解决起来就比较简单了,分析一下数据,发现索引(user_id, status)的区分度还不错,新增索引(user_id, status)后,问题解决。