让人心动的mysql体系结构和InnoDB存储引擎知识详解

MySQL基本架构图

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

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

连接器

连接器就是你连接到数据库时使用的，负责跟客户端建立连接、获取权限、维持和管理连接。

命令： mysql -h$ip -P$port -u$user -p，回车后输密码，也可以在 -p 后面输入密码，但是有密码泄露的风险。

show processlist,可以查看连接的情况，Command 列中有一个 Sleep 表示连接空闲。

空闲连接默认8小时会被断开，可以由wait_timeout参数配置。

在数据库中，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

由于建立连接比较耗资源，所以建议尽量使用长连接，但是使用长连接后，MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

解决方案：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

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

查询缓存在MYSQL8时被移除了，由于查询缓存失效频繁，命中率低。

分析器

分析器先会做“词法分析”，识别出里面的字符串分别是什么，代表什么。然后需要做“语法分析”，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

优化器

执行器

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

一条 Select 语句执行流程

上图以 InnoDB 存储引擎为例，处理过程如下：

• 用户发送请求到 tomcat ，通过 tomcat 链接池和 mysql 连接池建立连接，然后通过连接发送 SQL 语句到 MySQL；

• MySQL 有一个单独的监听线程，读取到请求数据，得到连接中请求的SQL语句；

• 将获取到的SQL数据发送给SQL接口去执行；

• SQL接口将SQL发送给SQL解析器进行解析；

• 将解析好的SQL发送给查询优化器，找到最优的查询路劲，然后发给执行器；

• 执行器根据优化后的执行方案调用存储引擎的接口按照一定的顺序和步骤进行执行。

• 举个例子，比如执行器可能会先调用存储引擎的一个接口，去获取“users”表中的第一行数据，然后判断一下这个数据的 “id”字段的值是否等于我们期望的一个值，如果不是的话，那就继续调用存储引擎的接口，去获取“users”表的下一行数据。 就是基于上述的思路，执行器就会去根据我们的优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL 语句的执行计划，大致就是不停的更新或者提取一些数据出来。

在这里涉及到几个问题：

MySQL驱动到底是什么东西？

以java为例，我们们如果要在Java系统中去访问一个MySQL数据库，必须得在系统的依赖中加入一个MySQL驱动，比如在Maven里面要加上

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

那么这个MySQL驱动到底是个什么东西?其实L驱动就会在底层跟数据库建立网络连接，有网络连接，接着才能去发送请求给数据库服务器！让语言编写的系统通过MySQL驱动去访问数据库，如下图

数据库连接池到底是用来干什么的？

假设用java开发一个web服务部署在tomcat上，tomcat可以多线程并发处理请求，所以首先一点就是不可能只会创建一个数据库连接（多个请求去抢一个连接，效率得多低下）。

其次，如果每个请求都去创建一个数据库连接呢? 这也是非常不好的，因为每次建立一个数据库连接都很耗时，好不容易建立好了连接，执行完了SQL语句，还把数据库连接给销毁，频繁创建和销毁带来性能问题。

所以一般使用数据库连接池，也就是在一个池子里维持多个数据库连接，让多个线程使用里面的不同的数据库连接去执行SQL语句，然后执行完SQL语句之后，不要销毁这个数据库连接，而是把连接放回池子里，后续还可以继续使用。基于这样的一个数据库连接池的机制，就可以解决多个线程并发的使用多个数据库连接去执行SQL语句的问题，而且还避免了数据库连接使用完之后就销毁的问题了。

MySQL数据库的连接池是用来干什么的？

MySQL数据库的连接池的作用和java应用端连接池作用一样，都是起到了复用连接的作用。

InnoDB 存储引擎

InnoDB 架构简析

从图中可见，InnoDB 存储引擎由内存池，后台线程和磁盘文件三大部分组成

再来一张突出重点的图：

InnoDB 存储引擎第一部分：内存结构

Buffer Pool缓冲池

InnoDB 存储引擎基于磁盘存储的，并将其中的记录按照页的方式进行管理，但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的整体性能。

在数据库进行读取操作，将从磁盘中读到的页放在缓冲池中，下次再读取相同的页中时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页，否则读取磁盘上的页。

