Mysql 5.X版本整体介绍

Mysql 8.X版本和 5.X版本相比，有比较大的调整。目前工作中应用最广的是Mysql 5.6/5.7的版本，所以本系列文章将聚焦于对Mysql 5.6/5.7版本进行介绍，8.X版本的相关改动，有机会再单独整理。

一、Mysql体系结构

Mysql是一个单进程多线程、基于C/S架构的关系型数据库管理系统，其体系结构如图1所示 (图片摘自Mysql官方手册，5.6+的版本进行了少许调整，总体上仍是准确的)，虚线标出的查询缓存和缓冲区部分在Mysql 8.X版本中已废弃，侧面反映了数据库的查询缓存实际上比较鸡肋。其中，“Connectors”是指提供连接能力的各种组件和API，我们可以简单地理解为各种客户端，“Server”代表服务端实例。

图1 Mysql体系结构

1.1 C/S通信协议相关内容

在服务器启动的过程中，创建了套接字且绑定了端口，然后在handle_connections_methods()函数中完成监听，监听其实就是等待用户的连接请求。这个函数中会处理三种连接方式：命名管道、套接字及共享内存。一般情况下，都是用套接字的连接方式，其他连接方式只有在一定条件下才能使用，所以本文只介绍套接字相关的部分。

MySQL客户端进程和服务器进程的Connection Pool之间是基于Mysql Client/Server协议[1]进行通讯的，该协议用于连接、代理、主备复制等操作，支持 SSL、压缩等特性。

Mysql C/S协议的内涵比较丰富，它规定了连接的各个生命周期的数据格式和行为，包括：各数据类型的编码格式、各种操作的请求数据包的格式、结果返回包的格式、字符集、数据包压缩和数据包TLS加密等等。

连接的生命周期包括三部分内容：连接阶段 (Connection Phase)、请求阶段 (Command Phase) 和拷贝协议 (Replication Protocol)。其中，拷贝协议比较独立，主要用于主从节点间的binlog同步。其大体流程是：建立连接后，Client向Server发送一个MySQL binlog dump的命令，随后，Server不断地给Client发送binlog event。本文不再详细介绍，如果希望进一步了解，推荐查看go-mysql-elasticsearch作者写的这篇文章：深入解析MySQL replication协议[3]。下面我们重点介绍一下连接阶段和请求阶段。

1.1.1 连接阶段(Connection Phase)通信

1.协议数据包
先简单介绍Mysql协议的数据包(packet)[2]，Mysql的数据包均由header和body两部分构成：header总共有4个字节，3个字节用来标识 payload 的大小，1个字节记录sequence ID，来保证交互时报文的顺序；body则保存实际的负载数据(payload)。

+--------------------+--------------+---------------------------------------------------+
|      3 Bytes       |    1 Byte    |                   N Bytes                         |
+--------------------+--------------+---------------------------------------------------+
|<= length of body =>|<= sequence =>|<==================== data =======================>|
|<============= header ============>|<==================== body =======================>|

这是Mysql最基础的数据包结构，所有具体操作的数据包都是基于这个数据包结构进行扩展的，即不同的操作对应不同的负载数据，具体的载荷内部结构可以自行查阅官网。此外，如果数据包的大小大于16MB，会被拆包。

2.工作流程
Mysql的连接阶段主要包含"握手"和"认证"两个步骤,具体参考图2。

图2 Mysql连接阶段时序图

3.握手
Mysql版本众多，客户端和服务端版本不一定匹配，二者支持的功能需要有个协商的过程，这就是“握手”部分的主要工作。协商时最主要依赖的是capabilities flags，这是一种功能标志，具体内容参考图3，包括是否支持压缩功能、是否支持CLIENT_PROTOCOL_41版本协议、是否使用SSL加密等等。

图3 Mysql capabilities flags介绍[4]

4.认证
“握手”完成后，执行“认证”操作。MySQL的认证授权采用插件方式实现，默认采用mysql_native_password 插件[5]。该插件的核心逻辑在函数scramble()中，该函数通过用户输入的password、服务器返回的scramble生成reply，返回给服务端；服务端再与数据库信息或缓存信息进行比对。

MySQL 的用户管理模块信息存储在系统表mysql.user中，其中包括了授权用户的基本信息以及一些权限信息。在登陆时，只会用到 host、user、passwd 三个字段。服务器在收到新的连接请求时，会调用 login_connection() 方法进行身份验证，先根据 IP 做 ACL 检查，然后才进入用户名密码验证阶段。

