今日大家一起来学MySQL的存储引擎和锁机制

最新推荐文章于 2022-07-28 21:30:00 发布

葡萄干是个程序员

最新推荐文章于 2022-07-28 21:30:00 发布

阅读量558

点赞数 4

分类专栏： MySQL 文章标签： MySQL 锁 InnoDB MyISAM

本文链接：https://blog.csdn.net/qq_15096707/article/details/88052572

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

前言

时光冉冉，转眼间，距我上回提笔写字发表文章，已经是很久之前的事了，久到甚至我需要翻看记录才回想起上一篇文章的标题，而且仅仅是标题。因为工作上的繁忙这一原因之外，更重要的原因还在于我能力有限的问题，所学的技术或者知识太过浅显，致使我在每每想提笔描述自己所学的知识点的时候却也望而却步。我实在想不出有什么好写的了，毕竟网上有太多太多技术文章供大家参考学习的了。
　　话虽如此，在学习知识点的时候，秉着记录知识点以及分享知识的想法，还是想写一下相关的文章。作为自己的学习过程的总结，以及给其他学习人的参考。所以，今日，提笔写一下关于《MySQL的存储引擎和锁机制》等相关知识点。在此，还要感谢一下某个可爱的小伙伴，可以分享相关的文章，一同学习讨论这些个知识点。文中参考了很多的文章，如有冒犯请海涵。文中描述有不当之处，还望读者友善指出。

MySQL常用的存储引擎

MySQL常用的存储引擎有两种，即MyISAM和InnoDB。MySQL 5.5之前默认的存储引擎为MyISAM，而5.6之后引入的新的存储引擎，即InnoDB，并作为默认的存储引擎。下面将对这两种分别做简单的介绍。（参考：MySQL常见的两种存储引擎：MyISAM与InnoDB的爱恨情仇）

MyISAM

MyISAM的特性：

不支持行级锁（只支持表级锁），读取时对需要读到的所有表加锁，写入时对表加排他锁；
不支持事务；
不支持外键；
不支持崩溃后的安全恢复；
支持全文索引；
支持延迟更新索引，极大提升写入性能；
支持在查询表的同时，往该表插入新的记录；（提问：读者看到这里的时候，可能就会纳闷了，明明第一点还说，读取时会对所有需要查询的表加锁的，为什么还可以插入新的记录到表中呢？这里简单地回答下，实际上可以通过对MySQL进行设置实现的，对于不同的设置的值，实现不同的作用，后续将详细讲解。）
对于不会进行修改的表，支持压缩表，极大地减少了磁盘空间的占用。

InnoDB

InnoDB的特性：

支持行级锁，采用MVCC来支持高并发，有可能死锁；（MVCC，即多版本的并发控制（与MVCC相对的，是基于锁的并发控制），实现读不加锁，读写不冲突）；
支持事务；
支持外键；
支持崩溃后的安全恢复；
在MySQL 5.6.24之后同样也支持全文索引。

温馨提示：这里提及到的行锁、表锁等知识将在后续内容中做更详细的说明。不要慌，问题不大。

MyISAM和InnoDB的对比

通过对MyISAM和InnoDB的特性进行描述后，我们可以知道：

锁机制不同：MyISAM支持表级锁，而InnoDB支持行级锁（当然InnoDB也是支持表级锁的）；
事务处理：MyISAM不支持事务，而InnoDB支持；
外键支持：MyISAM不支持外键，而InnoDB支持；
　　除此之外，还有如下几点说明：（参考：Innodb与Myisam引擎的区别与应用场景）
应用场景：MyISAM更适合读密集的表，而InnoDB更适合写密集的表（InnoDB在执行SELECT操作的时候，要维护的东西比MyISAM多很多）；
查询表的行数：

MyISAM：通过count(*)查询表的行数时，可以从缓存中获取，不需要扫描全表来计算行数，但是如果count(*)查询语句中包含where条件时，同InnoDB查询表行数的操作一样；
InnoDB：不保存表的行数，需要扫描全表来计算行数。

讲一下让人敬畏的锁机制

介绍完MySQL的存储引擎之后，接来的重头戏“锁”这些个让人脑阔疼的知识点来了。但是别怕，我相信读一遍可能还是读不懂的。（调皮）所以，我希望读者也能结合一些更好的文章反复阅读。
　　那么在讲MySQL中的不同存储引擎不同锁的机制前，我们先来学习理解一下基本的概念和知识。

