MySQL技术内幕 InnoDB存储引擎——第2章 InnoDB存储引擎（未完待续）

第2章 InnoDB存储引擎

2.1 InnoDB存储引擎概述

InnoDB存储引擎是第一个完整支持ACID事务的MySQL存储引擎（BDB是第一个支持事务的MySQL存储引擎，现在已经停止开发）。

特点：

1. 行锁设计
2. 支持MVCC
3. 支持外键
4. 提供一致性非锁定读
5. 被设计用来最有效地利用以及使用内存和CPU

2.2 InnoDB存储引擎的版本

MySQL 5.1版本中支持两个版本的InnoDB，一个是静态编译的InnoDB版本（老版本的InnoDB）；另一个是动态加载的InnoDB版本（InnoDB Plugin/InnoDB 1.0.x版本）

**MySQL 5.5版本 **升级到 InnoDB 1.1.x 版本

MySQL 5.6版本 升级到 InnoDB 1.2.x 版本

各版本功能对比：

InnoDB Plugin（InnoDB 1.0.x）和InnoDB 1.1.x区别：

1. 不支持 Linux Native AIO功能
2. 不支持多回滚段，所以最大并发事务数量被限制在1023

2.3 InnoDB体系架构

InnoDB存储引擎体系架构：

InnoDB 存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作：

1. 维护所有进程/线程需要访问的多个内部数据结构。
2. 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。
3. 重做日志（redo log）缓冲。

2.3.1 后台线程

后台线程主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB 能恢复到正常运行状态。

InnoDB 存储引擎是多线程模型，因此有多个不同的后台线程，负责处理不同的任务。

1.Master Thread

是核心的后台线程。

• 负责将缓冲池中的数据异步刷新到磁盘
• 保证数据的一致性：脏页的刷新、合并插入缓冲（INSERT BUFFER）、UNDO页的回收等。

2.IO Thread

InnoDB 存储引擎大量使用AIO（Async IO）来处理写IO请求

工作主要负责这些IO请求的回调（call back）处理。

InnoDB 1.0版本之前（即InnoDB 老版本）共有4个IO Thread，分别是write、read、insert buffer和log IO Thread。

IO Thread的数量，Linux不能调整，Windows下通过参数innodb_file_io_threads设置。InnoDB 1.0.x版本开始，read thread 和 write thread 分别增大到了4个，并且不再使用参数innodb_file_io_threads修改，而是分别使用innodb_read_io_threads 和 innodb_write_io_threads参数进行设置。

实战：

查看InnoDB版本：

SHOW VARIABLES LIKE 'innodb_version' \G;

查看read thread 和 write thread数：

SHOW VARIABLES LIKE 'innodb_%io_threads' \G;

**观察InnoDB中的IO Thread：**读线程ID总是小于写线程

SHOW ENGINE INNODB STATUS \G;

3.Purge Thread

事务被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread来回收已分配的undo页。

InnoDB 1.1版本之前：purge操作仅在InnoDB存储引擎的Master Thread中完成。

InnoDB 1.1版本开始：purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。只支持一个Purge Thread线程。

InnoDB 1.2版本开始：支持多个Purge Thread线程，可以进一步加快undo页的回收。由于Purge Thread需要离散地读取undo页，也能更进一步利用磁盘的随机读取性能。

实战：

启用独立的Purge Thread：

MySQL数据库的配置文件中添加以下命令

[mysqld]
innodb_purge_threads=1

InnoDB 1.1版本：innodb_purge_threads=1只能设置为1

InnoDB 1.2版本：innodb_purge_threads可以大于1。

查看Purge Thread数：

SHOW VARIABLES LIKE 'innodb_%io_threads' \G;

4.Page Cleaner Thread

InnoDB 1.2.x 版本引入的。

将之前版本中脏页的刷新操作都放入到单独的线程中来完成。

目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高 InnoDB存储引擎的性能。

2.3.2 内存

1.缓冲池

缓冲池可以当作一个很大的内存区域，用于存放各种类型的页。

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此也称为基于磁盘的数据库系统。CPU和磁盘速度差别太大，因此基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

操作页的操作都是在缓冲池中完成的。

