InnoDB存储引擎笔记-InnoDB存储引擎

概述

InnoDB是事务安全的MySQL存储引擎，设计上采用了类似于Oracle数据库的架构。通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎。同时，也正是因为InnoDB的存在，才使MySQL数据库变得更有魅力。

InnoDB体系架构

下图展示了InnoDB的存储引擎的体系架构，从图可见：

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

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

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

后台线程

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。
1、Master Thread
Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的数据，后台插入缓冲、UNDO页的回收等。
2、IO Thread
在InnoDB存储引擎中大量使用了AIO来处理写IO请求，这样可以极大提高了数据库的性能。而IO Thread的工作主要负责这些IO请求的回调处理。InnoDB1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO Thread。在Linux平台下，IO Thread的数量不能进行调整，但是在Windows平台下可以通过参数innodb_file_io_threads来增大IO Thread。从InnoDB1.0.x版本开始，read Thread和write Thread分别增大到了4个，并且不再使用innodb_file_io_threads参数，而是分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置。
3、Purge Thread
事务被提交后，其所使用的的undolog可能不再需要，因此需要PurgeThread来回收使用并分配的undo页。在InnoDB1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高了CPU的使用率以及提升存储引擎的性能。用户可以在MySQL数据库的配置文件中添加如下命令启动独立的Purge Thread。

[mysqld]
innodb_purge_threads=1

在InnoDB1.1版本中，即使将innodb_purge_threads设为大于1，InnoDB存储引擎启动时也会将其设为1，并在错误文件中出现提示。
从InnoDB1.2版本开始，InnoDB支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。
4、Page Cleaner Thread
Page Cleaner Thread是在InnoDB1.2版本中引入的。其作用是将之前版本中的脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高了InnoDB存储引擎的性能。

内存

1、缓冲池
InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统（Disk-base Database）。在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。
缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。
对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新会磁盘并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新会磁盘。同样，这也是为了提高数据库整体性能。
综上所述，缓冲池的大小直接影响着数据库的整体性能。对于InnoDB存储引擎而言，其缓冲池的配置通过参数innodb_buffer_pool_size来设置。
具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大一部分而已。

从InnoDB1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。可以通过参数innodb_buffer_pool_instances来进行配置，该值默认为1。
2、LRU List、Free list和Flush List
通常来说，数据库中的缓冲池是通过LRU（Latest Recent Used，最近最少使用）算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读到的页时，将首先释放LRU列表中尾端的页。
在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样地使用LRU算法对缓冲池进行管理。稍有不同的是InnoDB存储引擎对传统的LRU算法做了一些优化。在InnoDB存储引擎中，LRU列表中还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎中称为midpoint insertion strategy。在默认配置下，该位置在LRU列表长度的5/8处。参数innodb_old_blocks_pct默认值为37，表示新读取的页插入到LRU列表尾端的37%的位置。在InnoDB存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单地理解为new列表中的页都是最为活跃的热点数据。

那为什么不采用朴素的LRU算法，直接将读取的页放入到LRU列表的首部呢？这是因为若直接将读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响了缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这些操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说仅仅在这次查询中需要，并不是活跃的热点数据。如果页被放入到LRU的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。
为了解决这个问题，InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。因此当需要执行上述所说的SQL操作时，可以通过下面的方法尽可能使LRU列表中热点数据不被刷出。

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放到LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时，称此时发生的操作为page made young，而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。可以通过命令SHOW ENGINE INNODB STATUS来观察LRU列表及Free列表的使用情况和运行状态。

SHOW ENGINE INNODB STATUS\G; 

查看缓冲区状态
通过命令SHOW ENGINE INNODB STATUS可以看到：当前Buffer pool size共有327679个页，总共5GB的缓冲池。Free buffers表示当前Free列表中页的数量，Database pages表示LRU列表中页的数量。可能的情况是Free buffers与Database pages的数量之和不等于Buffer pool size。因为缓冲池中的页还可能会被分配给自适应哈希索引、Lock信息、Insert Buffer等页，而这部分页不需要LRU算法进行维护，因此不存在于LRU列表中。
pages made young显示了LRU列表中页移动到前端的次数，因为该服务器在运行阶段没有改变innodb_old_blocks_time的值，因此not young为0。youngs/s、non-youngs/s表示每秒这两类操作的次数。这里还有一个重要的观察变量-Buffer pool hit rate，表示缓冲池的命中率，这个例子中为100%，说明缓冲池运行状态非常好。通常该值不应该小于95%。若发生Buffer pool hit rate的值小于95%这种情况，用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题。

