数据库优化案例

现象：

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

初步检查：

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

进一步排查：

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- Schema CREATE TABLE ` general_mail ` (    ` id ` bigint ( 20 ) unsigned NOT NULL AUTO_INCREMENT ,    ` user_id ` bigint ( 20 ) unsigned NOT NULL DEFAULT '0' ,    ` value ` text NOT NULL ,    ` status ` tinyint ( 4 ) unsigned NOT NULL DEFAULT '0' ,    ` type ` tinyint ( 4 ) unsigned NOT NULL DEFAULT '0' ,      . . . . . .    PRIMARY KEY ( ` id ` ) ,    KEY ` idx_status_type ` ( ` status ` , ` type ` ) , ) ENGINE = InnoDB ;   -- 根据主键进行更新 UPDATE tbl_A SET status = N , value = '.....' , user_id = N WHERE id = N ;   -- 根据状态进行更新 UPDATE tbl_A SET status = N WHERE ( status = N or status = N ) and user_id = N limit N ;

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

------------------ ---TRANSACTION 43DF0254C, ACTIVE 51 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 118879, OS thread handle 0x7f51950b6700, query id 18995841 10.0.0.0 mysql_user Updating UPDATE tbl_A SET status=N, value='.....', user_id=N WHERE id = N ------- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2186 page no 188857 n bits 688 index `idx_status_type` of table `db`.`tbl_A` trx id 43DF0254C lock_mode X locks gap before rec insert intention waiting Record lock, heap no 497 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  0: len 1; hex 02; asc  ;;  1: len 1; hex 01; asc  ;;  2: len 8; hex 00000000011c1f0f; asc         ;; ------------------ ---TRANSACTION 43DF0216B, ACTIVE 65 sec fetching rows, thread declared inside InnoDB 155 mysql tables in use 1, locked 1 794767 lock struct(s), heap size 71694776, 13900037 row lock(s) MySQL thread id 118812, OS thread handle 0x7f51951dc700, query id 18995134 10.0.0.1 mysql_user Searching rows for update UPDATE tbl_A SET status=N WHERE (status=N or status=N) and user_id=N limit N ---TRANSACTION 43DF0254C, ACTIVE 51 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 118879, OS thread handle 0x7f51950b6700, query id 18995841 10.0.0.0 mysql_user Updating UPDATE tbl_A SET status=N, value='.....', user_id=N WHERE id = N ------- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2186 page no 188857 n bits 688 index `idx_status_type` of table `db`.`tbl_A` trx id 43DF0254C lock_mode X locks gap before rec insert intention waiting Record lock, heap no 497 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  0: len 1; hex 02; asc  ;;  1: len 1; hex 01; asc  ;;  2: len 8; hex 00000000011c1f0f; asc         ;;   ------------------ ---TRANSACTION 43DF0216B, ACTIVE 65 sec fetching rows, thread declared inside InnoDB 155 mysql tables in use 1, locked 1 794767 lock struct(s), heap size 71694776, 13900037 row lock(s) MySQL thread id 118812, OS thread handle 0x7f51951dc700, query id 18995134 10.0.0.1 mysql_user Searching rows for update UPDATE tbl_A SET status=N WHERE (status=N or status=N) and user_id=N limit N

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

解决

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