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

最新推荐文章于 2024-08-21 16:13:14 发布
最新推荐文章于 2024-08-21 16:13:14 发布
阅读量643 收藏
点赞数
分类专栏： mysql
  • 转载：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 的功能。低效的索引将被忽略,这也就倒逼开发者使用正确且高效的索引。

啊困兽犹斗
关注
专栏目录
【MySQL】MySQL 数据库表锁和行锁
九师兄
02-20 1309
文章目录1.概述1. 全局锁1.1. 全局锁的特点1.2. 全局锁的作用（全库逻辑备份）2.表级锁2.1. 表锁2.1.1. 特点2.2. MDL元数据锁（metadata lock）2.2.1. 特点2.3. MDL锁的坑————给一个小表加字段2.3.1. 问题描述2.3.2. 解决方法2.3.3. online ddl（ddl：更改表结构）3. 行锁3.1. 行锁特性3.2. 两阶段锁协议4. 死锁4.2. 死锁检测（⭐）4.3. 典型案例（CPU利用率高，但效率低的场景） 1.概述 转载：细谈数据
MySQL高级知识（十七）——行锁
dh12313012的博客
06-02 487
MySQL高级知识（十七）——行锁1. 特点2. 事务2.1. 事务主要是处理并发处理带来的问题。2.2. 事务隔离级别3. 案例分析3.1. 准备3.2. 行锁定基本演示3.3. 无索引行锁升级为表锁 此博客的内容主要来源于尚硅谷的视频中，在此记录，以备以后自己查看。 1. 特点 行锁特点： 偏向InnoDB存储引擎，开销大，加锁慢； 会出现死锁； 锁定粒度最小，发生锁冲突的概率...
mysql 行锁转换为表锁_[转]MySQL 表锁和行锁机制
weixin_36428079的博客
01-27 1344
MySQL 表锁和行锁机制行锁变表锁，是福还是坑？如果你不清楚MySQL加锁的原理，你会被它整的很惨！不知坑在何方？没事，我来给你们标记几个坑。遇到了可别乱踩。通过本章内容，带你学习MySQL的行锁，表锁，两种锁的优缺点，行锁变表锁的原因，以及开发中需要注意的事项。还在等啥？经验等你来拿！MySQL的存储引擎是从MyISAM到InnoDB，锁从表锁到行锁。后者的出现从某种程度上是弥补前者的不足。比...
mysql 行锁升级为表锁
小洪帽i的博客
07-20 4284
行锁升级为表锁 总所周知，MySQL 的 InnoDB 存储引擎支持事务，支持行级锁（InnoDB 的行锁是通过给索引项加锁实现的）。得益于这些特性，数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁，而是表锁呢？是的，InnoDB 其实很容易就升级为表锁，届时并发性将大打折扣了。 常用的索引有三类：主键、唯一索引、普通索引。主键不由 ...
深入探讨MySQL的锁机制：全局锁、表级锁和行级锁
最新发布
heromps的博客
08-21 814
MySQL提供了多种锁机制，以满足不同的并发控制需求。全局锁适用于需要全库一致性的操作，如备份和迁移；表级锁适合读密集型或写操作不频繁的场景；行级锁则提供了更好的并发性能，适用于高并发的读写操作。在实际应用中，选择合适的锁策略可以有效提升系统的性能和稳定性。通过本文的介绍和示例代码，希望能帮助你更好地理解和使用MySQL中的锁机制。如果你有任何问题或需要进一步的帮助，欢迎留言讨论！
mysql行锁升级表锁
weixin_64027360的博客
09-16 2104
需要注意，表锁通常会降低并发性，因为它会限制多个事务同时访问同一表的能力。因此，在设计数据库和应用程序时，应根据实际需求仔细选择锁的级别，并尽量避免不必要的锁升级。但在某些情况下，表级锁可能是必要的，例如执行表级操作或确保操作的原子性。如果锁等待时间过长，会导致性能下降。如果事务需要锁定多个行，MySQL可能会选择升级为表锁，以减少锁管理开销。：某些MySQL存储引擎，例如MyISAM，只支持表级锁，因此无论如何都会使用表锁。：有时，开发人员可能会显式请求表锁，以确保一组操作的原子性，而不是逐行锁定数据。
索引失效行锁变成表锁
实践求真知
09-25 396
一实战 # 第一个客户端，对 a = 4 执行更新操作，操作成功 mysql> update test_innodb_lock set b='4000' where a = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 第一个客户端，对 a = 9 执行更新操作，操作成功 mysql> update test_innodb_lock set b='9000' where .
mysql——行锁、表锁、页锁、间隙锁、Next-Key Locks
m0_38057941的博客
08-26 691
黄壮壮
java mysql 行锁_MySQL高级知识（十四）——行锁
weixin_42505477的博客
01-19 370
前言：前面学习了表锁的相关知识，本篇主要介绍行锁的相关知识。行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，但并发度高。0.准备#1.创建相关测试表tb_innodb_lock，注意数据库引擎为InnoDB。drop table if exists test_innodb_lock;CREATE TABLE test_innodb_lock (a INT ...
Mysql数据库的锁介绍——行锁与表锁
qq_42487559的博客
04-15 414
文章目录Mysql锁锁分类mysql中各种引擎对锁的支持情况MyISAM表锁加锁加读锁实例加写锁实例结论InnoDB行锁行锁实例 Mysql锁 锁分类 从对数据操作的粒度分 ： 1） 表锁：操作时，会锁定整个表。 2） 行锁：操作时，会锁定当前操作行。 从对数据操作的类型分： 1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。 2） 写锁（排它锁）：当前操作没有完...
MySQL实战45讲学习笔记：行锁功过-怎么减少行锁对性能的影响？（第7讲）
weixin_30235225的博客
09-30 414
一、上节回顾今日计划 在上一篇文章中，我跟你介绍了 MySQL 的全局锁和表级锁，今天我们就来讲讲 MySQL的行锁。 MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这...
MySQL高级——MySql行锁升级为表锁案例
小志的博客
03-29 1274
目录一、建表SQL二、MySql无索引行锁升级为表锁基本演示 一、建表SQL 1、创建test_innodb_lock表的SQL CREATE TABLE test_innodb_lock ( a INT(11), b VARCHAR(16) )ENGINE=INNODB; INSERT INTO test_innodb_lock VALUES(1,'b2'); INSERT INTO test_innodb_lock VALUES(3,'3'); INSERT INTO test_innodb_lock
mysql避免死锁的方法
luoqinglong的专栏
04-08 1229
1、在应用中，如果不同的程序会并发存取多个表，应该尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。由于两个session访问表的顺序不同，发生死锁的机会就非常高，但是如果以相同的顺序来访问，死锁就可以避免；　　 2、在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；　　 3、在事务中，如果要更新记录，应该直接申请...
**Mysql在innoDB引擎中将行锁升级为表锁**
yayalewang的博客
06-21 762
Mysql在innoDB引擎中将行锁升级为表锁 **众所周知，MySQL 的 InnoDB 存储引擎支持事务，支持行级锁（innodb的行锁是通过给索引项加锁实现的）。得益于这些特性，数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁，而是表锁呢？是的，InnoDB 其实很容易就升级为表锁，届时并发性将大打折扣了。** 1.行锁是建立在索引字段的基础上，如果行锁定的列不是索引列则会升级为表锁，我这两个测试的查询条件有重复字段，其实用两个值来测试也是同样的效果。 　2.索引列数据重复过多情况下，会
mysql锁升级？
tiny的专栏
06-08 372
锁升级是指将当前锁的力度降低。举例来说，数据库可以把一个表的1000个行锁升级一个页锁，或者将页锁升级为表锁。如果在数据库中的设计中认为锁是一种稀有资源，而且想避免锁的开销，那shujuku ...
MySQL高级 - 锁 - InnoDB行锁 - 行锁升级为表锁
Leon_Jinhai_Sun的博客
12-08 582
无索引行锁升级为表锁 如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。 查看当前表的索引 ： show index from test_innodb_lock ; Session-1 Session-2 关闭事务的自动提交 关闭事务的自动提交 执行更新语句 ： 执行更新语句， 但处于阻塞状态： 提交事务： 解除阻塞，执行更新成功 ： 执行提交操作 ： 由于 执行更新时 ， name字段本来为va..
mysql间隙锁于行锁升级为表锁
xiaonanhaijing的博客
03-06 1080
间隙锁:当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记 录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加 锁，这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。 危害: 因为Query 执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个 键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在 的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定
MySQL innoDB 中的锁升级
Data & Analysis
03-28 878
什么是锁升级？ 锁升级是指将当前锁的粒度降低，如一把行锁升级唯一把页锁，或者将页锁升级为表锁，如果在数据库设计中认为锁是一中稀有资源，哪么就会频繁有锁升级的现象 发生锁升级的现象 当一条SQL语句对一个对象上持有的锁数量超锁了阈值，默认这个阈值为5000，但是对于不同对象不会发生锁升级 锁资源占用的内存超过激活内存的百分之40 就会发生锁升级 但是！！！！！ innoDB 引擎不存在锁升级的问题，因为其不是根据每个记录来产生啊行锁的，是根据每个事务访问的每个页对锁进行管理的。 其实吧，这个根据页.
mysql 怎么使用行锁怎么使用表锁
01-14
MySQL提供了行锁和表锁来实现并发控制。下面是使用行锁和表锁的示例： 1. 使用行锁： ```sql -- 开启事务 START TRANSACTION; -- 设置行锁 SELECT * FROM table_name WHERE id = 1 FOR UPDATE; -- 执行其他操作 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值