注意，执行命令SHOW ENGINE INNODB STATUS显示的不是当前的状态，而是过去某个时间范围内InnoDB存储引擎的状态。

InnoDB存储引擎从1.0.x版本开始支持压缩页的功能，即将原本16KB的页压缩为1KB、2KB、4KB和8KB。而由于页的大小发生了变化，LRU列表也有了些许的改变。对于非16KB的页，是通过unzip_LRU列表进行管理的。unzip_LRU是怎样从缓冲池中分配内存的呢？
首先，在unzip_LRU列表中对不同压缩页大小的页进行分别管理。其次，通过伙伴算法进行内存分配。例如对需要从缓冲池中申请页为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列表中。

在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而flush列表中的页即为脏页列表。需要注意的是，脏页既存在于LRU列表中，也存在于flush列表中。LRU列表用来管理缓冲池中页的可用性，flush列表用来管理将页刷新回磁盘，二者互不影响。
同LRU列表一样，flush列表也可以通过命令SHOW ENGINE INNODB STATUS来查看，前面例子中Modified db pages 24673就显示了脏页的数量。
3、重做日志缓冲
InnoDB存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲（redo log buffer）。InnoDB存储引擎首先将重做日志信息放到这个缓冲区，然后按一定的频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置的特别大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在每个缓冲大小之内即可。该值可由配置参数innodb_log_buffer_size控制，默认值为8MB。
在通常情况下，8MB的重做日志缓冲池满足绝大部分的应用，因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到重做的日志文件中：

• Master Thread每一秒将重做日志缓冲刷新到重做日志文件中
• 每个事务提交的时候会将重做日志缓冲刷新到重做日志文件中
• 当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件中

4、额外的内存池
额外的内存通常被DBA忽略，它们认为该值并不十分重要，事实恰恰相反，该值同样十分重要。在InnoDB存储引擎中，对内存的管理是通过一种内存堆（heap）的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中申请。例如分配了缓冲池（innodb_buffer_pool），但是每个缓冲池中的帧缓冲（frame buffer）还有对应的缓冲控制对象（buffer control block），这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。因此，在申请了很大的InnoDB缓冲池时，也应考虑相应地增加这个值。

Checkpoint技术

前面讲到了，缓冲池的设计目的是为了协调CPU速度与磁盘速度的鸿沟。因此页的操作首先都是在缓冲池中完成的。如果一条DML语句，如update和delete改变了页中的记录，那么此时页是脏的，即缓冲池中的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池中刷新到磁盘。
倘若每次一个页发生变化，就将新页的版本刷新到磁盘，那么这个开销是非常大的。若热点数据集中在某几个页中，那么数据库的性能将变得非常差。同时，如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了。为了避免发生数据丢失的问题，当前事务数据库系统普遍采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中的D（Durability持久性）的要求。
思考下面的场景，如果重做日志可以无限地增大，同时缓冲池也足够大，能够缓冲所有数据库的数据，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。但是这需要两个前提条件：

• 缓冲池可以缓存数据库中所有的数据
• 重做日志可以无限增大

对于第一个前提条件，有经验的用户都知道，当数据库刚开始创建时，表中没有任何数据。缓冲池的确可以缓冲所有的数据库文件。然而随着市场的推广，用户的增加，产品越来越受到关注，使用量也越来越大。这时负责后台存储的数据库的容量必定会不断增大。
再来看第二个前提条件：重做日志可以无限增大。也许是可以的，但是这对成本的要求太高，同时不便于运维。
Checkpoint检查点技术的目的是解决一下几个问题：

• 缩短数据库恢复的时间
• 缓冲池不够用时，将脏页刷新到磁盘
• 重做日志不可用时，刷新脏页

当数据库发生宕机时，数据库不需要重做所有的日志，因为checkpoint之前的页都已经刷新回磁盘，故只需要数据库对checkpoint后的重做日志进行恢复。这样就大大缩短了恢复时间。
此外，当缓冲区不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行checkpoint，将脏页的数据页刷新回磁盘。

