【InnoDB详解一】体系架构和关键特性

Posted on | In , | | 浏览 次
Words count in article: 14.7k | Reading time ≈ 53

前言

InnoDB存储引擎最早由Innobase Oy公司°开发，被包括在MySQL数据库所有的二进制发行版本中，从MySQL5.5版本开始是默认的表存储引擎（之前的版本IlmoDB 存储引擎仅在Windows下为默认的存储引擎）。该存储引擎是第一个完整支持ACID事务的MySQL存储引擎（BDB是第一个支持事务的MySQL存储引擎，现在已经停止开发），其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和 CPU。

InnoBD通过有如下机制来优化性能：

  1. 缓冲池：使用缓冲池来优化读写性能，写的时候，将页从磁盘刷入缓冲池，再做修改，读的时候，读缓冲池的页，脏数据通过异步适时的刷回磁盘。
  2. 后台线程：使用后台线程来减少对用户线程的阻塞。
  3. 插入缓冲：使用插入缓冲（Insert Buffer）机制来优化非唯一性索引的写性能，使其在缓冲池技术的基础上再少一次磁盘IO。
  4. 两次写：使用两次写（doublewrite）机制来确保数据页从内存刷新到硬盘时如果中途宕机，则仍可以通过数据页副本来恢复该数据页，保证数据页向磁盘flush过程的可靠性。
  5. 自适应哈希索引：通过自适应哈希索引（Adaptive Hash Index，AHI）机制来对缓冲中高频热点的B+树索引页自动建立哈希索引，以替代等值查询，优化查询性能。
  6. 异步IO：通过异步IO（Asynchronous IO，AIO）机制，将read ahead方式的读取，磁盘的写入，数据的恢复等诸多操作通过异步来处理，提高处理效率。
  7. 刷新邻接页：通过刷新邻接页（Flush Neighbor Page）机制，可以在flush数据页进入缓存的时候，顺序将该页所在区（extent）的所有脏页一起flush，将本该多次IO的操作合并一次完成，该机制在传统机械硬盘场景中性能提升明显。

InnoDB存储引擎已经被许多大型网站使用，如用户熟知的Google、Yahoo!、Facebook、YouTube、Flickr，在网络游戏领域有《魔兽世界》、《Second Life》、《神兵玄奇》等。

1 InnoDB的体系架构

下图2简单显示了InoDB的存储引擎的体系架构，从图可见，InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作∶

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

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

1.1 InnoDB的后台线程

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

1.1.1 Master Thread

Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERTBUFER）、UNDO页的回收等。

Master Thread具有最高的线程优先级别。其内部由多个循环（loop）组成∶主循环（loop）、后台循环（backgroup loop）、刷新循环（flush loop）、暂停循环（suspend loop）。Master Thread会根据数据库运行的状态在loop、background loop、flush loop和suspend loop之间切换。

主循环（loop）

Loop被称为主循环，因为大多数的操作是在这个循环中，其中有两大部分的操作———每秒钟的操作和每10秒的操作。伪代码如下∶

1
2
3
4
5
6
7
8
9
void master thread(){
	loop:
	for(int i= 0;i<10;i++){
		do thing once per second;
		sleep 1 second if necessary;
	}
	do things once per ten seconds
	goto loop;
}

可以看到，loop循环通过 thread sleep来实现，这意味着所谓的每秒一次或每10秒一次的操作是不精确的。在负载很大的情况下可能会有延迟（delay），只能说大概在这个频率下。当然，InnoDB源代码中还通过了其他的方法来尽量保证这个频率。

每秒一次的操作包括：（有概念尚不清晰的，后文会做详解）

  1. 日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）;
    • 即使某个事务还没有提交，InoDB存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释为什么再大的事务提交（commit）的时间也是很短的。
  2. 合并插入缓冲（可能）;
    • 合并插入缓冲（Insert Buffr）并不是每秒都会发生的。InnoDB存储引擎会判断当前一秒内发生的IO次数是否小于5次，如果小于5次，InoDB认为当前的IO压力很小，可以执行合并插人缓冲的操作。
  3. 至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）;
    • 同样，刷新100个脏页也不是每秒都会发生的。InoDB存储引擎通过判断当前缓冲池中脏页的比例（buf get_modified_ratio pct）是否超过了配置文件中inodbmax_dirtypages pet这个参数（默认为90，代表90%），如果超过了这个阈值，InoDB存储引擎认为需要做磁盘同步的操作，将100个脏页写人磁盘中。
  4. 如果当前没有用户活动，则切换到 background loop（可能）。

每10秒一次的操作包括：（有概念尚不清晰的，后文会做详解）

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