下面简单对user、db、table_priv和columns_priv这几张系统表的含义进行说明。其中，user表是全局级的权限和信息，修改后可能发生对已有连接不生效的情况。

user：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db：记录各个帐号在各个数据库上的操作权限。
table_priv：记录数据表级的操作权限。
columns_priv：记录数据列级的操作权限。

Mysql这么处理，是为了保证效率，它在每个连接的内存里缓存了user表中的用户信息和权限信息。直接修改磁盘中user表的用户密码、权限等信息，是不会马上同步至内存的。要解决这个问题，只需执行“flush privileges;”即可，该操作的实质是：将系统表mysql.user中的用户信息和权限设置从磁盘中重新获取，并缓存到内存数组里。

5.线程缓存和线程池
在5.6版本以前，Mysql处理连接的方式是One-Connection-Per-Thread，即对于每一个数据库连接，Server端都会创建一个独立的线程服务。不过，Mysql也提供了thread-cache机制来缓解频繁创建和释放线程的问题(由图1中“Connection pool”内的“Thread Reuse”子模块实现)，我们可以将线程缓存起来，以供下次使用，但是它仍然无法解决高连接数的问题。

从5.6版本开始，Mysql在服务端提供的一个重要功能：线程池。线程池是一种有效的技术，通过预先创建一定数量的线程，当有请求达到时，线程池分配一个线程提供服务，请求结束后，该线程又去服务其他请求或者进入睡眠状态。

线程池和线程缓存方案的区别在于：线程池方案每个线程服务的最小单位是SQL语句，即一个线程可以对应多个活跃的连接。它避免了线程大量创建销毁带来的性能消耗，同时解决了高连接数引起的高内存消耗问题。

1.1.2 请求阶段(Command Phase)通信

如果只关注客户端和服务端的通信过程，其实非常简单：客户端通过建立好的连接向服务端发送数据包，服务端接收到数据包后，解析并按照客户端要求去执行命令，然后将结果集返回至客户端。

但Mysql的C/S协议关于请求阶段的内容其实非常丰富，因为它既包含了客户端和服务端之间的通信部分，也包含了服务端内部交互的命令的相关规定。总体而言，C/S协议为请求阶段规定了4个方面内容：文本协议 (Text Protocol)、通用命令 (Utility Commands)、预编译语句 (Prepared Statements)、存储程序 (Stored Programs)[6]。

1.文本协议(Text Protocol)
只包含COM_QUERY命令，该命令规定了客户端的请求数据格式和服务端返回的数据格式。

客户端请求的格式比较简单，下面是其payload的基本结构 (某些条件下可能包含其他内容)，其中“string[EOF]”表示字符串在payload的最后，它的特点是字符串的长度可以通过包的大小和当前初始位置计算出来[7]：

+--------------------+--------------------+--------------------------------------------------+
|       类型          |        内容        |                      说明                         |
+--------------------+--------------------+--------------------------------------------------+
|     int<1>         |        0x03        |            0x03表示当前操作是COM_QUERY             |
|    string[EOF]     |  the query string  |         希望立即执行的sql内容(不包含最后的英文分号)    |

图4 COM_QUERY响应结果示意图[1]

服务端返回的数据格式相对复杂，参考图4。可以看出返回包含4种情况：
1). 如果执行错误，比如SQL语法错误，返回ERR包，即0xFF标志；
2). 如果执行成功，但没有查到任何数据，返回OK包，即0x00标志；
3). 如果客户端的执行导入数据操作 (load data local infile ‘filename’ into table )，则返回方式遵循"本地文件导入"子协议 (Protocol::LOCAL_INFILE_Request)。协议的定义参考图5：服务端收到请求后，将0xfb标志和文件名返回给客户端，表示希望获取文件数据(GET_MORE_DATA)，客户端收到后发送文件数据 (SEND_MORE_DATA)，结束后服务端返回OK包或者ERR包；

图5 LOCAL_INFILE_REQUEST子协议的交互过程[7]