注意：此时刷脏的页有可能位于Flush LRU链表的后面，也就是LSN值比较大，此时也是可以的，我们允许checkpoint LSN后面的值的页可以刷新到磁盘，也可以不刷新到磁盘，恢复的时候会判断如果该页被刷新到磁盘了就不再恢复了。

重做日志不可用的情况是因为当前事务数据库对重做日志的设计都是循环使用的，并不是无限增大的。重做日志可以被重用的部分是指这些重做日志已经不再需要，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时重做日志还需要使用，那么必须强制产生checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。
对于InnoDB存储引擎而言，其是通过LSN来标记版本的，而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN， Checkpoint也有LSN。可以通过命令SHOW ENGINE INNODB STATUS来观察：
在InnoDB存储引擎中，Checkpoint发生的时间、条件及脏页的选择等都非常的复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘。不同之处在于每次刷新多少页到磁盘，每次从哪里去脏页，以及什么时间触发Checkpoint。
InnoDB引擎内部，有两种checkpoint：

• Sharp Checkpoint
• Fuzzy Checkpoint

Sharp CheckPoint发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式。即参数innodb_fast_shutdown=1。
但是若数据库在运行时使用Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在InnoDB存储引擎使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。
InnoDB存储引擎中可能发生如下几种情况的Fuzzy Checkpoint：

• Master Thread Checkpoint
• FLUSH_LRU_LIST Checkpoint
• Async/Sync Flush Checkpoint
• Dirty Page too much Checkpoint

对于Master Thread中发生的Checkpoint，差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。
FLUSH_LRU_LIST Checkpoint是因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。在InnoDB1.1x版本之前，需要检查LRU列表中是否有足够可用空间操作发生在用户查询线程中，显然这会阻塞用户的查询操作。倘若没有100个可用的空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint。
而从MySQL5.6版本，也就是InnoDB1.2.x版本开始，这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024。
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,async_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。

可见，Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性。在InnoDB1.2.x版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，而Sync Flush Checkpoint会阻塞所有的用户查询线程，并且等待脏页刷新完成。从InnoDB1.2.x版本开始-也就是MySQL5.6版本，这部分的刷新操作同样放入倒了单独的Page Cleaner Thread中，故不会阻塞用户线程。
最后一种Checkpoint的情况是Dirty Page too much，即脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制。
innodb_max_dirty_pages_pct值为75表示，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘。在InnoDB1.0.x版本之前，该参数默认值为90，之后的版本都为75。
InnoDB 刷脏页的控制策略
接下来，我就来和你说说 InnoDB 脏页的控制策略，以及和这些策略相关的参数。

首先，你要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
其实，因为没能正确地设置 innodb_io_capacity 参数，而导致的性能问题也比比皆是。之前，就曾有其他公司的开发负责人找我看一个库的性能问题，说 MySQL 的写入速度很慢，TPS 很低，但是数据库主机的 IO 压力并不大。经过一番排查，发现罪魁祸首就是这个参数的设置出了问题。

它的主机磁盘用的是 SSD，但是 innodb_io_capacity 的值设置的是 300。于是，InnoDB 认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。

虽然我们现在已经定义了“全力刷脏页”的行为，但平时总不能一直是全力刷吧？毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。所以接下来，我们就一起看看 InnoDB 怎么控制引擎按照“全力”的百分比来刷脏页。

根据我前面提到的知识点，试想一下，如果你来设计策略控制刷脏页的速度，会参考哪些因素呢？

这个问题可以这么想，如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是 redo log 写满。

所以，InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。

InnoDB 会根据这两个因素先单独算出两个数字。

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字，计算这个数字的伪代码类似这样：

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个计算公式可以记为 F2(N)。F2(N) 算法比较复杂，你只要知道 N 越大，算出来的值越大就好了。

然后，根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。
现在你知道了，InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

其中，脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具体的命令参考下面的代码：

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

接下来，我们再看一个有趣的策略。

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

Master Thread工作方式

InnoDB存储引擎的主要工作都是在一个单独的后台线程Master Thread中完成的。