MySQL中的锁

事实上，讲解MySQL中的锁，我们需要针对不同的存储引擎加以分析。因为在MySQL中不同的存储引擎之间的锁机制不一定相同。所以，在后续的内容中，我希望读者能结合不同的存储引擎进行分析，区分理论与具体在MySQL不同存储引擎中的应用，否则很容易把自己搞糊涂的呢。
　　那么在MySQL中，按照锁的粒度划分，我们可以将锁大体分为3种：

表锁：开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突几率高，并发度最低；（MyISAM和 InnoDB引擎都支持表级锁，其中MyISAM）
行锁：开销大，加锁慢；会出现死锁；锁粒度小，发生锁冲突几率小，并发度最高；（InnoDB默认采用行级锁，在InnoDB中有3种行锁的算法，后续再针对InnoDB的行锁做具体的分析）
页锁：开销和加锁时间介于表锁和行锁之间；会出现死锁，锁粒度介于表锁和行锁之间。（MyISAM和InnoDB均不支持，该粒度的锁将不在本文的讨论范围内）
　　按照是否可写，我们又可以把锁分为：
共享锁（S锁，也称读锁）：事务A对数据加S锁后，事务A只能对该数据进行读取操作；其他事务只能再加S锁，不能再加X锁，除非加在该数据上的所有S锁得到释放。这就保证了，被加了S锁的数据只能被读取，而不能被任何事务进行修改。
排他锁（X锁，也称写锁）：事务A对数据加X锁后，事务A可以对该数据进行读写操作；但其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了，被加了X锁的数据只能被加锁的事务修改，而不能被其他事务进行修改。

MyISAM的表锁

（参考：MySQL MyISAM与表锁）
　　MyISAM的表锁，有两种模式，即共享读锁和独占写锁。实现读锁与读锁兼容，读锁与写锁互斥，写锁与写锁互斥。也就是，MyISAM表的一个会话进行读取操作，并不会阻塞其他会话对该表的读取操作，但会阻塞其他会话对该表的写操作（但是在一定条件下，是允许查询和插入的并发执行）；MyISAM表的一个会话进行写操作，会阻塞其他会话对该表的读操作以及写操作。
　　MyISAM在执行查询（SELECT）操作时，会自动对所有涉及的表加读锁；在执行更新（INSERT、UPDATE、DELETE）时，会自动对所有涉及的表加写锁。加锁的过程是自动的，但是我们也可以手动的加锁或解锁。

案例分析

下面，我们通过手动加锁来模拟一下并发过程中MyISAM加锁影响。（采用手动加锁的原因是，如果不使用手动加锁，那么所有SQL操作将是“瞬时”的，马上加锁操作完就解锁了，通过两个会话的操作过程，我们并不能感受到加锁的效果，所以采用手动加锁。对于实际应用中，除非有业务或者其他特殊需要，否则无需我们手动加锁）
　　如果读者已经对上述的文字描述理解的特别通透了，那么这一部分的案例分析可以大致浏览下，最后细看总结部分。
（记得每个实验后，对有加锁的地方释放锁，否则可能对后续的实验产生影响。）　　
（1）创建案例所需的表mi_user和mi_news，并指定表的存储引擎为MyISAM

CREATE TABLE `mi_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `flag` int(10) unsigned NOT NULL DEFAULT '0',
  `flag_idx` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `flag_idx_index` (`flag_idx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mi_news` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

（2）验证“读锁与读锁兼容，读锁与写锁互斥”

会话A	会话B
#对表`mi_user`进行显示加读锁，并成功查询`mi_user`表
LOCK TABLE mi_user READ;
SELECT * FROM mi_user;
#向表`mi_user`插入数据，发生错误`Table 'mi_user' was locked with a READ lock and can't be updated`，原因是对表加的是读锁，无法进行更新操作
INSERT INTO mi_user(`name`) VALUES(‘Zzz’);
	#对表`mi_user`进行查询（自动地在表上加读锁），执行成功（释放读锁），未被阻塞
	SELECT * FROM mi_user;
	#向表`mi_user`插入数据（自动地在表上加写锁，但是加写锁失败），处于阻塞状态
	INSERT INTO mi_user(`name`) VALUES(‘DreamBoy’);
	#阻塞中
#释放锁
UNLOCK TABLES;
	#会话A释放锁后，会话B上述执行的插入语句不再阻塞，接着执行成功

