MySQL：缓存和存储架构_mysql缓存结构

参考链接：https://www.itzhai.com/articles/insight-into-the-underlying-architecture-of-mysql-buffer-and-disk.html

【关于作者】

关于作者，我目前蚂蚁金服搬砖任职，在营销投放领域工作了多年，目前在专注于内存数据库相关的应用学习，如果你有任何技术交流或大厂内推及面试咨询，都可以从我的个人博客（https://0522-isniceday.top/）联系上我~

1.MySQL架构

2.查询SQL执行流程

2.1.服务端与客户端建立连接

对于Java而言，会将建立好的连接放到连接池中复用，只要连接不关闭，就会一直在MySQL服务端保持，可以通过命令：

show processlist查看:

注意其中有个Time参数，代表连接多久没动静了，默认超过8小时还没有动静就会关闭连接，可以通过wait_timeout参数进行控制

2.2.执行SQL

• 分析器：会校验SQL的语法的正确性并生成SQLID，不正确在这里就直接返回了

• 优化器：会判断走哪个索引更优，并决定是否需要重写SQL，最后生成执行计划（Explain可以查看执行计划）

• 执行器：根据执行计划执行SQL，执行前会进行权限校验，然后调用表存储引擎对应接口进行数据查询，然后针对表返回结果进行进一步加工（执行计划中的rows指的就是存储引擎返回的数据记录的条数）

  eg：执行器拿到select * from t_user where user_id=10000的所有记录，在依次判断user_name是不是等于"arthinking"，获取到匹配的记录

3.InnoDB存储引擎

架构图：

3.1.buffer pool

buffer pool：主内存的一块区域，在InnoDB访问表数据或索引数据时，会顺应把对应的数据页缓存到这里，加快数据的处理速度（例如插入缓存、预读机制等）

为了提高缓存管理效率，会将数据组织为链表，并采取改进版LRU算法，将不常用的数据淘汰

3.1.1.LRU算法

算法运行方式：

• 默认 3/8缓冲池用于旧子列表
• 当新页面需要加入缓冲池时，放置到旧子列表的头部
• 重复访问旧子列表的数据，将其放到新子列表的头部（MRU端）
• 随机数据库的运行，最近最少使用的数据会逐步移动到旧子列表的末尾（LRU端），最终被淘汰

相关参数优化：

• innodb_old_blocks_pct：控制旧列表的百分占比，默认37，也就是3/8，可选值5~59

• innodb_old_blocks_time：指第一次访问数据的时间窗口，就是说在该窗口内访问第一次访问的该页面不会将其放到列表头部，默认1000，其实就是为了防止数据不会二次访问，从而造成缓存污染

  mysql查看当前的参数：

  SHOW GLOBAL VARIABLES like ‘xxx’

innodb_old_blocks_time很重要，有了这1秒，对于全表扫描，由于是顺序扫描的，一般同一个数据页的数据都是在一秒内访问完成的，不会升级到新子列表中，一直在旧子列表淘汰数据，所以不会影响到新子列表的缓存。

3.1.2.关于磁盘IO的方式

O_DIRECT是innodb_flush_method参数的一个可选值

与数据库性能密切相关的文件IO操作方法

3.1.2.1.文件IO操作方法

数据库系统是基于文件系统的，其性能和设备读写的机制有密切关系

open：打开文件

int open(const char *pathname, int flags..);
1

系统调用open会为该进程创建一个文件描述符（fd），常用的flag参数如下：

• O_WRONLY：表示我们以"写"的方式打开，告诉内核我们需要向文件中写入数据；

• O_DSYNC：每次write都等待物理I/O完成，但是如果写操作不影响读取刚写入的数据，则不等待文件属性更新；

• O_SYNC：每次write都等到物理I/O完成，包括write引起的文件属性的更新；

• O_DIRECT：执行磁盘IO时绕过缓冲区高速缓存(内核缓冲区)，从用户空间直接将数据传递到文件或磁盘设备，称为直接IO（direct IO）。因为没有了OS cache，所以会O_DIRECT降低文件的顺序读写的效率。

  关于文件描述符fd是什么：

  文件描述符fd形式上是一个非负整数。是一个索引值，指向内核为每一个进程所维护的该进程打开文件的记录表。