InnoDB1.0.x版本之前的Master Thread

Master Thread具有最高的线程优先级别。其内部由多个循环（loop）组成：主循环（loop）、后台循环（background loop）、刷新循环（flush loop）、暂停循环（suspend loop）。Master Thread会根据数据库的运行状态进行切换循环。
Loop被称为主循环，因为大部分的操作都是在这个循环中，其中有两大部分的操作每秒钟的操作和每10秒的操作。
每秒一次的操作主要有：

• 日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）
• 合并插入缓冲（可能）
• 至多刷新100InnoDB的缓冲池中的脏页到磁盘（可能）
• 如果当前没有活动用户，则切换到backgroup loop（可能）

即使某个事务还没有提交，InnoDB存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释为什么再大的事务提交（commit）的时间也是很短的。
合并插入缓冲（insert buffer）并不是每秒都会发生的。InnoDB存储引擎会判断当前一秒内发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前IO压力很小，可以执行合并插入缓冲的操作。
同样，刷新100个脏页也不是每秒都会发生的。InnoDB存储引擎通过判断当前缓冲池中脏页的比例是否超过了配置文件中innodb_max_dirty_pages_pct这个参数（默认为90，代表90%）如果超过了这个阈值，InnoDB存储引擎认为需要做磁盘同步的操作，将100个脏页写入磁盘中。

每十秒的操作，主要有：

• 刷新100个脏页到磁盘（可能的情况下）
• 合并至多5个插入缓冲（总是）
• 将日志缓冲刷新到磁盘中（总是）
• 删除无用的undo页（总是）
• 刷新100个或者10个脏页到磁盘（总是）

在以上过程中，InnoDB存储引擎会先判断过去10秒之内的磁盘IO操作是否小于200次，如果是，InnoDB存储引擎认为当前有足够的磁盘IO能力，因此将100个脏页刷新到磁盘。接着InnoDB存储引擎会合并插入缓冲。不同于每一秒操作时可能发生合并插入缓冲操作，这次的合并插入缓冲操作总会在这个阶段进行。之后InnoDB存储引擎会再进行一次将日志缓冲刷新到磁盘的操作。这和每秒一次时发生的操作是一样的。
接着InnoDB存储引擎会进行一步执行full purge操作，即删除无用的undo页。对表进行update、delete这类操作时，原先的行被标记为删除，但是因为一致性读的关系，需要保留这些行版本的信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，比如有时候可能还有查询操作需要读取之前版本的undo信息，如果可以删除，InnoDB会立即将其删除。从源代码中可以发现，InnoDB存储引擎在执行full purge操作时，每次最多尝试回收10个undo页。然后InnoDB存储引擎会判断缓冲池中脏页的比例。如果有超过70%的脏页，则刷新100个脏页到磁盘，如果脏页比例小于70%，则只需刷新10%的脏页到磁盘。
接下来看background loop，若当前没有用户活动（数据库空闲时）或者数据库关闭（shutdown），就会切换到这个循环。background loop会执行以下操作：

• 删除无用的undo页（总是）
• 合并20个插入缓冲（总是）
• 跳回到主循环（总是）
• 不断刷新100个页直到符合条件（可能，跳转到flush loop中完成）

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

InnoDB1.2.X版本之前的Master Thread

在了解了1.0.X版本之前的Master Thread的具体实现过程后，细心的读者会发现InnoDB存储引擎对于IO其实是有限制的，在缓冲池向磁盘刷新时其实都做了一定的硬编码。在磁盘技术飞速发展的今天，当固态磁盘（SSD）出现时，这种规定在很大程度上限制了InnoDB存储引擎对磁盘IO的性能，尤其是写入性能。
从前面的伪代码来看，无论何时，InnoDB存储引擎最大只会刷新100个脏页到磁盘，合并20个插入缓冲。如果是在写入密集的应用程序中，每秒可能会产生大于100个脏页，如果是产生大于20个插入缓冲的情况，Master Thread似乎会忙不过来，或者说它总是做得很慢。即使磁盘能在1秒内处理多于100个页的写入和20个插入缓冲的合并，但是由于hard coding，Master Thread也只会选择刷新100个脏页和合并20个插入缓冲。同时，当发生宕机需要恢复时，由于很多数据还没有刷新回磁盘，会导致恢复的时间可能需要很久，尤其是对于insert buffer来说。

