現象：

程序大量SQL運行超時;

數據庫中大量SQL長時間處于Updating階段；

初步檢查：

查看系統狀態，CPU有24個核，只有一個核user利用率100%，其余核的idle基本上100%；

內存、IO.util都正常；

查看processlist，所有UPDATE語句長時間處于Updating階段，其它類型的SQL沒有Block或者運行緩慢現象；

進一步排查：

分析運行緩慢的SQL，發現運行緩慢的Update語句操作的都是同一張表；

執行緩慢的Update語句總共有兩類：一類是通過主鍵更新一行數據；另一類是根據狀態字段批量更新數據；Schema和SQL示例如下：

123456789101112131415161718

-- SchemaCREATE 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)后，問題解決。

總結

以上是生活随笔為你收集整理的数据库优化案例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。