MySQL 避免行锁升级为表锁——使用高效的索引

爱被打了一巴掌 2022-04-04 13:24 292阅读 0赞

*  转载：[https://www.aliyun.com/jiaocheng/1115211.html][https_www.aliyun.com_jiaocheng_1115211.html]
 *  摘要：众所周知,MySQL的InnoDB存储引擎支持事务,默认是行锁。得益于这些特性,数据库支持高并发。如果InnoDB更新数据使用的不是行锁,而是表锁呢?是的,InnoDB其实很容易就升级为表锁,届时并发性将大打折扣了。经过我操作验证,得出行锁升级为表锁的原因之一是:SQL语句中未使用到索引,或者说使用的索引未被数据库认可(相当于没有使用索引)。我相信,MySQLInnoDB存储引擎引发表锁的原因肯定不止一个因素,针对其解决方法也不是只有一种。【掘金】上另一位作者【Blink-前 
 *  众所周知,MySQL 的 InnoDB 存储引擎支持事务,默认是行锁。得益于这些特性,数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁,而是表锁呢?是的,InnoDB 其实很容易就升级为表锁,届时并发性将大打折扣了。
    
     
    
    经过我操作验证,得出行锁升级为表锁的原因之一是: SQL 语句中未使用到索引,或者说使用的索引未被数据库认可(相当于没有使用索引)。
    
     
    
    我相信,MySQL InnoDB 存储引擎引发表锁的原因肯定不止一个因素,针对其解决方法也不是只有一种。
    
     
    
      
    【掘金】上另一位作者【Blink-前端】,提出行锁升级为表锁与事务的隔离级别  
    有关,并给出了事例。当然,我同意这个说法,因为事务的隔离性是靠加锁来实现的,而加锁势必会影响并发。本篇只针对索引影响并发  
    作出说明,并特别希望有朋友能提出质疑并给出独特见解,万分感谢。
    
      
    普通索引 
    
    既然谈及索引是影响并发的决定因素之一,那我们就来了解一下索引这位主角。
    
     
    
      
    常用  
    的索引有三类:主键、唯一索引、普通索引。主键  
    不由分说,自带最高效的索引属性;唯一索引  
    指的是该属性值重复率为0,一般可作为业务主键,例如学号;普通索引  
    与前者不同的是,属性值的重复率大于0,不能作为唯一指定条件,例如学生姓名。接下来我要说明是 “普通索引对并发的影响”。
    
     
    
    为什么我会想到 “普通索引对并发有影响”?这源自【掘金】微信群抛出的一个问题:
    
     
    
    mysql 5.6 在 update 和 delete 的时候,where 条件如果不存在索引字段,那么这个事务是否会导致表锁?
    
     
    
    有人回答:
    
     
    
    只有主键和唯一索引才是行锁,普通索引是表锁。
    
     
    
    我针对 “普通索引是表锁” 进行了验证,结果发现普通索引并不一定会引发表锁,在普通索引中,是否引发表锁取决于普通索引的高效程度。
    
     
    
    上文提及的“高效”是相对主键和唯一索引而言,也许“高效”并不是一个很好的解释,明白在一般i情况下,“普通索引”效率低于其他两者即可。
    
      
    属性值重复率高 
    
    为了突出效果,我将“普通索引”建立在一个“值重复率”高的属性下。以相对极端的方式,扩大对结果的影响。
    
     
    
    我会创建一张“分数等级表”,属性有“id”、“score(分数)”、“level(等级)”,模拟一个半自动的业务——“分数”已被自动导入,而“等级”需要手工更新。
    
     
    
    操作步骤如下:
    
      
    取消 MySQL 的事务自动提交   
    建表,id自增,并给“score(分数)”创建普通索引   
    插入分数值,等级为 null   
    开启两个事务 session\_1、session\_2,两个事务以“score”为条件指定不同值,锁定数据   
    session\_1 和 session\_2 先后更新各自事务锁定内容的“level”   
    观察数据库对两个事务的响应 
    
      
    取消事务自动提交  
    :
    
      
    mysql> set autocommit = off;   
    Query OK, 0 rows affected (0.02 sec)   
    mysql> show variables like "autocommit";   
    \+--------------------------+-------+   
    | Variable\_name| Value |   
    \+--------------------------+-------+   
    | autocommit | OFF |   
    \+--------------------------+-------+   
    1 rows in set (0.01 sec) 
    
    建表、创建索引、插入数据:
    
      
    DROP TABLE IF EXISTS \`test1\`;   
    CREATE TABLE \`test1\` (   
    \`ID\`int(5) NOT NULL AUTO\_INCREMENT ,   
    \`SCORE\`int(3) NOT NULL ,   
    \`LEVEL\`int(2) NULL DEFAULT NULL ,   
    PRIMARY KEY (\`ID\`)   
    )ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8\_general\_ci;   
    ALTER TABLE \`test2\` ADD INDEX index\_name ( \`SCORE\` );   
    INSERT INTO \`test1\`(\`SCORE\`) VALUE (100);   
    ……   
    INSERT INTO \`test1\`(\`SCORE\`) VALUE (0);   
    …… 
    
    "SCORE" 属性的“值重复率”奇高,达到了 50%,剑走偏锋:
    
      
    mysql> select \* from test1;   
    \+----+-------+-------+   
    | ID | SCORE | LEVEL |   
    \+----+-------+-------+   
    |1 | 100 | NULL|   
    |2 | 0 | NULL|   
    |5 | 100 | NULL|   
    |6 | 100 | NULL|   
    |7 | 100 | NULL|   
    |8 | 100 | NULL|   
    |9 | 100 | NULL|   
    | 10 | 100 | NULL|   
    | 11 | 100 | NULL|   
    | 12 | 100 | NULL|   
    | 13 | 100 | NULL|   
    | 14 | 0 | NULL|   
    | 15 | 0 | NULL|   
    | 16 | 0 | NULL|   
    | 17 | 0 | NULL|   
    | 18 | 0 | NULL|   
    | 19 | 0 | NULL|   
    | 20 | 0 | NULL|   
    | 21 | 0 | NULL|   
    | 22 | 0 | NULL|   
    | 23 | 0 | NULL|   
    | 24 | 100 | NULL|   
    | 25 | 0 | NULL|   
    | 26 | 100 | NULL|   
    | 27 | 0 | NULL|   
    \+----+-------+-------+   
    25 rows in set 
    
      
    开启两个事务(一个窗口对应一个事务  
    ),并选定数据:
    
      
    \-- SESSION\_1,选定 SCORE = 100 的数据   
    mysql> BEGIN;   
    SELECT t.\* FROM \`test1\` t WHERE t.\`SCORE\` = 100 FOR UPDATE;   
    Query OK, 0 rows affected   
    \+----+-------+-------+   
    | ID | SCORE | LEVEL |   
    \+----+-------+-------+   
    |1 | 100 | NULL|   
    |5 | 100 | NULL|   
    |6 | 100 | NULL|   
    |7 | 100 | NULL|   
    |8 | 100 | NULL|   
    |9 | 100 | NULL|   
    | 10 | 100 | NULL|   
    | 11 | 100 | NULL|   
    | 12 | 100 | NULL|   
    | 13 | 100 | NULL|   
    | 24 | 100 | NULL|   
    | 26 | 100 | NULL|   
    \+----+-------+-------+   
    12 rows in set 
    
    再打开一个窗口:
    
      
    \-- SESSION\_2,选定 SCORE = 0 的数据   
    mysql> BEGIN;   
    SELECT t.\* FROM \`test1\` t WHERE t.\`SCORE\` = 0 FOR UPDATE;   
    Query OK, 0 rows affected   
    \+----+-------+-------+   
    | ID | SCORE | LEVEL |   
    \+----+-------+-------+   
    |2 | 0 | NULL|   
    | 14 | 0 | NULL|   
    | 15 | 0 | NULL|   
    | 16 | 0 | NULL|   
    | 17 | 0 | NULL|   
    | 18 | 0 | NULL|   
    | 19 | 0 | NULL|   
    | 20 | 0 | NULL|   
    | 21 | 0 | NULL|   
    | 22 | 0 | NULL|   
    | 23 | 0 | NULL|   
    | 25 | 0 | NULL|   
    | 27 | 0 | NULL|   
    \+----+-------+-------+   
    13 rows in set 
    
    session\_1 窗口,更新“LEVEL”失败:
    
      
    mysql> UPDATE \`test1\` SET \`LEVEL\` = 1 WHERE \`SCORE\` = 100;   
    1205 - Lock wait timeout exceeded; try restarting transaction 
    
    在之前的操作中,session\_1 选择了 \`SCORE\` = 100 的数据,session\_2 选择了 \`SCORE\` = 0 的数据,看似两个事务井水不犯河水,但是在 session\_1 事务中更新自己锁定的数据失败,只能说明在此时引发了表锁。别着急,刚刚走向了一个极端——索引属性值重复性奇高,接下来走向另一个极端。
    
      
    属性值重复率低 
    
    还是同一张表,将数据删除只剩下两条,“SCORE” 的 “值重复率” 为 0:
    
      
    mysql> delete from test1 where id > 2;   
    Query OK, 23 rows affected   
    mysql> select \* from test1;   
    \+----+-------+-------+   
    | ID | SCORE | LEVEL |   
    \+----+-------+-------+   
    |1 | 100 | NULL|   
    |2 | 0 | NULL|   
    \+----+-------+-------+   
    2 rows in set 
    
    关闭两个事务操作窗口,重新开启 session\_1 和 session\_2,并选择各自需要的数据:
    
      
    \-- SESSION\_1,选定 SCORE = 100 的数据   
    mysql> BEGIN;   
    SELECT t.\* FROM \`test1\` t WHERE t.\`SCORE\` = 100 FOR UPDATE;   
    Query OK, 0 rows affected   
    \+----+-------+-------+   
    | ID | SCORE | LEVEL |   
    \+----+-------+-------+   
    |1 | 100 | NULL|   
    \+----+-------+-------+   
    1 row in set   
    \-- -----------------新窗口----------------- --   
    \-- SESSION\_2,选定 SCORE = 0 的数据   
    mysql> BEGIN;   
    SELECT t.\* FROM \`test1\` t WHERE t.\`SCORE\` = 0 FOR UPDATE;   
    Query OK, 0 rows affected   
    \+----+-------+-------+   
    | ID | SCORE | LEVEL |   
    \+----+-------+-------+   
    |2 | 0 | NULL|   
    \+----+-------+-------+   
    1 row in set 
    
    session\_1 更新数据成功:
    
      
    mysql> UPDATE \`test1\` SET \`LEVEL\` = 1 WHERE \`SCORE\` = 100;   
    Query OK, 1 row affected   
    Rows matched: 1Changed: 1Warnings: 0 
    
      
    相同的表结构,相同的操作,两个不同的结果让人出乎意料。第一个结果让人觉得“普通索引”引发表锁,第二个结果推翻了前者,两个操作中,唯一不同的是索引属性的“值重复率”。根据单一变量  
    证明法,可以得出结论:当“值重复率”低时,甚至接近主键或者唯一索引的效果,“普通索引”依然是行锁;当“值重复率”高时,MySQL 不会把这个“普通索引”当做索引,即造成了一个没有索引的 SQL,此时引发表锁  
    。
    
      
    小结 
    
    索引不是越多越好,索引存在一个和这个表相关的文件里,占用硬盘空间,宁缺勿滥,每个表都有主键(id),操作能使用主键尽量使用主键。
    
     
    
    同 JVM 自动优化 java 代码一样,MySQL 也具有自动优化 SQL 的功能。低效的索引将被忽略,这也就倒逼开发者使用正确且高效的索引。

[https_www.aliyun.com_jiaocheng_1115211.html]: https://www.aliyun.com/jiaocheng/1115211.html