（3）LOCK加锁后，只能访问加锁了的表，不能访问没加锁的表

会话A
#对表`mi_user`进行显示加读锁，并成功查询`mi_user`表
LOCK TABLE mi_user READ;
SELECT * FROM mi_user;
#访问未加锁的表`mi_news`，发生错误`Table 'mi_news' was not locked with LOCK TABLES`
SELECT * FROM mi_news;

（4）LOCK加锁后，使用到表别名操作，同样在LOCK时也需要对别名加锁

会话A
#对表`mi_user`进行显示加读锁，使用别名查询
LOCK TABLE mi_user READ;
SELECT * FROM mi_user u;
#发生错误`Table 'u' was not locked with LOCK TABLES`
#释放锁，重新加锁，进行测试
UNLOCK TABLES;
#为别名也加一下锁
LOCK TABLE mi_user u READ;
SELECT * FROM mi_user u;

总结：

MyISAM的表锁，读锁与读锁兼容，读锁与写锁互斥，写锁与写锁互斥；LOCK某个表加读锁后，该会话也无法对这个表做更新操作；
LOCK加锁后，只能访问加锁了的表，不能访问没加锁的表；
LOCK加锁后，使用到表别名操作，同样在LOCK时也需要对别名加锁。

MyISAM的并发插入

在MyISAM存储引擎下，表的读写操作是串行的。但是，在前文“MyISAM的特性”描述中提及MyISAM“支持在查询表的同时，往该表插入新的记录”，那么这又是为什么呢？
　　原来，在一定条件下，MyISAM表也是支持查询和插入并发执行的，通过设置系统变量concurrent_insert来控制并发插入的行为，其值说明如下：

`concurrent_insert`的值	说明
0	不允许并发插入，即查询表的同时无法向该表插入数据
1	如果表中没有空洞（即表中没有被删除的行）时，允许并发插入，即一个会话查询表的同时，允许另外一个会话向同个表插入数据。该值为默认值
2	无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

查询当前系统变量concurrent_insert的值：

SHOW GLOBAL VARIABLES LIKE '%concurrent_insert%';

设置系统变量concurrent_insert的值，如设置为0：

SET GLOBAL concurrent_insert = 0;

案例分析

这里演示一下concurrent_insert各个值的效果。其中这里需要强调的是：LOCK加锁时，需要加LOCAL关键字，LOCAL关键字用于指示允许并发插入。
（1）设置concurrent_insert为0

会话A	会话B
#设置`concurrent_insert`为0
SET GLOBAL concurrent_insert = 0;
#对表`mi_user`进行显示加读锁，并成功查询`mi_user`表
LOCK TABLE mi_user READ LOCAL;
SELECT * FROM mi_user;
	#向表`mi_user`插入数据，阻塞中
	INSERT INTO mi_user(`name`) VALUES(‘Yyy’);
#释放锁
UNLOCK TABLES;
	#释放锁后，插入数据成功

（2）设置concurrent_insert为1

会话A	会话B
#设置`concurrent_insert`为1
SET GLOBAL concurrent_insert = 1;
#对表`mi_user`进行显示加读锁，并成功查询`mi_user`表
LOCK TABLE mi_user READ LOCAL;
SELECT * FROM mi_user;
	#向表`mi_user`插入数据，直接插入成功（因为此时表没有空间碎片(可以用optimize table table_name整理碎片)）
	INSERT INTO mi_user(`name`) VALUES(‘Xxx’);
#释放锁
UNLOCK TABLES;

删除表中的某些数据制作空洞，并重新进行上述测试。

会话A	会话B
#删除数据制造空洞
DELETE FROM mi_user WHERE id = 1;
#对表`mi_user`进行显示加读锁，并成功查询`mi_user`表
LOCK TABLE mi_user READ LOCAL;
SELECT * FROM mi_user;
	#向表`mi_user`插入数据，因为空洞的原因，所以阻塞中
	INSERT INTO mi_user(`name`) VALUES(‘Aaa’);
#释放锁
UNLOCK TABLES;
	#释放锁后，插入数据成功

（3）设置concurrent_insert为2

会话A	会话B
#设置`concurrent_insert`为2
SET GLOBAL concurrent_insert = 2;
#对表`mi_user`进行显示加读锁，并成功查询`mi_user`表
LOCK TABLE mi_user READ LOCAL;
SELECT * FROM mi_user;
	#向表`mi_user`插入数据，直接插入成功
	INSERT INTO mi_user(`name`) VALUES(‘Baa’);