后台循环（backgroup loop）

接着来看background loop，若当前没有用户活动（数据库空闲时）或者数据库关闭（shutdown），就会切换到这个循环。background loop 会执行以下操作∶

  1. 删除无用的 Undo 页（总是）;
  2. 合并20个插人缓冲（总是）;
  3. 如果当前数据库还是空闲，则跳回到主循环，否则进入flush loop（总是）;

刷新循环（flush loop）
刷新循环只做一件事，每次刷新一百个页到磁盘，不断循环直到缓冲池中的脏页比例小于等于innodb_max_dirty_pages_pct的值（默认90）

  1. 不断刷新100个页直到符合条件（可能，跳转到flush loop中完成）。

暂停循环（suspend_loop）

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

上述核心逻辑是MySQL 1.0.x版本之前的逻辑，在1.0.x版本和1.2.x版本中，Master Thread两次引入了更新

1.0.x版本的改动：

  1. 磁盘技术的快速发展中，对于缓冲池向磁盘刷新时都做了一定的hard coding，这些限制很大程度上限制了InnoDB存储引擎对磁盘IO的性能，尤其是写入性能。因此提供参数innodb_io_capacity用来表示IO的吞吐量，默认200，对于刷新到磁盘页的数量，会按照innodb_io_capacity的百分比来控制：
    • 并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5%;
    • 缓冲池刷新脏页时，刷行脏页的数量为innodb_io_capcity;
  2. 脏页比例参数innodb_max_dirty_pages_pct为90太大了。新版本将其改为了75。
  3. innodb_adaptive_flushing参数的引入，该值影响每秒刷新脏页的数量。
    • 原来的刷新规则是∶脏页在缓冲池所占的比例小于innodb_max_dirty pages pect时，不刷新脏页;大于inodb maxdirtypages_pct时，刷新100个脏页。
    • 随着innodb_adaptive flushing参数的引入，InnoDB存储引擎会通过一个名为buf_flush get_desired_fush_rate的函数来判断需要刷新脏页最合适的数量。
    • 粗略地翻阅源代码后发现 buf flush get desired_fush rate通过判断产生重做日志（redo log）的速度来决定最合适的刷新脏页数量。因此，当脏页的比例小于inodb_max_dirtypages_pct时，也会刷新一定量的脏页。
  4. 引入参数innodb_purge_batch_size
    • 之前每次进行 full purge操作时，最多回收20个Undo页
    • 从InnoDB 1.0.x版本开始引人了参数，该参数可以控制每次 full purge回收的Undo页的数量。该参数的默认值为20，并可以动态地对其进行修改

1.2.x版本的改动：

对于刷新脏页的操作，从Master Thread 线程分离到一个单独的Page Cleaner Thread，从而减轻了Master Thread的工作，同时进一步提高了系统的并发性。

整体伪代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
void master_thread(){
    goto loop;
loop:
for (int i=0;i<10;i++){
    thread_sleep(1) //sleep 1 second-->每秒执行操作(负载在情况下会延迟)
    do log buffer flush to disk  //重做日志缓冲刷新到磁盘，即使这个事务没有提交(总是)
    if ( last_ten_second_ios < 5% innodb_io_capacity) //如果当前的10次数小于(5% * 200=10)(innodb_io_capacity默认值是200)
        do merger 5% innodb_io_capacity insert buffer //执行10个合并插入缓冲的操作(5% * 200=10)
    if ( buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct ) //如果缓冲池中的脏页比例大于innodb_max_dirty_pages_pct(默认是75时)
        do buffer pool plush 100% innodb_io_capacity dirty page //刷新200个脏页到磁盘
    else if enable adaptive flush  //如果开户了自适应刷新
        do buffer pool flush desired amount dirty page //通过判断产生redo log的速度决定最合适的刷新脏页的数量
    if ( no user activity ) //如果当前没有用户活动
        goto backgroud loop  //跳到后台循环
}

//每10秒执行的操作
if ( last_ten_second_ios < innodb_io_capacity)  //如果过去10内磁盘IO次数小于设置的innodb_io_capacity的值（默认是200）
    do buffer pool flush 100% innodb_io_capacity dirty page //刷新脏页的数量为innodb_io_capacity的值（默认是200）
do merger 5% innodb_io_capacity insert buffer  //合并插入缓冲是innodb_io_capacity的5%（10）（总是）
do log buffer flush to disk                    //重做日志缓冲刷新到磁盘，即使这个事务没有提交（总是）
do full purge       //删除无用的undo页 （总是）
if (buf_get_modified_ratio_pct > 70%)          //如果缓冲池中的胜页比例大于70%
    do buffer pool flush 100% innodb_io_capacity dirty page  //刷新200个脏页到磁盘
else
    do buffer pool flush 10% innodb_io_capacity dirty page   //否则刷新20个脏页到磁盘
goto loop
backgroud loop:   //后台循环
do full purge     //删除无用的undo页 （总是）
do merger 5% innodb_io_capacity insert buffer  //合并插入缓冲是innodb_io_capacity的5%（10）（总是）
if not idle:      //如果不空闲，就跳回主循环，如果空闲就跳入flush loop
goto loop:    //跳到主循环
else:
    goto flush loop
flush loop:  //刷新循环
do buf_get_modified_ratio_pct pool flush 100% innodb_io_capacity dirty page //刷新200个脏页到磁盘
if ( buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct ) //如果缓冲池中的脏页比例大于innodb_max_dirty_pages_pct的值（默认75%）
    goto flush loop            //跳到刷新循环，不断刷新脏页，直到符合条件
    goto suspend loop          //完成刷新脏页的任务后，跳入suspend loop
suspend loop:
suspend_thread()               //master线程挂起，等待事件发生
waiting event
goto loop;
}

