您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

大牛总结的MySQL锁优化,写得太好了

时间:2020-02-18 10:21:33  来源:  作者:

【51CTO.com原创稿件】随着 IT 技术的飞速发展,各种技术层出不穷,让人眼花缭乱。尽管技术在不断更新换代,但是有些技术依旧被一代代 IT 人使用至今。

 

大牛总结的MySQL锁优化,写得太好了

 

 

图片来自 Pexels

MySQL 就是其中之一,它经历了多个版本迭代。数据库锁是 MySQL 数据引擎的一部分,今天我们就一起来学习 MySQL 的数据库锁和它的优化。

MySQL 锁分类

当多个事务或者进程访问同一个资源的时候,为了保证数据的一致性,就需要用到锁机制。

从锁定资源的角度来看,MySQL 中的锁分为:

  • 表级锁
  • 行级锁
  • 页面锁

表级锁:对整张表加锁。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

行级锁:对某行记录加锁。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

在实际开发过程中,主要会使用到表级锁和行级锁两种。既然锁是针对资源的,那么这些资源就是数据,在 MySQL 提供插件式存储引擎对数据进行存储。

插件式存储引擎的好处是,开发人员可以根据需要选择适合的存储引擎。

在众多的存储引擎中,有两种引擎被比较多的使用,他们分别是:

  • MyISAM 存储引擎,它不支持事务、表锁设计,支持全文索引,主要面向一些在线分析处理(OLAP)数据库应用。说白了主要就是查询数据,对数据的插入,更新操作比较少。
  • InnoDB 存储引擎,它支持事务,其设计目标主要面向在线事务处理(OLTP)的应用。

其特点是行锁设计、支持外键,并支持类似于 Oracle 的非锁定读,即默认读取操作不会产生锁。

简单来说,就是对数据的插入,更新操作比较多。从 MySQL 数据库 5.5.8 版本开始,InnoDB 存储引擎是默认的存储引擎。

上面两种存储引擎在处理多进程数据操作的时候是如何表现的,就是我们接下来要讨论的问题。

为了让整个描述更加清晰,我们将表级锁和行级锁以及 MyISAM,InnoDB 存储引擎,就形成了一个 2*2 的象限。

 

大牛总结的MySQL锁优化,写得太好了

 

 

2*2 表行锁,MyISAM,InnoDB 示意图

由于 MyISAM 存储引擎不支持行级锁,实际上后面讨论的问题会围绕三个象限的讨论展开。

从内容上来看,InnoDB 作为使用最多的存储引擎遇到的问题和值得注意的地方较多,也是本文的重点。

MyISAM 存储引擎和表级锁

首先,来看第一象限的内容:

 

大牛总结的MySQL锁优化,写得太好了

 

 

2*2 表行锁,MyISAM,InnoDB 示意图-第一象限

MyISAM 存储引擎支持表级锁,并且支持两种锁模式:

  • 对 MyISAM 表的读操作(共享锁),不会阻塞其他进程对同一表的读请求,但会阻塞对其的写请求。当读锁释放后,才会执行其他进程的写操作。
  • 对 MyISAM 表的写操作(排他锁),会阻塞其他进程对同一表的读写操作,当该锁释放后,才会执行其他进程的读写操作。

MyISAM 优化建议

在使用 MyISAM 存储引擎时。执行 SQL 语句,会自动为 SELECT 语句加上共享锁,为 UDI(更新,删除,插入)操作加上排他锁。

由于这个特性在多进程并发插入同一张表的时候,就会因为排他锁而进行等待。

因此可以通过配置 concurrent_insert 系统变量,来控制其并发的插入行为。

①concurrent_insert=0 时,不允许并发插入。

②concurrent_insert=1 时,如果 MyISAM 表中没有空洞(即表中没有被删除的行),允许一个进程读表时,另一个进程向表的尾部插入记录(MySQL 默认设置)。

注:空洞是行记录被删除以后,只是被标记为“已删除”其存储空间没有被回收,也就是说没有被物理删除。由另外一个进程,异步对这个数据进行删除。

因为空间长度问题,删除以后的物理空间不能被新的记录所使用,从而形成了空洞。

③concurrent_insert=2 时,无论 MyISAM 表中有没有空洞,都允许在表尾并发插入记录。

