目录
3.2 MySQL中的版本控制（Multiversion concurrency control， MVCC）
前言
维基百科对于MySQL的介绍：MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，比如维基百科、Google和Facebook等网站。
MySQL在实际生产中有广泛的应用，因此在面试中很多面试官对MySQL有很深度的考查。
1. 事务的隔离级别
1.1 什么是事务？
事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。
通过定义可以看出：事务是用户定义的一组执行序列；这组执行序列要不全部执行成功，只要有一个执行失败，那么这组执行序列就失败；通过总结，事务可以终结出以下几个特质：
-
Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
-
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
具备了以上特质的数据库就能够满足基本的事务处理。但是这还不够，数据库一定要求能够处理并行化的数据输入或者输出；但是，能够正确地处理并行化的数据其实是不容易的；数据库中由于并行化可能会导致一下几种错误：
- 脏读
某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
原因：某个事物撤销 - 不可重复读
在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
原因：插入了事务 - 幻读在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
原因：插入了列的数据
并发度是考量数据性能的一个重要指标。因此考量并发度和正确处理事务这两个维度，数据库提出了以下的几个隔离级别：
- 读未提交(Read uncommitted)
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- 读已提交(Read committed)
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- 可重复度(Repeatable read)
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- 串行化 (Serializable)
最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
2.MySQL的存储引擎
2.1 MyISAM
使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。
（1）frm文件：存储表的定义数据
（2）MYD文件：存放表具体记录的数据
（3）MYI文件：存储索引
frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。下面这张图就是MYI文件保存的机制：
从这张图可以发现，这个存储引擎通过MYI的B+树结构来查找记录页，再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩。
有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎。一般用于读取数据，数据分析的情况比较多。
2.2 InnoDB
InnoDB是默认的数据库存储引擎，他的主要特点有：
（1）可以通过自动增长列，方法是auto_increment。
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
（3）使用的锁粒度为行级锁，可以支持更高的并发；
（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
（5）配合一些热备工具可以支持在线热备份；
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；
当然InnoDB的存储表和索引也有下面两种形式：
（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。
（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。
对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。
其中最重要的如何用MVCC来提高InnoDB的存储引擎，之后会进一步介绍MVCC
3.2 Memory
将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。
（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；
（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；
（5）默认使用hash索引。
（6）如果一个内部表很大，会转化为磁盘表。
3. MySQL的版本控制（MVCC）
3.1 MySQL中的锁
MySQL中应用了大量的锁，从不同的角度，分为几种情况。
3.1.1 按照读写角度
- 独占锁：又称排它锁、X锁、写锁。X锁不能和其他锁兼容，只要有事务对数据上加了任何锁，其他事务就不能对这些数据再放置X了，同时某个事务放置了X锁之后，其他事务就不能再加其他任何锁了，只有获取排他锁的事务是可以对数据进行读取和修改。
- 共享锁：又称读锁、S锁。S锁与S锁兼容，可以同时放置。
- 更新锁：又称U锁。它允许再加S锁，但不允许其他事务再施加U锁或X锁，当被读取的数据要被更新时，则升级S锁为X锁。U锁的优点是允许事务A读取数据的同时不阻塞其它事务，并同时确保事务A自从上次读取数据后数据没有被更改，因此可以减少X锁和S锁的冲突，同时避免使用S锁后再升级为X锁造成的死锁现象。注意，MySQL并不支持U锁，SQLServer才支持U锁。
3.1.2 从粒度角度
MySQL支持不同级别的锁，其锁定的数据的范围也不同，也即我们常说的锁的粒度。MySQL有三种锁级别：行级锁、页级锁、表级锁。不同的存储引擎支持不同的锁粒度，例如MyISAM和MEMORY存储引擎采用的是表级锁，页级锁仅被BDB存储引擎支持，InnoDB存储引擎支持行级锁和表级锁，默认情况下是采用行级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。数据库引擎总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁从而避免死锁。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行锁总是逐步获得的，因此会出现死锁。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
下面详细介绍行锁和表锁，页锁由于使用得较少就不介绍了。
行锁
按行对数据进行加锁。InnoDB行锁是通过给索引上的索引项加锁来实现的，Innodb一定存在聚簇索引，行锁最终都会落到聚簇索引上，通过非聚簇索引查询的时候，先锁非聚簇索引，然后再锁聚簇索引。如果一个where语句里面既有聚簇索引，又有二级索引，则会先锁聚簇索引，再锁二级索引。由于是分步加锁的，因此可能会有死锁发生。
MySQL的行锁对S、X锁上做了一些更精确的细分，使得行锁的粒度更细小，可以减少冲突，这就是被称为“precise mode”的兼容矩阵。（该矩阵没有出现在官方文档上，是有人通过Mysql lock0lock.c:lock_rec_has_to_wait源代码推测出来的。）
行锁兼容矩阵
- 间隙锁(Gap Lock)：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据。
- 记录锁(Record Lock)：只锁记录，特定几行记录。
- 临键锁(Next-Key Lock)：同时锁住记录和间隙，前开后闭区间(a,b]。
- 插入意图锁(Insert Intention Lock)：插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。
表级锁
直接对整个表加锁，影响表中所有记录，表读锁和表写锁的兼容性见上面的分析。
MySQL中除了表读锁和表写锁之外，还存在一种特殊的表锁：意向锁，这是为了解决不同粒度的锁的兼容性判断而存在的。
3.2 MySQL中的版本控制（Multiversion concurrency control， MVCC）
为什么需要MVCC呢？数据库通常使用锁来实现隔离性。最原生的锁，锁住一个资源后会禁止其他任何线程访问同一个资源。但是很多应用的一个特点都是读多写少的场景，很多数据的读取次数远大于修改的次数，而读取数据间互相排斥显得不是很必要。所以就使用了一种读写锁的方法，读锁和读锁之间不互斥，而写锁和写锁、读锁都互斥。这样就很大提升了系统的并发能力。之后人们发现并发读还是不够，又提出了能不能让读写之间也不冲突的方法，就是读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据。当然快照是一种概念模型，不同的数据库可能用不同的方式来实现这种功能。
具体的实现原理是：
事务版本号 每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。
行记录的隐藏列
InnoDB 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：
DB_ROW_ID：6-byte，隐藏的行 ID，用来生成默认聚簇索引。如果我们创建数据表的时候没有指定聚簇索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚簇索引的方式可以提升数据的查找效率。DB_TRX_ID：6-byte，操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。DB_ROLL_PTR：7-byte，回滚指针，也就是指向这个记录的 Undo Log 信息。
从图中能看到回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。
查询（SELECT）
InnoDB 会根据以下两个条件检查每行记录：
- InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
- 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果。
插入（INSERT）
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
删除（DELETE）
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
删除在内部被视为更新，行中的一个特殊位会被设置为已删除。
更新（UPDATE）
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
多版本并发控制（MVCC） 在一定程度上实现了读写并发，它只在 可重复读（REPEATABLE READ） 和 提交读（READ COMMITTED） 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容，因为 未提交读（READ UNCOMMITTED），总是读取最新的数据行，而不是符合当前事务版本的数据行。而 可串行化（SERIALIZABLE） 则会对所有读取的行都加锁。
行锁，并发，事务回滚等多种特性都和MVCC相关。
4. 引用
[1]. https://zhuanlan.zhihu.com/p/102147497
254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