InnoDB 存储引擎的缓冲池的配置是通过参数innodb_buffer_pool_size来设置。

• 在数据库中进行读取页的操作：

1. 首先将页“FIX”到缓冲池中：将从磁盘读到的页存放在缓冲池中
2. 下次读相同页，首先判断是否在缓冲池，若在则命中直接读取，否则读取磁盘上的页。

• 在数据库中进行修改页的操作：

1. 首先修改在缓冲池中的页
2. 再以一定的频率刷新到磁盘上

注意：页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。

• 缓冲池中缓存的数据页类型有：

索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引、innoDB存储的锁信息、数据字典信息等。

• InnoDB存储引擎中内存的结构情况：

• 多个缓冲池实例：

从InnoDB 1.0.x版本开始，允许多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样可以减少数据库内部的资源竞争，增加数据库的并发处理能力。可以通过配置文件innodb_buffer_pool_instances（默认为1）设置大于1，就可以得到多个缓冲池实例。

通过“SHOW ENGINE INNODB STATUS”可以看每个缓冲池的实例对象运行的状态。

从MySQL 5.6 版本开始，还可以通过information_schema架构下的表INNODB_BUFFER_POOL_STATS来观察缓冲的状态。如“SELECT POOL_ID, POOL_SIZE , FREE_BUFFERS,DATABASE_PAGES FROM INNODB_BUFFER_POOL_STATS”

2.LRU List、Free List 和 Flush List

通常来说，数据库中的缓冲池（大的内存区域）是通过LRU算法来进行管理的。即最频繁使用的页在LRU列表的前端。当缓冲池不能存放新读取到的页时，优先释放LRU列表的尾端页。

InnoDB存储引擎中，缓冲池中页的默认大小为16KB。

• midpoint位置：

在InnoDB存储引擎中，对LRU算法做了优化，LRU列表中还加入了midpoint位置。新读取到的页，先放入midpoint位置。这个算法在InnoDB存储引擎下成为midpoint insertion strategy。

• innodb_old_blocks_pct：

默认配置下，midpoint位置在LRU列表长度的5/8处。midpoint可由参数innodb_old_blocks_pct控制。innodb_old_blocks_pct默认为37，表示新读取的页插入到LRU列表尾端的37%的位置，之前的列表称为new列表中的页都是最为活跃的热点数据。

若弱点数据不止63%，可在执行SQL语句前，通过“SET innodb_old_blocks_pct=20;”

• innodb_old_blocks_time：

若页被放入LRU列表的首部，那么某些SQL操作（索引或数据的扫描操作）将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘，影响缓冲池效率。

InnoDB存储引擎引入了另一个参数innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。可以通过“SET GLOBAL innodb_old_blocks_time=1000;”，尽可能使LRU列表中热点数据不被刷出。

• LRU列表及Free列表的使用情况和运行状态：

LRU列表用来管理以及读取的页码。

但当数据库刚启动时，LRU列表是空的，这时页都在Free列表中。

当需要从缓冲池中分页时：先从Free列表中查找是否有可用的空闲页

1. 若有则从Free列表中删除，放入到LRU列表中。
2. 若无，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。当页从LRU列表的old部分移动到到new部分时，这个操作称为page made young。因innodb_old_blocks_time的设置导致页没有从old部分移动到new部分的操作称为page not made young。

观察LRU列表及Free列表的使用情况和运行状态：（显示的不是当前的状态，而是过去某个时间范围内InnoDB存储引擎的状态）

SHOW ENGINE INNODB STATUS \G;

[ps]:
Buffer pool size: 共有多少页
Free buffers: 当前Free列表中页的数量
Database pages: LRU列表中页的数量
Free buffers + Database pages != Buffer pool size;(因为缓冲池的页还看被分配给自适应哈希索引、Lock信息、Insert Buffer等页，这部分页不需要LRU算法进行维护，因此不存在于LRU列表中。)

pages made young: LRU列表中页移动到前端的次数 not young: 运行阶段改变innodb_old_blocks_time的值
youngs/s、non-youngs/s: 每秒这两类操作的次数
Buffer pool hit rate: 缓冲池的命中率,该值通常不应该小于95%。若小于95%，用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题。