InnoDB关键特性

InnoDB存储引擎的关键特性包括：

• 插入缓冲
• 两次写
• 自适应哈希索引
• 异步IO
• 刷新邻接页

插入缓冲

1、insert buffer：insert buffer可能是InnoDB存储引擎关键特性中最令人激动的一个功能。
在InnoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。比如按下列SQL定义表：

CREATE TABLE t (
	a int AUTO_INCREMENT,
    b varchar(30),
    PRIMARY KEY(a)
);

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

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

但是不可能每张表只有一个聚集索引，更多情况下，一张表上有多个非聚集的辅助索引（secondary index）。在这样的情况下产生了一个非聚集的且不唯一的索引。在进行插入操作时，数据页的存放还是按主键a进行存放的，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就需要离散地访问非聚集索引页，由于随机读取的存在而导致插入操作性能下降。当然这不是这个字段上索引的错误，而是因为B+树索引的特性决定了非聚集索引插入的离散性。
需要注意的是，在某些情况下，附注索引的插入依然是顺序的，或者说是比较顺序的。比如用户购买表中的时间字段。在通常情况下，用户购买时间是一个附注索引，用来根据时间条件进行查询。但是在插入时却是根据时间的递增而插入的，因此插入也是较为顺序的。
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代表了已经合并记录页的数量。Insert代表了插入的记录数；merged recs代表了合并的插入记录的数量；merges代表合并的次数，也就是实际读取页的次数。merges：merged recs大约为1:3，代表了插入缓冲将对于非聚集索引页的离散IO逻辑请求大约降低了2/3。

正如前面所说的，目前insert buffer存在的一个问题是，在写密集的情况下，插入缓冲会占用过多的缓冲池内存（innodb_buffer_pool），默认最大可以占到1/2缓冲池内存。

2、Change buffer：InnoDB从1.0.x版本开始引入了Change Buffer，可将其视为Insert Buffer的升级，从这个版本开始，InnoDB存储引擎可以对DML操作-insert DELETE update 都进行缓冲，他们分别是insert buffer，delete buffer，purge buffer。
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
当然和之前一样，change buffer使用的对象依然是非唯一的辅助索引。
对一条记录进行update操作可能分为两个过程：

• 将记录标记为删除
• 真正将其删除

因此DELETE buffer对应的update操作的第一个过程，即将记录标记为删除。purge buffer对应的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最大使用内存的数量：
innodb_change_buffer_max_size值的默认为25，表示最多使用1/4的缓冲池内存空间。而需要注意的是，该参数的最大有效值为50。
3、Insert Buffer的内部实现
通过前一个小节读者应该已经知道了Insert Buffer的使用场景，即非唯一辅助索引的插入操作。但是对于Insert Buffer具体是什么，以及内部怎么实现可能依然模糊，这正是本节所要阐述的内容。
可能令绝大部分用户感到吃惊的是，Insert Buffer的数据结构是一颗B+树。在MySQL4.1之前的版本中每张表有一颗Insert Buffer B+树。而现在的版本中，全局只有一颗Insert BufferB+树，负责对所有的表的辅助索引进行Insert Buffer。而这颗B+树存放在共享表空间中，默认也就是ibdata1中。因此，试图通过独立表空间ibd文件恢复表中数据时，往往会导致CHECK TABLE失败。这是因为表的辅助索引中的数据可能还在Insert Buffer中，也就是共享表空间中，所以通过ibd文件进行恢复后，还需要进行REPAIR TABLE操作来重建表上所有的辅助索引。
Insert Buffer是一颗B+树，因此其也由叶节点和非叶节点组成。非叶节点存放的是查询的search key（键值）。
search key一共占用9字节，其中space表示待插入记录所在表的表空间id，在InnoDB存储引擎中，每个表有一个唯一的space id，可以通过space id查询的得知是哪张表。space占用4字节，marker占用1字节，它是用来兼容老版本的Insert Buffer，offset表示页所在的偏移量，占用4字节。
当一个辅助索引要插入到页（space offset）时，如果这个页不在缓冲池中，那么InnoDB存储引擎首先根据上述规则构造一个search key，接下来查询Insert Buffer这颗B+树，然后再将这条记录插入到Insert Buffer B+树的叶子节点中。
对于插入到Insert Buffer B+树叶子节点的记录，并不是直接将待插入的记录插入，而是需要根据如下规则进行构造。