4). 其他情况服务端将遵循“结果集"子协议(ProtocolText::Resultset)：结果集的第一包含有列长度信息(FIELD_COUNT，LengthEncodedInteger类型，存储列的个数)；接下来是n个字段描述包，每一个字段描述就是一个包(Protocol::ColumnDefinition)；当所有字段信息描述包发送完成后，会发送一个EOF包或OK包(5.7版本之前如果没有设置CLIENT_DEPRECATE_EOF标记，会发送EOF包，5.7后均返回OK包)作为字段定义与数据(Row)的分隔符号；接下来是行数据包(Protocol::ColumnDefinition)，每一行一个数据包；最后的结束包，同样可能是EOF或OK包，参考图4"Text Resultset"部分。

注：EOF数据包的格式比较简单，它主要用于标明查询结果的结尾，包内带有两个信息：警告数和状态码。在5.7.5版本之后，已被改进版的OK数据包替代

2.通用命令(Utility Commands)
通用命令包括：COM_QUIT、COM_INIT_DB、COM_FIELD_LIST、COM_REFRESH、COM_STATISTICS、COM_PROCESS_INFO、COM_PROCESS_KILL、COM_DEBUG、COM_PING、COM_CHANGE_USER、COM_RESET_CONNECTION、COM_SET_OPTION等。其中，COM_REFRESH、COM_PROCESS_INFO、COM_PROCESS_KILL等已被标志为deprecated，可能在较新的版本中会被替换成其他的命令来实现。

下面对常用的几个命令进行简单的说明：COM_QUIT用于断开和服务端的连接；COM_PING用来测试服务端是否处于alive状态；COM_CHANGE_USER用于切换当前连接的用户信息，会执行与握手阶段类似的认证操作流程并重置连接的状态。

3.预编译语句(Prepared Statements)
预编译是Mysql在4.1版本之后引入的，它包括两大步骤：
1). 客户端提交不传递真实的参数值的SQL，服务端完成预编译解析，这里的预编译解析包括解析参数的个数、类型等，然后响应给客户端；

//不传递真实参数值的SQL的示例
insert into sys_user(id, name) values(?, ?);
select * from `sys_user` where id = ?;

2). 客户端第二次传数据时，只传递参数，服务端收到后就可以完成一个完整的SQL的执行，并返回结果。

相比4.1版本之前的SQL执行方式，预编译方式需要两次交互，才能完成一次SQL执行，表面看增加了步骤，实际上由于预编译相关命令的请求参数和返回的结果集，均采用二进制格式来封装数据 (ProtocolBinary::Resultset)，体积更小，更面向底层，能直接被Mysql服务端利用，实际上是预编译是非常高效的。并且预编译一次编译、多次运行，节省了重复解析优化的时间，同时还能一定程度上防止sql注入。

预编译阶段相关的命令主要有COM_STMT_PREPARE、COM_STMT_EXECUTE、COM_STMT_FETCH、COM_STMT_CLOSE、COM_STMT_RESET、COM_STMT_SEND_LONG_DATA。其中最重要的是COM_STMT_PREPARE和COM_STMT_EXECUTE：COM_STMT_PREPARE命令用于客户端往服务端提交一个不传递真实的参数值的待预编译的SQL；COM_STMT_EXECUTE用于服务端接收参数，并执行预编译后的SQL。

4.存储程序(Stored Programs)
从5.0版本开始，关于存储程序的协议内容包括两个部分：多结果集协议(Multi-Resultset)和多语句协议(Multi-Statement)，分别规定了服务器端如何存储多结果集和如何处理多重查询。

其中，多结果集协议 (Multi-Resultset)主要用于多语句或存储过程等场景。多结果集是由基本的结果集结构拼接而成，如果第一个结果集中的标识行中的status设置了SERVER_MORE_RESULTS_EXISTS标记位，表明是多结果集，其它结果集紧随其后。
对于多语句协议 (Multi-Statement)，本文不展开解释，仅介绍两个工作中常见的应用场景，方便大家理解。

1). 在各种Mysql的客户端程序中 (Navicat、SQLyog或者Mysql自带客户端等)，我们都可以一次执行多条sql，中间用英文逗号隔开即可，就是利用了Multi-Statement功能。

2). 在Java的数据库连接配中，如果设置了allowMultiQueries=true选项，则可以在一个Mybatis代码块中写入多个sql语句，语句间使用英文分号隔开即可。

url: jdbc:mysql://xxx.com:3306/test_db?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8

1.2 SQL执行过程[8]

SQL执行过程分为两类：
1). 查询语句执行过程如下：查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
2). 更新语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit

图6以Mysql Update语句为例，对SQL执行流程进行了比较清晰的展示，其他操作类型流程上类似。连接阶段在上文已经较为深入的介绍过，不再赘述。下面将聚焦在SQL的执行阶段，按顺序对执行阶段关键的步骤展开介绍。

图6 Mysql Update语句执行流程图[8]

1.2.1 缓存

Mysql的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端 。由于查询缓存的失效非常频繁，如果在一个写多读少的环境中,缓存会频繁的新增和失效，优势并不明显。目前在5.6+版本中已经默认关闭了，且在8.0+版本中，缓存模块已被删除。

1.2.2 分析器

分析器的主要作用是将客户端发过来的SQL语句进行分析，这将包括预编译与解析过程。这个阶段包括两个步骤：第一步是词法分析，第二步是语法分析。

分析器会解析SQL语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等。如果分析到语法错误，会直接给客户端抛出异常:You have an error in your SQL syntax。

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来，Mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验：比如校验当前数据库是否存在user表，同时假如user表中不存在userId这个字段同样会报错：unknown column in field list。

1.2.3 优化器

能够进入到优化器阶段表示SQL是符合Mysql的标准语义规则的并且可以执行的。此阶段主要是进行SQL语句的优化，会根据执行计划进行最优的选择，匹配合适的索引，选择最佳的执行方案。

比如一个典型的例子是这样的：已知表T对A、B、C列建立联合索引，在进行查询的时候，SQL是：select xx where B=x and A=x and C=x。很多人会以为是用不到索引的，但其实会用到，虽然索引必须符合最左原则才能使用，但是本质上，优化器会自动将这条SQL优化为：where A=x and B=x and C=X，这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理，mysql会计算各个执行方法的最佳时间，最终确定一条执行的sql交给最后的执行器。

1.2.4 执行器

在执行器的阶段，会调用存储引擎的API，API会触发存储引擎执行实际逻辑，并返回结果。Mysql支持的引擎可参图7，其中，最常用的是InnoDB。

图7 Mysql 引擎特性示意图[8]

二、InnoDB介绍[9,10,11]

InnoDB是Mysql取得成功的最关键的引擎，其重要性不言而喻，下面将单独对该引擎的核心特性进行介绍，其他引擎如MyISAM、MEMORY、NDB等，本文不作说明。

1. 这部分内容主要参考Mysql官方文档、《Mysql技术内幕：InnoDB存储引擎（第2版）》和《MySQL运维内参：MySQL、Galera、Inception核心原理与最佳实践》
2. 本文主要从宏观角度整体性地对InnoDB进行介绍，索引、事务、锁等具体的知识点在后续文章单独介绍

2.1 InnoDB体系架构和关键技术

在MySQL 5.1中，可以支持两个版本的InnoDB，一个是静态编译的InnoDB版本，可将其视为老版本的InnoDB；另一个是动态加载的InnoDB版本，官方称为InnoDB Plugin，可将其视为InnoDB 1.0.x版本。MySQL 5.5版本中又将InnoDB的版本升级到了1.1.x，InnoDB Plugin成为历史。在MySQL 5.6版本中，InnoDB的版本又升级为1.2.x，这也是本文即将展开介绍的版本。表1显示了各个版本中InnoDB存储引擎的功能。

表1 InnoDB各版本功能对比

Mysql版本	InnoDB版本	功能描述
5.1-5.4	老版本InnoDB	支持ACID、行锁设计、MVCC
5.1-5.4(插件形式)	InnoDB Plugin(1.0.x)	继承上版本InnoDB所有功能，增加compress和dynamic页格式
5.5	InnoDB 1.1.x	继承上版本InnoDB所有功能，增加Linux AIO、多回滚段
5.6	InnoDB 1.2.x	继承上版本InnoDB所有功能，增加全文索引、在线索引添加

2.1.1 InnoDB体系架构 (5.7版本)

本文第一部分对Mysql数据库整体的体系架构进行了介绍，这里将继续深入介绍InnoDB内部的架构设计，参考图8。

图8 InnoDB引擎官方架构设计图[11]

InnoDB内部主要包括内存架构和硬盘存储架构两部分，打通这两部分的工作主要由后台线程来完成。下面分别对三者进行介绍，同时对Checkpoint机制进行简单说明。

2.1.2 内存架构

