MySQL的InnoDB存储引擎与日志

1.Mysql内部组件结构

总体来说，Mysql可以分为Server层和存储引擎层两部分。

1.1Server层

主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

1.1.1连接器

Mysql有很多客户端，比如navicat，mysql front，jdbc，SQLyog等，这些客户端要向mysql发起通信都必须先跟Server端建立通信连接，而建立连接的工作就是由连接器完成的。

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：

[root@192 ~]# mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306

总结，连接器的工作：

• 与客户端进行TCP三次握手建立连接
• 校验客户端的用户名和密码，如果用户名或密码不对，则会报错
• 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限

1.1.2查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是这条语句查询的结果。如果你的查询能够直接在这个缓存中找到，也就是可以找到key，那么这个就可以将缓存中的结果直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。

缺点：对于更新比较频繁的表，查询缓存的失效会非常频繁，一个表只要进行更新，那么这个表上所有的查询缓存都会被清空。所以，在MySQL8.0中查询缓存被废弃了。

注意：这里说的查询缓存是server层的，也就是MySQL8.0版本移除的是server层的缓存，并不是InnoDB存储引擎中的Buffer pool。

1.1.3分析器（解析器）

分析器会做词法分析和语法分析。SQL语句经过分析器分析之后，会生成一个类似于下图的语法树。如果SQL语句的语法不正确，这时MySQL解析器就会报错。

解析器不会检查SQL语句中的字段是否存在，它是在预处理阶段进行的。

1.1.4预处理器

prepare阶段一共做两件事情：

1.检查SQL查询语句中的表或者字段是否存在；

2.将select * 中的 * 符号，扩展为表上的所有列。

1.1.5优化器

优化器主要负责将SQL查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；以及MySQL内部的一些优化机制。

1.1.6执行器

开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个执行引擎提供的接口。

1.2存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

2.InnoDB引擎的SQL执行流程(重点)

SQL语句：update t set name = 'zhuge666' where id = 1;

client执行该SQL语句，首先经过Mysql的Server层，依次经过连接器（管理连接与权限检验）、分析器（语法词法分析）、优化器（执行计划生成索引选择），最终执行器调用执行引擎来执行这条SQL语句。

执行过程：

1.加载缓存数据：加载id为1的记录所在的整页数据到Buffer Pool中

2.写入更新数据的旧值到undo log中，便于回滚。

如果事务提交失败要回滚数据，可以用undo日志中的数据恢复buffer pool里的缓存数据。

3.更新内存数据。（更新Buffer Pool中的数据）

4.写redo log，向Redo Log Buffer中写redo log日志。

5.将Redo Log Buffer中的redo log日志顺序写入磁盘，准备提交事务。（prepare阶段）

6.准备提交事务，binlog日志写入磁盘，binlog主要用来恢复数据库磁盘里的数据。

7.写入commit标记到redo日志文件里，提交事务完成。该标记为了保证事务提交后redo与binlog数据一致。（commit阶段）（两阶段2PC）

8.在磁盘空闲时，将缓存池中修改后的数据随机写入磁盘，以page为单位写入，这步做完，磁盘的文件才完成修改。

关于redo log：

1.如果事务提交成功，buffer pool里的数据还没来得及写入磁盘，此时系统宕机了，可以用redo log中的数据恢复磁盘ibd文件里的数据；

2.redo日志文件顺序写：redo log是一个或几个预先分配好磁盘空间的文件，写入永远都是在文件末尾追加；

3.WAL机制（日志先行的机制）：先写redo log后刷新数据表文件的机制（Write Ahead Logging）。写日志虽然也是写磁盘，但是它是顺序写，相比随机写开销更小，能提升语句执行的性能。

3.日志

3.1redo log重做日志

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

redo log的顺序写：redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如下所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里。

3.1.1redo log的三种写入策略

innodb_flush_log_at_trx_commit：这个参数控制redo log的写入策略，它有三种可能取值：

设置为0：表示每次事务提交时都只是把redo log留在redo log buffer中，数据库宕机可能会丢失数据。

设置为1(默认值)：表示每次事务提交时都将redo log直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。

设置为2：表示每次事务提交时都只是把redo log写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

3.1.2redo log写入磁盘的过程

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用操作系统函数 write 写到文件系统的page cache，然后调用操作系统函数fsync持久化到磁盘文件。

