mysql 5.7 io 性能 aio_深入理解MySQL的InnoDB引擎

在MySQL中的引擎一文中说了，我们在几乎所有的情况下其实用的都是InnoDB引擎，这里我们就重点再看一下这个引擎，包括他的存储结构，线程模型和数据文件。

我们可以通过show engine innodb status \G;(\G只是表示输出结果纵向表格输出)命令可以查看innodb引擎的的一些基本信息，如下图：

image.png

image.png

上图中可以看到有线程、事务、文件IO，缓冲区，内存，日志等等一些信息，我们把这些信息分为存储结构、线程模型和数据文件三类，以下分别说明一下。

存储结构

InnoDB的存储引擎，其结构从大的划分来讲分为内存区域和磁盘区域两部分：

image.png

InnoDB的内存区域

内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件，这里分别看一下：

1. Buffer Pool(缓冲池，简称BP)

BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率。以下我们看一下BP的页管理，内存的淘汰算法LRU以及缓冲池的一些配置参数：

1)Page管理机制

Page根据状态可以分为三种类型：

free page ： 空闲page，未被使用

clean page：被使用page，数据没有被修改过

dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

针对上述三种page类型，InnoDB通过三种链表结构来维护和管理：

free list ：表示空闲缓冲区，管理free page

flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页数据既存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作。

lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。

2)改进型LRU算法维护

普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰。

改进型LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。

每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

3)Buffer Pool配置参数

show variables like '%innodb_page_size%'; //查看page页大小

show variables like '%innodb_old%'; //查看lru list中old列表参数

show variables like '%innodb_buffer%'; //查看buffer pool参数

建议：将innodb_buffer_pool_size设置为总内存大小的60%-80%，

innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。

2. Change Buffer(写缓冲区，简称CB)

在进行DML操作时，如果BP没有相应的Page数据，并不会立刻将磁盘加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以通过参数innodb_change_buffer_max_size设置，具体调多大要根据读写业务量。

当更新一条记录时，如果该记录在BufferPool存在，就直接在BufferPool修改，也就是一次内存操作。如果该记录在BufferPool不存在(没有命中)，会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进行磁盘读取，然后再从ChangeBuffer中读取信息合并，最终载入BufferPool中。

这里要注意写缓冲区只适用于非唯一普通索引页。如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，这样就要做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作。

3. Adaptive Hash Index(自适应哈希索引)

用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

4. Log Buffer(日志缓冲区)

日志缓冲区用来保存要写入磁盘上log文件(Redo/Undo)的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。

LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。

LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少磁盘IO频率。

innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1

0 ： 每隔1秒写日志文件和刷盘操作(写日志文件LogBuffer-->OS cache，刷盘OS

cache-->磁盘文件)，最多丢失1秒数据

1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作

2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

InnoDB的磁盘区域

InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log和Undo Logs，这里我们看一下前面三个区域，后面两个我们在MySQL日志的学习中再讨论。

1. 表空间(Tablespaces)

表空间用于存储表结构和数据。表空间又分为系统表空间、独立表空间、通用表空间、临时表空间、Undo表空间等多种类型；

1.1 系统表空间(The System Tablespace)

包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。

该空间的数据文件通过参数innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)。

1.2 独立表空间(File-Per-Table Tablespaces)

独立表空间是一个单表表空间，默认是开启的，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表空间中。否则，innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表空间的表文件支持动态(dynamic)和压缩(commpressed)行格式。

1.3通用表空间(General Tablespaces)

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空间ts1

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间

1.3 撤销表空间(Undo Tablespaces)

撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。

InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0。参数值为0表示使用系统表空间ibdata1;大于0表示使用undo表空间undo_001、undo_002等。

1.4 临时表空间(Temporary Tablespaces)

CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空间ts1

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间

分为session temporary tablespaces 和global temporary tablespace两种。session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace储存用户临时表的回滚段(rollback segments )。mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

2. 数据字典(InnoDB Data Dictionary)

InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。

元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件(.frm文件)中存储的信息重叠。

3. 双写缓冲区(Doublewrite Buffer)

双写缓冲区位于系统表空间，是一个存储区域。

在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。