write：写文件

ssize_t write(int fd, const void *buf, size_t count);
1

open打开文件并获取fd之后，使用write函数进行文件写入，具体表现根据open函数的入参决定（pathname、flag）

fsync & fdatasync：刷新文件[8]

#include <unistd.h>

int fsync(int fd);

int fdatasync(int fd);
1
2
3
4
5

• fdatasync：操作完write之后，我们可以调用fdatasync将文件数据块flush到磁盘，只要fdatasync返回成功，则可以认为数据已经写到磁盘了；
• fsync：与O_SYNC参数类似，fsync还会更新文件metadata到磁盘；
• sync：sync只是将修改过的块缓冲区写入队列，然后就返回，不等实际写磁盘操作完成；

为了保证数据持久化到磁盘，除了调用write之外，还需要调用fsync，流程如下：

fsync性能问题：除了刷脏页到磁盘，fsync还会同步文件metadata，而文件数据和metadata通常存放在磁盘不同地方，所以fsync至少需要两次IO操作。

fsync的优化建议：由于上述的性能问题，如果能够减少metadata的更新，就可以使用fdatasync。因此需要确保文件的尺寸在write前后没有发生变化。因此可以创建固定大小的文件进行写，写完则开启新的文件继续写

3.1.2.2.InnoDB_Flush_Method

该参数用于数据刷新到InnoDB数据文件和日志文件的方法，可能影响IO的吞吐量以下是具体参数说明：

比较常用的是这三种：

fsync

默认值，使用fsync()系统调用来flush数据文件和日志文件到磁盘；

O_DSYNC

由于open函数的O_DSYNC参数在许多Unix系统上都存中问题，因此InnoDB不直接使用O_DSYNC。

InnoDB用于O_SYNC 打开和刷新日志文件，fsync()刷新数据文件。

表现为：写日志操作是在write函数完成，数据文件写入是通过fsync()系统调用来完成；

O_DIRECT

使用O_DIRECT （在Solaris上对应为directio()）打开数据文件，并用于fsync()刷新数据文件和日志文件。此选项在某些GNU/Linux版本，FreeBSD和Solaris上可用。

表现为：数据文件写入直接从buffer pool到磁盘，不经过操作系统缓冲，日志还是需要经过操作系统缓存；

O_DIRECT_NO_FSYNC

在刷新I/O期间InnoDB使用O_DIRECT，并且每次write操作后跳过fsync()系统调用。

此设置适用于某些类型的文件系统，但不适用于其他类型的文件系统。例如，它不适用于XFS。如果不确定所使用的文件系统是否需要fsync()（例如保留所有文件元数据），请改用O_DIRECT。

为什么使用了O_DIRECT配置后还需要调用fsync()?

参考MySQL的这个bug：Innodb calls fsync for writes with innodb_flush_method=O_DIRECT[10]

Domas进行的一些测试表明，如果没有fsync，某些文件系统（XFS）不会同步元数据。如果元数据会更改，那么您仍然需要使用fsync（或O_SYNC来打开文件）。

例如，如果在启用O_DIRECT的情况下增大文件大小，它仍将写入文件的新部分，但是由于元数据不能反映文件的新大小，因此如果此刻系统发生崩溃，文件尾部可能会丢失。

为此：当重要的元数据发生更改时，请继续使用fsync或除O_DIRECT之外，也可以选择使用O_SYNC。

MySQL从v5.6.7起提供了O_DIRECT_NO_FSYNC选项来解决此类问题。

3.2.Change Buffer

Change Buffer是一种特殊的数据结构，当辅助索引（非唯一索引）不在缓冲池中时，Change Buffer会缓存这些值。当页面通过读取操作加载到缓冲池中时，会将由INSERT，UPDATE或DELETE操作（DML）产生的change buffer合并到buffer pool的数据页中

为什么唯一索引不可以使用change buffer？