#释放锁
UNLOCK TABLES;

MyISAM的并发调度

MyISAM存储引擎下，读与写是互斥的，读操作是串行的。那么，如果一个进程请求某个表的读锁的同时，另外一个进程也在请求这个表的写锁，那么谁会获得锁呢？答案是，写进程先获得锁。
　　不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。（转自：MySql锁的概念）具体调节的方式，可以查询参考的文章。

InnoDB中的行锁

在MySQL InnoDB引擎中，默认查询（SELECT）操作并不加锁（属于MVCC的快照读）；更新（INSERT、UPDATE、DELETE）操作对涉及的数据加排他锁。通过如下语句可以对SELECT加共享锁或排他锁：

共享锁（Ｓ）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB存储引擎有3种行锁的算法，其分别是：
（下列描述摘自：Mysql锁机制简单了解一下）

Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
Next-key Lock：锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。InnoDB事务隔离级别RR下，通过Next-key Lock，可解决幻读问题。

实际上，看完上述的描述，我想作为一个初学者的话，仍将是一脸的懵逼的。“什么时候会应用到Gap Lock间隙锁？”、“什么时候又应用到Next-key Lock？”、“使用Gap Lock的前置条件是什么呢？”等等一系列问题在脑海中还将不停打转。
　　所以这里强烈推荐阅读一下：MySQL 加锁处理分析、Innodb锁机制：Next-Key Lock 浅谈，对理解MySQL的InnoDB加锁机制将会有所帮助。
　　InnoDB行锁是通过索引上的索引项来实现的，只有通过索引条件检索数据，且EXPLAIN执行计划中确实用了索引查询（因为有可能MySQL认为全表扫描效率更高而放弃索引检索）才会使用行锁，否则InnoDB将使用表锁（20190309，“将会在聚簇索引上所有的记录都被加上锁，其次对于事务隔离级别RR来说在这些记录的“间隙”中默认还会加GAP锁。这里对于不满足查询条件的记录也同样会被加锁，这是为什么呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。”这句话描述可能存在一些问题，所以这里暂做保留！）（在《MySQL 加锁处理分析》一文中案例有所提及）
　　
// TODO 未完待续

乐观锁和悲观锁

讨论

最后，我们来讨论一下，这样的一个问题：MySQL Innodb 中 RR 隔离级别能否防止幻读？
　　首先参考一下：Innodb 中 RR 隔离级别能否防止幻读？

总结

文中讲述了MySQL的两大存储引擎MyISAM和InnoDB的特性，并对两者进行了对比。
分别讲述了MyISAM和InnoDB的锁机制。
最后简单介绍了一下乐观锁和悲观锁。

重要知识点概括

** 相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。**
MyISAM表的读和写是串行的（读写互斥），读操作串行，但在一定条件下，MyISAM表也支持查询和插入的并发执行，通过设置concurrent_insert进行调整。MyISAM表的读和写是串行的（读写互斥），读操作串行，但在一定条件下，MyISAM表也支持查询和插入的并发执行，通过设置concurrent_insert进行调整。
不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。
通过锁定机制可以实现事务隔离性要求，使得事务可以并发的工作。
在事务隔离级别RC和RR下，InnoDB存储引擎使用非锁定的一致性读。然而对于快照数据的定义却不同，在RC级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在RR级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
InnoDB除了通过范围条件加锁时使用间隙锁外，通过主键或者唯一索引来锁定不存在的值，也会产生GAP锁定。（记得间隙锁的前置条件）
GAP锁只会阻塞要INSERT到“间隙”中的INSERT操作，因为GAP间隙中是不存在任何记录的，除了INSERT操作，其他的操作结果应该都等价于空操作，MySQL就不去阻塞它了。

参考资料

葡萄干是个程序员

关注

4
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
今日大家一起来学MySQL的存储引擎和锁机制

目录文章目录目录前言MySQL的存储引擎MyISAMInnoDBMyISAM和InnoDB的对比讲一下让人敬畏的锁机制乐观锁和悲观锁共享锁和排他锁MySQL中的锁总结参考资料前言时光冉冉，转眼间，距我上回提笔写字发表文章，已经是很久之前的事了，久到甚至我需要翻看记录才回想起上一篇文章的标题，而且仅仅是标题。因为工作上的繁忙这一原因之外，更重要的原因还在于我能力有限的问题，所学的技术或者知识太...
复制链接

扫一扫