1.1.2 IO Thread

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

InnoDB1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO thread。在Linux平台下，IO Thread的数量不能进行调整，但是在Windows平台下可以通过参数innodb file_io_threads来增大IO Thread。

从InnoDB1.0x版本开始，read thread和 write thread分别增大到了4个，并且不再使用innodb_file io threads参数，而是分别使用innodb_read_io threads和inodb_write io threads参数进行设置，如∶

可以通过命令SHOW ENGINE INNODB STATUS来观察 InnoDB中的IO Thread∶

可以看到IO Thread0为insert buffer thread。IO Thread1为log thread。之后就是根据参数innodb_readio threads及innodb_write_io threads来设置的读写线程，并且读线程的 ID总是小于写线程。

1.1.3 purge Thread

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

在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。

用户可以在 MySQL数据库的配置文件中添加如下命令来启用独立的Purge Thread:

1
2
[mysqld]
innodb_purge_threads=1

从InnoDB 1.2版本开始，InnoDB 支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。如用户可以设置4个Purge Thread∶

1.1.4 Page Cleaner Thread

Page Cleaner Thread是在InnoDB1.2x版本中引人的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

1.2 InnoDB的内存

1.2.1 缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统（Disk-base Database）。在数据库系统中，由于CPU 速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页”FIX”在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。同样，这也是为了提高数据库的整体性能。

对于InnoDB存储引擎而言，其缓冲池的配置通过参数innodb_buffer poolsize来设置。下面显示一台 MySQL数据库服务器，其将InnoDB存储引擎的缓冲池设置为15GB。

具体来看，缓冲池中缓存的数据页类型有∶索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。下图很好地显示了InnoDB存储引擎中内存的结构情况。

从InnoDB1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。实例数量可以通过参数innodb_buffer_pool_instances来进行配置，该值默认为1：

从 MySQL5.6版本开始，还可以通过information_schema架构下的表INNODB_BUFFER_POOL_STATS来观察缓冲的状态，如运行下列命令可以看到各个缓冲池的使用状态∶

1.2.2 LRU/Free/Flush List

在前一章节中我们知道了缓冲池是一个很大的内存区域，其中存放各种类型的页，一个页的大小默认为16KB，即缓冲池中会存在大量16KB的数据页结构。那么InnoDB存储引擎是怎么对这么大的内存区域进行管理的呢?

1.2.2.1 LRU List

通常来说，数据库中的缓冲池是通过LRU（Latest Recent Used，最近最少使用）算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放从磁盘新读取到的页时，将首先释放LRU列表中尾端的页。

在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法对缓冲池进行管理。稍有不同的是InoDB存储引擎对传统的LRU算法做了一些优化。在InoDB的存储引擎中，LRU列表中还加入了midpoint位置。在默认配置下，该位置在LRU列表长度的5/8处。midpoint位置可由参数inodb old blocks pct控制，如∶

从上面的例子可以看到，参数 innodb oldblocks pect默认值为37。表示新读取的页插入到LRU列表尾端的37%的位置（差不多3/8的位置）。

在InnoDB存储引擎中，把midpoint 之后的列表称为old列表，之前的列表称为new列表。可以简单地理解为new 列表中的页都是最为活跃的热点数据。

当用户需要访问数据时，InnoDB首先会在InnoDB缓冲池查找数据，如果缓冲池中没有数据时，InnoDB会查询硬盘上的数据，并将缓冲池中生成新的页；如果InnoDB缓冲池已满，InnoDB通过LRU算法清除InnoDB缓存池中个别数据块。