针对唯一索引，如果buffer pool中不存在对应的数据页，还是需要先去磁盘加载数据页，才能判断数据是否重复。

而普通索引是非唯一的，插入顺序相对随机，更新和删除操作也会影响索引树中不相邻的数据页，通过使用合并缓冲，避免了在磁盘中产生大量的随机IO访问获取普通索引页

总结：change buffer的存在目的就是为了防止辅助索引产生写操作时，每次都去更新磁盘上辅助索引，因为这样会产生很多随机IO，效率很低，所以如果写操作的索引数据所在的数据页缓存在了buffer pool的话，就先修改缓存中的索引数据也，然后再去刷盘，由于是数据页维度的刷盘，效率会比随机IO快很多，避免了在磁盘产生大量的随机IO访问获取普通索引页。而唯一索引还需要将对应的数据页先加载并判断是否唯一，还是无法避免从磁盘加载索引页的过程（加载过程需要先定位数据页，需要查找可能会产生多次IO）。

问题

当有许多受影响的行和许多辅助索引要更新时，change buffer合并可能需要几个小时，在此期间，I/O会增加，可能会导致查询效率大大降低，即使在事务提交之后，或者服务器重启之后，change buffer合并操作也会继续发生。相关阅读：Section 14.22.2, “Forcing InnoDB Recovery”

3.3.自适应哈希索引

自适应哈希索引功能由innodb_adaptive_hash_index变量启用 ，或在服务器启动时由--skip-innodb-adaptive-hash-index禁用。

3.4.Log Buffer

log buffer(日志缓冲区)：用于保存要写入磁盘上的log file（日志文件）的数据，缓冲区的内容会定期刷写到磁盘

innodb_log_buffer_size：定义log buffer的大小，默认为16MB。较大的日志缓冲区可以让大型事务在提交之前无需将redo log写入磁盘。

如果您有更新，插入或者删除多行的事务，尝试增大日志缓冲区的大小可以节省磁盘I/O。

3.4.1.配置参数

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 变量控制如何将日志缓冲区的内容写入并刷新到磁盘。

该参数控制是否严格存储ACID还是尝试获取更高的性能，可以通过该参数获取更好的性能，但是会导致在系统崩溃的过程中导致数据丢失。

可选参数：

• 0，事务提交之后，日志只记录到log buffer中，每秒写一次日志到缓存并刷新到磁盘，尚未刷新的日志可能会丢失；
• 1，要完全符合ACID，必须使用该值，表示日志在每次事务提交时写入缓存并刷新到磁盘；
• 2，每次事务提交之后，日志写到page cache，每秒刷一次到磁盘，尚未刷新的日志可能会丢失；

innodb_flush_log_at_timeout

innodb_flush_log_at_timeout 变量控制日志刷新频率。可让您将日志刷新频率设置为*N秒（其中N*为1 ... 2700，默认值为1）

为了保证数据不丢失，请执行以下操作：

• 如果启用了binlog，则设置：sync_binlog=1;
• innodb_flush_log_at_trx_commit=1;

配置效果如下图所示：

3.5.表空间

一个InnoDB表及其索引可以在建在系统表空间中，或者是在一个 独立表空间 中，或在 通用表空间。

• 当innodb_file_per_table启用时，通常是将表存放在独立表空间中，这是默认配置；
• 当innodb_file_per_table禁用时，则会在系统表空间中创建表；
• 要在通用表空间中创建表，请使用 CREATE TABLE ... TABLESPACE语法。有关更多信息，请参见官方文档 14.6.3.3 General Tablespaces。

表空间涉及的文件

相关文件默认在磁盘中的innodb_data_home_dir目录下：

|- ibdata1  // 系统表空间文件
|- ibtmp1  // 默认临时表空间文件，可通过innodb_temp_data_file_path属性指定文件位置
|- test/  // 数据库文件夹
    |- db.opt  // test数据库配置文件，包含数据库字符集属性
    |- t.frm  // 数据表元数据文件，不管是使用独立表空间还是系统表空间，每个表都对应有一个
    |- t.ibd  // 数据库表独立表空间文件，如果使用的是独立表空间，则一个表对应一个ibd文件，否则保存在系统表空间文件中
