MySQL中的数据用各种不同的技术存储在文件(或者内存)中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

2：常用存储引擎

2.1：MyISAM特点

每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：

.frm(存储表定义)
MYD(MYData，存储数据)
MYI(MYIndex，存储索引)

　MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。如下图所示：

　　这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

　　同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

1：在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。由于数据索引和存储数据分离，MyISAM引擎的索引结构是B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是所他的索引和实际数据是分开的。不过索引指向实际的数据，这种索引也就是非聚合索引。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2.但是它没有提供对数据库事务的支持，是表级锁（插入修改锁表），因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

2.2：Innodb特征(指针大小事6B)

每个InnoDB在磁盘上存储成2个文件

.frm(存储表定义)
ibd(数据和索引文件)

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

　　主键索引：上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

　　辅助索引：第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在name上的一个辅助索引：

　　这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引（也就是除了主键我们创建的其他索引）搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

　　了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调(可能是指“非递增”的意思)的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调(可能是指“非递增”的意思)的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

与MyISAM引擎的索引结构同样也是B+Tree，但是Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

　　并且和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

2.2.1：Innodb优点

1:就是针对事务的支持，事务（ACID），行级锁，外键，Java中使用事务处理，首先要求数据库支持事务。如使用MySQL的事务功能，就要求MySQL的表类型为Innodb才支持事务

2:使用MVCC多版本控制器来控制事务。

2.2.2：innoDB内存模型

1：缓冲池数据页和索引页：

数据以叶的方式操作数据，16K大小来自于机械硬盘时代，因为我们增删改查的时候，都是把这一页的数据查询到内存中，并且事先在内存中开辟好很多个空闲叶。InnoDB将磁盘中的数据按照叶的方式缓冲到内存中的一个区域，包含了索引页和数据页，用来弥补磁盘的速度差距，并且修改数据的时候，也是先修改缓冲池的数据，然后才会把数据刷新到内存中去的。缓冲页的大小默认是16KB。缓冲池还包含锁信息，插入缓存和redo日志等等。当在缓冲区的数据页中修改数据的，页数据会变脏，这个时候需要redo文件来记录修改后的数据，先做redo日志，然后再来修改页数据。

2：插入缓存(insert buffer)

insert buffer 在聚簇索引（主键id自增的的时候）在磁盘中按照顺序插入数据，没有磁盘的随机插入数据，提升效率。但是主键是uuid的时候不行，或者插入其他的索引，比如身份证的时候不是，索引不是顺序的，插入的时候身份证的索引会慢。

这个时候引入插入缓存：索引是辅助索引索引不唯一

现将索引插入缓冲池的数据页和索引页然后在插入到磁盘。

3：两次写和异步IO

两次写：当内存中的数据页16KB将数据写到数据表的时候，数据库宕机，只写了4KB。这个时候发生写失效。innodb数据引擎会生成这个16KB数据页的副本，当写失效的时候回从副本写入数据，这就是两次写。

异步IO:用户查询全表的数据集，这个时候用户不用等每个数据页扫描完毕，在扫描下一个数据页。可以多个io来处理，等待处理完毕合并返回结果。

3：MVCC多版本控制器前提

3.1：首先了解事务ACID 的实现

（A:原子性 C:一致性 I:隔离性 D:持久性）

实现原子性：将修改的多条数据所在的数据页从磁盘加载到内存缓冲池，然后修改数据，有undolog 备份修改前的数据用于回滚修改后的数据redolog一起提交，从而保证了原子性

实现一致性：依托于其他的三个特性

实现隔离性：MVCC版本控制器（多个事务之间修改了同一条数据，通过mvcc，不是简单的加锁来解决，从而提升效率）

实现持久性：持久性就是事务一旦提交，就会改变数据，数据库故障也不会影响。我们在一个事务中修改了多条数据，不会一次一次的向磁盘修改。会先把修改的数据记录到redolog(重做日志)。但是这个redolog是属于存储引擎INNODB的。mysql分为存储引擎层和service层。这个时候回再写一份Binlog日志。binlog属于service层。

1：首先执行器调用引擎获取数据，如果数据在内存中就直接返回；否则先从磁盘中读取数据，写入内存后再返回。
2：修改数据后再调用引擎接口写入这行数据
3：引擎层将这行数据更新到内存中，然后将更新操作写入redo log，这时候redo log标记prepare状态。然后告诉执行器我处理完了，可以提交事务了。
4：执行器生成这个操作的binlog，并把binlog写入磁盘，然后调用引擎提交事务
引擎收到commit命令后，把刚才写入的redo log改成commit状态
5：至此我们的一条更新语句就算基本完成了，这里面涉及了两阶段提交prepare阶段和commit阶段。

什么是两段式提交？

之前我们也清楚了，binlog是MySQL中Server层的日志，redo log是InnoDB存储引擎特有的。我们为了一致性就需要把这两个日志很好的持久化下来。而上面的redo log经历prepare和commit两个阶段才算提交。