如果在数据插入的时候,没有并发删除操作的话,可以尝试把 concurrent_insert 设置为 1。

反之,在数据插入的时候有删除操作且量较大时,也就是会产生“空洞”的时候,就需要把 concurrent_insert 设置为 2。

另外,当一个进程请求某个 MyISAM 表的读锁,另一个进程也请求同一表的写锁。

即使读请求先到达,写请求后到达,写请求也会插到读请求之前。因为 MySQL 的默认设置认为,写请求比读请求重要。

我们可以通过 low_priority_updates 来调节读写行为的优先级:

  • 数据库以读为主时,要优先保证查询性能时,可通过 low_priority_updates=1 设置读优先级高于写优先级。
  • 数据库以写为主时,则不用设置 low_priority_updates 参数。

InnoDB 存储引擎和表级锁

再来看看第二象限的内容:

 

大牛总结的MySQL锁优化,写得太好了

 

 

2*2 表行锁,MyISAM,InnoDB 示意图-第二象限

InnoDB 存储引擎表锁。当没有对数据表中的索引数据进行查询时,会执行表锁操作。

上面是 InnoDB 实现行锁,同时它也可以实现表锁。其方式就是意向锁(Intention Locks)。

这里介绍两种意向锁:

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前,必须先取得该表的 IS 锁。
  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前,必须先取得该表的 IX 锁。

注:意向共享锁和意向排他锁是数据库主动加的,不需要我们手动处理。对于 UPDATE、DELETE 和 INSERT 语句,InnoDB 会自动给数据集加排他锁。

InnoDB表锁的实现方式:假设有一个表 test2,有两个字段分别是 id 和 name。

没有设置主键同时也没有设置任何索引(index)如下:

 

大牛总结的MySQL锁优化,写得太好了

 

 

InnoDB 表锁实现方式图

InnoDB 存储引擎和行级锁

第四象限我们使用的比较多,讨论的内容也相对多些:

 

大牛总结的MySQL锁优化,写得太好了

 

 

2*2 表行锁,MyISAM,InnoDB 示意图-第四象限

InnoDB 存储引擎行锁,当数据查询时针对索引数据进行时,会使用行级锁。

共享锁(S):当一个事务读取一条记录的时候,不会阻塞其他事务对同一记录的读请求,但会阻塞对其的写请求。当读锁释放后,才会执行其他事务的写操作。

例如:select … lock in share mode

排他锁(X):当一个事务对一条记录进行写操作时,会阻塞其他事务对同一表的读写操作,当该锁释放后,才会执行其他事务的读写操作。

例如:select … for update

行锁的实现方式:假设有一个表 test1,有两个字段分别是 id 和 name。

id 作为主键同时也是 table 的索引(index)如下:

 

大牛总结的MySQL锁优化,写得太好了

 

 

InnoDB 行锁实现方式图

在高并发的情况下,多个事务同时请求更新数据,由于资源被占用等待事务增多。

如此,会造成性能问题,可以通过 innodb_lock_wait_timeout 来解决。innodb_lock_wait_timeout 是事务等待获取资源的最长时间,单位为秒。如果超过时间还未分配到资源,则会返回应用失败。

四种锁的兼容情况:

 

大牛总结的MySQL锁优化,写得太好了

 

 

共享锁,排他锁,意向共享锁,意向排他锁兼容图例

如果一个事务请求的锁模式与当前的锁兼容, InnoDB 就将请求的锁授予该事务;反之, 如果两者不兼容,该事务就要等待锁释放。

间隙锁

前面谈到行锁是针对一条记录进行加锁。当对一个范围内的记录加锁的时候,我们称之为间隙锁。

当使用范围条件索引数据时,InnoDB 会对符合条件的数据索引项加锁。对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB 也会对这个“间隙”加锁,这就是间隙锁。间隙锁和行锁合称(Next-Key锁)。

如果表中只有 11 条记录,其 id 的值分别是 1,2,...,10,11 下面的 SQL:

Select * from table_gapwhere id > 10 for update;

这是一个范围条件的检索,InnoDB 不仅会对符合条件的 id 值为 10 的记录加锁,会对 id 大于 10 的“间隙”加锁,即使大于 10 的记录不存在,例如 12,13。