1
2
3
4
5
6

frm文件

创建一个InnoDB表时，MySQL 在数据库目录中创建一个.frm文件。frm文件包含MySQL表的元数据(如表定义)。每个InnoDB表都有一个.frm文件。

与其他MySQL存储引擎不同， InnoDB它还在系统表空间内的自身内部数据字典中编码有关表的信息。MySQL删除表或数据库时，将删除一个或多个.frm文件以及InnoDB数据字典中的相应条目。

因此，在InnoDB中，您不能仅通过移动.frm 文件来移动表。有关移动InnoDB 表的信息，请参见官方文档14.6.1.4 Moving or Copying InnoDB Tables。

ibd文件

对于在独立表空间创建的表，还会在数据库目录中生成一个 .ibd表空间文件。

在通用表空间中创建的表在现有的常规表空间 .ibd文件中创建。常规表空间文件可以在MySQL数据目录内部或外部创建。有关更多信息，请参见官方文档14.6.3.3 General Tablespaces。

ibdata文件

系统表空间文件，在 InnoDB系统表空间中创建的表在ibdata中创建

3.5.1.系统表空间

系统表空间由一个或多个数据文件(ibdata文件)组成，其中包含与InnoDB相关对象有关的元数据（InnoDB 数据字典 data dictionary），以及更改缓冲区（change buffer），双写缓冲区（doublewrite buffer）和撤销日志（undo logs）的存储区

InnoDB如果表是在系统表空间中创建的，则系统表空间中也包含表的表数据和索引数据

系统表空间的问题：

MySQL 5.6.7之前，默认设置是将所有InnoDB表和索引保留 在系统表空间内，这通常会导致该文件变得非常大。因为系统表空间永远不会缩小，所以如果先加载然后删除大量临时数据，则可能会出现存储问题。

在MySQL 5.7中，默认设置为 独立表空间模式，其中每个表及其相关索引存储在单独的 .ibd文件中。此默认设置使使用**Barracuda文件格式的InnoDB功能更容易使用，例如表压缩**，页外列的有效存储以及大索引键前缀（innodb_large_prefix）。

将所有表数据保留在系统表空间或单独的 .ibd文件中通常会对存储管理产生影响。

InnoDB在MySQL 5.7.6中引入了通用表空间[11]，这些表空间也由.ibd文件表示 。通用表空间是使用CREATE TABLESPACE语法创建的共享表空间。它们可以在MySQL数据目录之外创建，能够容纳多个表，并支持所有行格式的表。

3.5.2.独立表空间

MySQL 5.7中，配置参数：innodb_file_per_table，默认处于启用状态，这是一个重要的配置选项，会影响InnoDB文件存储，功能的可用性和I/O特性等。

启用之后，每个表的数据和索引是存放在单独的.ibd文件中的，而不是在系统表空间的共享ibdata文件中。

优点：

• 您可以更加灵活的选择数据压缩 [12] 的行格式，如：

  • 默认情况下（innodb_page_size=16K），前缀索引[13]最多包含768个字节。如果开启innodb_large_prefix，且Innodb表的存储行格式为 DYNAMIC 或 COMPRESSED，则前缀索引最多可包含3072个字节，前缀索引也同样适用；

• TRUNCATE TABLE执行的更快，并且回收的空间不会继续保留，而是让操作系统使用；

• 可以在单独的存储设备上创建表文件表空间数据文件，以进行I / O优化，空间管理或备份。请参见 14.6.1.2 Creating Tables Externally；

缺点：

• 独立表空间中的未使用空间只能由同一个表使用，如果管理不当，会造成空间浪费；
• 多个表需要刷盘，只能执行多次fsync，无法合并多个表的写操作，这可能会导致更多的fsync操作总数；
• mysqld必须为每个表文件空间保留一个打开的文件句柄，如果表数量多，可能会影响性能；
• 每个表都需要自己的数据文件，需要更多的文件描述符；