观察缓冲池的运行状态：INNODB_BUFFER_POOL_STATS

SELECT POOL_ID,HIT_RATE,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG FROM information_schema.INNODB_BUFFER_POOL_STATS \G;

观察每个LRU列表中每个页的具体信息：通过表INNODB_BUFFER_PAGE_LRU

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE SPACE = 1;

• 压缩页

InnoDB存储引擎从1.0.x版本开始支持压缩页的功能，将原本的16KB的页压缩为1KB、2KB、4KB和8KB。而由于页的大小发生了变化，LRU列表也有了些许的改变。

对于非16KB的页，是通过unzip_LRU列表进行管理的。LRU页包含了unzip_LRU页。

unzip_LRU从缓冲池分配内存的流程

1. 首先，对不同压缩页大小的页分别管理
2. 其次，通过伙伴算法进行内存的分配

对需要从缓冲池申请页为4KB的大小的过程：

1. 检查4KB的unzip_LRU列表，检查是否有可用的空闲页
2. 若有，直接使用
3. 若无，检查8KB的unzip_LRU列表
4. 若能够得到空闲页，将页分成2个4KB页，存放到4KB的unzip_LRU列表；
5. 若不能得到空闲页，从LRU列表中申请一个16KB的页，将页分为1个8KB的页，2个4KB的页，分别存放到对应的unzip_LRU列表中。

观察unzip_LRU列表中的页：

SHOW ENGINE INNODB STATUS \G;
或
SELECT TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE FROM INNODB_FUFFER_PAGE_LRU WHERE COMPRESSED_SIZE <> 0;

LRU列表中的页被修改后，称改页为脏页(dirty page)，即缓冲池的页和磁盘上的页产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。脏页既存在于LRU列表中，也存在于Flush列表中，用户可以通过元数据表INNODB_BUFFER_PAGE_LRU来查看，唯一不同的是，需要加入OLDEST_MODIFICATION大于0的SQL查询条件。

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU
WHERE OLDEST_MODIFICATION > 0;

[ps]:
TABLE_NAME = NULL : 表示该页属于系统表空间。

3.重做日志缓冲区（redo log buffer）

InnoDB存储引擎首先将重做日志（redo log）先放入到这个重做日志缓冲区，然后按一定频率将其刷新到重做日志文件。

• 重做日志缓冲大小：

重做日志缓冲一般不用很大，因为一般情况下，每一秒钟会将重做日志刷新到日志文件，因此用户只需保证每秒发生的事务量在这个缓冲大小之内即可。

可用配置参数 innodb_log _buffer_size 控制，默认为8MB。

• 重做日志缓冲中的内容刷新到外部磁盘的重做日志文件的情况：

1. Master Thread 每秒
2. 每个事务提交时
3. 当重做日志缓冲池剩余空间小于1/2时

4.额外的内存池

InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。

在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该额外内存池的内存不够时，会从缓冲池中进行申请。

例如，分配了缓冲池，但是每个缓冲池中的帧缓冲，还有对应缓冲控制对象，这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。因此在申请了很大的InnoDB缓冲池时，应考虑相应地增加这个值。

2.4 Checkpoint技术

缓冲池的设计目的是为了协调CPU和磁盘速度的不匹配。因此页的操作都是在缓冲池中完成的。

如果一条DML（update/delete）改变了页中的记录，那么此时页是脏的（即缓冲池中的页的版本要比磁盘的新），数据库需要将新版本的页刷新到磁盘。

Write Ahead Log 策略

若从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了。为了避免数据丢失的问题，当前事务数据库系统大多都采用了Write Ahead Log 策略，即当事务提交时，先写重做日志，再修改页。当发生宕机导致数据丢失时，通过重做日志 redo log 来完成数据的恢复。这也是事务ACID中D（Durability 持久性）的要求。

Write Ahead Log 策略的问题

Write Ahead Log 策略的适用条件：

1. 缓冲池可以缓存数据库中所有的数据（生产环境应用中的数据库难以保证）
2. 重做日志可以无限增大（成本高）

满足条件后，宕机后数据库的恢复时间和代价很长。因此引入Checkpoint（检查点）。

Checkpoint（检查点）技术