分析：时刻A发生故障redolog写完，binlog没有写。事务回滚

分析：时刻B发生故障redolog和binlog都已经写完数据。查看redolog和binlog的数据一致性。如果一直就提交，否则回滚。

分步拆解：

1. 第一阶段：Prepare（准备阶段）

触发：执行COMMIT后，MySQL 先调用 InnoDB 的prepare流程；
操作：
1. InnoDB 将内存中的 redo log buffer 写入磁盘（redo log 文件），并将该事务的 redo log 标记为「PREPARE 状态」；
2. 此时 redo log 已持久化，但事务并未真正提交（状态是 “准备就绪”）；
3. 若这一步失败，直接返回 “提交失败”，事务回滚，binlog 不会被写入。

2. 第二阶段：Commit（提交阶段）

触发：只有 Prepare 阶段成功后，才会进入 Commit 阶段；
操作：
1. MySQL Server 将 binlog 从内存 buffer 刷到磁盘（binlog 文件）；
2. 若 binlog 刷盘成功，InnoDB 将 redo log 的状态从「PREPARE」改为「COMMIT」（仅修改一个标记，几乎无开销）；
3. 释放事务持有的锁，事务正式提交完成；
4. 若 binlog 刷盘失败，InnoDB 会回滚该事务（删除 redo log 的 PREPARE 记录），保证两份日志都未刷盘。

三、为什么需要两阶段提交？（反例说明）

如果没有两阶段提交，直接 “先刷 redo log、再刷 binlog” 或 “先刷 binlog、再刷 redo log”，都会出现一致性问题：

反例 1：先刷 redo log，再刷 binlog

plaintext

1. redo log刷盘成功 → 2. 服务器宕机 → 3. binlog未刷盘

重启后，InnoDB 通过 redo log 恢复数据（事务生效）；
但 binlog 中无该事务记录，主从同步时从库不会执行该事务 → 主从数据不一致。

反例 2：先刷 binlog，再刷 redo log

plaintext

1. binlog刷盘成功 → 2. 服务器宕机 → 3. redo log未刷盘

重启后，InnoDB 无 redo log，事务未生效（数据未修改）；
但 binlog 中有该事务记录，主从同步时从库执行该事务 → 主从数据不一致。

两阶段提交的解决效果：

若 Prepare 阶段失败：redo log 未刷盘，binlog 也不会刷，事务完全未生效；
若 Commit 阶段（binlog）失败：回滚 redo log 的 PREPARE 记录，事务仍未生效；
只有两份日志都刷盘成功，事务才真正提交，从根本上保证 redo log 和 binlog 的一致性。

MySQL 的整体架构可分为四大层级：连接层、服务层、引擎层与存储层。通过清晰的模块划分，实现了“通用处理”与“引擎定制”的有效解耦。

3.2：首先了解undo和redo和binlog的作用。

undo（撤销日志）：数据修改前的内容（用于回滚），当把数据从磁盘读取内存的时候，每次数据操作这些数据都会变脏，就是脏数据，需要都会记录undo日志。当发生回滚的时候，就需要这些日志用来回滚数据

redo（重置日志）：数据修改后的内容，在缓冲池，然后数据修改后生成redo日志，需要把这些内存中的数据插入到磁盘。这个时候当数据库宕机的时候。这些redo就是重要的记录，重启之后会把redo日志也就是修改的数据重新写入数据库。

binlog：二进制日志，保存在磁盘中，记录了数据库的所有改变。它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主从数据库的复制）。

假设有A、B两个数据，值分别为1,2，开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下（简化）：

A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.

E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。

I.事务提交

3.3：快照读和当前读

在开启mvcc之前我们需要首先了解当前读和快照度以及mysql的各种锁比如读锁、写锁、间隙、行锁、表锁等概念

快照度：简单的select

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

当前读：

select lock in share mode(共享锁-读锁),

select for update ;(排它锁-写锁读的时候锁定数据，其他事务不能使用排它锁，只能select，只能等待事务提交)

update, insert ,delete(排他锁修改的时候，锁定数据，其他线程不能修改)

这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

1：开启两个事务验证事务1开启写锁（排它锁）之后，事务2的写锁操作会等待事务1提交

2：开启两个事务验证事务1开启读锁（共享锁）之后，事务2的锁操作会等待事务1提交

3.4：读锁、写锁、间隙锁、表锁、行级锁

首先我们建表

CREATE TABLE `z` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('1', '1', '0');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('3', '6', '1');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('5', '4', '2');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('7', '8', '3');
INSERT INTO `study`.`z` (`id`, `b`, `c`) VALUES ('8', '10', '4');

b的索引结构如下，间隙锁分析

我们验证b的值[4-8]之间不能才插入数据,b是非唯一索引，如果在插入b的值是6的时候，会在b的值在4和8之间，并且id的值在5和7之间，锁定数据。这就出现了间隙锁。并且主键在不能大于5 小于7，防止出现幻读。

当b=6的时候的任意主键id的值。

事务1

事务2