对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为 CheckPoint 的机制刷新回磁盘。所以，缓冲池的大小直接影响着数据库的整体性能，可以通过配置参数 innodb_buffer_pool_size 来设置，缓冲池默认是128MB，还是有点小的，如果你的数据库是16核32G的机器，那么你就可以给Buffer Pool分配个2GB的内存。

由于缓冲池不是无限大的，随着不停的把磁盘上的数据页加载到缓冲池中，缓冲池总要被用完，这个时候只能淘汰掉一些缓存页，淘汰方式就使用最近最少被使用算法（LRU），具体来说就是引入一个新的LRU链表，通过这个LRU链表，就可以知道哪些缓存页是最近最少被使用的，那么当你缓存页需要腾出来一个刷入磁盘的时候，可以选择那个LRU链表中最近最少被使用的缓存页淘汰。

缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息和数据字典信息。

数据页和索引页

页(Page)是 Innodb 存储的最基本结构，也是 Innodb 磁盘管理的最小单位，与数据库相关的所有内容都存储在 Page 结构里。Page 分为几种类型，数据页和索引页就是其中最为重要的两种类型。

插入缓冲（Insert Buffer）

在 InnoDB 引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获取较高的插入性能。当一张表中存在非聚簇的不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于非聚簇索引叶子节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。

所以 InnoDB 存储引擎开创性地设计了 Insert Buffer ，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个 Insert Buffer 对象中，好似欺骗。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

然而 Insert Buffer 的使用需要同时满足以下两个条件：

• 索引是辅助索引（ secondary index ) ;

• 索引不是唯一（ unique ）的。

当满足以上两个条件时， InnoDB 存储引擎会使用 Insert Buffer ，这样就能提高插入操作的性能了。不过考虑这样一种情况:应用程序进行大量的插入操作，这些都涉及了不唯一的非聚集索引，也就是使用了 Insert Buffer。若此时 MySQL数据库发生了宕机这时势必有大量的 Insert Buffer 并没有合并到实际的非聚集索引中去。

因此这时恢复可能需要很长的时间,在极端情况下甚至需要几个小时。辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致 Insert Buffer 失去了意义。

可以通过命令 SHOW ENGINE INNODB STATUS 来查看插入缓冲的信息

seg size显示了当前 Insert Buffer的大小为11336×16KB,大约为177MB; free list len代表了空闲列表的长度;size代表了已经合并记录页的数量。而黑体部分的第2行可能是用户真正关心的,因为它显示了插入性能的提高。 Inserts代表了插入的记录数;merged recs代表了合并的插入记录数量; merges代表合并的次数,也就是实际读取页的次数。 merges: merged recs大约为1:3,代表了插入缓冲将对于非聚集索引页的离散IO逻辑请求大约降低了2/3。

正如前面所说的,目前 Insert Buffer存在一个问题是:在写密集的情况下,插入缓冲会占用过多的缓冲池内存( innodb buffer pool),默认最大可以占用到1/2的缓冲池内存。以下是 InnoDB存储引擎源代码中对于 insert buffer的初始化操作:

Change Buffer

InnoDB 从1.0.x版本开始引入了 Change Buffer,可将其视为 Insert Buffer的升级版本, InnodB 存储引擎可以对DML操作— INSERT、 DELETE、 UPDATE 都进行缓冲,他们分别是: Insert Buffer、 Delete Buffer、 Purge buffer当然和之前 Insert Buffer一样, Change Buffer适用的对象依然是非唯一的辅助索引。

对一条记录进行 UPDATE 操作可能分为两个过程:

• 将记录标记为已删除;

• 真正将记录删除

因此 Delete Buffer对应 UPDATE操作的第一个过程,即将记录标记为删除。 PurgeBuffer对应 UPDATE 操作的第二个过程,即将记录真正的删除。同时, InnoDB 存储引擎提供了参数 innodb_change_buffering,用来开启各种Buffer的选项。该参数可选的值为: Inserts、 deletes、 purges、 changes、all、none。 Inserts、 deletes、 purges 就是前面讨论过的三种情况。 changes 表示启用 Inserts 和 deletes,all表示启用所有,none表示都不启用。该参数默认值为all。

从 InnoDB1.2.x版本开始,可以通过参数 innodb_change_buffer_max_size 来控制Change Buffer最大使用内存的数量:

mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.05 sec)

innodb_change_buffer_max_size 值默认为25,表示最多使用1/4的缓冲池内存空间。