InnoDB 使用间隙锁的目的:

  • 一方面是为了防止幻读。对于上例,如果不使用间隙锁,其他事务插入了 id 大于 10 的任何记录,本事务再次执行 select 语句,就会发生幻读。
  • 另一方面,也是为了满足恢复和复制的需要。

 

大牛总结的MySQL锁优化,写得太好了

 

 

间隙锁图

死锁

两个事务都需要获得对方持有的排他锁才能继续完成任务,这种互相等待对方释放资源的情况就是死锁。

 

大牛总结的MySQL锁优化,写得太好了

 

 

死锁图

检测死锁:InnoDB 存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复:死锁发生以后,只有部分或完全回滚其中一个事务,才能打破死锁。

InnoDB 方法是,将持有最少行级排他锁的事务回滚。在应用程序设计时必须考虑处理死锁,多数情况下重新执行因死锁回滚的事务即可。

避免死锁:

  • 在事务开始时,如果有记录要修改,先使用 SELECT... FOR UPDATE 语句获取锁,即使这些修改语句是在后面执行。
  • 在事务中,如果要更新记录,直接申请排他锁。而不是查询时申请共享锁、更新时再申请排他锁。

这样做会导致,当申请排他锁时,其他事务可能已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。

简单来说,如果你要更新记录要做两步操作,第一步查询,第二步更新。就不要第一步上共享锁,第二部上排他锁了,直接在第一步就上排他锁,抢占先机。

  • 如果事务需要锁定多个表,那么尽量按照相同的顺序使用加锁语句,可以降低产生死锁的机会。
  • 通过 SELECT ... LOCK INSHARE MODE(共享锁)获取行的读锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。所以,如果要对行记录进行修改,直接上排他锁。
  • 改变事务隔离级别(事务隔离级别在后面详细说明)。

MySQL 锁定情况的查询

在实际开发中无法避免数据被锁的问题,那么我们可以通过哪些手段来查询锁呢?

表级锁可以通过两个变量的查询:

  • Table_locks_immediate,产生表级锁的次数。
  • Table_locks_waited,数显表级锁而等待的次数。

行级锁可以通过下面几个变量查询:

  • Innodb_row_lock_current_waits,当前正在等待锁定的数量。
  • Innodb_row_lock_time(重要),从系统启动到现在锁定总时长。
  • Innodb_row_lock_time_avg(重要),每次等待所花平均时间。
  • Innodb_row_lock_time_max,从系统启动到现在等待最长的一次花费时间。
  • Innodb_row_lock_waits(重要),从系统启动到现在总共等待的次数。

MySQL 事务隔离级别

前面讲的死锁是因为并发访问数据库造成。当多个事务同时访问数据库,做并发操作的时候会发生以下问题。

脏读(dirty read),一个事务在处理过程中,读取了另外一个事务未提交的数据。未提交的数据称之为脏数据。

 

大牛总结的MySQL锁优化,写得太好了

 

 

脏读例子

不可重复读(non-repeatable read),在事务范围内,多次查询某条记录,每次得到不同的结果。

第一个事务中的两次读取数据之间,由于第二个事务的修改,第一个事务两次读到的数据可能不一样。

 

大牛总结的MySQL锁优化,写得太好了

 

 

不可重复读例子

幻读(phantom read),是事务非独立执行时发生的一种现象。

 

大牛总结的MySQL锁优化,写得太好了

 

 

大牛总结的MySQL锁优化,写得太好了

 

 

幻读的例子

在同一时间点,数据库允许多个并发事务,同时对数据进行读写操作,会造成数据不一致性。

 

大牛总结的MySQL锁优化,写得太好了

 

 

四种隔离级别,解决事务并发问题对照图

隔离性就是用来防止这种数据不一致的。事务隔离根据级别不同,从低到高包括:

  • 读未提交(read uncommitted):它是最低的事务隔离级别,一个事务还没提交时,它做的变更就能被别的事务看到。有脏读的可能性。
  • 读提交(read committed):保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。可避免脏读的发生,但是可能会造成不可重复读。
  • 可重复读(repeatable read MySQL 默认方式):多次读取同一范围的数据会返回第一次查询的快照,即使其他事务对该数据做了更新修改。事务在执行期间看到的数据前后必须是一致的。
  • 串行化(serializable):是最可靠的事务隔离级别。“写”会加“排他锁”,“读”会加“共享锁”。