CheckPoint（检查点）主要解决的问题

1.缩短数据库恢复时间

数据库发生宕机时，数据库不需要重做所有的日志。因为CheckPoint之前的页都已经刷新回磁盘，因此数据库只需对CheckPoint后的重做日志进行恢复。这样大大缩短恢复时间。

2.缓冲池不够用时，将脏页刷新到磁盘

缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行CheckPoint，将脏页（即页的新版本）刷回磁盘。

3.重做日志不可用时，刷新脏页

重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，当数据库发生宕机时，数据库恢复操作不需要重做日志，因此这部分可以被覆盖重用。若此时重做日志还需要使用，必须强制CheckPoint，将缓冲池中的页至少刷新到当前重做日志的位置。

对于InnoDB 存储引擎而言，是通过LSN（Log Sequence Number）来标记版本的。而LSN是8字节的数字，单位是字节。每个页都有LSN，重做日志也有LSN，CheckPoint也有LSN。可以通过SHOW ENGINE INNODB STATUS来观察。

CheckPoint做的事情就是将缓冲池中的脏页刷回到磁盘。不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间出发CheckPoint。

在InnoDB存储引擎中有两种 CheckPoint：

1. Sharp Checkpoint（默认的工作方式，参数innodb_fast_shutdown=1）：发生在数据库关闭时，将所有脏页都刷新回磁盘。
2. Fuzzy Checkpoint：运行时只刷新一部分脏页而不是刷新所有脏页。

在InnoDB存储引擎中有以下几种情况 Fuzzy Checkpoint：

1. Master Thread Checkpoint：以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个操作是异步的，可以同时进行其他操作，用户查询线程不会阻塞。

2. FLUSH_LRU_LIST Checkpoint：

   因为InnoDB存储引擎需要保证LRU列表中需要有近100个空闲页可供使用。在InnoDB1.1x版本之前，需要检查LRU列表是否有足够的可用空间操作发生在用户查询线程中，会阻塞用户查询操作。若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除，如果这些页有脏页，则需要进行 Checkpoint ，这些页是来自LRU列表的，因此成为FLUSH_LRU_LIST Checkpoint。

   从InnoDB1.2.x版本开始（即MySQL 5.6版本），这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth（默认为1024）控制LRU列表中可用页的数量。

3. Async/Sync Flush Checkpoint：

   Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性。指的是重做日志不可用的情况，需要强制将一些页刷新回磁盘，此时脏页是从脏页列表选取的。

   若将已经写入到重做日志的LSN记为redo_lsn，将已经刷新回磁盘最新页的LSN记为checkpoint_lsn。

   则，

   checkpoint_age = redo_lsn - checkpoint_lsn。

   async_water_mark = 75% * total_redo_log_file_size。

   sync_water_mark = 90% * total_redo_log_file_size。

   若每个重做日志文件的大小为1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为2GB。那么async_water_mark = 1.5GB，sync_water_mark = 1.8GB。则：

   • 当checkpoint_age<async_water_mark时，不需要刷新任何脏页到磁盘
   • 当async_water_mark<checkpoint_age<sync_water_mark 时，触发Async Flush，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<async_water_mark（不需要刷新任何脏页到磁盘的情况）。
   • checkpoint_age>sync_water_mark 这种情况较少发生，除非设置的重做日志文件太小，并且在类似LOAD DATA的BULK INSERT操作。此时触发Sync Flush 操作，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<async_water_mark （不需要刷新任何脏页到磁盘的情况）。

4. Dirty Page too much Checkpoint

   脏页数量太多，导致InnoDB存储引擎强制进行Checkpoint。主要目的是为了保证缓冲池有足够可用的页。由参数innodb_max_dirty_pages_pct控制。innodb_max_dirty_pages_pct为75表示当缓冲池中的脏页数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘。在InnoDB 1.0.x版本之前，该参数默认为90，之后版本为75。

2.5 Master Thread工作方式

2.5.1 InnoDB 1.0.x版本之前的 Master Thread

Master Thread 具有最高的线程优先级别。内部由多个循环（loop）组成：主循环（loop）、后台循环（backgroup loop）、刷新循环（flush loop）、暂停循环（suspend loop）。