而需要注意的是,该参数的最大有效值为50在 MySQL5.5版本中通过命令 SHOW ENGINE INNODB STATUS,可以观察到类似如下的内容:

可以看到这里显示了 merged operations和 discarded operation,并且下面具体显示 Change Buffer中每个操作的次数。 Insert 表示 Insert Buffer; delete mark表示 Delete Buffer; delete表示 Purge Buffer; discarded operations表示当 Change Buffer发生 merge时,表已经被删除,此时就无需再将记录合并(merge)到辅助索引中了。

自适应哈希索引

InnoDB 会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB 存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。

自适应哈希索引通过缓冲池的B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须一样的，也就是说其查询的条件必须完全一样，而且必须是连续的。

锁信息（lock info）

我们都知道，InnoDB 存储引擎会在行级别上对表数据进行上锁，不过 InnoDB 打开一张表，就增加一个对应的对象到数据字典。

数据字典

对数据库中的数据、库对象、表对象等的元信息的集合。在 MySQL 中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容，MySQL INFORMATION_SCHEMA 库提供了对数据局元数据、统计信息、以及有关MySQL Server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。

预读机制

MySQL的预读机制，就是当你从磁盘上加载一个数据页的时候，他可能会连带着把这个数据页相邻的其他数据页，也加载到缓存里去！

举个例子，假设现在有两个空闲缓存页，然后在加载一个数据页的时候，连带着把他的一个相邻的数据页也加载到缓存里去了，正好每个数据页放入一个空闲缓存页！

哪些情况下会触发MySQL的预读机制？

• 有一个参数是innodb_read_ahead_threshold，他的默认值是56，意思就是如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去。

• 如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去这个机制是通过参数innodb_random_read_ahead来控制的，他默认是OFF，也就是这个规则是关闭的。

所以默认情况下，主要是第一个规则可能会触发预读机制，一下子把很多相邻区里的数据页加载到缓存里去。

预读机制的好处为了提升性能。假设你读取了数据页01到缓存页里去，那么接下来有可能会接着顺序读取数据页01相邻的数据页02到缓存页里去，这个时候，是不是可能在读取数据页02的时候要再次发起一次磁盘IO？

所以为了优化性能，MySQL才设计了预读机制，也就是说如果在一个区内，你顺序读取了好多数据页了，比如数据页01到数据页56都被你依次顺序读取了，MySQL会判断，你可能接着会继续顺序读取后面的数据页。那么此时就提前把后续的一大堆数据页（比如数据页57到数据页72）都读取到Buffer Pool里去。

缓冲池内存管理

这里需要了解三个链表（Free List、Flush List、LRU List），

• Free List磁盘上的数据页和缓存页是一 一对应起来的，都是16KB，一个数据页对应一个缓存页。数据库会为Buffer Pool设计一个free链表，他是一个双向链表数据结构，这个free链表里，每个节点就是一个空闲的缓存页的描述数据块的地址，也就是说，只要你一个缓存页是空闲的，那么他的描述数据块就会被放入这个free链表中。刚开始数据库启动的时候，可能所有的缓存页都是空闲的，因为此时可能是一个空的数据库，一条数据都没有，所以此时所有缓存页的描述数据块，都会被放入这个free链表中，除此之外，这个free链表有一个基础节点，他会引用链表的头节点和尾节点，里面还存储了链表中有多少个描述数据块的节点，也就是有多少个空闲的缓存页。

• Flush List和 Free List 链表类似，flush链表本质也是通过缓存页的描述数据块中的两个指针，让被修改过的缓存页的描述数据块，组成一个双向链表。凡是被修改过的缓存页，都会把他的描述数据块加入到flush链表中去，flush的意思就是这些都是脏页，后续都是要flush刷新到磁盘上去。
  

• LRU List由于缓冲池大小是一定的，换句话说 free 链表中的空闲缓存页数据是一定的，当你不停的把磁盘上的数据页加载到空闲缓存页里去，free 链表中不停的移除空闲缓存页，迟早有那么一瞬间，free 链表中已经没有空闲缓存页，这时候就需要淘汰掉一些缓存页，那淘汰谁呢？这就需要利用缓存命中率了，缓存命中多的就是常用的，那不常用的就可以淘汰了。所以引入 LRU 链表来判断哪些缓存页是不常用的。
  