InnoDB的内存架构主要包括4个概念：缓存池 (Buffer Pool)、更新缓冲 (Change Buffer)、自适应哈希 (Adaptive Hash Index)、日志缓冲 (Log Buffer)。

2.1.2.1 缓存池(Buffer Pool)

缓存池是主内存中的一块区域，在专用的MySQL服务器上，通常会将最多80％的物理内存分配给它。缓存池允许直接从内存中处理经常使用的数据，从而加快处理速度。

Mysql缓存池存储的内容主要有：索引页、数据页、undo页、Change Buffer、自适应哈希索引、InnoDB存储的锁信息 (lock info)、数据字典信息 (data dictionary) 等，其中最主要的内容是索引页和数据页。

Mysql缓存池方面的设计思想主要体现在三个方面：1). 在为了提高大容量的读操作的效率，Mysql将缓冲池划分为可以容纳多行的数据页；2). 为了提高缓存管理的效率，将缓冲池实现为数据页的链表；3). 很少访问的数据使用经过变体的LRU算法从缓存中淘汰。下面介绍一下缓存池的LRU算法实现细节，参考图9。

图9 Buffer Pool数据页链表的官方示意图[11]

当需要新的空间将数据页添加到缓冲池时，最近最少使用的数据页将被移除，并将新数据页添加到数据页链表的“中点” (注意，并不是指正中心位置)。 这个“中间插入策略”将列表视为两个子列表：靠近表头的一部分为young区，这里的数据页是最近访问过的；靠近表尾的一部分为old区，这里的数据页是最近较少使用的。默认情况下，算法按照下面的规则运行：
1). 链表的3/8被设置为old区；
2). “中点”并不是指不是链表的中间点，而是young区的表尾节点和old区的表头节点中间的位置；
3). 当读取的数据不在缓冲池里的时候，读取到的数据页需要插入到链表中，插入点为“中点” 。但是插入的新节点被看作old区的节点，如果此时old区满了，则移除链表尾部的数据页；
4). 当读取old区的数据页时，该节点将变成young节点：此节点移动到young区的表头位置
5). 这样一来，随着数据库不断操作，在young区中的未被访问的节点将逐渐往表尾移动，当移动过“中点” ，则变为old区的节点。old区中未被访问的节点也会随着往表尾移动，当链表满时，表尾那个数据页会被淘汰。

2.1.2.2 更新缓冲区(Change Buffer)

在内存架构里，更改缓冲区本质是一块内存空间，同时以一种特殊的数据结构组织，只用来存储辅助索引页 (也就是说，不会作用于主键索引、全文索引、空间索引)。其基本的工作原理是：1). 当辅助索引页不在缓存池 (Buffer Pool) 中时，会先将对辅助索引页的更改保存至到Change Buffer内，如果已经在缓存池内，当然是直接更新缓存池内的数据页；2). 对Change Buffer数据页的修改操作(INSERT、UPDATE或DELETE)合并更新至缓存池里对应的数据页的时机是：有其他读操作将数据页加载到缓冲池时，可参考图10。

图10 Change Buffer工作原理示意图[11]

为什么需要更新缓冲区呢？因为与聚集索引不同，辅助索引通常是非惟一的，并且插入辅助索引的顺序相对随机，同时，删除和更新也可能影响索引树中实际并不相邻的辅助索引页，直白的说，会引起随机I/O访问。引入更新缓冲区可以大大减少随机I/O的次数，从而提高效率。

需要特别注意的是，更新缓冲区同时存在于内存架构里面的缓存池内和硬盘存储架构里面的系统表空间内。在硬盘存储架构里，更新缓冲区主要用于在服务器关闭时，缓存对辅助索引页的更新。

更新缓冲区是一个非常重要的概念，如果还有疑问，可以阅读官方FAQ：MySQL 5.7 FAQ: InnoDB Change Buffer[12]

2.1.2.3 自适应哈希(Adaptive Hash Index, AHI)

哈希(hash)是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为O(1)，即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3～4层，故需要3～4次的查询。

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引。它是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。

InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引，这个过程完全是引擎自主控制的，没有提供参数让用户进行配置。

2.1.2.4 日志缓冲(Log Buffer)