Master Thread会根据数据库的运行状态在上述循环中切换。

主循环（loop）：

大多操作都在这个循环中，其中两大部分的操作——每秒钟的操作和每十秒的操作。Master Thread完整的伪代码如下：

void master_thread(){

	// 主循环
	loop:
	// 每一秒的操作
    for(int i = 0;i<10;i++){
    	thread_sleep(1) // sleep 1 second 
		do log buffer flush to disk // 日志缓冲刷新到磁盘
		if(last_one_second_ios < 5) // 当前一秒内发生的IO次数小于5
			do merge at most 5 insert buffer // 合并插入缓冲
		if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)// 当前缓冲池中脏页的比例>配置文件中的innodb_max_dirty_pages_pct
			do buffer pool flush 100 dirty page // 至多刷新100个InnoDB的缓冲池中的脏页到磁盘
		if(no user activity) // 当前没有用户活动：数据库空闲 或者 数据库关闭
			goto background loop // 切换到后台循环
    }
    
    // 每十秒的操作
   	if(last_ten_second_ios < 200) // 过去10秒之内磁盘的IO操作次数小于200次
   		do buffer pool flush 100 dirty page // 刷新100个脏页到磁盘
   	do merge at most 5 insert buffer // 合并至多5个插入缓冲
   	do log buffer flush to disk // 将日志缓冲刷新到磁盘
   	do full purge // 删除无用的Undo页
   	if(buf_get_modified_ratio_pct > 70%) // 缓冲池中脏页的比例>70%
   		do buffer pool flush 100 dirty page // 刷新100个脏页到磁盘
   	else // 缓冲池中脏页的比例 <=70%
   		buffer pool flush 10 dirty page // 刷新10个脏页到磁盘
	goto loop;
	
	// 后台循环
	background loop: 
	do full purge // 删除无用的Uodo页
	do merge 20 insert buffer // 合并20个插入缓冲
	
	// 如果空闲 跳回主循环,不空闲 跳回刷新循环
	if not idle:
        goto loop
	else:
		goto flush loop
		
	// 刷新循环：不断刷新100个页直到符合条件
	flush loop:
	do buffer pool flush 100 dirty pages // 刷新100个页
	if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct) // 当前缓冲池中脏页的比例>配置文件中的innodb_max_dirty_pages_pct
		goto flush loop // 跳到刷新循环
	
	// 没有什么事情可以做时跳到暂停循环
	goto suspend loop // 跳到暂停循环
	
	// 暂停循环
	suspend loop:
	suspend_thread()
	waiting event // 等待事件
	goto loop;
	
}

可以看到loop循环通过thread sleep来实现，这意味着所谓的每秒一次或每10秒一次的操作是不精确的。在负载很大的情况下可能会延迟（delay），只能说大概在这个频率下。

每秒一次的操作包括：

• 日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）：因此再大的事务提交（commit）的时间也是很短的。
• 合并插入缓冲（可能）：并不是每秒发生，InnoDB存储引擎会判断当前一秒内发生的IO次数last_one_second_ios是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插入缓冲的操作。
• 至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）：InnoDB存储引擎通过判断当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）是否超过了配置文件中的innodb_max_dirty_pages_pct这个参数（默认90，代表90%），如果超过了这个阈值，InnoDB存储引擎则认为需要做磁盘同步的操作，将100个脏页写入磁盘中。
• 如果当前没有用户活动（数据库空闲时）或者数据库关闭时，则切换到后台循环（backgroup loop）（可能）

每十秒的操作包括：

1. 刷新100个脏页到磁盘（可能）: 判断过去10秒之内磁盘的IO操作次数是否小于200次，如果是，InnoDB存储引擎认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘。
2. 合并至多5个插入缓冲（总是）：与每秒的合并插入缓冲不同的是，这次插入缓冲操作总会在这个阶段进行
3. 将日志缓冲刷新到磁盘（总是）:InnoDB存储引擎会再行一次将日志缓冲刷新到磁盘的操作（和每秒一次的合并插入缓冲操作一样）
4. full purge操作，即删除无用的Undo页（总是）：对表进行update、delete这类操作时，原先的行被标记为删除，但是因为一致性读的关系，需要保留这些行版本的信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，比如有时候可能还有查询操作需要读取之前版本的undo信息，如果可以删除，InnoDB会立即将其删除。InnoDB存储引擎在执行full purge操作时，每次最多尝试回收20个undo页。
5. 刷新100个或者10个脏页到磁盘（总是）：判断缓冲池中脏页的比例（buf_get_modified_ratio_pct），如果脏页的比例小于70%，则只需刷新10%的脏页到磁盘。

