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

众所周知,MySQL 的 InnoDB 存储引擎支持事务,支持行级锁(innodb的行锁是通过给索引项加锁实现的)。得益于这些特性,数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁,而是表锁呢?是的,InnoDB 其实很容易就升级为表锁,届时并发性将大打折扣了。

经过我操作验证,得出行锁升级为表锁的原因之一是: SQL 语句中未使用到索引,或者说使用的索引未被数据库认可(相当于没有使用索引)。

我相信,MySQL InnoDB 存储引擎引发表锁的原因肯定不止一个因素,针对其解决方法也不是只有一种。

据掘金上另一位作者【Blink-前端】,提出行锁升级为表锁与 事务的隔离级别 有关,并给出了事例。当然,我同意这个说法,因为事务的隔离性是靠加锁来实现的,而加锁势必会影响并发。本篇只针对 索引影响并发 作出说明,并特别希望有朋友能提出质疑并给出独特见解,万分感谢。

普通索引

既然谈及索引是影响并发的决定因素之一,那我们就来了解一下索引这位主角。

常用的索引有三类:主键、唯一索引、普通索引。主键 不由分说,自带最高效的索引属性;唯一索引指的是该属性值重复率为0,一般可作为业务主键,例如学号;普通索引 与前者不同的是,属性值的重复率大于0,不能作为唯一指定条件,例如学生姓名。接下来我要说明是 “普通索引对并发的影响”。

为什么我会想到 “普通索引对并发有影响”?这源自【掘金】微信群抛出的一个问题:

mysql 5.6 在 update 和 delete 的时候,where 条件如果不存在索引字段,那么这个事务是否会导致表锁?

有人回答:

只有主键和唯一索引才是行锁,普通索引是表锁。

我针对 “普通索引是表锁” 进行了验证,结果发现普通索引并不一定会引发表锁,在普通索引中,是否引发表锁取决于普通索引的高效程度。

上文提及的“高效”是相对主键和唯一索引而言,也许“高效”并不是一个很好的解释,明白在一般i情况下,“普通索引”效率低于其他两者即可。

属性值重复率高

为了突出效果,我将“普通索引”建立在一个“值重复率”高的属性下。以相对极端的方式,扩大对结果的影响。

我会创建一张“分数等级表”,属性有“id”、“score(分数)”、“level(等级)”,模拟一个半自动的业务——“分数”已被自动导入,而“等级”需要手工更新。

操作步骤如下:

  1. 取消 MySQL 的 事务自动提交
  2. 建表,id自增,并给“score(分数)”创建普通索引
  3. 插入分数值,等级为 null
  4. 开启两个事务 session_1、session_2,两个事务以“score”为条件指定不同值,锁定数据
  5. session_1 和 session_2 先后更新各自事务锁定内容的“level”
  6. 观察数据库对两个事务的响应

取消 事务自动提交

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 affectedmysql> 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 affectedRows matched: 1  Changed: 1  Warnings: 0

相同的表结构,相同的操作,两个不同的结果让人出乎意料。第一个结果让人觉得“普通索引”引发表锁,第二个结果推翻了前者,两个操作中,唯一不同的是索引属性的“值重复率”。根据 单一变量 证明法,可以得出结论:当“值重复率”低时,甚至接近主键或者唯一索引的效果,“普通索引”依然是行锁;当“值重复率”高时,MySQL 不会把这个“普通索引”当做索引,即造成了一个没有索引的 SQL,此时引发表锁

小结

索引不是越多越好,索引存在一个和这个表相关的文件里,占用硬盘空间,宁缺勿滥,每个表都有主键(id),操作能使用主键尽量使用主键。

同 JVM 自动优化 java 代码一样,MySQL 也具有自动优化 SQL 的功能。低效的索引将被忽略,这也就倒逼开发者使用正确且高效的索引。

已标记关键词 清除标记
相关推荐
<p> <b><span style="font-size:14px;"></span><span style="font-size:14px;background-color:#FFE500;">【Java面试宝典】</span></b><br /> <span style="font-size:14px;">1、68讲视频课,500道大厂Java常见面试题+100个Java面试技巧与答题公式+10万字核心知识解析+授课老师1对1面试指导+无限次回放</span><br /> <span style="font-size:14px;">2、这门课程基于胡书敏老师8年Java面试经验,调研近百家互联网公司及面试官的问题打造而成,从筛选简历和面试官角度,给出能帮助候选人能面试成功的面试技巧。</span><br /> <span style="font-size:14px;">3、通过学习这门课程,你能系统掌握Java核心、数据库、Java框架、分布式组件、Java简历准备、面试实战技巧等面试必考知识点。</span><br /> <span style="font-size:14px;">4、知识点+项目经验案例,每一个都能做为面试的作品展现。</span><br /> <span style="font-size:14px;">5、本课程已经在线下的培训课程中经过实际检验,老师每次培训结束后,都能帮助同学们运用面试技巧,成功找到更好的工作。</span><br /> <br /> <span style="font-size:14px;background-color:#FFE500;"><b>【超人气讲师】</b></span><br /> <span style="font-size:14px;">胡书敏 | 10年大厂工作经验,8年Java面试官经验,5年线下Java职业培训经验,5年架构师经验</span><br /> <br /> <span style="font-size:14px;background-color:#FFE500;"><b>【报名须知】</b></span><br /> <span style="font-size:14px;">上课模式是什么?</span><br /> <span style="font-size:14px;">课程采取录播模式,课程永久有效,可无限次观看</span><br /> <span style="font-size:14px;">课件、课程案例代码完全开放给你,你可以根据所学知识,自修改、优化</span><br /> <br /> <br /> <span style="font-size:14px;background-color:#FFE500;"><strong>如何开始学习?</strong></span><br /> <span style="font-size:14px;">PC端:报名成功后可以直接进入课程学习</span><br /> <span style="font-size:14px;">移动端:<span style="font-family:Helvetica;font-size:14px;background-color:#FFFFFF;">CSDN 学院APP(注意不是CSDN APP哦)</span></span> </p>
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页