日志缓冲区用于在内存中暂存要写入磁盘日志文件的数据，日志缓冲区的内容会定期刷新到磁盘。日志缓冲区大小由innodb_log_buffer_size变量定义，默认大小为16MB。在日志缓冲区空间比较大的情况下，允许运行大型事务时，可以不需要在事务提交之前将重做日志 (redo log) 数据写入磁盘。因此，如果有更新、插入或删除许多行的事务，那么增加日志缓冲区的大小可以节省磁盘I/O。

2.1.3 硬盘存储架构

在Mysql 5.7版本中，硬盘存储架构包括5个表空间和重做日志文件 (redo log) 。5个表空间分别是：系统表空间 (The System Tablespace)、独立表空间 (File-Per-Table Tablespaces)、通用表空间 (General Tablespaces)、Undo表空间、临时表空间 (The Temporary Tablespace)。

重做日志：在服务端正常运行期间，对SQL语句或低级API调用产生的更改表数据的请求进行编码，并写入磁盘中的重做日志，并用于在崩溃恢复期间纠正不完整事务写入的数据。

系统表空间中包含4个部分：InnoDB数据目录 (the InnoDB data dictionary)、两次写缓存 (the doublewrite buffer)、更新缓存区(the change buffer)和回滚日志 (undo logs)。默认情况下，系统表空间的数据文件名为ibdata1，不过InnoDB提供了配置的方式来设置数据文件的位置、大小甚至文件的个数。

独立表空间用来存储一张单独的逻辑表的数据和索引，所有数据存储于一个单独的磁盘文件中，文件名的后缀是.ibd。

通用表空间可以存储多张逻辑表的数据和索引。创建通用表空间后，有两种方式把表保存于通用表空间：
1). 创建的方式：CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
2). 更新的方式：ALTER TABLE t2 TABLESPACE ts1;

Undo表空间用来存储回滚日志。其实回滚日志默认是存储于系统表空间的，但它也可以存储于一个或多个Undo表空间。Undo表空间存在的优势是：1). 减少系统表空间中存储回滚日志所需的空间；2). Undo表空间如果单独采取SSD存储，也与回滚日志的I/O模式相匹配，Undo表空间的存在使整体的灵活性更高。

临时表空间也是一个共享的表空间，用于存储所有非压缩的由用户创建的临时表和内部的查询临时表，压缩的临时表则存储于独立表空间中。

2.1.4 后台线程(InnoDB 1.2.x)

Mysql后台线程包括4种：Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。

2.1.4.1 Master Thread

不同版本InnoDB的Master Thread并不相同，本文仅介绍1.2.x版本的工作内容和原理。
Master Thread的伪代码如下：

if InnoDB is idle
srv_master_do_idle_tasks();
else
srv_master_do_active_tasks();

srv_master_do_idle_tasks()即之前版本中每10秒执行的操作：1). 刷新100个脏页到磁盘 (可能)；2). 合并同步更新缓冲区的数据至缓存池 (总是)；3). 将日志缓冲刷新到磁盘 (总是)；4). 删除无用的Undo页 (总是)。

srv_master_do_active_tasks()即之前版本中每秒执行的操作：1). 日志缓冲刷新到磁盘，即使这个事务还没有提交 (总是)；2). 合并同步更新缓冲区数据至缓存池 (可能)；3). 如果当前没有用户活动，则切换到background loop(可能)。

扩展：InnoDB 1.2.x之前的版本中，master thread还负责刷新脏页的操作，从1.2.x版本开始，Mysql将这一工作分离给单独的Page Cleaner Thread，减轻master thread工作，提高系统并发性。

2.1.4.2 IO Thread

在InnoDB存储引擎中大量使用了AIO (Async IO) 来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调 (call back) 处理。

2.1.4.3 Purge Thread

事务被提交后，其所使用的Undo log可能不再需要，由PurgeThread来承担回收已经使用并分配的Undo页的工作。

从InnoDB 1.2.x版本开始，InnoDB支持多个Purge Thread，加快了Undo页的回收。同时由于Purge Thread需要离散地读取Undo页，采用多个Purge Thread也能更进一步利用磁盘的随机读取性能。

2.1.4.4 Page Cleaner Thread

Page Cleaner Thread的作用是：将之前版本中由master thread执行的脏页的刷新操作都放入到单独的线程中来完成，提高性能。

2.1.5 Checkpoint机制

Checkpoint机制用于脏页的刷新，该机制InnoDB存储引擎通过LSN (Log Sequence Number) 标志redo log来实现，InnoDB存储引擎通过LSN (Log Sequence Number) 来标记版本的。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘；当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。