每当有新数据块需要加载到InnoDB缓冲池中时，该数据块应变为‘‘数据页’’被插到midpoint的位置，并声明为old数据页。这个算法在InnoDB存储引擎下称为midpoin insertion strategy。

那么old链表中的数据页什么时候能移动到new链表中呢？参数InnoDB_old_blocks_time可以控制这个时间：

  1. 当InnoDB_old_blocks_time的参数值设置为0时。当old部分的数据页被访问到时，该数据页会被提升到链表的头部，并被标记为new数据页。
  2. 当InnoDB_old_blocks_time的参数值大于0时（以1000毫秒或者1秒为例）。old部分数据页插入缓冲池后，1秒之后再次被访问，则该数据页会被提升到链表的头部，并被标记为new数据页。在刚插入到一秒内，即便old部分的数据页被访问，该数据页也不会移动到new链表的头部。

那为什么不采用朴素的LRU算法，直接将最近被sql访问的页放入到LRU列表的首部呢?

这是因为若直接将最近被访问到的页放入到LRU的首部，那么某些SQL操作可能会使热点的页被顶到靠后的位置去，从而影响LRU List的效率。

常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将原本在队首的热点数据页顶到队尾，甚至因为内存空间原因从LRU列表中移除，导致在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。

1.2.2.2 Free List

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free List中。当需要在缓冲池中划分数据页时，首先从Free列表中查找是否有可用的空闲页。

  • 若有，则用磁盘中读取的数据填充该页，并将该页从Free列表中移动到LRU列表中。

  • 若没有，则根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。

1.2.2.3 Flush List

在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。

需要注意的是，脏页既存在于LRU列表中，也存在于 Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

1.2.2.4 List状态查看

可以通过命令SHOW ENGINE INNODB STATUS来观察LRU列表，Free列表和Flush列表的使用情况和运行状态。

当页从LRU列表的old部分加入到new部分时，称此时发生的操作为page made young，而因为innodb old_blocks time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。

通过命令SHOW_ENGINE_INNODB_STATUS可以看到∶当前Buffer_pool_size共有327679个页，即327679*16K，总共5GB的缓冲池。

Free buffers表示当前Free列表中页的数量，Database pages表示LRU列表中页的数量。可能的情况是Free buffers与Database pages的数量之和不等于Buffer pool size。正如之前所说的那样，因为缓冲池中的页还可能会被分配给自适应哈希索引、Lock信息、Insrt Buffer等页，而这部分页不需要LRU算法进行维护，因此不存在于LRU列表中。

pages made young 显示了LRU列表中页从old端移动到new端的次数，因为该服务器在运行阶段没有改变inodb old blocks_time的值，因此not young为0。

youngs/s、non-youngs 表示每秒这两类操作的次数。

Modifed db pages24673就显示了Flush List中脏页的数量。

这里还有一个重要的观察变量——Buffer pool hit rate，表示缓冲池的命中率，这个例子中为100%，说明缓冲池运行状态非常良好。通常该值不应该小于95%。若发生Bufer pool hit rate的值小于95%这种情况，用户需要观察是否是由于全表扫描引起的LRU 列表被污染的问题。

1.2.3 重做日志缓冲

在看上图，InnoDB存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲（redo log buffer）。InoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数 innodb_log buffrsize控制，默认为8MB:

在通常情况下，8MB的重做日志缓冲池足以满足绝大部分的应用，因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

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

1.2.4 额外的内存池

额外的内存池通常被DBA忽略，他们认为该值并不十分重要，事实恰恰相反，该值同样十分重要。在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

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

2 InnoDB的关键特性

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

  • Checkpoint技术
  • 插入缓冲（Insert Buffer）
  • 两次写（Double Write）
  • 自适应哈希索引（Adaptive Hash Index）
  • 异步IO（Async IO）
  • 刷新邻接页（Flush Neighbor Page）

上述这些特性为InnoDB存储引擎带来更好的性能以及更高的可靠性。

2.1 Checkpoint技术

前面已经讲到了，缓冲池的设计目的为了协调 CPU速度与磁盘速度的鸿沟。因此页的操作首先都是在缓冲池中完成的。如果一条 DML语句，如 Update或Delete改变了页中的记录，那么此时页是脏的，即缓冲池中的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池刷新到磁盘。

刷新到磁盘的操作，就是Checkpoint。

