MySQL InnoDB 未使用索引导致行锁转表锁
MySQL InnoDB是MySQL中使用最广泛的一种存储引擎，支持事务，支持表级锁，另外还实现了行级锁，支持更好的并发写操作。InnoDB行级锁是通过给索引上的索引项加锁来实现的，这就导致只有通过索引条件检索数据，InnoDB才会使用行级锁，否则InnoDB将使用表级锁。下面通过一个例子来验证在未使用索引的情况下，行锁转成了表锁，案例使用的版本为MySQL 5.7.19。
1、表结构：
表tb中只有两个字段，id和name，两个字段上都没有索引，表中也只有5条数据，如下：
# 表结构
CREATE TABLE `tb` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 表中数据
mysql> select * from tb;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+------+------+
5 rows in set (0.00 sec)
2、未使用索引导致行锁转成表锁
启动两个连接，分别为session1和session2，session1更新id为1的记录，session2更新id为2的记录，两个session更新的数据没有交集。执行情况如下：
session1 | session2 | 备注 |
begin; | begin; | |
select * from tb where id=1 for update; | ||
select * from tb where id=2 for update; | session2阻塞 | |
commit; | ||
commit; |
发现session2被session1阻塞，只有当session1提交或者回滚之后，session2才能返回，session1与session2更新的数据并没有交集，显然行锁升级成了表锁。
3、加上索引，再次执行
给表tb加上索引，再执行一次，看看session2还会不会阻塞。
alter table tb add index idx_id(id);
session1 | session2 | 备注 |
begin; | begin; | |
select * from tb where id=1 for update; | ||
select * from tb where id=2 for update; | session2执行正常，不再阻塞。 | |
commit; | ||
commit; |
session2执行不再被阻塞，显然在id字段加了索引之后，由于session1和session2更新的数据没有交集，行级锁能够并行更新数据，session2不再阻塞。
MySQL InnoDB 的这一特性在实际开发中应当特别注意，避免由于未使用索引，行级锁升级为表级锁，导致并发度降低，SQL执行阻塞，影响正常业务。
文章评论