该机制原理简单，但在InnoDB存储引擎中，Checkpoint发生的时间、条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘。不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint。发挥的作用非常大，解决了内存容量瓶颈，并大大缩短了数据库的平均故障恢复时间。

InnoDB会在四种情况下会触发Checkpoint机制：master thead的定时刷新、LRU列表中没有足够的空闲页时 (脏页太多时)、redo log不可用时 (async/sync flush checkpoint)、数据库关闭时。

Checkpoint有两种工作模式：刷新所有脏页 (Sharp Checkpoint) 和刷新部分脏页 (Fuzzy Checkpoint)。一般情况下使用刷新部分脏页模式，只有数据库关闭且设置了innodb_fast_shutdown=1时，才会刷新所有脏页回磁盘的模式。

2.2 InnoDB其他特性

上面介绍了InnoDB的更新缓冲 (Change Buffer)、自适应哈希 (Adaptive Hash Index) 等特性的原理和作用，除此之外，还有一些重要的特性可以关注。

2.2.1 两次写(Double Write)

插入缓冲可以带来性能上的提升，两次写特性则可以提高InnoDB引擎数据页的可靠性。

当发生数据库宕机时，可能InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，比如16KB的页，只写了前4KB，之后就发生了宕机，这种情况被称为部分写失效(partial page write)。

如果发生写失效，可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到，重做日志中记录的是对页的物理操作，如偏移量800，写’aaaa’记录。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。这就是说，在重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite的，其体系架构设计参考图8。

图7 doublewrite架构[9]

doublewrite由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个区 (extent)，大小同样为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。

在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite
buffer中的页写入各个表空间文件中，此时的写入则是离散的

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

2.2.2 异步IO(Async IO)

如果使用同步IO，用户发出一条索引扫描的查询，这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作。每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作，效率会非常低。

如果Mysql采用AIO模式，则在发出一个IO请求后，会立即发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成即可，效率能得到很大的提高。此外，使用AIO模式，还可以进行IO Merge操作，也就是将多个IO操作合并为1个IO操作，这样可以提高IOPS的性能。

在InnoDB1.1.x之前，AIO的实现通过InnoDB存储引擎中的代码来模拟实现。而从InnoDB 1.1.x开始（InnoDB Plugin不支持），提供了内核级别AIO的支持，称为Native AIO。官方的测试显示，启用Native AIO，恢复速度可以提高75%。

2.2.3 刷新邻接页(Flush Neighbor Page)

刷新邻接页的工作原理为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。

但是需要考虑到下面两个问题：1). 是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页；2). 固态硬盘有着较高的IOPS，是否还需要这个特性。

因此，InnoDB存储引擎从1.2.x版本开始提供了参数innodb_flush_neighbors，用来控制是否启用该特性。对于传统机械硬盘建议启用该特性，而对于固态硬盘有着超高IOPS性能的磁盘，则建议将该参数设置为0，即关闭此特性。

三、总结

本文分为两大部分，第一部分主要着眼于Mysql的体系结构、通信协议和工作流程；第二部分重点介绍Mysql最重要的存储引擎InnoDB, 对其核心特性进行概要性的解析。

需要注意的是，Mysql8.X相对于Mysql5.X，有非常多的改动，新增了非常多功能和属性，同时也修改或删除了许多功能特性，具体可以参考官方文档对应版本的release文档。在此，强烈建议如果要从Mysql5.X切换至Mysql8.X，一定要提前了解改动的详细信息，避免踩坑。

参考文献：

[1] https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_PROTOCOL.html
[2] https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_packets.html#sect_protocol_basic_packets_packet
[3] https://www.jianshu.com/p/5e6b33d8945f
[4] https://cloud.tencent.com/developer/article/1768901
[5] https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_authentication_methods.html
[6] https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_command_phase.html
[7] https://dev.mysql.com/doc/internals/en/
[8] https://www.cnblogs.com/wyq178/p/11576065.html
[9] Mysql技术内幕：InnoDB存储引擎 (第2版). 姜承尧
[10] MySQL运维内参：MySQL、Galera、Inception核心原理与最佳实践. 周彦伟，王竹峰，强昌金
[11] https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
[12] https://dev.mysql.com/doc/refman/5.7/en/faqs-innodb-change-buffer.html