那LRU链表的淘汰策略是什么样的呢？

假设我们从磁盘加载一个数据页到缓存页的时候，就把这个缓存页的描述数据块放到 LRU 链表头部去，那么只要有数据的缓存页，他都会在 LRU 里了，而且最近被加载数据的缓存页，都会放到LRU链表的头部去，然后加入某个缓存页在尾部，只要发生查询，就把它移到头部，那么最后尾部就是需要淘汰了。

但是这样真的就可以吗？

第一种情况预读机制破坏

由于预读机制会把相邻的没有被访问到的数据页加载到缓存里，实际上只有一个缓存页是被访问了，另外一个通过预读机制加载的缓存页，其实并没有人访问，此时这两个缓存页可都在LRU链表的前面，如下图

这个时候，假如没有空闲缓存页了，那么此时要加载新的数据页了，是不是就要从LRU链表的尾部把所谓的“最近最少使用的一个缓存页”给拿出来，刷入磁盘，然后腾出来一个空闲缓存页了。这样显然是很不合理的。

第二种情况可能导致频繁被访问的缓存页被淘汰的场景

全表扫描导致他直接一下子把这个表里所有的数据页，都从磁盘加载到Buffer Pool里去。这个时候可能会一下子就把这个表的所有数据页都一一装入各个缓存页里去！此时可能LRU链表中排在前面的一大串缓存页，都是全表扫描加载进来的缓存页！那么如果这次全表扫描过后，后续几乎没用到这个表里的数据呢？此时LRU链表的尾部，可能全部都是之前一直被频繁访问的那些缓存页！然后当你要淘汰掉一些缓存页腾出空间的时候，就会把LRU链表尾部一直被频繁访问的缓存页给淘汰掉了，而留下了之前全表扫描加载进来的大量的不经常访问的缓存页！

优化LRU算法：基于冷热数据分离的思想设计LRU链表

MySQL在设计LRU链表的时候，采取的实际上是冷热数据分离的思想。LRU链表，会被拆分为两个部分，一部分是热数据，一部分是冷数据，这个冷热数据的比例是由 innodb_old_blocks_pct 参数控制的，他默认是37，也就是说冷数据占比37%。数据页第一次被加载到缓存的时候，实际上缓存页会被放在冷数据区域的链表头部。

然后MySQL设定了一个规则，他设计了一个 innodb_old_blocks_time 参数，默认值1000，也就是1000毫秒也就是说，必须是一个数据页被加载到缓存页之后，在1s之后，你访问这个缓存页，它会被挪动到热数据区域的链表头部去。因为假设你加载了一个数据页到缓存去，然后过了1s之后你还访问了这个缓存页，说明你后续很可能会经常要访问它，这个时间限制就是1s，因此只有1s后你访问了这个缓存页，他才会给你把缓存页放到热数据区域的链表头部去。

这样的话预读和全表扫描的数据都只会在冷数据头部，不会一开始就进去热数据区。

LRU算法极致优化

LRU链表的热数据区域的访问规则优化一下，即只有在热数据区域的后3/4部分的缓存页被访问了，才会给你移动到链表头部去。如果你是热数据区域的前面1/4的缓存页被访问，他是不会移动到链表头部去的。

举个例子，假设热数据区域的链表里有100个缓存页，那么排在前面的25个缓存页，他即使被访问了，也不会移动到链表头部去的。但是对于排在后面的75个缓存页，他只要被访问，就会移动到链表头部去。这样的话，他就可以尽可能的减少链表中的节点移动了。

LRU链表淘汰缓存页时机

MySQL在执行CRUD的时候，首先就是大量的操作缓存页以及对应的几个链表。然后在缓存页都满的时候，必然要想办法把一些缓存页给刷入磁盘，然后清空这几个缓存页，接着把需要的数据页加载到缓存页里去！

我们已经知道，他是根据LRU链表去淘汰缓存页的，那么他到底是什么时候把LRU链表的冷数据区域中的缓存页刷入磁盘的呢？实际上他有以下三个时机：

定时把LRU尾部的部分缓存页刷入磁盘

• 后台线程，运行一个定时任务，这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页，刷入磁盘里去，清空这几个缓存页，把他们加入回free链表去。

把flush链表中的一些缓存页定时刷入磁盘