后台循环（backgroup loop）

在主循环（Loop）中，如果当前没有用户活动（数据库空闲时）或者数据库关闭时，则切换到后台循环（backgroup loop）

backgroup loop会执行以下操作：

• 删除无用的Uodo页（总是）
• 合并20个插入缓冲（总是）
• 跳回到主循环（总是）
• 不断刷新100个页直到符合条件（可能，跳转到刷新循环 flush loop完成）。InnoDB存储引擎通过判断当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）是否超过了配置文件中的innodb_max_dirty_pages_pct这个参数（默认90，代表90%），如果超过了这个阈值，InnoDB存储引擎则认为需要做磁盘同步的操作，将100个脏页写入磁盘中。

刷新循环（flush loop）

后台循环（backgroup loop）跳转到刷新循环（flush loop），刷新100个页直到符合条件。

暂停循环（suspend loop）

若刷新循环（flush loop）也没有什么事情可以做时，InnoDB存储引擎会切换到暂停循环（suspend loop），将Master Thread挂起，等待事件的发生。若用户启用（enable）了 InnoDB存储引擎，却没有使用任何InnoDB存储引擎的表，那么Master Thread总是处于挂起的状态。

2.5.2 InnoDB 1.2.x版本之前的 Master Thread

2.5.3 InnoDB 1.2.x版本的 Master Thread

2.6 InnoDB关键特性

关键特性包括：

1. 插入缓冲（insert buffer）
2. 两次写（double write）
3. 自适应哈希索引
4. 异步IO（Async IO）
5. 刷新邻接页（Flush Neighbor Page）

这些特性为Innodb带来更好的性能以及更高的可靠性。

2.6.1 插入缓冲（insert buffer）对于非聚集索引（非唯一辅助索引）提高插入操作的性能

insert buffer和数据页一样是物理页的组成部分，不是缓冲池的组成部分。

插入缓冲（insert buffer）对于非聚集索引提高插入操作的性能

聚集索引和非聚集索引的插入操作

1.只有一个聚集索引（Primary Key）的插入

主键是行唯一的标识符。行记录的插入顺序是按照主键递增的顺序插入的，因此插入聚集索引（Primary Key）一般是顺序的，不需要磁盘的随机读取。

前置条件：SQL定义表，a列为Primary Key且自增。

若对a插入NULL，则a会自动增长，其中页中的行记录按a的值进行顺序存放。一般情况下不需要随机读取另一个页中的记录，因此，这类情况下，插入操作速度非常快。

需要注意的是，并不是所有的主键插入都是顺序的，若主键类是UUID这样的，那么插入和辅助索引一样，同样是随机的。即使主键是自增的类型，但是插入的是指定的值不是NULL，那么同样可能导致扎入并非连续的情况。

2.除了聚集索引，还有多个非聚集索引的辅助索引（secondary index）

前置条件：SQL定义表，a列为Primary Key且自增，b列为key（非聚集的且不是唯一的索引）

插入操作时，数据页的存放还是按主键a的顺序存放，但是非聚集索引叶子节点的插入不再是顺序的了，这时需要离散地访问非聚集索引页，由于随机读取的存在导致了插入操作性能下降。这不是b字段上索引的错误导致，而是因为B+树的特性决定了非聚集索引插入的离散性。

需要注意的是，在某些情况下辅助索引的插入依然是顺序的或者说较为顺序的，比如时间字段。通常情况下，时间是一个辅助索引，用来根据时间条件进行查询。但是在插入时却是根据时间的递增而插入的，因此插入也是较为顺序的。

1.insert buffer

insert buffer 提高非聚集索引的插入性能

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

insert buffer的使用需要满足以下两个条件：