即使启用了innodb_file_per_table参数，每张表空间存放的只是数据、索引和插入缓存Bitmap页，其他数据如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在原来的共享表空间(系统表空间)中。

3.5.3.通用表空间

通用表空间使用CREATE TABLESPACE语法创建。

类似于系统表空间，通用表空间是共享表空间，可以存储多个表的数据。

通用表空间比独立表空间具有潜在的内存优势，服务器在表空间的生存期内将表空间元数据保留在内存中。一个通用表空间通常可以存放多个表数据，消耗更少的表空间元数据内存。

数据文件可以放置在MySQL数据目录或独立于MySQL数据目录。

3.5.4.UNDO表空间

undo表空间包含undo log。

innodb_rollback_segments变量定义分配给每个撤消表空间的回滚段的数量。

undo log可以存储在一个或多个undo表空间中，而不是系统表空间中。

在默认配置中，撤消日志位于系统表空间中。SSD存储更适合undo log的I/O模式，为此，可以把undo log存放在有别于系统表空间的ssd硬盘中。

innodb_undo_tablespaces 配置选项控制undo表空间的数量。

3.5.5.临时表空间

由用户创建的非压缩临时表和磁盘内部临时表是在共享临时表空间中创建的。

innodb_temp_data_file_path 配置选项指定零时表空间文件的路径，如果未指定，则默认在 innodb_data_home_dir目录中创建一个略大于12MB 的自动扩展数据文件ibtmp1 。

使用ROW_FORMAT=COMPRESSED属性创建的压缩临时表，是在独立表空间中的临时文件目录中创建的 。

服务启动的时候创建临时表空间，关闭的时候销毁临时表空间。如果临时表空间创建失败，则意味着服务启动失败。

3.6.InnoDB底层逻辑存储架构

3.6.1.idb文件组织结构

独立表空间创建的数据都会独立创建一个.idb文件，那么idb文件是如何组织数据的呢？

下图所示，表空间由段（segment）、区（extent）、页（page）组成

InnoDB最小的存储单位是页，为16KB

而InnoDB存储引擎是面向行的(row-oriented)，数据按行进行存放，每个页规定最多允许存放的行数=16k/2 - 200，即7992行。

段：段包括数据段、索引段、回滚段等。InnoDB存储引擎是B+树索引组织的，所以数据即索引，索引即数据。B+树的叶子节点存储的都是数据段的数据

3.6.2.数据页结构

关于Infimun和Supremum：首次创建索引时，InnoDB会在根页面中自动设置一个最小记录和一个最高记录，并且永远不会删除它们。最低记录和最高记录可以视为索引页开销的一部分。最初，它们都存在于根页面上，但是随着索引的增长，最低记录将存在于第一或最低叶子页上，最高记录将出现在最后或最大关键字页上

3.6.3.行记录结构描述

User Records中的Compact行记录格式：，Compact是MySQL5.0引入的，设计目标是高效的存储数据，让一个页能够存放更多的数据，从而实现更快的B+树查找

记录头用于将连续的记录链接在一起，用于行级锁定（行锁）

除了用户定义的列外，每行还有两个隐藏列：

• 6个字节的事务ID列
• 7个字节的回滚指针列
• 如果没有指定主键，还会增加一个6个字节的rowid列

记录头包含信息如下：

3.6.3.1.MySQL中Varchar最大长度是多少

由行记录结构我们可以得知，一个字段最长限制是2^16=65535个字节，这是存储长度的限制

而MySQL中对存储是有限制的，具体参考：8.4.7 Limits on Table Column Count and Row Size

• MySQL对每个表有4096列的硬限制，但是对于给定的表，有效最大值可能会更少

• MySQL最大行的限制为65535字节=64KB，这是逻辑的限制，实际存储时，表的物理最大行大小略小于页面的一半。

  如果一行的长度少于一页的一半，则将所有行都存储在本地页内，如果超过页的一半大小，那么将选择可变长度列用于外部存储，直到该行大小控制在半页之内为止

实际存储的内容还和编码相关，因为一行限制最多存储65535字节，如果采取utf-8编码，那么每个字符最多占用3个字节，也就是最多定义varchar(21845)个字符