如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个好备份。

如果要禁用Doublewrite 缓冲区，可以将innodb_doublewrite设置为0。

在大多数情况下，默认情况下启用双写缓冲区，使用Doublewrite 缓冲区时，建议将innodb_flush_method设置为O_DIRECT。这里的MySQL的innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式。他有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。设置O_DIRECT表示数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从Innodb Buffer写到磁盘文件。

默认的fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文件与redo log的缓存信息。

新版本存储结构的改变

MySQL在5.7和后续的8.0版本中的存储结构，做了一定改变，如下图所示：

image.png

MySQL 5.7 版本

1)将 Undo日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用户自行指定文件大小和数量。

2)增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据。

3)Buffer Pool 大小可以动态修改，无需重启数据库实例。

MySQL 8.0 版本

1)将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了，以前需要ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。

2)emporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建索引，这样加快了处理的速度。

3)用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。

4)将Doublewrite Buffer从共享表空间ibdata中也分离出来了。

线程模型

InnoDB的线程模型分为IO Thread、Purge Thread、Page Thread和Master Thread，如下图所示:

image.png

IO Thread

在InnoDB中使用了大量的AIO(Async IO)来做读写处理，这样可以极大提高数据库的性能。在InnoDB1.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 Thread

事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo页。

show variables like '%innodb_purge_threads%';

Page Cleaner Thread

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

show variables like '%innodb_page_cleaners%';

Master Thread

Master thread是InnoDB的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘 ，保证数据的一致性。

这些工作包含：脏页的刷新(page cleaner thread)、undo页回收(purge thread)、redo日志刷新(log thread)、合并写缓冲等。在其内部有两个主去处理，分别是每隔1秒和10秒处理。

每1秒的操作：

刷新日志缓冲区，刷到磁盘

合并写缓冲区数据，根据IO读写压力来决定是否操作

刷新脏页数据到磁盘，根据脏页比例达到75%才操作(innodb_max_dirty_pages_pct，innodb_io_capacity)

每10秒的操作：

刷新脏页数据到磁盘

合并写缓冲区数据

刷新日志缓冲区

删除无用的undo页

数据文件

InnoDB数据文件存储结构

分为一个ibd数据文件-->Segment(段)-->Extent(区)-->Page(页)-->Row(行)

image.png

1. Tablesapce(表空间)

表空间用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。

2. Segment(段)

段用于管理多个Extent，分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment)。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。

3. Extent(区)

一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会一页一页分，直接分配一个区。

4 Page(页)

页用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。

Page是文件最基本的单位，无论何种类型的page，都是由page header，page trailer和page body组成。如下图所示：

image.png

5. Row(行)

行包含了记录的字段值，事务ID(Trx id)、滚动指针(Roll pointer)、字段指针(Fieldpointers)等信息。

InnoDB文件存储格式

我们通过show table satus \G;命令，查看InnoDB的文件状态(示例的数据库employee表)：

image.png

一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda。

通过 information_schema 查看指定表的文件格式

select * from information_schema.innodb_sys_tables;

File文件格式(File-Format)

在早期的InnoDB版本中，文件格式只有一种，随着InnoDB引擎的发展，出现了新文件格式，用于

支持新的功能。目前InnoDB只支持两种文件格式：Antelope 和 Barracuda。

Antelope: 先前未命名的，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope。

Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC。

通过innodb_file_format 配置参数可以设置InnoDB文件格式，之前默认值为Antelope，5.7版本开始改为Barracuda。

Row行格式(Row_format)

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。

InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。

image.png

DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前缀。

每个表的数据分成若干页来存储，每个页中采用B树结构存储。

如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页，该字段被称为页外列。

1. REDUNDANT 行格式

使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便能够在页外存储。

2. COMPACT 行格式

与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。

3. DYNAMIC 行格式

使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。

DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数控制。

4. COMPRESSED 行格式

COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。

在创建表和索引时，文件格式都被用于每个InnoDB表数据文件(其名称与*.ibd匹配)。

修改文件格式的方法是重新创建表及其索引，最简单方法是对要修改的每个表使用以下命令：ALTER TABLE 表名 ROW_FORMAT=格式类型;