Innodb是如何运转的

---

本文适用于对Innodb存储引擎实现有过基本了解的开发者进行阅读,为了尽可能浓缩核心要点，会删繁就简，对于不懂之处，需要大家自行额外查询资料进行学习。

---

Innodb体系架构

Innodb存储引擎由多个不同作用的内存块组成，主要负责:

• 维护所有线程需要访问的内部数据结构
• 缓存磁盘数据
• 重做日志缓冲
  …

Innodb运行时会创建很多后台运行的线程来负责将内存缓存的脏数据刷盘。

---

后台线程

Master Thread

Master Thread是非常核心的后台线程，主要负责脏页的刷新,合并插入缓冲,UNDO页回收等。

---

IO Thread

Innodb存储引擎中使用大量AIO进行IO读写, 而IO thread的工作主要是负责这些IO请求的回调接口处理。

异步IO在IO请求完毕后，可以通过先前指定的回调函数直接对请求进行处理。

Innodb主要提供了四种类型的IO Thread,分别是write,read,insert buffer和log。通过:

show engine innodb status\G
1

命令，我们观察Innodb中的IO Thread。

---

Purge Thread

事务提交后，当前事务使用的undolog可能不再需要，此时就可以通过PurgeThread来回收这些已经使用并分配的undo页。

起初purge操作仅在Master Thread中完成，后面随着Innodb升级，又单独开启了线程作为Purge Thread，我们可以在配置文件中添加如何命令来设置purge Thread的线程数:

[mysqld]
innodb_purge_threads=1
1
2

Innodb 1.1版本中，最多只能设置一个Purge Thread,及时设置大于1,启动时也会改为1，并给出相关错误提示。
+从1.2版本开始，支持多个Purge Thread,通过下面命令可以查看当前系统的purge_thread线程数量:

---

Page Cleaner Thread

负责脏页的异步刷新操作，目的也是为了减轻原Master Thread的工作，以及减少用户线程因为主动刷脏而阻塞的可能性和时间。

---

内存

缓存池

Innodb基于磁盘进行存储，并将记录按照页的方式进行管理，而缓冲池减少用来将磁盘读到的页存放在缓冲池中，下一次要读取某个页的时候，会先去检查缓冲池中是否存在，如果存在，则称该页在缓冲池中被命中，直接读取该页，否则读取磁盘上的页。

当我们要修改某个页的时候，首先还是判断该页是否在缓冲池中，不在就先读取到缓冲池中，然后修改在缓冲池中的页，随后不是直接将修改后的页刷盘，而是通过一种CheckPoint的机制刷新回磁盘。

因此，可以见得缓冲池的大小直接影响数据库的整体性能，毕竟频繁的磁盘IO是降低性能的最大元凶，对于Innodb存储引擎而言，其缓冲池的配置通过参数innodb_buffer_pool_size来设置，如下所示:

具体来看，缓冲池中缓存的数据页类型有: 索引页,数据页,undo页,插入缓存,自适应哈希索引,Innodb存储的锁信息,数据字典信息等。

由于缓冲池是共享资源，在多线程环境下，势必存在资源竞争问题，因此，我们可以可以通过增加缓冲池实例的方式，来减少数据库内部资源竞争，每个页根据哈希值平均分配到不同的缓冲池实例中。

我们可以通过下面这个参数进行配置,该值默认为1:

如果我们想看到当前buffer pool使用的详细信息，可以通过下面这个通用命令查看:

---

LRU List

Innodb缓冲池中使用页作为最小分配单位，页的默认大小为16KB，提到缓冲池就离不开缓存空闲区间管理，缓冲满时需要淘汰部分缓存的旧数据，缓存中的脏数据刷盘等

数据库缓冲池通常采用LRU(最近最少使用)算法进行管理，即最频繁使用的页在LRU列表前端，而最少使用的页在LRU列表的尾端，当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。

Innodb存储引擎同样采用了LRU思想，但是结合实际情况进行了相关调整:

LRU列表加入了mid point位置，新读取到的页并不是直接放到LRU列表首部，而是放入到了midPoint位置处，我们可以通过下面命令查看midPoint的分界点具体位置:

说明默认热点区域占据LRU列表头部67%大小，而冷数据占据LRU尾部37%大小。

采用midPoint的好处在于可以避免某些查询操作一次性查询出一堆页面，但是这些页面都只会被访问一次，却把经常访问的热点页面都挤出去了。

引入了midPoint之后，随之而来的问题就是在满足什么条件的情况下，才会将冷区域中的页面移动到热点区域中呢？

innodb引入了innodb_old_blocks_time参数来控制页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

---

Free List

LRU列表用来管理已经读取的页，但是数据库刚启动的时候，LRU列表是空的，这时页都存放在Free列表中，当需要从缓冲池中分页时，首先从Free列表中查询是否有可用页，如果有则将该页从Free列表中删除，放入到LRU链表中。

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

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

可以通过下面的命令来观察LRU列表及FREE列表的使用情况和运行状态:

注意: Free链表中页加上LRU链表管理的页之和，一般不等于buffer pool size，因为缓冲池中的页还会分配给自适应哈希索引，Lock信息，Insert Buffer等页，而这些页不需要LRU算法进行维护，因此不存在与LRU列表中。

还有一个重要指标是buffer pool hit rate,表示缓冲池的命中率，该值通常不应该小于百分之95，否则就需要检查看看是不是因为全部扫描导致LRU列表被污染了。

---

unzip_LRU

Innodb从1.0.x版本开始支持压缩页功能，即将原本16kb的页压缩为1kb,2kb,4kb和8kb,而由于页的大小发生了变化，LRU列表也有了些许改变，对于非16KB的页，是通过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的页和两个4KB的页，分别存放到对应的unzip_LRU列表中。

---

flush list

当LRU列表中某个页被修改后，会被放入flush列表中，该列表中存放的都是脏页，脏页同时存在于LRU列表和flush列表中。

LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

---

重做日志缓冲(redo log buffer)

Innodb存储引擎会首先将重做日志信息放入到这个缓冲区，然后按一定频率将其刷新到redo log日志文件中去，redo log buffer一般不需要设置很大，因为一般情况下每秒种都会将重做redo log buffer刷新到日志文件。

redo log buffer刷新主要有以下三种情况:

1. Master Thread每一秒钟将redo log buffer刷新到redo log file；
2. 每个事务提交时会刷新redo log buffer
3. 当redo log buffer剩余空间小于1/2时，会刷新一次

---

额外的内存池

额外内存池中主要存储一些数据结构本身的内存，如: 每个缓冲池的帧缓冲(frame buffer)，缓冲控制对象(buffer control block)这些对象记录了一些诸如LRU，锁，等待等信息。

---

checkpoint技术

如果每次修改了buffer pool中一个页面，就将被修改的页面刷新到磁盘上，那么这个开销是非常大的,并且如果这个页面是热点页面，可能刚刷新完，立马又变成脏页了，因此最好的办法是延迟写入；

并且如果采用同步刷盘策略，必定也会阻塞客户端线程，因此最好的办法是采用异步刷脏的策略。

但是，异步刷脏需要考虑系统突然奔溃导致脏数据未能及时刷盘的问题，此时系统重启就会造成数据丢失，因此为了避免数据丢失问题，许多数据库系统采用预写日志的方式，即事务提交时，把redo log 日志刷盘就行了，那么脏页就不着急刷盘了。

如果发生奔溃，那么系统重新启动时，可以通过redo log日志将奔溃前未刷盘的脏数据进行重放进行数据恢复。

重做日志的设计一般都是循环使用的，并不是无限增大的，通过两个指针就可以指出当前日志文件哪部分保存的是还没有刷盘的脏操作，哪部分是已经剩余空间。