space、marker、page_no字段和之前的非叶节点中的含义相同，一共占用9字节。第4个字段metadata占用4字节。
IBUF_REC_OFFSET_COUNT是保存两个字节的整数，用来排序每个记录进入Insert Buffer的顺序。因为从InnoDB1.0.x开始支持Change Buffer，所以这个值同样记录进入Insert Buffer的顺序。通过这个顺序回放（replay）才能得到记录的正确值。
从Insert Buffer叶子节点的第5列开始，就是实际插入记录的各个字段了。因此较之原插入记录，Insert Buffer B+树的叶子节点记录需要额外13字节的开销。
因为启用Insert Buffer索引后，辅助索引页（space page_no）中的记录可能被插入到Insert Buffer B+树中，所以为了保证每次Merge Insert Buffer页必须成功，还需要有一个特殊的页用来标记每个辅助页（space page_no）的可用空间。这个页的类型为Insert Buffer Bitmap。
每个Insert Buffer Bitmap页用来追踪16384个辅助索引页，也就是256个区（Extend）。每个Insert Buffer Bitmap页都在16384个页的第二个页中。
4、Merge Insert Buffer
Insert/Change Buffer是一颗B+树。若需要实现插入记录的辅助索引页不在缓冲池中。那么需要将辅助索引记录首先插入到这颗B+树中。但是Insert Buffer中的记录何时合并（merge）列真正的辅助索引中呢？
概括地说：

• 辅助索引页被读取到缓冲池时。
• Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时。
• Master Thread

第一种情况为当辅助索引页被读取到缓冲池时，例如这在执行正常的SELECT查询操作，这时需要检查Insert Buffer Bitmap页，然后确认该辅助索引页是否有记录存放于Insert Buffer B+树中。若有，则将Insert Buffer B+树中该页的记录插入到该辅助索引页中。可以看到对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中，因此性能会有大幅提高。

Insert Buffer Bitmap页用来追踪每个辅助索引页的可用空间，并至少有1/32页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于1/32页，则会强制进行一次合并操作，即强制读取辅助索引页，将Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况。

还有一种情况，之前在分析Master Thread时曾讲到，在Master Thread线程中每秒或每10秒会进行一次Merge Insert Buffer操作，不同之处在于每次进行Merge操作的页的数量不同。

在Master Thread中，执行merge操作的不止是一个页，而是根据srv_innodb_io_capacity的百分比来决定真正要合并多少个辅助索引页。
在Insert Buffer B+树中，辅助索引页根据（space offset）都已排序好，故可以根据（space offset）的排序顺序进行页的选择。然而，对于Insert Buffer页的选择，InnoDB存储引擎并非采用这个方式，它随机地选择Insert Buffer B+树的一个页，读取该页中的space 及之后所需要数量的页。该算法在复杂的情况下应有更好的公平性。同时，若进行merge时，要进行merge的表已经被删除，此时可以直接丢弃已经被Insert/Change Buffer的数据记录。

两次写

如果说Insert Buffer带给InnoDB存储引擎的是性能上的提升，那么doublewrite（两次写）带给InnoDB存储引擎的是数据页的可靠性。
当发生数据库宕机时，，可能InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，比如16KB的页，只写了前4KB，之后就发生了宕机，这种情况被称为部分写失效（partial page write）。在InnoDB存储引擎未使用doublewrite技术前，曾经出现过因为部分写失效而导致数据丢失的情况。
有经验的DBA也许会想，如果发生了写失效，可以通过重做日志进行恢复，这是一个办法。但是必须清楚地认识到，重做日志中记录的是对页的物理操作，如偏移量800，写‘aaa’记录。如果这个页本身已经发生了破坏，再对其重做是没有意义的。这就是说，在应用（apply）重做日志之前，用户需要一个页的副本，当写入失效时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。在InnoDB存储引擎中doublewrite的体系架构如图：