3.6.3.2.行记录超过页大小如何存储

如果包含 可变长度列的InnoDB 行超过最大行大小，那么将选择可变长度列用于外部页外存储

可变长度列（VARCHAR， VARBINARY和 BLOB和 TEXT类型）由于太长而无法容纳在B树页面上，这个时候会把可变长度列单独分配在磁盘页面上，这些页面称为溢出页面，这些列称为页外列，页外列的值存储在由溢出页面构成的单链接列表中

InnoDB支持四种行格式（row_format）:REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。不同的格式针对溢出的阈值和处理方式不同

COMPACT行格式处理方式：

最多存储768个字节，其余都存储到溢出页面

使用COMPACT行格式的表将前768个字节的变长列值（VARCHAR， VARBINARY和 BLOB和 TEXT类型）存储在B树节点内的索引记录中，其余的存储在溢出页上。

如果列的值等于或小于768个字节，则不使用溢出页，因此可以节省一些I / O。

如果超过了768个字节，那么会按照如下方式进行存储：

DYNAMIC行格式处理方式：

B+树中只存储指针，不存储实际内容

DYNAMIC行格式提供与COMPACT行格式相同的存储特性，但改进了超长可变长度列的存储能力和支持大索引键前缀。

InnoDB 可以完全在页外存储过长的可变长度列值（针对 VARCHAR（VARCHAR列中的 值是可变长度的字符串。长度可以指定为 0 到 65,535 之间的值。）， VARBINARY和 BLOB和 TEXT类型），而聚集索引记录仅包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段被编码为可变长度字段

表中大字段引发的问题

如果一个表中有过多的可变长度大字段，导致一行记录太长，而整个时候使用的是COMPACT行格式，那么就可能会插入数据报错。

如，页面大小是16k，根据前面描述我们知道，MySQL限制一页最少要存储两行数据，如果很多可变长度大字段，在使用COMPACT的情况下，仍然会把大字段的前面768个字节存在索引页中，可以算出最多支持的大字段：1024 * 16 / 2 / 768 = 10.67，那么超过10个可变长度大字段就会插入失败了。

这个时候可以把row format改为：DYNAMIC。

3.7.索引

为什么MySQL使用B+树

• 哈希表虽然可以提供O(1)的单行数据操作性能，但却不能很好的支持排序和范围查找，会导致全表扫描
• B树可以再非叶子节点存储数据，但这可能会导致查询连续数据的时候增加更多的IO操作，并且排序需要通过中序遍历
• 而B+树数据都存放在叶子节点，叶子节点通过指针相互连接，可以减少顺序遍历时产生的额外随机I/O

3.7.1.聚簇索引

就是主键索引

该B+树中，我们以主键为索引进行构建，并且把完整的记录存到对应的页下面：

其中蓝色的是索引页，橙色的是数据页

每个页大小默认为16k，插入新的数据页时，这个时候需要申请新的数据页，然后挪动部分数据过去，重新调整B+树，这个过程称为分页

相反，如果随着数据减少，索引页的填充因子下降到MERGE_THRESHOLD之下，默认为50%，则InnoDB尝试收缩索引树以释放页

自增主键的插入是递增顺序插入的，每次添加记录都是追加的，不涉及到记录的挪动，不会触发叶子节点的分裂，而一般业务字段做主键，往往都不是有序插入的，写成本比较高，所以我们更倾向于使用自增字段作为主键。

注意事项：

• 表上定义了主键后，会将其作为聚簇索引，建议使用递增
• 没有定义，则MySQL会找到第一个非null值的UNIQUE索引作为聚簇索引
• 上述都不满足的话，InnoDB内部会生成一个隐藏的聚簇索引GEN_CLUST_INDEX，作为行ID，大小为6字节，自增

3.7.2.辅助索引

覆盖索引：不需要回表扫描

最左匹配原则：不仅仅是用到索引的全部定义字段会走索引，只要满足最左前缀，就可以利用索引来加速检索

