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

本是古典何须时尚 2022-12-12 15:24 234阅读 0赞

# 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);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU5MDUx_size_16_color_FFFFFF_t_70_pic_center]

# 会锁全表的 update 语句 #

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

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">start transaction with consistent snapshot;</td> 
   <td align="left">start transaction with consistent snapshot;</td> 
  </tr> 
  <tr> 
   <td align="left">update student set name=name where gender=1</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">update student set name=name where gender=0 (block)</td> 
  </tr> 
  <tr> 
   <td align="left">commit</td> 
   <td align="left">commit</td> 
  </tr> 
 </tbody> 
</table>

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

![在这里插入图片描述][20201008150445587.png_pic_center]

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

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

![在这里插入图片描述][20201008150250132.png_pic_center]

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

![在这里插入图片描述][20201008150220743.png_pic_center]

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

# 指定索引 #

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

<table> 
 <thead> 
  <tr> 
   <th align="left">sessionA</th> 
   <th align="left">sessionB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">start transaction with consistent snapshot;</td> 
   <td align="left">start transaction with consistent snapshot;</td> 
  </tr> 
  <tr> 
   <td align="left">update student force index(gender_idx) set name=name where gender=1</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">update student force index(gender_idx) set name=name where gender=0</td> 
  </tr> 
  <tr> 
   <td align="left">commit</td> 
   <td align="left">commit</td> 
  </tr> 
 </tbody> 
</table>

explain update student force index(gender_idx) set name=name where gender=1;

![在这里插入图片描述][20201008150200490.png_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU5MDUx_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/3a05efdd03c348ee941241edf6a8c8ed.png
[20201008150445587.png_pic_center]: /images/20221123/ef4cd6e178794b89b0147dc7077c63a3.png
[20201008150250132.png_pic_center]: /images/20221123/dd35bff6da464449aa20a88d4c69266f.png
[20201008150220743.png_pic_center]: /images/20221123/a6e8e5db805b45b59d1e8f5cffa7914b.png
[UPDATE]: https://blog.csdn.net/n88Lpo/article/details/78099094
[20201008150200490.png_pic_center]: /images/20221123/17df6d506019421184ec7256892eda16.png