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

SQL 2020-10-08 2707 字 51 浏览 点赞

起步

同事告诉我 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);

Alt text

会锁全表的 update 语句

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

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

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

Alt text

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

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

Alt text

可以看到,尽管 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;

Alt text

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

指定索引

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

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

Alt text



本文由 Guan 创作,采用 知识共享署名 3.0,可自由转载、引用,但需署名作者且注明文章出处。

2 条评论

  1. tflins
    tflins

    大佬牛批

    1. Guan
      Guan

      标准商业胡吹模式……想去营业,没找到你的留言板

添加新评论