索引条件下推：使用索引下推的时候，执行计划中的Extra会提示：Using index condition，而不是Using index，因为必须回表查询整行数据。Using index代表使用到了覆盖索引

3.8.InnoDB Data Directory

InnoDB的数据字典存放于系统表空间中，主要包含元数据，用于追踪表、索引、表字段等信息。由于历史的原因，InnoDB数据字典中的元数据与.frm文件中的元数据重复了。

3.9.Doublewrite Buffer

待补充

3.10.Redo Log

重做日志（Redo Log）：主要适用于数据库的崩溃恢复，用于实现数据的完整性，通常是物理日志，记录的是数据页的物理修改，它用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置）

组成：

• 重做日志缓存区Log Buffer
• 重做日志文件，其在磁盘上由两个名为ib_logfile0和log_logfile1的物理文件表示

为了实现数据完整性，将脏盘刷新到磁盘之前，都会先将重做日志写入磁盘。除了数据页，聚簇索引，辅助索引以及undo log都需要记录重做日志

3.10.1.Redo Log在事务中的写入时机

事务中除了需要写入Redo Log，还需要写入binlog

3.10.1.1.Binlog

Binlog：Binary Log，二进制log，二进制日志是一组日志文件，其中包含对MySQL服务器实例进行的数据修改的信息

和Redo Log的区别：

• Redo Log是InnoDB引擎持有的，而binlog是MySQL的Server层实现的，所有引擎都可以使用。
• Redo Log的文件是循环写的，空间会用完。binlog日志是追加写，不会覆盖之前的日志
• binlog主要是用于主从同步和数据恢复，Redo Log主要用于实现事务数据的完整性，让InnoDB具有不会丢失数据的能力

binlog的主要目的：

• 主从同步，主服务器将binlog中发生的事件发送到从服务器，从服务器执行这些事件，以保持和主服务器相同的数据修改
• 数据恢复：某些数据恢复操作需要使用到二进制日志，还原到某个备份点

binlog的两种记录形式：

• 基于SQL的日志记录：事件包含产生数据写操作的SQL语句
• 基于行的日志记录：直接描述对当个行的更改

混合日志默认情况下使用基于SQL的日志记录

3.10.1.2.Redo Log在事务中的写入时机

Redo Log的写入流程如下：

假设我们这里执行一条sql

update t20 set a=10 where id=1;
1

执行流程如下：

3.10.2.如何保证数据不丢失

前面我们介绍Log Buffer的时候，提到过，为了保证数据不丢失，我们需要执行以下操作：

• 如果启用了binlog，则设置：sync_binlog=1;
• innodb_flush_log_at_trx_commit=1;

• sync_binlog=0：表示每次提交事务都只 write，不 fsync；
• sync_binlog=1：表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) ：表示每次提交事务都 write，但累积 N 个事务后才 fsync。

发生作用的时机就是上面所说的流程最后一步，事务提交的结果返回server之前，把binlog cache和log buffer都fsync到磁盘中，这样就保证了数据的落盘，即使崩溃了，也可以通过binlog和redo log进行数据恢复

在磁盘和内存中的处理流程如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7SVxR7gb-1680445610202)(C:/Users/98347/Desktop/%E5%AD%A6%E4%B9%A0%E5%8A%A0%E6%B2%B9%E5%95%8A/redis/%E5%9B%BE%E7%89%87%E8%B5%84%E6%BA%90/image-20200529001256504-a.png-itzhai)]

可以看到数据的写操作流程大致如下：先更新buffer pool，记录日志，提交事务的话就将日志（log buffer、trx cache）fsync入磁盘，buffer pool的脏页写入磁盘，如果没有事务提交的话，那么日志（log buffer、trx cache）何时写入磁盘以及buffer pool写入磁盘的时机又是什么呢？

单条SQL是否涉及到事务呢？

如果启用了自动提交,则将自动执行.除非禁用了自动提交,否则每个语句都是一个事务.（MySQL默认是自动提交的）

第四步log buffer持久化到磁盘的时机：

