资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

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

创作时间:

作者:

@小白创作中心

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

引用

CSDN

https://blog.csdn.net/destinm/article/details/105856078

随着IT技术的飞速发展，各种技术层出不穷，让人眼花缭乱。尽管技术在不断更新换代，但是有些技术依旧被一代代IT人使用至今。MySQL就是其中之一，它经历了多个版本迭代。数据库锁是MySQL数据引擎的一部分，今天我们就一起来学习MySQL的数据库锁和它的优化。

MySQL锁分类

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

从锁定资源的角度来看，MySQL中的锁分为：

表级锁：对整张表加锁。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：对某行记录加锁。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

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

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

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

MyISAM存储引擎：它不支持事务、表锁设计，支持全文索引，主要面向一些在线分析处理（OLAP）数据库应用。说白了主要就是查询数据，对数据的插入，更新操作比较少。
InnoDB存储引擎：它支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。简单来说，就是对数据的插入，更新操作比较多。从MySQL数据库5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。

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

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

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

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

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

MyISAM存储引擎和表级锁

首先，来看第一象限的内容：

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存储引擎和表级锁

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

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

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

这里介绍两种意向锁：

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前，必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前，必须先取得该表的IX锁。

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

InnoDB表锁的实现方式：假设有一个表test2，有两个字段分别是id和name。没有设置主键同时也没有设置任何索引（index）如下：

InnoDB存储引擎和行级锁

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

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

共享锁（S）：当一个事务读取一条记录的时候，不会阻塞其他事务对同一记录的读请求，但会阻塞对其的写请求。当读锁释放后，才会执行其他事务的写操作。
排他锁（X）：当一个事务对一条记录进行写操作时，会阻塞其他事务对同一表的读写操作，当该锁释放后，才会执行其他事务的读写操作。

行锁的实现方式：假设有一个表test1，有两个字段分别是id和name。id作为主键同时也是table的索引（index）如下：

在高并发的情况下，多个事务同时请求更新数据，由于资源被占用等待事务增多。如此，会造成性能问题，可以通过innodb_lock_wait_timeout来解决。innodb_lock_wait_timeout是事务等待获取资源的最长时间，单位为秒。如果超过时间还未分配到资源，则会返回应用失败。

四种锁的兼容情况

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

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

间隙锁

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

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

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

Select * from table_gap where id > 10 for update;

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

InnoDB使用间隙锁的目的：

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

间隙锁图

死锁

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

死锁图

检测死锁：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）：一个事务在处理过程中，读取了另外一个事务未提交的数据。未提交的数据称之为脏数据。
不可重复读（non-repeatable read）：在事务范围内，多次查询某条记录，每次得到不同的结果。第一个事务中的两次读取数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能不一样。
幻读（phantom read）：是事务非独立执行时发生的一种现象。在同一时间点，数据库允许多个并发事务，同时对数据进行读写操作，会造成数据不一致性。

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

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

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