当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,所以事务执行是串行的。可避免脏读、不可重复读、幻读。

InnoDB 优化建议

从锁机制的实现方面来说,InnoDB 的行级锁带来的性能损耗可能比表级锁要高一点,但在并发方面的处理能力远远优于 MyISAM 的表级锁。这也是大多数公司的 MySQL 都是使用 InnoDB 模式的原因。

但是,InnoDB 也有脆弱的一面,下面提出几个优化建议供大家参考:

  • 尽可能让数据检索通过索引完成,避免 InnoDB 因为无法通过索引加行锁,而导致升级为表锁的情况。换句话说就是,多用行锁,少用表锁。
  • 加索引的时候尽量准确,避免造成不必要的锁定影响其他查询。
  • 尽量减少给予范围的数据检索(间隙锁),避免因为间隙锁带来的影响,锁定了不该锁定的记录。
  • 尽量控制事务的大小,减少锁定的资源量和锁定时间。
  • 尽量使用较低级别的事务隔离,减少 MySQL 因为事务隔离带来的成本。

总结

 

大牛总结的MySQL锁优化,写得太好了

 

 

MySQL 数据库锁的思维导图

MySQL 的锁主要分为表级锁和行级锁。MyISAM 引擎使用的是表级锁,针对表级的共享锁和排他锁,可以通过 concurrent_insert 和 low_priority_updates 参数来优化。

InnoDB 支持表锁和行锁,根据索引来判断如何选择。行锁有,行共享锁和行排他锁;表锁有,意向共享锁,意向排他锁,表锁是系统自己加上的;锁范围的是间隙锁。遇到死锁,我们如何检测,恢复以及如何避免。

MySQL 有四个事务级别分别是,读未提交,读提交,可重复读,串行化。他们的隔离级别依次升高。

通过隔离级别的设置,可以避免,脏读,不可重复读和幻读的情况。最后,对于使用比较多的 InnoDB 引擎,提出了一些优化建议。

作者:崔皓

简介:十六年开发和架构经验,曾担任过惠普武汉交付中心技术专家,需求分析师,项目经理,后在创业公司担任技术/产品经理。善于学习,乐于分享。目前专注于技术架构与研发管理。

【51CTO原创稿件,合作站点转载请注明原文作者和出处为51CTO.com】



Tags:MySQL锁   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
查看mysql执行的线程 show full processlist; 查看mysql 事务处理列表 select * from information_schema.INNODB_TRX 杀死事务进程 kill 16490...【详细内容】
2021-04-30  Tags: MySQL锁  点击:(192)  评论:(0)  加入收藏
最近遇到一个由于唯一性索引,导致并发插入产生死锁的场景,在分析死锁产生的原因时,发现这一块还挺有意思的,涉及到MySql中不少的知识点,特此总结记录一下。 一、MySql常见的锁谈...【详细内容】
2021-02-19  Tags: MySQL锁  点击:(168)  评论:(0)  加入收藏
前言今天就为大家介绍一下MySQL中锁相关的知识。本文在没有特别声明的情况下,均是默认InnoDB引擎,如涉及到其他引擎或者数据库则会特别指出。什么是锁锁是一种用于保证在并发...【详细内容】
2020-10-19  Tags: MySQL锁  点击:(122)  评论:(0)  加入收藏
【51CTO.com原创稿件】随着 IT 技术的飞速发展,各种技术层出不穷,让人眼花缭乱。尽管技术在不断更新换代,但是有些技术依旧被一代代 IT 人使用至今。 图片来自 PexelsMySQL...【详细内容】
2020-02-18  Tags: MySQL锁  点击:(74)  评论:(0)  加入收藏
本文章向大家介绍MySQL锁详细讲解,包括数据库锁基本知识、表锁、表读锁、表写锁、行锁、MVCC、事务的隔离级别、悲观锁、乐观锁、间隙锁GAP、死锁等等,需要的朋友可以参考一下...【详细内容】
2019-12-24  Tags: MySQL锁  点击:(67)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
相关文章
最新更新
栏目热门
栏目头条