如果只是把 LRU 链表的冷数据区域的缓存页刷入磁盘是不够，因为链表的热数据区域里的很多缓存页可能也会被频繁的修改，难道他们永远都不刷入磁盘中了吗？

所以这个后台线程同时也会在MySQL不怎么繁忙的时候，把flush链表中的缓存页都刷入磁盘中，这样被你修改过的数据，迟早都会刷入磁盘的！

只要flush链表中的一波缓存页被刷入了磁盘，那么这些缓存页也会从flush链表和lru链表中移除，然后加入到free链表中去！

所以整体效果就是不停的加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动。

另外一边，你的后台线程不停的在把lru链表的冷数据区域的缓存页以及flush链表的缓存页，刷入磁盘中来清空缓存页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加。

free链表没有空闲缓存页

如果所有的free链表都被使用了，这个时候如果要从磁盘加载数据页到一个空闲缓存页中，此时就会从LRU链表的冷数据区域的尾部找到一个缓存页，他一定是最不经常使用的缓存页！然后把他刷入磁盘和清空，然后把数据页加载到这个腾出来的空闲缓存页里去！

总结一下，三个链表的使用情况，Buffer Pool被使用的时候，实际上会频繁的从磁盘上加载数据页到他的缓存页里去，然后free链表、flush链表、lru链表都会同时被使用，比如数据加载到一个缓存页，free链表里会移除这个缓存页，然后lru链表的冷数据区域的头部会放入这个缓存页。

然后如果你要是修改了一个缓存页，那么flush链表中会记录这个脏页，lru链表中还可能会把你从冷数据区域移动到热数据区域的头部去。

如果你是查询了一个缓存页，那么此时就会把这个缓存页在lru链表中移动到热数据区域去，或者在热数据区域中也有可能会移动到头部去。

Redo log Buffer 重做日志缓冲

InnoDB 有 buffer pool（简称bp）。bp 是数据库页面的缓存，对 InnoDB 的任何修改操作都会首先在bp的page上进行，然后这样的页面将被标记为 dirty(脏页) 并被放到专门的 flush list 上，后续将由 master thread 或专门的刷脏线程阶段性的将这些页面写入磁盘（disk or ssd）。

这样的好处是避免每次写操作都操作磁盘导致大量的随机IO，阶段性的刷脏可以将多次对页面的修改 merge 成一次IO操作，同时异步写入也降低了访问的时延。然而，如果在 dirty page 还未刷入磁盘时，server非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。

为了避免上述问题的发生，Innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作，这个文件就是redo log file。这样的技术推迟了bp页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时延。

带来的问题是额外的写redo log操作的开销（顺序IO，当然很快），以及数据库启动时恢复操作所需的时间。

redo日志由两部分构成：redo log buffer、redo log file（在磁盘文件那部分介绍）。innodb 是支持事务的存储引擎，在事务提交时，必须先将该事务的所有日志写入到 redo 日志文件中，待事务的 commit 操作完成才算整个事务操作完成。在每次将redo log buffer写入redo log file后，都需要调用一次fsync操作，因为重做日志缓冲只是把内容先写入操作系统的缓冲系统中，并没有确保直接写入到磁盘上，所以必须进行一次fsync操作。因此，磁盘的性能在一定程度上也决定了事务提交的性能（具体后面 redo log 落盘机制介绍）。

InnoDB 存储引擎会首先将重做日志信息先放入重做日志缓冲中，然后在按照一定频率将其刷新到重做日志文件，重做日志缓冲一般不需要设置的很大，因为一般情况每一秒钟都会将重做日志缓冲刷新到日志文件中，可通过配置参数 Innodb_log_buffer_size 控制，默认为8MB。

Double Write 双写

如果说 Insert Buffer 给 InnoDB 存储引擎带来了性能上的提升，那么 Double wtite 带给 InnoDB 存储引擎的是数据页的可靠性。

InnoDB 的 Page Size 一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以 Page 为单位进行操作的。我们知道，由于文件系统对一次大数据页（例如InnoDB的16KB）大多数情况下不是原子操作，这意味着如果服务器宕机了，可能只做了部分写入。16K的数据，写入4K时，发生了系统断电 os crash ，只有一部分写是成功的，这种情况下就是 partial page write 问题。

