本篇内容介绍了“MySQL优化indexmerge引起的死锁怎么解决”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!生产环境出现死锁流水,通过查看死锁日志,看到造成死锁的是两条一样的update语句(只有where条件中的值不同),如下:一开始比较费解,通过大量查询跟学习后,分析出了死锁形成的具体原理,特分享给大家,希望能帮助到遇到同样问题的朋友。因为MySQL
知识点较多,这里对很多名词不进行过多介绍,有兴趣的朋友,可以后续进行专项深入学习。简要分析下上边的死锁日志:1、第一块内容(第1行到第9行)中,第6行为事务(1)执行的SQL语句,第7和第8行意思为事务(1)在等待 idx_status 索引上的X锁;2、第二块内容(第11行到第19行)中,第16行为事务(2)执行的SQL语句,第17和第18行意思为事务(2)持有 idx_status 索引上的X锁;3、第三块内容(第21行到第23行)的意思为,事务(2)在等待 PRIMARY 索引上的X锁。(but not gap指不是间隙锁)4、最后一句的意思即为,MySQL将事务(1)进行了回滚操作。通过表结构可以看出,trans_id
列上有一个唯一索引uniq_trans_id
,status
列上有一个普通索引idx_status
,id列为主键索引 PRIMARY
。InnoDB引擎中有两种索引:聚簇索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据。辅助索引:辅助索引叶子节点存储的是主键值,也就是聚簇索引的键值。主键索引 PRIMARY
就是聚簇索引,叶子节点中会保存行数据。uniq_trans_id
索引和idx_status
索引为辅助索引,叶子节点中保存的是主键值,也就是id列值。当我们通过辅助索引查找行数据时,先通过辅助索引找到主键id,再通过主键索引进行二次查找(也叫回表),最终找到行数据。通过看执行计划,可以发现,update语句用到了索引合并,也就是这条语句既用到了uniq_trans_id
索引,又用到了idx_status
索引,Using intersect(uniq_trans_id,idx_status)
的意思是通过两个索引获取交集。MySQ免费云主机域名L5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了index merge
优化技术,对同一个表可以使用多个索引分别进行条件扫描。如执行计划中的语句:MySQL会根据trans_id = ‘38’
这个条件,利用uniq_trans_id
索引找到叶子节点中保存的id值;同时会根据status = 0
这个条件,利用idx_status
索引找到叶子节点中保存的id值;然后将找到的两组id值取交集,最终通过交集后的id回表,也就是通过 PRIMARY 索引找到叶子节点中保存的行数据。这里可能很多人会有疑问了,uniq_trans_id
已经是一个唯一索引了,通过这个索引最终只能找到最多一条数据,那MySQL优化器为啥还要用两个索引取交集,再回表进行查询呢,这样不是多了一次idx_status
索引查找的过程么。我们来分析一下这两种情况执行过程。第一种 只用uniq_trans_id索引 :根据trans_id = ‘38’
查询条件,利用uniq_trans_id
索引找到叶子节点中保存的id值;通过找到的id值,利用PRIMARY索引找到叶子节点中保存的行数据;再通过status = 0
条件对找到的行数据进行过滤。第二种 用到索引合并Using intersect(uniq_trans_id,idx_status)
:根据trans_id = ‘38’
查询条件,利用uniq_trans_id
索引找到叶子节点中保存的id值;根据status = 0
查询条件,利用idx_status
索引找到叶子节点中保存的id值;将1/2中找到的id值取交集,然后利用PRIMARY索引找到叶子节点中保存的行数据上边两种情况,主要区别在于,第一种是先通过一个索引把数据找到后,再用其它查询条件进行过滤;第二种是先通过两个索引查出的id值取交集,如果取交集后还存在id值,则再去回表将数据取出来。当优化器认为第二种情况执行成本比第一种要小时,就会出现索引合并。(生产环境流水表中status = 0
的数据非常少,这也是优化器考虑用第二种情况的原因之一)。为什么用了index_merge
就死锁了上面简要画了一下两个update事务加锁的过程,从图中可以看到,在idx_status
索引和 PRIMARY (聚簇索引) 上都存在重合交叉的部分,这样就为死锁造成了条件。如,当遇到以下时序时,就会出现死锁:事务1等待事务2释放锁,事务2等待事务1释放锁,这样就造成了死锁。MySQL检测到死锁后,会自动回滚代价更低的那个事务,如上边的时序图中,事务1持有的锁比事务2少,则MySQL就将事务1进行了回滚。where 查询条件中,只传trans_id
,将数据查询出来后,在代码层面判断 status 状态是否为0;使用force index(uniq_trans_id)
强制查询语句使用uniq_trans_id
索引;where 查询条件后边直接用 id 字段,通过主键去更新。删除idx_status
索引或者建一个包含这俩列的联合索引;将MySQL优化器的index merge
优化关闭。“MySQL优化indexmerge引起的死锁怎么解决”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注百云主机网站,小编将为大家输出更多高质量的实用文章!
本篇内容主要讲解“vue的key怎么使用”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“vue的key怎么使用”吧!在vue中,key是DOM对象的标识,是给每一个vnode的唯一id,也是diff的一种优化策略;可…
免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。