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

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

文章插图

图片来自 Pexels
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 存储引擎和表级锁
首先，来看第一象限的内容：

文章插图

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 。