每当取出flush链表尾部一个脏页进行刷盘时，就会对应把checkpoint往前推进，将记录该脏页对应的日志删除掉。

flush链表中的页面是按照更新时间排序的，最近一次更新时间越短的排在前端，后端是更新时间已经隔了很长时间的。

而指示当前checkPoint位置的，是通过LSN(Log Sequence Number)来标记的，每个页有LSN，重做日志中也有LSN，CheckPoint也有LSN:

LSN是不断增大的

CheckPoint所做的事情就是将脏页刷盘，然后更新对应的LSN即可，难点在于每次刷新多少页面到磁盘，啥时候触发checkpoint。

Innodb存储引擎内部，有两种CheckPoint。

---

Sharp Checkpoint

在数据库关闭的时候，把所有的脏页都刷新会磁盘，这是默认的工作方式。

---

Fuzzy Checkpoint

每次刷新一部分脏页，在Innodb存储引擎中会发生如下几种情况的Fuzzy Checkpint:

• Master Thread Checkpoint: Master Thread以每秒或者每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。
• FLUSH_LRU_LIST_Checkpoint: Innodb1.1.x版本前，每次用户查询时，都会同步检查LRU链表是否有100个左右的剩余空闲页可供使用，如果没有，那么此时会将LRU列表尾端的页移除，如果有脏页，那么还需要进行CheckPoint,此过程会阻塞用户查询线程。Innodb 1.2.x开始，该检查会由page cleaner线程完成，并且用户可以通过innodb_lru_scan_depth来控制lru列表中可用页的数量，该值默认为1024。
• Async/Sync Flush Checkpoint: redo log 日志剩余空间不足时，需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。

对于判断redo log日志剩余空间是否不足，innodb设置了高水位和低水位标记:

async_water_mark(低水位)=75% * redo log日志文件总大小
sync_water_mark(高水位)=95% * redo log日志文件总大小
1
2

• 如果redo log 中未刷盘脏数据占据日志大小小于低水位,那么不需要刷新任何脏页到磁盘
• 如果大于低水位,小于高水位,那么此时触发async flush,从flush列表中刷新足够的脏页回磁盘，使得刷新后满足小于低水位
• 如果超过高水位，那么触发sync flush,从flush列表中刷新足够的脏页会磁盘，使得刷新后满足小于低水位

Innodb 1.2.x版本前，Async flush checkpoint会阻塞发现问题的用户查询线程，而sync flush checkpoint会阻塞所有的用户查询线程，并且等待脏页刷新完成。

从innodb 1.2.x版本开始，也就是mysql 5.6版本，这部分的刷新操作同样放到了单独的page cleaner thread中，因此不会阻塞用户查询线程。

---

• Dirty Page too much: 脏页数量太多，也会强制触发checkpoint, 触发阈值通过下面这个参数控制

---

Master Thread的工作方式

Innodb 1.0.x版本之前

Master Thread具有最高的线程优先级，内部由多个循环(loop)组成:

• 主循环(loop)
• 后台循环(backgroup loop)
• 刷新循环(flush loop)
• 暂停循环(suspend loop)

Master Thread会根据数据库的运行状态在上面这几个循环中进行切换。

---

Loop主循环由两大部分组成: 每秒钟的操作和每10秒钟的操作，伪代码如下:

void master_thread(){
   loop:
      for(int i=0;i<10;i++){
         做每秒钟的操作
         根据条件判断是否需要睡眠1秒(尽量保证一次循环的耗时在1秒内)
     }
     做10秒钟的操作
     goto loop;
}
1
2
3
4
5
6
7
8
9

每秒一次的操作包括:

• redo log缓存刷新到磁盘，即使这个事务没有提交，这也解释了为什么再大的事务提交的时间也是很短的(总是)
• 合并插入缓存(可能)，该操作只会在存储引擎判断当前一秒发生的IO次数小于5次时发生，因为innodb认为当前IO压力很小。
• 至多刷新100个innodb的缓冲池中的脏页到磁盘(可能)，该操作只会在innodb判断脏页比例(buffer_get_modified_ratio_pct)超过配置文件中innodb_max_dirty_pages_pct(默认90%)这个参数时发生。
• 如果当前没有用户活动，则切换到backgroup loop(可能)

上述伪代码继续完善:

void master_thread(){
   loop:
      for(int i=0;i<10;i++){
         thread_sleep(1)
         flush redo log buffer to disk
         if(last_one_second_ios<5)
            merge at most 5 insert buffer
         if(buffer_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
           flush 100 dirty page
         if(no user activity)
           goto backgroup loop     
     }
     做10秒钟的操作
     goto loop
     backgroup loop:
          do something
          goto loop
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

每10秒的操作包括:

• 刷新100个脏页到磁盘(可能),如果过去10秒内磁盘的IO操作小于200次，那么Innodb就认为此事磁盘负载较低，可以执行刷脏工作。
• 合并至多5个插入缓存(总是)
• 将redo log buffer刷盘(总是)
• 删除无用的undo页(总是)，该操作也被称为full purge, 因为为了支持MVCC，当对表进行修改或者删除时，原先的行只是被标记为删除，因此full purge过程中会判断这些被标记为删除的行是否可以被删除，如果可以，就立即删除。每次最多尝试回收20个undo 页。
• 刷新100个或者10个脏页到磁盘(总是)，如果脏页比例超过70%，则刷新100个脏页，小于70%，则只需刷新10%的脏页。

void master_thread(){
   loop:
      for(int i=0;i<10;i++){
         thread_sleep(1)
         flush redo log buffer to disk
         if(last_one_second_ios<5)
            merge at most 5 insert buffer
         if(buffer_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
           flush 100 dirty page
         if(no user activity)
           goto backgroup loop     
     }
     
     if(last_ten_second_ios<200)
      flush 100 dirty page
      
     merge at most 5 insert buffer
     
     flush log buffer to disk
     
     do full purge
     
     if(buffer_get_modified_ratio_pct>70%)
       flush 100 dirty page
     else
       flush 10 dirty page
         
     goto loop
     backgroup loop:
          do something
          goto loop
}
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

当前没有用户活动(数据库空闲时)或者数据库关闭时，就会切换到backgroup loop执行以下操作:

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

若flush loop中也没有事情可做,Innodb会切换到suspend loop,将master thread挂起，等待事件发生，如果用户启用了innodb存储引擎，但是没有使用任何innodb存储引擎表，那么master thread总是处于挂起状态。

void master_thread(){
   loop:
      for(int i=0;i<10;i++){
         thread_sleep(1)
         flush redo log buffer to disk
         if(last_one_second_ios<5)
            merge at most 5 insert buffer
         if(buffer_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
           flush 100 dirty page
         if(no user activity)
           goto backgroup loop     
     }
     
     if(last_ten_second_ios<200)
      flush 100 dirty page 
     merge at most 5 insert buffer
     flush log buffer to disk
     do full purge  
     if(buffer_get_modified_ratio_pct>70%)
       flush 100 dirty page
     else
       flush 10 dirty page
     goto loop
     
     backgroup loop:
          full purge
          merge 20 insert buffer
          if not idle
             goto loop:
         else
            goto flush loop    
    
    flush loop:
         flush 100 dirty page
         if(buffer_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
           goto flush loop
    goto suspend loop
     
    suspend loop:
         suspend_thread()
         waiting event
         goto loop                
}
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

---

Innodb 1.2.x版本之前

Innodb 1.0.x版本之前的Master Thread最大问题在于硬编码限制死了从缓冲区向磁盘刷新页的数量，例如: 最大只会刷新100个脏页到磁盘，合并20个插入缓冲。

但是随着磁盘技术的发展，当固态硬盘(SSD)出现的时候，这种规定无法让Innodb完全释放出磁盘IO的性能，并且对于写入密集型应用来说，会让master thread忙不过来。

刷脏慢，如果遇到宕机，也会导致恢复时间变长。

Innodb 1.0.x版本开始提供了innodb_io_capacity用来表示磁盘IO的吞吐量，默认值为200，对于刷新到磁盘页的数量，会按照innodb_io_capacity的百分比来进行控制:

• 合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5%
• 在从缓冲区刷新脏页时,刷新脏页的数量为innodb_io_capacity

如果用户使用了SSD类的磁盘，或者将几块磁盘做了RAID，当存储设备拥有更高的IO速度时，完全可以将innodb_io_capacity的值调得再高点，直到符合磁盘IO的吞吐量为止。

---

另一个问题就是innodb_max_dirty_pages_pct默认值为90的问题，innodb存储引擎在每秒刷新缓冲池和flush loop时会判断当前脏页比例是否大于innodb_max_dirty_pages_pct，如果大于，才刷新100个脏页。

如果在内存很大的情况下，或者数据库服务压力很大的情况下，这时刷新脏页的速度反而会降低，同样，在数据库的恢复阶段可能需要更多的时间。

innodb_max_dirty_pages_pct经过测试，默认值已经被改为了75%，和Google测试的80比较接近，这样既可以加快刷脏效率，也能保证磁盘IO负载。

---

Innodb .10.x版本还引入了innodb_adaptive_flushing(自适应刷新)，该值影响每秒刷新脏页的数量。

原来的刷新规则是，脏页在缓冲池占比小于innodb_max_dirty_pages_pct时，不刷脏，大于时，刷新100个脏页。

innodb_adaptive_flushing参数引入后，innodb会通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页的最合适数量，该函数核心是通过判断产生redo log的速度来决定最合适的刷新脏页数量。

因此，当脏页比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页。

---

除此之外，还将每次进行full purge操作时，最多回收20个undo页的硬编码换为了由innodb_purge_batch_size参数进行动态控制，该参数默认为20:

innodb 5.7.36版本中已经被设置为了300

---

从innodb 1.0.x版本开始，master thread的伪代码经过优化，最终变成如何所示:

void master_thread(){
   loop:
      for(int i=0;i<10;i++){
         thread_sleep(1)
         flush redo log buffer to disk
         if(last_one_second_ios < 5% * innodb_io_capacity)
            merge at most 5% * innodb_io_capacity insert buffer
         if(buffer_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
           flush 100% * innodb_io_capacity  dirty page
         else if enable adaptive flush
              flush amount dirty page by redo log produce rate
         if(no user activity)
           goto backgroup loop     
     }
     
     if(last_ten_second_ios<innodb_io_capacity)
      flush 100% * innodb_io_capacity  dirty page
     merge at most 5% * innodb_io_capacity insert buffer
     flush log buffer to disk
     do full purge  
     if(buffer_get_modified_ratio_pct>70%)
       flush 100% * innodb_io_capacity  dirty page
     else
      flush 10% * innodb_io_capacity  dirty page
     goto loop
     
     backgroup loop:
          full purge
          merge  100% * innodb_io_capacity insert buffer
          if not idle
             goto loop:
         else
            goto flush loop    
    
    flush loop:
         flush  100% * innodb_io_capacity  dirty page
         if(buffer_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
           goto flush loop
    goto suspend loop
     
    suspend loop:
         suspend_thread()
         waiting event
         goto loop                
}
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
45

我们可以通过show engine innodb status来查看master thread目前运行状态:

主循环运行了45次，每秒挂起45次(说明负载不大)，10秒活动进行了4次，符合1:10。

background loop进行了6次，flush loop也进行了6次。

当前服务器压力偏小，因此可以看到和理论值相差不大，如果是一台压力很大的mysql数据库服务器，可能会看到下面场景:

可以看到主循环运行了2188次，但是循环中每秒挂起的sleep操作只运行了1537次，这是因为innodb并不总是每个循环sleep一次，当压力大的时候，会跳过sleep。

因此通过这两者之间的差值也可以看出当前数据库的负载压力

---

Innodb 1.2.x版本

1.2.x版本中再次对Master Thread进行了优化，此时Master thread的伪代码如下:

if Innodb is idle
   srv_master_do_idle_tasks();
else
   srv_master_do_active_tasks();   
1
2
3
4

其中srv_master_do_idle_tasks()就是之前版本中每10秒的操作，srv_master_do_active_tasks()处理的是之前每秒中的操作。 同时对于刷新脏页的操作，从Master Thread线程分离到一个单独的Page Cleaner Thread,从而减轻了Master Thread的工作，同时进一步提高了系统的并发性。

---

Innodb存储引擎的核心操作大部分都集中在Master Thread后台线程中，因此对Master Thread逻辑进行优化处理，可以提升Innodb存储引擎整体运行效率。

---

Innodb关键特性

插入缓冲

Innodb存储引擎中，主键是行唯一的标识符,聚簇索引中行记录的插入顺序是按照主键递增的顺序进行插入的，因此，插入聚簇索引一般是顺序的，不需要磁盘的随机读取。

我们假设有下面一张结构的表:

create table t(
 a int auto_increment,
 b int,
 primary key(a),
 key(b)
);
1
2
3
4
5
6

假设聚簇索引叶子节点排列如下:

二级索引b叶子节点排列如下:

假设我们执行下面这条sql语句:

insert into t(b) values(4);
1

因为a列是自增长的，若对a列插入null值，该列的值会自动增长，同时聚簇索引页中的行记录按a的值进行顺序存放,在一般情况下，不需要随机读取另一个页中的记录:

注意: 这里不考虑页分裂情况； 如果主键是UUID这样的类，那么插入和辅助索引一样，同样是随机的，即使主键是自增类型，但是插入的是指定的值，而不是NULL值，那么同样可能导致插入并非连续的情况。

此时对二级索引b而言，插入是什么情况呢?

对于二级索引的插入来说，通常都是随机IO，随机IO很慢！！！

在某些情况下，二级索引插入也可以做到顺序，例如: 通常很多记录插入时，都会附带一个create_time字段，该字段和主键的排序顺序其实是相关联的，如果按照create_time字段建立二级索引，那么就可以做到插入是顺序的。

---

那么对记录的插入和更新过程有哪些比较耗时的过程呢？

1.如果需要的数据页未被缓存在Buffer pool中，那么需要从磁盘进行读取

2.二级索引的插入和更新大部分都是随机IO，性能较差

为了优化上面的两种情况，innodb设计了insert buffer，对于二级非唯一索引的插入和更新操作，不是每一次直接插入到对应的索引页，而是先判断插入的二级非唯一索引是否在缓冲池中，如果在，则直接插入；

如果不在，则先放到一个insert buffer对象中，然后响应告诉用户更新完成。

后台线程会以一定的频率进行Insert Buffer和二级非唯一索引进行merge操作，并且此时通常能够一次性将多个插入合并到一个操作中,这就大大提高了对于非聚簇索引插入的性能。

后台线程需要合并Insert Buffer,首先需要把涉及到的页读取到内存中来，然后从Insert Buffer中快速定位出所有对某个页进行修改的操作，然后一次性通通执行完。

---

为啥必须要满足非唯一条件

首先，使用Insert Buffer需要同时满足下面两个条件:

• 索引是二级索引
• 索引不是唯一的

其实本质还是一条，索引不是唯一的，因为聚簇索引主键默认要求唯一。

对于要求唯一性的索引来说，每次更新记录主键和插入新记录时，都必须确保对应的数据页存在于缓冲池中，然后判断插入的记录是否能够保证索引列值的唯一性; 而对于非唯一索引而言，因为不需要判断更新或插入后的索引列值是否唯一，因此可以借助insert buffer实现异步懒更新操作，而不需要阻塞用户线程。

---

查看Insert Buffer的状态

show engine innodb status\G
1

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

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

• Inserts代表了插入的记录数；
• merged recs代表了合并的插入记录数量；
• merges代表合并的次数，也就是实际读取页的次数。

merges：merged recs大约为1：3，代表了插入缓冲将对于非聚集索引页的离散10逻辑请求大约降低了2／3。

---

Insert Bufferd的问题

在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占用到1/2的缓冲池内存。

---

Change buffer

Change Buffer可视为Insert Buffer的升级。 Change Buffer可以对DML操作INSERT、DELETE、UPDATE 都进行缓冲，他们分别是：Insert Buffer、Delete Buffer、Purge buffer。

当然和之前 Insert Buffer一样，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。

从Innodb 1.2.x版本开始，我们可以通过innodb_change_buffer_max_size来控制Change Buffer最大使用的内存数量:

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

我们可以通过show engine innodb status\G 命令来查看change buffer状态:

可以看到这里显示了 merged operations 和 discarded operation，并且下面具体显示Change Buffer中每个操作的次数。insert表示InsertBuffer；delete mark 表示 Delete Buffer；delete 表示 Purge Buffer；discarded operations 表示当 Change Buffer 发生 merge 时，表已经被删除，此时就无需再将记录合并（merge）到辅助索引中了。

---

insert buffer内部实现

Insert Buffer的数据结构是一颗B+树，在MySQL 4.1之前的版本中每张表有一棵Insert Buffer B＋树。而在现在的版本中，全局只有一棵Insert Buffer B＋树，负责对所有的表的辅助索引进行 Insert Buffer。

而这棵B＋树存放在共享表空间中，默认也就是ibdatal中。因此，试图通过独立表空间ibd文件恢复表中数据时，往往会导致CHECK TABLE失败。这是因为表的辅助索引中的数据可能还在Insert Buffer中，也就是共享表空间中，所以通过ibd文件进行恢复后，还需要进行REPAIR TABLE操作来重建表上所有的辅助索引。

Insert Buffer对应的B+树长下面这个样子:

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

对于Innodb来说，为了更好管理那么多页，采用了区管页，组管区的办法，每64页划分为一个区，每256个区划分为一组进行管理； 区为了管理好手下的64个页，选择64个页的前几个页记录本区相关信息，组为了管理好256个区，也采用前几个页记录本组相关信息。

每个组第一个分区第二个页面是用来存放Insert Buffer Bitmap的，Insert Buffer Bitmap中保存了如下信息:

---

Insert Buffer merge何时发生

Insert Buffer merge可能发生在以下几种情况:

• 辅助索引页被读取到缓冲池时,例如: 执行正常的SELECT查询操作，这时需要检查 Insert Buffer Bitmap页，然后确认该辅助索引页是否有记录存放于Insert Buffer B＋树中，若有，则将 Insert Buffer B＋树中该页的记录插入到该辅助索引 页中。可以看到对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中，因此性能会有大幅提高。
• Insert Buffer Bitmap 页用来追踪每个辅助索引页的可用空间，并至少有1／32页的空间。若插人辅助索引记录时检测到插人记录后可用空间会小于1／32页，则会强制进行一个合并操作，即强制读取辅助索引页，将Insert Buffer B＋树中该页的记录及待插人的记录插人到辅助索引页中。这就是上述所说的第二种情况。
• Master Thread线程中每秒 或每10秒会进行一次 Merge Insert Buffer的操作，不同之处在于每次进行merge操作的页的数量不同。

在Master Thread中，执行merge操作的不止是一个页，而是根据 srv_innodb_io_capactiy的百分比来决定真正要合并多少个辅助索引页。但InnoDB存储引擎又是根据怎样的算法来得知需要合并的辅助索引页呢？

在Insert Buffer B＋树中，辅助索引页根据（space，offset）都已排序好，故可以 根据（space，offset）的排序顺序进行页的选择。然而，对于Insert Buffer页的选择， InnoDB存储引擎并非采用这个方式，它随机地选择 Insert Buffer B＋树的一个页，读取该页中的space及之后所需要数量的页。该算法在复杂情况下应有更好的公平性。同时，若进行merge时，要进行merge的表已经被删除，此时可以直接丢弃已经被 Insert／Change Buffer的数据记录。

---

两次写

如果某个脏页在没有刷新到磁盘前，数据库系统就奔溃了，那么还可以根据redo log日志将奔溃前还没来得及刷脏的操作进行重放。

但是，由于重做日志记录的是偏底层的日志，如偏移量800，写’aaa’记录，如果该页本身结构被破坏了，那么重放也会失败。因此，在应用redo log日志前，我们需要保存一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。

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

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

在完成doublewrite页的写入后，再将double write buffer中的页写入各个表空间文件中，此时的写入则是离散的。

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

我们可以通过下面的命令观察到doublewrite的运行情况:

可以看到，doublewrite一共写了6325194个页，但实际的写入次数为100399，基本上符合64：1。如果发现系统在高峰时的Innodb_dblwr_pages_written:Innodb_dblwr_writes远小于64：1，那么可以说明系统写入压力并不是很高。

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

注意: 有些文件系统本身就提供了部分写失效的防范机制，如ZFS文件系统。在这种情况下，用户就不要启用doublewrite了。

---

自适应哈希索引

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

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。AHI是通过缓冲池的B＋树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

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

• WHERE a=xxx
• WHERE a=xxx and b=xxx

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

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

通过 show engine innodb status可以看到当前AHI的使用状态:

现在可以看到AHI的使用信息了，包括AHI的大小、使用情况、每秒使用AHI搜索的情况。值得注意的是，哈希索引只能用来搜索等值的查询，如SELECT＊FROM tableWHERE index_col＝＇xxx＇。而对于其他查找类型，如范围查找，是不能使用哈希索引的，因此这里出现了 non-hash searches/s 的情况。通过 hash searches:non-hash searches 可以大概了解使用哈希索引后的效率。

由于AHI是由InnoDB存储引擎控制的，因此这里的信息只供用户参考。不过用户可以通过观察 SHOW ENGINE INNODB STATUS的结果及参数innodb_adaptive_hash_index来考虑禁用或启动此特性，默认AHI为开启状态。

---

异步IO

为了提高磁盘操作性能，当前的数据库系统都采用异步IO（Asynchronous IO，AIO）的方式来处理磁盘操作。InnoDB存储引擎亦是如此。

与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作。在每扫描一个页并等待其完成后再进行下一次的扫描，这是没有必要的。用户可以在发出一个I0请求后立即再发出另一个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的页。

如果Mysql所运行的操作系统本身不支持AIO，那么Innodb存储引擎会采用代码来模拟实现。

参数 innodb_use_native_aio用来控制是否启用Native AIO，在Linux操作系统下， 默认值为ON：

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

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

---

刷新邻接页

InnoDB存储引还提供了Flush Neighbor Page （刷新邻接页）的特性。

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

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

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

---

Innodb的启动,关闭和恢复

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存储引擎的表进行恢复操作。

参数 innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。该参数值默认为0，代表当需要恢复时，进行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，MySQL数据库可能发生宕机，并把错误写入错误日志中去。

但是，在某些情况下，可能并不需要进行完整的恢复操作，因为用户自己知道怎么进行恢复。比如在对一个表进行alter table操作时发生意外了，数据库重启时会对InnoDB表进行回滚操作，对于一个大表来说这需要很长时间，可能是几个小时。这时用户可以自行进行恢复，如可以把表删除，从备份中重新导入数据到表，可能这些操作的速度要远远快于回滚操作。

参数 innodb_force_recovery 还可以设置为6个非零值：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操作是不允许的。