倘若每次一个页发生变化，就将新页的版本刷新到磁盘，那么这个开销是非常大的。若热点数据集中在某几个页中，那么数据库的性能将变得非常差。同时，如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了。为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成对未刷新到硬盘的数据的恢复。这也是事务 ACID中D（Durability持久性）的要求。

既然不能每次一个页发生变化，就将新页的版本刷新到磁盘，那么，什么时候将脏页数据刷新到硬盘是合适的呢？先不谈我们应该以什么频率进行一次Checkpoint，我们先来谈什么时候必须要Checkpoint（否则会导致 缓冲池+重做日志 机制出问题）

  1. 当缓冲池不够用时：
    • 当缓冲池不够用时，根据LRU算法会清除最近最少使用的页，如果此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。
  2. 重做日志出现不可用/不够用时：
    • 因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的，这从成本及管理上都是比较困难的。重做日志中记录的已经被flush到磁盘中的部分，我们就认为它是可覆盖重用的。如果重做日志空间中没有可重用的部分，即目前重用日志记录的都是未flush到磁盘的数据，那么必须强制Checkpoint，使得部分重做日志变为可重用。

2.1.1 LSN

对于InnoDB存储引擎而言，是通过LSN（Log Sequence Number）来标记版本的。LSN是一个一直递增的8字节整型数字，表示事务写入到redo日志的字节总量（注意LSN的含义是日志的字节总量）。每个页都有LSN字段，重做日志中也有LSN，Checkpoint也有LSN。

在每个数据页头部的LSN字段，记录当前页最后一次数据修改所对应的重做日志的LSN值，用于在recovery时对比重做日志LSN值，以决定是否对该页进行恢复数据。前面说的checkpoint也是有LSN号记录的，checkpoint的LSN表示已刷新到磁盘的最新的数据所对应的重做日志的LSN，LSN号串联起一个事务开始到恢复的过程。

比如重做日志的文件是600M，LSN的值已经为1G了，也就是LSN=1000000000。因为重做日志是循环使用的，所以我们可以知道LSN=1G=600M+400M，所以重做日志已经重复使用过一整遍后，目前最新的可写入点，在重做日志偏移量400M的位置。

我们执行了一个update语句，产生了一个事务t，这次数据的修改，假设产生了512个字节的日志量，那么LSN就会增加到1000000512，而事务t的修改使得A、B、C三个数据页成为了脏页，那么A、B、C三个数据页的LSN值就会更新为1000000512。如果这时，触发了checkpoint，刚刚好将事务t为止的修改刷新到磁盘，那么此时checkpoint LSN也是1000000512。

可以通过命令SHOW ENGINE INNODB STATUS来观察∶

2.1.2 Checkpoint发生的时机

在InnoDB存储引擎中，Checkpoint发生的时间、条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘。不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint。

在InnoDB存储引擎内部，有两种Checkpoint，分别为∶

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

在InnoDB存储引擎中可能发生如下几种情况的Fuzzy Checkpoint:

  1. Master Thread Checkpoint
  2. FLUSH_LRU_LIST Checkpoint
  3. Async/Sync Flush Checkpoint
  4. Dirty Page too much Checkpoint

2.1.2.1 Master Thread Checkpoint

对于Master Thread中发生的Checkpoint，差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。

2.1.2.2 FLUSH_LRU_LIST Checkpoint

FLUSH_LRU_LIST Checkpoint是因为InoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。在InoDB1.1.x版本之前，需要检查LRU列表中是否有足够可用空间的操作发生在用户查询线程中，显然这会阻塞用户的查询操作。

倘若没有100个可用空闲页，那么InoDB存储引擎会将LRU列表尾端的页移除。如果要移除的这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU和FLUSH列表的，因此称为FLUSH_LRU_LIST Checkpoint。

而从MySQL5.6版本，也就是InnoDB1.2.x版本开始，这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_Iru_scan_depth控制LRU列表中可用页的数量，该值默认为1024。

2.1.2.3 Async/Sync Flush Checkpoint

Async/Sync Flush Checkpoint指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。

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

checkpoint_age = redo_lsn - checkpoint_lsn，表示重做日志中还有多少个字节量的数据没有刷新到磁盘。

再定义以下的变量：

  1. async_water_mark = 75% * total_redo_log_file_size
  2. sync_water_mark = 90% * total_redo_log_file_size

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

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

可见，Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性。在InnoDB 1.2.x版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，而Sync Flush Checkpoint会阻塞所有的用户查询线程，并且等待脏页刷新完成。从InnoDB 1.2.x版本开始——也就是MySQL 5.6版本，这部分的刷新操作同样放入到了单独的Page Cleaner Thread中，故不会阻塞用户查询线程。

2.1.2.4 Dirty Page too much Checkpoint

即脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制：

innodb_max_dirtypages_pct值为75表示，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘。在InnoDB 1.0.x版本之前，该参数默认值为90，之后的版本都为75。

2.2 插入缓冲

插入缓冲本质上是对于为非唯一索引而言的，即对辅助索引的修改操作并非实时更新磁盘中索引的叶子页（索引存于该表的ibd文件中），而是把若干对同一页面的更新缓存起来做，合并为一次性更新操作，减少IO，转随机IO为顺序IO，这样可以避免随机IO带来性能损耗，提高数据库的写性能

2.2.1 Insert Buffer

Insert Buffer可能是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。insert buffer是一种特殊的数据结构（B+ tree）并不是缓存的一部分，而是物理页。

在InoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插人顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引（Primary Key）一般是顺序的，不需要磁盘的随机读取。

但一个表除了聚集索引外，还可能定义辅助索引，我们知道InnoDB中辅助索引是非聚集的。假设我们有一张表t，其中主键是id字段，除此之外还在name字段上面建了一个辅助索引。那么我们在表t每插入一条数据，都需要在id聚集索引树和name非聚集索引上新增索引节点。

  • 前面说过，因为表t的插入顺序就是按照主键自增的，而id聚集索引又是按照id排序的，所以在id聚集索引上新增节点十分方便，只要在顺序插入即可，性能很高。

  • 而在name非聚集索引上新增索引节点，因为表t记录的插入顺序按照id自增的顺序，不是按照name自增的顺序，但name非聚集索引又是按照name字段顺序排列的，所以表t的每次插入，都需要在name非聚集索引上离散的插入新的索引节点，随机IO的消耗太大，性能十分蛋疼。

为了应对这种情况，InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入;若不在，则先放入到一个Insert Buffer对象中。

好似欺骗数据库这个非聚集的索引已经插到叶子节点了，而实际并没有，只是存放在另一个位置。然后再以Master Thread的调度规则进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为插入的都是在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

然而Insert Buffer的使用需要同时满足以下三个条件∶

  1. 修改的非聚集索引页不在缓冲池中
    • 因为如果在缓冲池中，直接改缓冲池就行了，改内存不比改磁盘，没有什么顺序IO/随机IO的性能差异。
  2. 索引是辅助索引（secondary index）;
    • 因为聚集索引的性能很好，不需要用到Insert Buffer。
  3. 辅助索引不是唯一（unique）的。
    • 因为如果辅助索引是唯一的，那么在插入辅助索引树前，要先判断插入的值是否已经在树中重复了，查询操作又是随机IO。
    • 本来Insert Buffer就是为了避免随机IO，既然唯一性辅助索引的插入避免不了随机IO，那Insert Buffer也就没有什么意义了。

2.2.2 Change Buffer

InnoDB从1.0.x版本开始引入了Change Buffer，可将其视为Insert Buffer的升级。从这个版本开始，InnoDB存储引擎可以对DML操作——INSERT、DELETE、UPDATE 都进行缓冲，他们分别是∶Insert Buffer、Delete Buffer、Purge buffer。

当然和之前Insert Buffer一样，Change Buffer适用的对象依然是非唯一的辅助索引。

同时，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_buffr_max_size来控制Change Buffer最大使用内存的数量∶

innodb_change_buffer_max_size值默认为25，表示最多使用1/4的缓冲池内存空间。而需要注意的是，该参数的最大有效值为 50。

2.2.3 Insert Buffer的内部实现

Insert Buffer具体是什么呢，内部怎么实现呢？

可能令绝大部分用户感到吃惊的是，Insert Buffer的数据结构是一棵B+树。在MySQL 4.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存储引擎中，每个表有一个唯一的 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、offset字段和之前非叶节点中的含义相同，一共占用9字节。
  • 第4个字段metadata 占用4字节，其存储的内容如下表所示。
    • 最核心的字段是IBUF_REC_OFFSET_COUNT字段，它保存两个字节的整数，用来排序每个记录进入Insert Buffer的顺序。因为从InnoDB1.0.x开始支持Change Buffer，所以这个值同样记录进入Change Buffer的顺序。merge的时候通过这个顺序回放（replay）才能得到记录的正确值。
  • 从Insert Buffer 叶子节点的第5列开始，就是实际插入记录的各个字段了。因此较之原插入记录，Insert Buffer B+树的叶子节点记录需要额外13字节的开销。

因为启用Insert Buffer索引后，辅助索引页（space，offset）中的记录可能被插入到Insert Buffer B+树中，所以为了保证每次Merge Insert Buffer页都能成功，还需要有一个特殊的页用来标记每个辅助索引页（space，offset）的可用空间。这个页的类型为Insert Buffer Bitmap。

