请在update语句中加上limit

MySQL版本 mysql-8.0.19-winx64

1. mysql使用update语句的时候尽量加上limit，误操作风险低
2. 当更新语句中的where字段没有索引的时候，如果没有使用limit的话会锁全表；而如果使用了limit的话，mysql会通过主键索引找到对应的行记录，此时只会给索引搜寻过的行加上行锁，并不会把后面的行给锁住；

CREATE TABLE `person` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `realname` varchar(100) DEFAULT '' COMMENT '名字',
  `age` int  NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  INDEX `idx_age`(age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO person ( realname, age ) 
VALUES ( "小明", 18 )
,( "小明2", 27 )
,( "小明222", 27 )
,( "小明3", 28 )
,( "小明4",  29);

A语句: update person set `realname` = "小红" where age = 27;

B语句: update person set `realname` = "小黄" where age = 27 limit 1;

上述两条sql执行，分为两种情况

age字段上有索引 （A、B两条语句的效果是一样的）

1. 此时mysql会利用索引定位到age=27的行记录，然后给他们上行锁；
2. 即使是B语句中加上了limit 1 也是会把两条age=27的记录加上行锁；
3. 并不会对其他age!=27的行更新造成阻塞，并没有给其他行加锁，效率是最高的;

age字段上没有索引 （A、B两条语句的效果也是一样的）

去掉索引alter table person drop index `idx_age`;

age字段上没有索引的测试截图如下：

A语句: update person set `realname` = "小红" where age = 27;

B语句: update person set `realname` = "小黄" where age = 27 limit 1;

1. A语句：因为此时age字段没有索引并且没有加上limit 1，所以是通过主键索引全表扫描，发现此时整个表是锁住的，其他事务对其他行的更新语句会阻塞掉；
2. B语句：即使加上了limit 1。所有更新语句也会被阻塞；这是为什么呢？不是说不会锁全表吗？
3. 不过insert插入语句是能够正常执行的，但是新插入的行记录做更新update操作也还是会阻塞掉（感觉是锁了全表）

答案

1. 事务A中，更新语句执行的时候，由于age字段没有索引，那么只能通过主键索引逐一遍历，那么遍历过的行都会加上行锁（锁在了主键索引上）。只有当找到了符合age=27的那一行记录才会停下来，不再继续遍历，后面的行是不会锁住的。
2. 那事务B中的更新语句为什么会阻塞呢？是因为事务B中的语句where查询字段也是age，那么也是无法利用索引的，也就是只能通过主键索引去逐一遍历，那由于主键索引中前面行的记录已经被事务A的更新语句给加上了行锁，那事务B语句更新遍历主键索引的时候自然就阻塞掉了（锁冲突）。
3. 如果事务B的语句where字段用的是主键字段或者其他有索引项的字段，那么该where条件下指向的行记录不处于事务A更新语句遍历主键索引扫描的行记录中，那么是不会阻塞的，是可以更新成功的。

疑惑

1. 根据上述的测试，对mysql执行一条更新语句的加锁过程有点迷糊。比如 update person set realname = “小黄” where age = 27 limit 1; （age没有索引）
2. 为什么从主键索引逐一扫描的时候，要不停的加锁，直到找到age=27这一行（之后的记录不加锁）
3. 为什么不在遍历的时候先判断一下条件是否满足，即age是否等于27。如果条件满足才加锁，不满足则不加锁岂不是效率会更高吗？