update中where条件把索引的字段一定要带上，否则会全表锁

一、问题描述：

innodb下在对没用含有索引的表执行修改一条操作时，会导致锁全表。
为什么：
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
下面通过测试，来探讨什么情况下才会锁全表。

二、测试走起

测试环境：
数据库版本： 5.7.29-log （ select version() ）
隔离级别：REPEATABLE-READ （ select @@tx_isolation）

测试-单个索引

1、先创建无索引的表并插入数据

CREATE TABLE `stu` (
  `sid` int(11) NOT NULL,
  `sname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `scj` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO `test`.`stu`(`sid`, `sname`, `scj`, `cid`) VALUES (1, '张三', '80', 1);
INSERT INTO `test`.`stu`(`sid`, `sname`, `scj`, `cid`) VALUES (2, '李四', '80', 1);
INSERT INTO `test`.`stu`(`sid`, `sname`, `scj`, `cid`) VALUES (3, '王二', '70', 2);
INSERT INTO `test`.`stu`(`sid`, `sname`, `scj`, `cid`) VALUES (4, '麻子', '60', 3);

2、navicate 模拟测试开启事务

3、在 步骤2事务 未提交之前，测试修改，
这里模拟测试 修改 sname =“张三” =》 sname =“张三1”
发现阻塞中。说明已经出发了表锁
注：
这里我直接在 navicate 里，直接修改数据，实际上就是执行

UPDATE `test`.`stu` SET `sname` = '张三1' WHERE `sid` = 1

4、增加索引 sname 索引再次测试，必须要是唯一索引，否则无效

ALTER TABLE `test`.`stu`  ADD UNIQUE INDEX `scj_1`(`scj`);

测试-复合索引

注：数据库字段是 varchar 则不能 ：如 … where rso=2 应该是 … where rso=‘2’,否则依然会锁全表
1、创建复合索引

CREATE TABLE `app_pay_log_test` (
  `rso` varchar(50) NOT NULL DEFAULT '-1' ,
  `order_id` varchar(100) NOT NULL ,
  `amount` decimal(20,2) NOT NULL ,
  `product` varchar(50) DEFAULT NULL,
  UNIQUE KEY `rso_orderid_idx` (`rso`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `test`.`app_pay_log_test`(`rso`, `order_id`, `amount`, `product`) VALUES ('1', '1', 1.00, '');
INSERT INTO `test`.`app_pay_log_test`(`rso`, `order_id`, `amount`, `product`) VALUES ('2', '2', 2.00, '');

2、测试正常的（两个索引都在，并且依次顺序使用）

-- 最左原则
UPDATE `test`.`app_pay_log_test` SET `product` = '4' WHERE rso='2' and `order_id` = '2';
-- 没有符合最左原则
UPDATE `test`.`app_pay_log_test` SET `product` = '4' WHERE `order_id` = '2' and rso='2';

测试流程同理，先不执行 commit，去修改其他数据，看看是不是会阻塞。
begin；
update xxx ;
commit;

这里测试发现都是正常的。
3、测试最左一个索引时，发现修改其他行数据是正常的，没有发生死锁

UPDATE `test`.`app_pay_log_test` SET `product` = '4' WHERE rso='2'

4、测试非最左一个索引

UPDATE `test`.`app_pay_log_test` SET `product` = '4' WHERE `order_id` = '2'

出现问题：

三、结论

通过测试发现了什么：

• 在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
• 复合索引只要命中一个索引，就不会锁全表。