每个Insert Buffer Bitmap页用来追踪16384个辅助索引页，也就是256个区（Extent）。每个辅助索引页在Insert Buffer Bitmap页中占用4位（bit），这四位的含义见下表

2.2.4 Merge Insert Buffer

我们已经知道了Insert/Change Buffer是一棵B+树。若需要做插入操作的辅助索引页不在缓冲池中，那么需要将辅助索引记录首先插入到这棵B+树中。但是Insert Buffer中的记录何时合并（merge）到真正的辅助索引中呢?这是我们接下来关注的重点。

概括地说，Merge Insert Buffer的操作可能发生在以下几种情况下∶

  1. 辅助索引页被读取到缓冲池时;
    • 例如这在执行正常的SELECT查询操作，如果辅助索引页不在缓冲池中，这时我们需要优先将辅助索引读入缓冲池
    • 紧接着检查Insert Buffer Bitmap页，看看该辅助索引页是否有记录存放于Insert Buffer B+树中。若有，则将Insert Buffer B+树中该页的记录插入到缓冲池中的该辅助索引页中。
    • 这样便可以将对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中，因此性能会有大幅提高。
  2. Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时;
    • Insert Buffer Bitmap页用来追踪每个辅助索引页的可用空间，若插入辅助索引记录时检测到插入记录后，辅助索引页的可用空间会小于1/32页，则会强制进行一个合并操作，即强制读取辅助索引页至缓冲池，然后将Insert Buffer B+树中该页的记录及待插入的记录插入到缓冲池的辅助索引页中。
  3. Master Thread。
    • 在Master Thread线程中每秒或每10秒会进行一次Merge Insert Buffer的操作，不同之处在于每次进行merge操作的页的数量不同。
    • 在Master Thread中，执行merge操作的不止是一个页，而是根据 srv_inodb io capactiy的百分比来决定真正要合并多少个辅助索引页。

那么InnoDB存储引擎又是根据怎样的算法来确定Insert Buffer B+树中哪些记录是需要合并的呢?

在Insert Buffer B+树中，辅助索引修改记录会根据（space，offset）排序好，故可以根据（space，offset）的排序顺序进行页的选择。然而，对于Insert Buffer页的选择，InnoDB存储引擎并非采用这个方式，它随机地选择Insert Buffer B+树的一个页，读取该页中的space及之后所需要数量（不同场景需要的数量不同）的页。

该算法在复杂情况下应有更好的公平性。

同时，若进行merge时，要进行merge的表已经被删除，此时可以直接丢弃已经被Insert/Change Buffer 的数据记录。

2.2.5 缓冲池和Insert Buffer的区别

我们前面学过缓冲池技术，假如我们要修改页号为40的索引页，而这个页正好不在缓冲池内。

此时我们依照缓冲池的机制，整个写过程如上图需要3步：

  1. 先把需要为40的索引页，从磁盘加载到缓冲池，一次磁盘随机读操作；
  2. 修改缓冲池中的页，一次内存操作；
  3. 写入redo log，一次磁盘顺序写操作；

注意：没有命中缓冲池的时候，至少产生一次磁盘IO？

而InnoDB加入Insert Buffer优化后，则写入流程优化为：

  1. 在Insert Buffer B+树中记录这个操作，一次内存操作；
  2. 写入redo log，一次磁盘顺序写操作；

可以看到，Insert Buffer机制能在缓冲池技术的基础上减少一次磁盘IO，其性能与这个索引页在缓冲池中的情况相近。可以看到，40这一页，并没有加载到缓冲池中。此时数据库异常奔溃，则能够从redo log中恢复数据；

假设稍后的一个时间，有请求查询索引页40的数据。

此时的流程如序号1-3：

  1. 缓冲池未命中，则从磁盘载入索引页，这次磁盘IO不可避免；
  2. 从Insert Buffer B+树中读取辅助索引页的修改记录；
  3. 根据Insert Buffer修改从缓存载入的索引页，使其达到最终态，并放到缓冲池LRU List里；

可以看到，40这一页，在真正被读取时，才会被加载到缓冲池中。

注意，insert buffer的merge操作是将索引文件从磁盘载入到缓冲池的索引页中，并且将insert buffer里的更改再执行到缓冲池索引页上。

系统大部分空闲时或在慢速关闭期间运行的清除（purge）操作会定期将缓冲池中的辅助索引页（此时一般为脏页）写入磁盘。与每个值立即写入磁盘相比，purge操作可以更有效地为一系列索引值写入磁盘块。

