MySQL 优化六（InnoDB 下 update 数据出现表锁之优化）

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;Copy

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;
ENDCopy

执行结果（速度非常慢）
时间: 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';
...等等Copy

执行结果（持续一段时间后速度越来越慢，出现等待锁）

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 协议》，转载必须注明作者和本文链接