1. 索引是辅助索引（secondary index）：key
2. 索引不是唯一的

问题：应用程序进行大量插入操作，这些都涉及了非聚集索引，也就是使用了insert buffer。若此时数据库发生了宕机，这时会有大量insert buffer没有merge（合并）实际的非聚集索引中去。因此这时恢复可能需要很长时间，极端情况可能几个小时。

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致insert buffer失去了意义。

insert buffer 存在的问题是：在写密集的情况下，插入缓冲会用过多的缓冲池内存（innodb_buffer_pool），默认最大可占用到1/2的缓冲池内存。这对其他操作可能会带来一定影响。可以通过修改IBUF_POOL_SIZE_PER_MAX_SIZE可以对插入缓冲的大小进行控制，比如改为3，则最大只能使用1/3的缓冲池内存。

2.change buffer：insert 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操作可能分为两个过程：

1. 将记录标记为已删除：delete buffer
2. 真正将记录删除：purge buffer

innodb_change_buffering开启各种buffer的启用情况。可选inserts、deletes、purges、changes、all、none。inserts、deletes、purge就是前面的三种情况。changes表示启用inserts和deletes。默认为all。

Innodb 1.2.x版本开始，可以通过参数innodb_change_buffer_max_size来控制change buffer的最大使用内存数量。默认值25，表示最多1/4的缓冲池内存空间。最大有效值为50。

3.insert buffer的内部实现

insert buffer的数据结构是一棵B+树。

MySQL4.1之前版本每张表有一棵insert buffer B+树。现在的版本全局只要一棵insert buffer B+树，负责对所有的表的辅助索引进行insert buffer。

而这棵B+树存放在共享表空间中，默认也就是ibdata1中。

因此，试图通过独立表空间ibd文件恢复表中数据时，往往会导致check table失败。因为表的辅助索引中的数据可能还在insert buffer中（即共享表空间）中，所以通过ibd文件恢复后，还需要进行repair table 操作来重建表上的所有辅助索引。

insert buffer是一棵B+树，因此由叶节点和非叶节点组成。非叶节点存放的是查询的search key（键值）。

search key 一共占用9个字节，其中space表示待插入记录所在表的表空间id，在innodb存储引擎中，每个表

4.merge insert buffer

2.6.2 两次写（double write）数据页的可靠性

double write（两次写）带给Innodb存储引擎的是数据页的可靠性。

发生部分写失效时，需要恢复。在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write。

double write架构图

double write由两部分组成，一部分是内存中的double write buffer（大小2MB），另一部分是物理磁盘上共享表空间中连续的128个页，即2个区（extent）大小同样2MB。

在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过double write buffer 分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。因为double write页是连续的，因此这个过程是顺序写的，开销并不是很大。完成double write页的写入吼，再将double write buffer中的页写入各个表空间文件中，此时的写入则是离散的。

2.6.3 自适应哈希索引

2.6.4 异步IO（Async IO）

2.6.5 刷新邻接页（Flush Neighbor Page）提高传统机械硬盘IO性能

刷新邻接页（Flush Neighbor Page）的工作原理

当刷新一个脏页时，Innodb存储引擎会检测该页所在区（extent）的所有页，如果是脏页那么一起刷新。这样的好处是，通过AIO将多个IO写入操作合并为一个IO操作，所以这个工作机制在传统机械磁盘下有着显著的优势。

需要考虑以下两个问题：

1. 是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页？
2. 固态硬盘有着很高的IOPS，是否还需要这个特性？

为此，Innodb存储引擎从1.2.x版本开始提供了参数innodb_flush_neighbors，用来控制是否启用该特性。传统机械硬盘启用（1），固态硬盘关闭（0）。

2.7 启动、关闭与恢复

Innodb存储引擎的启动和关闭：MySQL实例的启动过程中对Innodb存储引擎的处理过程。

1.innodb_fast_shotdown（数据库关闭时）

数据库关闭时，参数 innodb_fast_shotdown 影响着表的存储引擎为Innodb的行为。可取值0、1、2，默认值1。