有经验的DBA可能会想到，如果发生写失效，MySQL可以根据redo log进行恢复。这是一个办法，但是必须清楚地认识到，redo log中记录的是对页的物理修改，如偏移量800，写’aaaa’记录。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。MySQL在恢复的过程中检查page的checksum，checksum就是检查page的最后事务号，发生partial page write问题时，page已经损坏，找不到该page中的事务号。在InnoDB看来，这样的数据页是无法通过 checksum 验证的，就无法恢复。即时我们强制让其通过验证，也无法从崩溃中恢复，因为当前InnoDB存在的一些日志类型，有些是逻辑操作，并不能做到幂等。

为了解决这个问题，InnoDB实现了double write buffer，简单来说，就是在写数据页之前，先把这个数据页写到一块独立的物理文件位置（ibdata），然后再写到数据页。这样在宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，这就是double write。double write技术带给innodb存储引擎的是数据页的可靠性，下面对doublewrite技术进行解析

如上图所示，Double Write 由两部分组成，一部分是内存中的 double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的该区域，之后通过 double write buffer 再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write 页的写入后，再将 double wirite buffer 中的页写入各个表空间文件中。

在这个过程中，doublewrite 是顺序写，开销并不大，在完成 doublewrite 写入后，在将 double write buffer写入各表空间文件，这时是离散写入。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中的double write 中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。

InnoDB 存储引擎第二部分：后台线程

IO 线程

在 InnoDB 中使用了大量的 AIO（Async IO） 来做读写处理，这样可以极大提高数据库的性能。在 InnoDB 1.0 版本之前共有4个 IO Thread，分别是 write，read，insert buffer和log thread，后来版本将 read thread和 write thread 分别增大到了4个，一共有10个了。

• - read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。4个

• - write thread：负责写操作，将缓存脏页刷新到磁盘。4个

• - log thread：负责将日志缓冲区内容刷新到磁盘。1个

• - insert buffer thread ：负责将写缓冲内容刷新到磁盘。1个

Purge 线程

事务提交之后，其使用的 undo 日志将不再需要，因此需要 Purge Thread 回收已经分配的 undo 页。show variables like '%innodb*purge*threads%';

Page Cleaner 线程

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的 redo log 也就可以覆盖，即可以同步数据，又能达到 redo log 循环使用的目的。会调用write thread线程处理。show variables like '%innodb*page*cleaners%';

InnoDB 存储引擎第三部分：磁盘文件

InnoDB 的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是 redo 日志文件和归档文件。

二进制文件（binlong）等文件是 MySQL Server 层维护的文件，所以未列入 InnoDB 的磁盘文件中。

系统表空间和用户表空间

系统表空间包含 InnoDB 数据字典（元数据以及相关对象）并且 double write buffer , change buffer , undo logs 的存储区域。

系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。

系统表空间是一个共享的表空间，因为它是被多个表共享的。

系统表空间是由一个或者多个数据文件组成。默认情况下,1个初始大小为10MB，名为 ibdata1 的系统数据文件在MySQL的data目录下被创建。用户可以使用 innodb_data_file_path 对数据文件的大小和数量进行配置。

innodb_data_file_path 的格式如下：

innodb_data_file_path=datafile1[,datafile2]...

用户可以通过多个文件组成一个表空间，同时制定文件的属性:

innodb_data_file_path = /db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

这里将 /db/ibdata1 和 /dr2/db/ibdata2 两个文件组成系统表空间。如果这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。两个文件的文件名之后都跟了属性，表示文件 ibdata1 的大小为1000MB，文件 ibdata2 的大小为1000MB，而且用完空间之后可以自动增长。

设置 innodb_data_file_path 参数之后，所有基于 InnoDB 存储引擎的表的数据都会记录到该系统表空间中，如果设置了参数 innodb_file_per_table ，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立的用户空间。

用户表空间的命名规则为：表名.ibd。通过这种方式，用户不用将所有数据都存放于默认的系统表空间中，但是用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统表空间中。

下图显示 InnoDB 存储引擎对于文件的存储方式，其中frm文件是表结构定义文件，记录每个表的表结构定义。

重做日志文件（redo log file）和归档文件

默认情况下，在 InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件，这就是 InnoDB 的重做文件（redo log file），它记录了对于 InnoDB 存储引擎的事务日志。

