一、MySQL 如何预防 Update 上升为表锁
在MySQL中，进行任何数据的修改操作都会进行一定的锁操作，而锁的不同直接导致性能的差异。例如MyISAM引擎，更新时采用表锁，并发性较差。而Innodb引擎支持事务，更新时采用行锁，锁的粒度更细，所以并发性较高。
由于表锁的粒度过大，即使只有部分行被修改，也会阻塞其他事务对整个表的写操作，限制了系统的吞吐量和响应速度。对于在Innodb引擎中，虽然采用了粒度更细的行锁，但也不是所有的数据修改操作都是仅锁住相关的行，有时很可能不注意就导致了表锁。
下面通过一个实验进行深入：
例如有如下表结构：
CREATETABLE`user`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(255)DEFAULTNULL,`age`intDEFAULTNULL,`sex`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_user_name_age`(`name`,`age`),KEY`name`(`name`,`age`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;写入一些测试数据：
INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(1,'小明',18,'男');INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(2,'小红',19,'女');INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(3,'小蓝',16,'女');INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(4,'小王',17,'男');INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(5,'张三',18,'男');INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(6,'李四',19,'男');INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(7,'王五',20,'男');INSERTINTO`test`.`user`(`id`,`name`,`age`,`sex`)VALUES(8,'赵六',21,'男');下面尝试在事务中修改姓名为张三的年龄为20岁，注意这里先不要着急提交事务：
BEGIN;UPDATEuserSETage=20WHEREname='张三';下面查看下前正在发生的数据锁情况：
SELECT*FROMperformance_schema.data_locksWHEREOBJECT_NAME="user"从这里可以看出，锁住了表里所有的数据，已经上升为了表锁，但是上面仅更新了姓名为张三的数据，怎么会锁住那么多数据呢？
这是因为name字段没有索引，要找到姓名为张三的数据，就要进行全表扫描，但是update的时候要保证数据的一致性，所以此时相关的数据就是全部的表数据，因此也就相当于表锁了。
那怎么降低锁的粒度呢，既然是因为name字段没有索引，那给name增加索引，再次进行上面实验呢。
增加索引：
ALTERTABLEuserADDINDEXindex_name(name);再次尝试修改但不提交事务：
BEGIN;UPDATEuserSETage=20WHEREname='张三';查看下前正在发生的数据锁情况：
SELECT*FROMperformance_schema.data_locksWHEREOBJECT_NAME="user"
可以看出此时并没有锁住全部的数据，但是锁住的'张三', 5和'李四', 6是什么呢？
这其实是索引信息，如果修改操作涉及到了非主键索引，MySql会首先锁住非主键索引，再锁定具体数据的主键索引。至于会锁住李四就是MySQL中Next-Key和GAP间隙锁的原因了，当准备更新张三时，以防止在这个范围内插入新的记录，所以将临近的李四也进行上锁。
从上面结果看增加了索引后已经解决了表锁的问题，但还是会锁住多余的内容，下面直接尝试根据主键进行修改：
BEGIN;UPDATEuserSETage=20WHEREid=5;
再次查看下前正在发生的数据锁情况：
SELECT*FROMperformance_schema.data_locksWHEREOBJECT_NAME="user"此时就仅锁住相关的数据了。
二、总结
从上面的实验过程来看，MySQL中的修改操作很有可能导致表锁，因此最好在更新语句中使用主键列或其他索引列进行筛选。另外索引最好设置在不经常变更的字段上比较好，不然容易造成冲突死锁的情况。
)