• 0：数据库关闭时，Innodb需要完成所有的full purge和merge insert buffer，并且将缓冲池所有的脏页刷新回磁盘。这需要一些时间，有时候甚至几个小时。如果在进行Innodb升级时，必须将这个参数调为0，然后再关闭数据库。
• 1（默认值）：不需要完成上述full purge和merge insert buffer，但需要将缓冲池所有的脏页刷新回磁盘。
• 2：不完成full purge和merge insert buffer，也不将缓冲池所有的脏页刷新回磁盘，而是将日志都写入日志文件。这样不会有任何事务的丢失，但是下次MySQL数据库启动时，会进行恢复操作（recovery）。

以下非正常关闭时，下次MySQL数据库启动时，都会对Innodb存储引擎的表进行恢复操作（recovery）

kill命令关闭数据库

在MySQL数据库运行了重启服务器

关闭时数据库时设置了innodb_fast_shotdown为2

2.innodb_force_recovery（影响整个存储引擎恢复的状况）

innodb_force_recovery影响整个存储引擎恢复的状况。包括完整恢复（0：默认值），非完整恢复（1~6）。

非完整恢复的应用场景：用户自己知道怎么进行恢复的情况。

如对一个表进行alter table时发生意外，数据库重启时会对Innodb表进行回滚操作，对于大表需要很长时间，可能几小时。这时用户可以自行恢复，如把表删除，从悲愤中重新导入数据到表，可能这些操作的速度远远快于回滚操作。

完整恢复：

• 0：当发生需要恢复时，进行所有的恢复操作。当不能进行有效恢复时（如数据页发生了corruption），MySQL数据库可能会发生宕机（crash），并把错误写入错误日志中去。

非完整恢复：

可以设置6个非0值：1~6，大的数字表示包含了前面所有小数字表示的影响。即大的包含小的。

• 1（SRV_FORCE_IGNORE_CORRUPT）：忽略检查到的corrupt页。
• 2（SRV_FORCE_NO_BACKGROUND）：阻止Master Thread线程的运行，如Master Thread线程需要进行full purge，而这会导致crash（宕机）。
• 3（SRV_FORCE_NO_TRX_UNDO）：不进行事务的回滚操作。
• 4（SRV_FORCE_NO_IBUF_MERGE）：不进行插入缓冲的合并操作。
• 5（SRV_FORCE_NO_UNDO_LOG_SCAN）：不插卡撤销日志（undo log），Innodb存储引擎将会对未提交的事务视为已提交。
• 6（SRV_FORCE_NO_LOG_REDO）：不进行前滚的操作。

需要注意的是，innodb_force_recovery设置为非0值，用户只可以对表进行select、create和drop操作，但不能对表进行insert、update、delete这类DML操作。

实验：模拟故障的发生。在第一个session中，对一张近一千万行的Innodb存储引擎进行更新操作，但完成后不要马上提交。

START TRANSACTION;
UPDATE Profile SET password='';

start transaction（开启事务），同时防止自动提交。update操作会产生大量的UNDO日志（undo log）。这时，用kill命令杀掉MySQL数据库服务器。

ps -ef | grep mysqld
显示的有id
kill -9 id

通过kill模仿数据库的宕机操作。下次MySQL数据库启动时，会对之前的UPDATE事务进行回滚操作，而这些信息都将会记录在错误日志文件（默认后缀名为err）中。如果查看错误日志文件，可得到如下结果：

Rolling back trx with id 0 5828930,9946784 rows to undo

默认策略innodb_force_recovery为0时，通过错误日志文件可以看出这次回滚操作需要回滚9946784行记录，耗时10分钟。

同样的测试，将innodb_force_recovery为3，观察Innodb存储引擎还会进行回滚操作。错误日志文件可得：

!!!innodb_force_recovery is set to 3 !!!

!!!，innodb警告已经将innodb_force_recovery设置为3，不进行回滚操作，因此数据库很快就启动完成了。

但用户需要小心当前数据库的状态，并仔细确认是否不需要回滚事务的操作。

2.8 小结

InnoDB 存储引擎及其体系结构进行概述->InnoDB 存储引擎历史->InnoDB 存储引擎体系结构（包括后台线程和内存结构）->InnoDB 存储引擎的关键特性 -> 启动和关闭MySQL时配置文件参数对InnoDB 存储引擎的影响。