doublewrite由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是在物理磁盘上共享表空间中连续的128个页，即2个分区（event），大小同样为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MG顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的，可以通过以下命令观察到doublewrite运行的情况。

SHOW GLOBAL STATUS LIKE 'innodb_dblwr%'

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件再应用重做日志。
参数skip_innodb_doublewrite可以禁止使用doublewrite功能，这时可能会发生前面提及的写失效问题。不过如果用户有多个从服务器（slave server），需要提供较快的性能，也许启用这个参数是一个办法。不过对于需要提供数据高可靠性的主服务器（master server），任何时候用户都应确保开启doublewrite功能。

自适应哈希索引

哈希（hash）是一种非常快的查找方法，在一般的情况下这种查找时间复杂度为O(1)，即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3-4层，故需要3-4次的查询。
InnoDB存储引擎会监控对表上的各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。
AHI有一个要求，即对这个页的连续访问模式必须是一样的。例如对于（a,b）这样的联合索引页，共访问模式可以是以下情况：

• WHERE a = xx
• WHERE a = xx and b = xx

访问模式一样指的是查询条件一样，若交替进行上述两种查询，那么InnoDB存储引擎不会对该页构造AHI。此外AHI还有如下的要求：

• 以该模式访问了100次
• 页通过该模式访问了N次，其中N = 页中记录 * 1/16

根据InnoDB存储引擎官方的文档显示，启用AHI后，读取和写入速度可以提高了2倍，辅助索引的连接操作性能可以提高5倍。毫无疑问，AHI是非常好的优化模式，其设计思想是数据库自优化的（self-running），即无需DBA对数据库进行认为的调整。
通过命令SHOW ENGINE INNODB STATUS可以看到当前AHI的使用情况。
值得注意的是，哈希索引只能用来搜索等值的查询，如SELECT * FROM table WHERE index_col = ‘xxx’，而对于其他查找类型，如范围查找，是不能使用哈希索引的，因此这里出现了non-hash searchs/s的情况。通过hash searches:non-hash searches可以大概了解使用哈希索引后的效率。
由于AHI是由InnoDB存储引擎控制的，因此这里的信息只提供用户参考。默认AHI是开启状态。

异步IO

为了提高磁盘操作性能，当前的数据库系统都采用异步IO（Asynchronous IO，AIO）的方式来处理磁盘操作。InnoDB存储引擎亦是如此。
与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作。在每扫描一个页并等待其完成后再进行下一次的扫描，这是没必要的。用户可以在发出一个IO请求后立即再发出一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO。
AIO的另一个优势是可以进行IO Merge操作，也就是将多个IO合并为1个IO，这样可以提高IOPS的性能。例如用户需要访问页的（space， page_no）为：
（8,6）（8,7）（8,8）
每个页的大小为16KB，那么同步IO需要进行3次IO操作。而AIO会判断到这三个页是连续的。因此AIO底层会发送一个IO请求，从（8,6）开始，读取48KB的页。
若通过Linux操作系统下的iostat命令，可以通过观察rrqm/s和wrqm/s。
在InnoDB1.1.x之前，AIO的实现是通过InnoDB存储引擎中的代码来模拟实现。而从InnoDB1.1.x开始提供了内核级别的AIO的支持，称为Native AIO。
在InnoDB存储引擎中，read abead方式的读取都是通过AIO完成，脏页的刷新，即磁盘的写入操作则全部由AIO完成。

刷新邻接页

InnoDB存储引擎提供了Flush Neighbor Page（刷新邻接页）的特性。其工作原理为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统的机械磁盘下有着显著的优势。但是需要考虑下面两个问题：

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

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

启动、关闭与恢复

InnoDB是MySQL数据库的存储引擎之一，因此InnoDB存储引擎的启动和关闭更准确的是指在MySQL实例的启动过程中对InnoDB存储引擎的处理过程。
在关闭时，参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。该参数可取值为0,1,2，默认值为1。

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

当正常关闭MySQL数据库时，下次的启动应该会非常正常。但是如果没有正常地关闭数据库，如用kill命令关闭数据库，在MySQL数据库运行中重启了服务器，或者在关闭数据库时，将参数innodb_fast_shutdown设为2时，下次MySQL数据库启动时都会对InnoDB存储引擎的表进行恢复操作。