update 走索引后会不会锁全表？

020.10.6

文章目录

update 走索引后会不会锁全表？起步环境准备会锁全表的 update 语句指定索引

起步

同事告诉我 update 时，如果没走索引，会锁全表；如果走了索引，只锁住符合条件的行记录。

所以果真如此吗？好像不是欸！

环境准备

在进入正文之前，需要做一些准备。

create table student( id int not null primary key auto_increment, name varchar(10) not null, gender tinyint not null ) engine=InnoDB;

为 gender 创建普通索引：

create index gender_idx on student(gender);

假设 gender=0 时，为女生；gender=1 时，为男生。现准备三男三女，插入数据：

insert into student( name, gender ) values ('小庭', 0), ('小英', 0), ('小慧', 0), ('小钟', 1), ('小丁', 1), ('小易', 1);

会锁全表的 update 语句

执行步骤如下。sessionA 和 session B 代表两个事务，从上到下是操作顺序。

sessionAsessionBstart transaction with consistent snapshot;start transaction with consistent snapshot;update student set name=name where gender=1update student set name=name where gender=0 (block)commitcommit

操作时会发现，sessionB 中执行 update 会被阻塞住，超过锁等待时间就会报错。如下图。

借助 explain 查看 sql 的执行计划：

explain update student set name=name where gender=1;

可以看到，尽管 gender 上有索引，但是 mysql 优化器选择了主键索引。在主键索引树上为了找到符合 gender=1 的数据，就得一个一个的遍历，所以该语句锁住了全表，导致 sessionB 的 update 语句被阻塞住。只有当 sessionA commit 之后，sessionB 才能执行下去。

那么为什么 MySQL 优化器放着可以扫描更少行数的 gender 索引不走，要去全表扫描呢？我想，主要是优化器做选择时不但要考虑扫描行数，还要考虑走二级索引的回表时间。当查询的目标集合在总集合中的占比较大时，优化器会觉得回表更浪费时间，不如走主键索引快。

目前男女各 3 人，占比为 50%。继续增加女生数量：

insert into student( name, gender ) values ('小小庭', 0), ('小小英', 0), ('小小慧', 0);

此时 6 女 3 男，男生占总数的 33.33%。再对之前的 sql 分析：

explain update student set name=name where gender=1;

现在走 gender 索引了。看到一些网友分析，给出这样一个结论：当 MySQL 预估扫描行数超过全表总数约 20% ～ 30% 时，即便有二级索引，也会直接升级为全表扫描（《UPDATE能走索引还会锁全表吗》）。

指定索引

面对锁全表的更新操作，高并发下的热表很容易给出锁等待超时异常。我们可以利用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

sessionAsessionBstart transaction with consistent snapshot;start transaction with consistent snapshot;update student force index(gender_idx) set name=name where gender=1update student force index(gender_idx) set name=name where gender=0commitcommit explain update student force index(gender_idx) set name=name where gender=1;