当 InnoDB 的数据存储文件发生错误时，重做日志文件就能派上用场。InnoDB 存储引擎可以使用重做日志文件将数据恢复为正确状态，以此来保证数据的正确性和完整性。

每个 InnoDB 存储引擎至少有1个重做日志文件，每个文件组下至少有2个重做日志文件，加默认的 ib_logfile0 和 ib_logfile1。

为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上，以此来提高重做日志的高可用性。

在日志组中每个重做日志文件的大小一致，并以【循环写入】的方式运行。InnoDB 存储引擎先写入重做日志文件1，当文件被写满时，会切换到重做日志文件2，再当重做日志文件2也被写满时，再切换到重做日志1。

用户可以使用 Innodb_log_file_size 来设置重做日志文件的大小 ，这对 InnoDB 存储引擎的性能有着非常大的影响。

如果重做日志文件设置的太大，数据丢失时，恢复时可能需要很长的时间；另一个方面，如果设置的太小，重做日志文件太小会导致依据 checkpoint 的检查需要频繁刷新脏页到磁盘中，导致性能的抖动。

重做日志的落盘机制

InnoDB 对于数据文件和日志文件的刷盘遵守WAL（write ahead redo log）和 Force-log-at-commit 两种规则，二者保证了事务的持久性。WAL 要求数据的变更写入到磁盘前，首先必须将内存中的日志写入到磁盘；Force-log-at-commit 要求当一个事务提交时，所有产生的日志都必须刷新到磁盘上，如果日志刷新成功后，缓冲池中的数据刷新到磁盘前数据库发生了宕机，那么重启时，数据库可以从日志中恢复数据。

如上图所示，InnoDB 在缓冲池中变更数据时，会首先将相关变更写入重做日志缓冲中，然后再按时（比如每秒刷新机制）或者当事务提交时写入磁盘，这符合 Force-log-at-commit 原则；当重做日志写入磁盘后，缓冲池中的变更数据才会依据 checkpoint 机制写入到磁盘中，这符合 WAL 原则。

在 checkpoint 择时机制中，就有重做日志文件写满的判断，所以，如前文所述，如果重做日志文件太小，经常被写满，就会频繁导致 checkpoint 将更改的数据写入磁盘，导致性能抖动。

操作系统的文件系统是带有缓存的，当 InnoDB 向磁盘写入数据时，有可能只是写入到了文件系统的缓存中，没有真正的“落袋为安”。

InnoDB 的 innodb_flush_log_at_trx_commit 属性可以控制每次事务提交时 InnoDB 的行为。当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入；当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的 fsync ，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。

日志的刷盘机制如下图所示：

Innodb_flush_log_at_commit 是 InnoDB 性能调优的一个基础参数，涉及 InnoDB 的写入效率和数据安全。当参数数值为0时，写入效率最高，但是数据安全最低；参数值为1时，写入效率最低，但是数据安全最高；参数值为2时，二者都是中等水平，一般建议将属性值设置为1，以获得较高的安全性，而且也只有设置为1，才能保证事务的持久性。

用一条 UPDATE 语句再来了解 InnoDB 存储引擎

有了上面 InnoDB 存储引擎的架构基础介绍，我们再来分析一下一次 UPDATE 数据更新具体流程。

我们把这张图分为上下两部分来看，上面那部分是 MySQL Server 层处理流程，下面那部分是 MySQL InnoDB存储引擎处理流程。

MySQL Server 层处理流程

这部分处理流程无关于哪个存储引擎，它是 Server 层处理的，具体步骤如下：

• 用户各种操作触发后台sql执行，通过web项目中自带的数据库连接池：如 dbcp、c3p0、druid 等，与数据库服务器的数据库连接池建立网络连接；

• 数据库连接池中的线程监听到请求后，将接收到的sql语句通过SQL接口响应给查询解析器，查询解析器将sql按照sql的语法解析出查询哪个表的哪些字段，查询条件是啥；

• 再通过查询优化器处理，选择该sq最优的一套执行计划；

• 然后执行器负责调用存储引擎的一系列接口，执行该计划而完成整个sql语句的执行

这部分流程和上面分析的 一次 Select 请求处理流程分析的基本一致。

InnoDB 存储引擎处理流程

具体执⾏语句得要存储引擎来完成，如上图所示：