间隙锁在数据库事务RR的情况，在使用间隙锁的情况下能防止幻读产生，但是会有间隙，造成不必要的锁。锁住命中索引项的前一个索引到命中索引项的后一个索引之间的一个左开右闭区间

在RR的情况下，不使用间隙锁，修改数据，查询（当前读 for update）,会出现幻读,快照度不会产生幻读。

1)、唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
2)、普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；

4：正式了解MVCC多版本控制器

4.1：什么是MVCC多版本控制器？

MVCC（Multi-Version Concurrency Control）,简称多版本并发控制，适用于READ COMMITTED和REPEATABLE READ隔离级别。

4.2：简单的使用读锁、写锁实现事务

在上边我们知道了事务的原子性依靠undo和redo日志对于多条数据的统一需改和统一提交。持久性也依靠了redo和binlog的两段式提交。但是这些操作不能实现多个事务修改一条数据的隔离性。我们要实现隔离性可能凭经验对数据加锁。锁定这条被多个事务修改的数据来实现（行锁）。但是单纯的行锁会影响效率。我们可以想到行级读写锁。

MVCC之前，我们简单的使用读锁、写锁来实现看看什么缺点？

缺点1：

事务1修改id=1的数据，加了写锁，其他的事务不能写，也不能读，事务1只要稍有卡顿。只要并发量高了，其他的事务都会被阻塞，导致在项目中体验很差，页面卡顿。这显然不是理想的解决方案

缺点2：

随着mysql的事务隔离级别的复杂化，RR（重复读）和RC（读已提交）带来的影响个不相同。所以单纯的读写锁已经不能满足需求了。

所以有了MVCC多版本控制器，实现了更复杂的机制。做到了有读写冲突的时候也能不加锁。非阻塞的读取数据。

MVCC是行锁的变种,主要是相比较以前的行锁，解决的读写之间的冲突，不需要加锁，但是写写之间需要加行锁。

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

4.3：MVCC实现原理

1：首先看一个例子，4个事务修改id=1的数据，修改name.

2：在事务104的第一次查询的时候，然后我们查看undolog如下

3：然后readview的4个属性值如下

4：在事务104的第一次查询中，我们分析readview的判断逻辑，首先是undolog的第一个事务103

根据判断可以查询到name="axing3"

5.1：在事务104的第二次查询查询中，可以按照隔离级别分为读已提交、可重复读。读已提交会生成新的readview，可重复读复用第一次查询的readview，在读已提交的情况下readview如下，新增了undolog的101事务日志，readview的4个参数值也会变化。能查询到name=axing1

5.2：在事务104的第二次查询查询中，可以按照隔离级别分为读已提交、可重复读。读已提交会生成新的readview，可重复读复用第一次查询的readview，在可重复读的readview如下，新增了undolog的101事务日志，readview的4个参数值不会变化。能查询到name=axing1

MVCC的意思就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖数据行中记录中的 3个隐式字段，undo日志，Read View 来实现的。

Read View演示：说白了Read View就是事务进行快照读操作的时候生产的读视图

现在来看看MySQL数据库为我们提供的四种隔离级别

　　① Serializable (串行化)：（锁表）

　　② Repeatable read (可重复读)：可避免脏读、不可重复读、幻读的发生。

（mvcc每次select查询之后使用第一个查询的read view 只有一个链表，避免读取到别人事务提交的数据，当有update的时候，会当前读，同时配合next key lock锁解决一部分幻读，不能完全解决幻读）

　　③ Read committed (读已提交)：可避免脏读的发生，会读取到别人提交的事务数据，两次查询不一致。

（mvcc每次select同一条数据都会有一个新的read view，也就是会有多个，形成多个链表，那么就会出现读取到其他事务提交的数据

重复读取数据不一致）

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

4.4：MVCC会加锁吗？

MVCC不会加传统的锁，但是我们依然会感到阻塞，比如多个事务同时修改id=1的数据的name

由于update是当前读，当前读加锁。

MVCC主要解决的是传统的悲观锁，当一个事务修改数据id=1,那么传统的读、写锁。其他的事务就不能读这条数据了。但是mvcc的快照读可以根据MVCC的规则读取数据。

也就是说MVCC的快照读，不会加锁

但是MVCC的当前读，会加锁

4.5：MVCC能解决幻读吗？

不能（举例如下），使用select for update能解决幻读

START TRANSACTION -- 事务1
SELECT * from mcvvTable where age=40 -- 第一步执行 查到age=40数据两条
UPDATE mcvvTable SET name="aa" where age=40 -- 第六步执行（这是当前读，就会出现幻读）
SELECT * from mcvvTable where age=40 -- 第七步执行 查到age=40数据三条

COMMIT




START TRANSACTION -- 事务2
SELECT * from mcvvTable where age=40 -- 第二步执行
INSERT INTO mcvvTable (name,age) VALUES ("a",40) -- 第三步执行
SELECT * from mcvvTable where age=40 -- 第四步执行

COMMIT -- 第五步执行