3.2binlog二进制归档日志

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。

启动binlog会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处大于对服务器的影响。在MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。

3.2.1binlog的日志格式

1.STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中。这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。

2.ROW：基于行的复制，日志中会记录每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。

3.MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

3.2.2binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

• 为0的时候，表示每次提交事务都只 write 到 page cache，由系统自行判断什么时候执行 fsync 写入磁 盘。虽然性能得到提升，但是机器宕机，page cache里面的binlog会丢失。
• 也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
• 还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

3.2.3生成新的binlog的时机

• 服务器启动或重新启动
• 服务器刷新日志，执行命令flush logs
• 日志文件大小达到 max_binlog_size 值，默认值为 1GB

3.2.4为什么会有redo log和binlog两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有crash-safe的能力，binlog日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入MySQL的，既然只依靠 binlog 是没有 crash-safe 能力的，所以InnoDB 使用另外一套日志系统——也就是redo log来实现 crash-safe能力。有了redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

在 InnoDB 存储引擎中，事务提交过程中任何阶段，MySQL突然奔溃，重启后都能保证事务的完整性，已提交的数据不会丢失，未提交完整的数据会自动进行回滚。这个能力就会依赖redo log。

奔溃重启后会检查redo log中是完整并且处于prepare状态的事务，然后根据XID（事务ID），从binlog中找到对应的事务，如果找不到，则回滚；找到并且事务完整则重新commit redo log，完成事务的提交。

3.3undo log回滚日志

InnoDB对undo log文件的管理采用回滚段的方式，每个回滚段记录了1024个undo log segment，每个事务只会使用一个undo log segment。

在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大128个回滚段，故其支持同时在线的事务限制提高到了128*1024 。

3.3.1undo log删除的时机

对于新增类型的，在事务提交之后就可以清除掉了。

对于修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

3.3.2为什么Mysql不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL？

来一个请求就直接对磁盘文件进行随机读写，但磁盘随机读写的性能是非常差的，每次来一个请求就直接更新磁盘文件是不能让数据库抗住很高并发的。

Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存Buffer Pool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

3.4错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

3.5通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。

通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。

general_log_file：通用查询日志记录的位置参数。

4.补充-2PC

4.1两阶段提交

为什么redo log要分两步写，中间再穿插写binlog呢？

因为redo log影响主库的数据，binlog影响从库的数据，所以redo log和binlog必须保持一致才能保证主从数据一致，这是前提。

相信很多有过开发经验的同学都知道分布式事务，这里的redo log和binlog其实就是很典型的分布式事务场景，因为两者本身就是两个独立的个体，要想保持一致，就必须使用分布式事务的解决方案来处理。而将redo log分成了两步，其实就是使用了两阶段提交协议（Two-phase Commit，2PC）。

下面对更新语句的执行流程进行简化，看一下MySQL的两阶段提交是如何实现的：

4.2数据恢复流程（重点）

在不同的阶段时刻，看看MySQL突然奔溃后，按照上述流程是如何恢复数据的。

1. 时刻A（刚在内存中更改完数据页，还没有开始写redo log的时候奔溃）：
   因为内存中的脏页还没刷盘，也没有写redo log和binlog，即这个事务还没有开始提交，所以奔溃恢复跟该事务没有关系；
2. 时刻B（正在写redo log或者已经写完redo log并且落盘后，处于prepare状态，还没有开始写binlog的时候奔溃）：
   恢复后会判断redo log的事务是不是完整的，如果不是则根据undo log回滚；如果是完整的并且是prepare状态，则进一步判断对应的事务binlog是不是完整的，如果不完整则一样根据undo log进行回滚；
3. 时刻C（正在写binlog或者已经写完binlog并且落盘了，还没有开始commit redo log的时候奔溃）：
   恢复后会跟时刻B一样，先检查redo log中是完整并且处于prepare状态的事务，然后判断对应的事务binlog是不是完整的，如果不完整则一样根据undo log回滚，完整则重新commit redo log；
4. 时刻D（正在commit redo log或者事务已经提交完的时候，还没有反馈成功给客户端的时候奔溃）：
   恢复后跟时刻C基本一样，都会对照redo log和binlog的事务完整性，来确认是回滚还是重新提交。

遵循下图的逻辑，日志与数据一致，双日志一致（主从一致）：