• 更新users表中id=10的这条数据，如果缓冲池中没有该条数据的，得要先从磁盘中将被更新数据的原始数据加载到缓冲池中。

• 同时为了保证并发更新数据安全问题，会对这条数据先加锁，防⽌其他事务进⾏更新。

• 接着将更新前的值先备份写⼊到undo log中（便于事务回滚时取旧数据），⽐如 update 语句即存储被更新字段之前的值。

• 更新 buffer pool 中的缓存数据为最新的数据，那么此时内存中的数据为脏数据（内存中数据和磁盘中数据不一致）

⾄此就完成了在缓冲池中的执⾏流程（如上图）。

缓冲池中更新完数据后，需要将本次的更新信息顺序写到 Redo Log ⽇志，因为现在已经把内存里的数据进行了修改，但是磁盘上的数据还没修改，此时万一 MySQL所在的机器宕机了，必然会导致内存里修改过的数据丢失，redo 日志就是记录下来你对数据做了什么修改，比如对“id=10这行记录修改了name字段的值为xxx”，这就是一个日志，用来在MySQL突然宕机的时候，用来恢复你更新过的数据的。不过注意的是此时 Redo Log 还没有落盘到日志文件。

这个时候思考一个问题：如果还没提交事务，MySQL宕机了怎么办？

上面我们知道到目前我们修改了内存数据，然后记录了 Redo Log Buffer 日志缓冲，如果这个时候 MySQL 奔溃，内存数据和 Redo Log Buffer 数据都会丢失，但是此时数据丢失并不要紧，因为一条更新语句，没提交事务，就代表他没执行成功，此时MySQL宕机虽然导致内存里的数据都丢失了，但是你会发现，磁盘上的数据依然还停留在原样子。

接下来要提交事物了，此时就会根据一定的策略把redo日志从redo log buffer里刷入到磁盘文件里去，此时这个策略是通过 innodb_flush_log_at_trx_commit 来配置的。

innodb_flush_log_at_trx_commit=0，表示提交事物不会把redo log buffer里的数据刷入磁盘文件的，此时可能你都提交事务了，结果mysql宕机了，然后此时内存里的数据全部丢失，所以这种方式不可取。

innodb_flush_log_at_trx_commit=1，redo log从内存刷入到磁盘文件里去，只要事务提交成功，那么redo log就必然在磁盘里了，所以如果这个时候MySQL奔溃，可以根据Redo Log日志恢复数据。

innodb_flush_log_at_trx_commit=2，提交事务的时候，把redo日志写入磁盘文件对应的os cache缓存里去，而不是直接进入磁盘文件，可能1秒后才会把os cache里的数据写入到磁盘文件里去。

提交事务的时候，同时会写入binlog，binlog也有不同的刷盘策略，有一个sync_binlog参数可以控制binlog的刷盘策略，他的默认值是0，此时你把binlog写入磁盘的时候，其实不是直接进入磁盘文件，而是进入os cache内存缓存。⼀般我们为了保证数据不丢失会配置双1策略，Redo Log 和 Binlog落盘策略都选择1。

Binlog 落盘后，再将Binlog的⽂件名、⽂件所在路径信息以及commit标记给同步顺序写到Redo log中，这一步的意义是用来保持 redo log 日志与 binlog 日志一致的。commit标记是判定事务是否成功提交的⼀个⽐较重要的标准，举个例子，如果如果第5步或者第6步执行成功后MySQL就奔溃了，这个时候因为没有最终的事务commit标记在redo日志里，所以此次事务可以判定为不成功。不会说redo日志文件里有这次更新的日志，但是binlog日志文件里没有这次更新的日志，不会出现数据不一致的问题。

做完前面后，内存数据已经修改，事物已经提交，日志已经落盘，但是磁盘数据还没有同步修改。InnoDB存储引擎后台有⼀个IO线程，会在数据库压⼒的低峰期间，将缓冲池中被事务更新、但还没来得及写到磁盘中的数据（脏数据，因为磁盘数据和内存数据已经不⼀致了）给刷到磁盘中，完成事务的持久化。

所以 InnoDB 处理写入过程可以用下面这幅图表示

推荐学习：mysql视频教程

以上就是让人心动的mysql体系结构和InnoDB存储引擎知识详解的详细内容，更多请关注站长家园其它相关文章！