• log buffer的所占空间即将达到innodb_log_buffer_size一半的时候，后台线程主动写盘
• InnoDB后台有个线程，每隔1s会把log buffer刷到磁盘
• 由于log buffer是所有线程共享的，当其他事务线程提交时会导致已写入log buffer但是还未提交的事务的redo log一起写入磁盘

第五步脏页刷新到磁盘的时机：

• 系统内存不足，需要淘汰脏页的时候
• MySQL空闲的时候
• MySQL正常关闭

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是 75%。

为什么第二步redo log的prepare状态也需要写入磁盘：

为什么第二步 redo log prepare状态也要写磁盘？

因为这里先写了，才能确保在把binlog写到磁盘后崩溃，能够恢复数据：如果判断到redo log是prepare状态，那么查看是否存XID对应的binlog，如果存在，则表示事务成功提交，需要用prepare状态的redo log进行恢复。

这样即使崩溃了，也可以通过redo log来进行恢复了，恢复流程如下：

Redo Log是循环写的，如下图:

• writepos记录了当前写的位置，一边写位置一边往前推进，当writepos与checkpoint重叠的时候就表示logfile写满了，绿色部分表示是空闲的空间，红色部分是写了redo log的空间；
• checkpoint处标识了当前的LSN，每当系统崩溃重启，都会从当前checkpoint这个位置执行重做日志，根据重做日志逐个确认数据页是否没问题，有问题就通过redo log进行修复。

LSN Log Sequence Number的缩写。代表日志序列号。在InnoDB中，LSN占用8个字节，单调递增，LSN的含义：

• 重做日志写入的总量；
• checkpoint的位置；
• 页的版本；

除了重做日志中有LSN，每个页的头部也是有存储了该页的LSN，我们前面介绍页面格式的时候有介绍过。

在页中LSN表示该页最后刷新时LSN的大小。[19]

3.11.Undo Log

如果说redo log记录了事务的行为，可以通过其对页进行重写操作（重试机制？），但是事务有时候需要回滚，此时就需要undo Log了

Undo Log的存储：InnoDB中有回滚段(rollback segment)，每个回滚段记录1024个undo log segment，在每个undo log segment段进行申请undo页，系统表空间偏移量为5的页记录了所有的rollback segment header所在的页。

3.11.1.Undo Log的格式

根据行为不同分为两种：

• insert undo log
• update undo log

insert undo log:

只对事务本身可见，所以insert undo log在事务提交后可直接删除，无需执行purge操作；

log主要记录了：

假设在事务1001中，执行以下sql，t20的table_id为10：

insert into t20(id, a, b, c, d) values(12, 2, 3, 1, "init")
1

那么对应会生成一条undo log：

update undo log：

会影响已存在的记录，为了实现MVCC，update undo log不能在事务提交时立即删除，需要将事务提交时放到history list上，等待purge线程进行最后的删除操作

update undo log主要记录了：

假设在事务1002中，执行以下sql，t20的table_id为10：

update t20 set d="update1" where id=60;
1

那么对应会生成一条undo log：

下面我们在执行一个delete sql：

delete from t20 where id=60;
1

对应的undo log变为如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gcATVYYD-1680445610206)(C:/Users/98347/Desktop/%E5%AD%A6%E4%B9%A0%E5%8A%A0%E6%B2%B9%E5%95%8A/redis/%E5%9B%BE%E7%89%87%E8%B5%84%E6%BA%90/image-20200530172640974-a.png-itzhai)]

如上图，实际的行记录不会立刻删除，而是在行记录头信息记录了一个deleted_flag标志位。最终会在purge线程purge undo log的时候进行实际的删除操作，这个时候undo log也会清理掉。

总结

• 数据完整性依靠：redo log
• 事务隔离级别的实现依靠MVCC，MVCC依靠undo log实现
• IO性能提升方式：buffer pool加快查询效率和普通索引更新的效率，log buffer对日志写的性能提升
• 查询性能提升依赖于索引，底层用页存储，字段越小页存储越多行记录，查询效率越快；自增字段作为聚集索引可以加快插入操作；
• 故障恢复：双写缓冲区、redo log
• 主从同步：binlog