MySQL 優化六（InnoDB 下 update 資料出現表鎖之優化）

huxiaobai_001發表於2020-04-14
MySql優化

1.資料表結構：

CREATE TABLE `jx_attach` (
  `attach_id` int(11) NOT NULL AUTO_INCREMENT,
  `feed_id` int(11) DEFAULT NULL ,
  `attach_name` varchar(255) NOT NULL,
  `cycore_file_id` varchar(255) DEFAULT NULL , 
  `attach_size` bigint(20) NOT NULL DEFAULT '0',
  `complete` smallint(6) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`attach_id`),
  KEY `jx_trend_attach_FK` (`feed_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=394160 DEFAULT CHARSET=utf8;

2.現象
當多個連線同時對一個表的資料進行更新操作，那麼速度將會越來越慢，持續一段時間後將出現資料表被鎖，從而影響到其它的查詢及更新
儲存過程迴圈30次更新操作

/*30次更新操作*/
BEGIN
  DECLARE v1 INT DEFAULT 30;
  WHILE v1 > 0 DO
    update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
    SET v1 = v1 - 1;
  END WHILE;
END

執行結果（速度非常慢）
時間: 29.876s
Procedure executed successfully
受影響的行: 0
200個資料更新操作，三個資料庫連線同時執行

update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
...等等

執行結果（持續一段時間後速度越來越慢，出現等待鎖）
mysql優化六（InnoDB下update資料出現表鎖之優化）

3.原因分析
MySQL的innodb儲存引擎支援行級鎖，innodb的行鎖是通過給索引項加鎖實現的，這就意味著只有通過索引條件檢索資料時，innodb才使用行鎖，否則使用表鎖。根據當前的資料更新語句（update jx_attach set complete=1,attach_size=63100 where cycore_file_id=’56677142da502cd8907eb58f’;），該條件欄位cycore_file_id並沒有新增索引，所以導致資料表被鎖。

4.解決辦法：
為cycore_file_id新增索引

5.最終效果（30次更新操作）
時間: 0.094s
Procedure executed successfully
受影響的行: 0

本作品採用《CC 協議》，轉載必須註明作者和本文連結

胡軍

相關文章