2.2.6 查看insert/change Buffer

用户可以通过命令SHOW ENGINE INNODB STATUS来查看Insert Buffer的信息∶


  • seg size显示了当前Insert Buffer的大小为11336×16KB，大约为177MB;
  • free list len代表了空闲列表的长度;
  • size代表了已经合并记录页的数量。

而黑体部分的第2行可能是用户真正关心的，因为它显示了插入性能的提高。

  • Inserts代表了插入的记录数;
  • mergedrecs代表了合并的插入记录数量;
  • merges代表合并的次数，也就是实际读取页的次数。
  • merges∶merged recs大约为1∶3，代表了插入缓冲将对于非聚集索引页的离散1O 逻辑请求大约降低了23。

在MySQL5.5版本中通过命令SHOW ENGINE INNODB STATUS，可以观察到change Buffer的信息∶


可以看到这里显示了merged operations和discarded operation，并且下面具体显示Change Buffr中每个操作的次数。

  • insert表示Insert Buffer;
  • delete mark表示 Delete Buffer;
  • delete表示Purge Buffer;
  • discarded operations表示当Change Buffer发生merge 时，表已经被删除，此时就无需再将记录合并（merge）到辅助索引中了。

2.3 两次写

如果说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个区（extent），大小同样为2MB。

在对缓冲池的脏页进行flush时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。

在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的。可以通过以下命令观察到doublewrite 运行的情况∶

可以看到，doublewrite一共写了6325194个页，但实际的写人次数为100399，基本上符合64∶1。

如果发现系统在高峰时的Innodb_dblwr_pages_written∶Innodb_dblwr_writes远小于64∶1，那么可以说明系统写人压力并不是很高。

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

参数skip_innodb_doublewrite可以禁止使用doublewrite功能，这时可能会发生前面提及的写失效问题。不过如果用户有多个从服务器（slave server），需要提供较快的性能（如在slaves server上做的是RAID0），也许启用这个参数是一个办法。不过对于需要提供数据高可靠性的主服务器（master server），任何时候用户都应确保开启doublewrite 功能。

2.4 自适应哈希索引

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

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。

AHI是通过缓冲池中的的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

AHI有一个要求，即对这个页的连续访问模式必须是一样的。例如对于（a，b）这样的联合索引页，其访问模式可以是以下情况∶

  1. WHERE a=xxx
  2. WHERE a=xxx and b=xxx

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

此外 AHI 还有如下的要求∶

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

根据InnoDB存储引擎官方的文档显示，启用AHI后，读取和写入速度可以提高2 倍，辅助索引的连接操作性能可以提高5倍。毫无疑问，AHI是非常好的优化模式，其设计思想是数据库自优化的（self-tuning），即无需 DBA对数据库进行人为调整。

通过命令SHOW ENGINE INNODB STATUS可以看到当前AHI的使用状况∶

现在可以看到AHI的使用信息了，包括AHI的大小、使用情况、每秒使用AHI搜索的情况。

值得注意的是，哈希索引只能用来搜索等值的查询，如SELECT* FROM table WHERE index_col='xxx'。而对于其他查找类型，如范围查找，是不能使用哈希索引的，因此这里出现了non-hash searches/s的情况。通过 hash searches∶non-hash searches的比值，可以大概了解使用哈希索引后的效率。

参数 innodb_adaptive_hash_index可以控制是否启动AHI。

2.5 异步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会判断到这三个页是连续的（显然可以通过（space，page_no）得知）。因此AIO底层会发送一个IO 请求，从（8，6）开始，读取48KB的页。

在InnoDB1.1.x之前，AIO的实现通过InnoDB存储引擎中的代码来模拟实现。而从InnoDB1.1.x开始（InnoDB Plugin不支持），提供了内核级别AIO的支持，称为Native AIO。因此在编译或者运行该版本MySQL时，需要libaio库的支持。

需要注意的是，Native AIO需要操作系统提供支持。Windows系统和Linux系统都提供Native AIO支持，参数innodb_use_native_aio用来控制是否启用Native AIO，在Linux操作系统下，默认值为 ON。

用户可以通过开启和关闭Native AIO功能来比较InnoDB性能的提升。官方的测试显示，启用Native AIO，恢复速度可以提高 75%。

在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，即磁盘的写人操作则全部由 AIO完成。

2.6 刷新邻接页

InnoDB存储引擎还提供了Flush Neighbor Page（刷新邻接页）的特性。其工作原理为∶当flush一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行flush。

这样做的好处显而易见，通过AIO可以将多个IO写人操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。但是需要考虑到下面两个问题∶

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

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

0%