参考文献

  • MySQL技术内幕 InnoDB存储引擎

InnoDB存储引擎的关键特性

  • 插入缓冲(Insert Buffer)

  • 两次写(Double Write)

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

  • 异步IO(Async IO)

  • 刷新邻接页(Flush Neighbor Page)

Insert Buffer

  • 虽然InnoDB缓冲池中也有Insert Buffer 信息,但是Insert Buffer 和数据页一样,也是物理页的一个组成部分.
  • 在InnoDB存储引擎中,主键是行唯一的标识符.通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的.因此,插入聚集索引(Primary Key)一般是顺序的,不需要磁盘的随机读取.
  • TIPS: 并不是所有的主键插入都是顺序的,若主键类是UUID这样的,那么插入和辅助索引一样,同样是随机的.即使主键是自增类型,但是插入的是指定的值,而不是NULL值,那么同样可能导致插入并非连续的情况.

B+树的特性决定了非聚集索引插入的离散性

解释:

B+树的叶节点是有序的。当它用于聚集索引的时候，叶节点本身既是索引又是真实值。当它用于非聚集索引的时候，叶节点仅仅是索引，索引的指针指向的才是真实值。由于此时索引是有序的，因此其指向通常是无序的，所以两个连续的索引值可能对应的真实值所在的行可能会离得很远。

举个例子，一个表用整数id作为主键，且将主键当做聚集索引。此时再用表中的另一列age当做非聚集索引。由于表的行本身就是按主键排序的，因此age是无序的，所以age=10的行可能在第八行，而age=11的行却可能位于第三十行，差别很大。所以在插入的时候就无法做到连续的索引插入到连续的行中，而只能一条一条地定位和插入.

引用自segmentfault,代码宇宙的回答

  • InnoDB存储引擎开创性的设计了Insert Buffer ,对于非聚集索引的插入或更新操作,不是每次直接插入到索引页,而是先判断插入的非聚集索引页是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个Insert Buffer 对象中,好似欺骗.
    • 数据库这个非聚集的索引已经插入到叶子节点,而实际上并没有,只是存放在另一个位置.然后再以一定的频率和情况进行Insert Buffer 和辅助索引页子节点的merge(合并)操作.这时通常能将多个插入合并到一个操作中(因为在一个索引页中),大大提高了对于非聚集索引插入的性能.
  • Insert Buffer 的使用需要同时满足以下两个条件:
    • 索引是辅助索引(secondary index);
    • 索引不是唯一(unique)的;
  • 满足以上两个条件时,InnoDB存储引擎会使用Insert Buffer ,这样就能提高插入操作的性能.
    • 不过有一种情况需要考虑:应用程序进行大量的插入操作,这些都涉及了不唯一的非聚集索引,也就是使用了Insert Buffer .若此时MySQL数据库发生了宕机,这时势必有大量的Insert Buffer 并没有合并到实际的非聚集索引中去.因此这时回复可能需要很长的时间,在极端情况下甚至需要几个小时.
  • 辅助索引不能是唯一的,因为在插入缓冲时,数据库并不去查找索引页来判断插入的记录的唯一性.如果去查找肯定又会有离散读取的情况发生,从而导致Insert Buffer 失去意义.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 6 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

  • seg size显示了当前Insert Buffer 的大小为2*16KB,free list len代表了空闲列表的长度,size代表了已经合并记录页的数量.

  • 目前Insert Buffer 存在一个问题是: 在写密集的情况下,插入缓冲占用过多缓冲池内存(innodb_buffer_pook),默认最大可以占用到1/2的缓冲池内存.(MySQL 5.1.11版本代码)

    1
    2
    3
    4
    5
    6
    7
    /* Buffer pool size per the maximum insert buffer size */
    #define IBUF_POOL_SIZE_PER_MAX_SIZE	2
    /* Note that also a pessimistic delete can sometimes make a B-tree
    grow in size, as the references on the upper levels of the tree can
    change */
    ibuf->max_size = buf_pool_get_curr_size() / UNIV_PAGE_SIZE
    					/ IBUF_POOL_SIZE_PER_MAX_SIZE;

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适用的对象依然是非唯一的辅助索引.

  • 对一条记录进行UPDATE操作可能分为两个过程:

    • 将记录标记为已删除;
    • 真正将记录删除;

  • 因此Delete Buffer对应UPDATE操作的第一个过程,即将记录标记为删除.Purge Buffer对应UPDATE操作的第二个过程,即将记录真正的删除.同时,InnoDB存储引擎提供了参数innodb_change_buffering,用来开始各种Buffer的选项.

    • 该参数可选值有:inserts,deletes,purges,changes,all,none
    • changes表示启用inserts和deletes
    • all表示启用所有,none表示都不启用,默认为all.

  • 从InnoDB1.2x版本开始,可以通过参数innodb_change_buffer_max_size来控制Change Buffer最大使用内存的数量:

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_change_buffer_max_size';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | innodb_change_buffer_max_size | 25    |
    +-------------------------------+-------+
    1 row in set (0.01 sec)
    • innodb_change_buffer_max_size值默认为25,表示最多使用1/4的缓冲池内存空间.需要注意的是,该参数最大有效值为50.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 6 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
  • merged operations和discarded operations,并且下面具体显示Change Buffer中每个操作的次数.
    • insert表示Insert Buffer;
    • delete mark表示Delete Buffer;
    • delete表示Purge Buffer
    • discarded operations表示Change Buffer发生merge时,表示已经被删除,此时就无需再将记录合并(merge)到辅助索引中了.

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(键值),其构造如下:

    1
    2
    3
    4
    5
    |<-- 			    9KB         --->|
    -------------------------------
    |  space    |marker|  offset  |
    -------------------------------
    |   4KB     |  1KB |   4KB    |
    • 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 BufferB+树的叶子节点中.

  • 对于插入到Insert Buffer B+树叶子节点的记录,并不是直接将待插入的记录插入,而是需要根据如下的规则进行构造:

    1
    2
    3
    4
    5
    |<-- 			    9KB         --->|						|		    secondary index record			 |	
    --------------------------------------------------------------------------------
    |  space    |marker|  offset  |	 metadata |					|					|				|				 |
    --------------------------------------------------------------------------------
    |   4KB     |  1KB |   4KB    |		 4KB		|

    • space,marker,offset字段和之前非节点中的含义相同,一共占用9节点.第四个字段metadata占用4字节,器存储的内容如下:

      | 名称 | 字节 |
      | ------------------------ | ---- |
      | IBUF_REC_OFFSET_COUNT | 2 |
      | IBUF_REC_OFFSET_TYPE | 1 |
      | IBUF_REC_OFFSET_FLAGES | 1 |

  • IBUF_REC_OFFSET_COUNT是保存两个字节的整数,用来排序每个记录进入Insert Buffer的顺序.因为从InnoDB1.0.x开始支持Change Buffer,所以这个值同样记录进入Insert Buffer的顺序.通过这个顺序回放(replay)才能得到记录的正确值.

  • 从Insert Buffer叶子节点的第5列开始,就是实际插入记录的各个字段了,.因此相对原插入记录,Insert BufferB+树的叶子节点记录需要额外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页都在16384个页的第二个页中.

  • 每个辅助索引页Insert Buffer Bitmap页中占用4位(bit),由表中三个部分组成

    名称 大小(bit) 说明
    IBUF_BITMAP_FREE 2 表示该辅助索引页的可用空间数量,可取值为:
    * 0表示无可用剩余空间
    * 1表示剩余空间大于1/32页(512字节)
    * 2表示剩余空间大于1/16页
    * 3表示剩余空间大于1/8页
    IBUF_BITMAP_BUFFERED 1 1表示该辅助索引有记录被缓存在Insert BufferB+树中
    IBUF_BITMAP_IBUF 1 1表示该页为Insert BufferB+树的索引页

Merge Insert Buffer

  • Insert Buffer中的记录何时合并(merge)到真正的辅助索引中?
  • Merge Insert Buffer的操作可能发生以下几种情况下:
    • 辅助索引页被读取到缓冲池时;
      • 当辅助索引页被读取到缓冲池中时,例如执行正常的SELECT查询操作时,这是需要检查Insert Buffer Bitmap页,然后确认该辅助索引页是否有记录存放于Insert BufferB+树中.若有,则将Insert BufferB+树中该页的记录插入到辅助索引页中.
    • Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时;
      • Insert Buffer Bitmap页用来追踪到每个辅助索引页的可用空间,并至少有1/32页的空间.若插入辅助索引记录是检测到插入记录后可用空间会小于1/32页,则会强制进行一次合并操作,即强制读取辅助索引页,将Insert BufferB+树中该页的记录及待插入的记录插入到辅助索引页中.
    • Master Thread
      • 在Master Thread中每秒或每10秒会进行一次Merge Insert Buffer的操作,不同之处在于每次merge操作的页的数量不同.
      • 在Master Thread中,执行merge操作的不止是一个页,而是根据srv_innodb_io_capactiy的百分比来决定真正要合并多少辅助索引页.
      • 在Insert BufferB+树中,辅助索引页根据(space,offset)都已排序好,故可以根据(space,offset)的顺序进行页的选择.然而,对于Insert Buffer页的选择,InnoDB存储引擎并非采用这个方式,它随机地选择Insert BufferB+树的一个页,读取该页中的space集之后所需数量的页.该算法在复杂情况下应有更好的公平性.同时,若进行merge时,要进行merge的表已经被删除,此时可以进行丢弃已经被Insert Change Buffer的数据记录

两次写(Double write)

  • 若说Insert Buffer带给InnoDB存储引擎的是性能上提升,那么doublewrite带给InnoDB存储引擎的是数据页的可靠性.

  • 当发生数据库宕机时,可能InnoDB存储引擎正在写入某个页到表中,而这个页只写了一部分,比如16KB页,只写了前4KB,之后就发生了宕机,这种情况被称为部分写失效(partial page write).在InnoDB存储引擎未使用doublewrite技术之前,曾经出现过因为部分写失效而导致数据丢失的情况.

  • doublewrite由两部分组成,一部分在内存中的doublewrite buffer大小为2MB,另外一部分在物理磁盘上共享表空间中连续128个页,即2个区,大小同样为2MB.

    img

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

      1
      2
      3
      4
      5
      6
      7
      8
      mysql> show global status like 'innodb_dblwr%';
      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Innodb_dblwr_pages_written | 342   |
      | Innodb_dblwr_writes        | 89    |
      +----------------------------+-------+
      2 rows in set (0.03 sec)
      • 可以看到doublewrite一共写入了342个页,但实际写入次数为89.若发现系统在高峰时的Innodb_dblwr_pages_written:Innodb_dblwr_writes远小于64:1,那么可以说明写入压力并不是很高.

  • 参数skip_innodb_doublewrite可以禁止使用doublewrite功能,这时可能会发生提及的写失效问题.

自适应哈希索引(Adaptive Hash Index,AHI)

  • 哈希是一种非常快的查找方法,在一般情况下这种查找的时间复杂度为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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 34679, node heap has 1 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 1 buffer(s)
    Hash table size 34679, node heap has 2 buffer(s)
    Hash table size 34679, node heap has 3 buffer(s)
    Hash table size 34679, node heap has 6 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    • 值得注意的是,**哈希索引只能用来搜索等值的查询.**如SELECT * FROM table WHERE index_col='xxx'.对于其他查找类型,如范围查找,是不能使用哈希索引的,因此这了出现了non-hash searches/s的情况.可以通过hash searches/s:non-hash searches/s可以大概了解使用哈希索引后的效率.

  • 参数innodb_adaptive_hash_index控制启用还是禁用这特性,默认AHI为开启状态.

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_adaptive_hash_index';
    +----------------------------+-------+
    | Variable_name              | Value |
    +----------------------------+-------+
    | innodb_adaptive_hash_index | ON    |
    +----------------------------+-------+
    1 row in set (0.02 sec)

异步IO(AIO)

  • 与AIO对应的Sync IO,即每进行一次IO操作,需要等待此次操作结束才能继续接下来的操作.但是如果用户发出的是一条索引扫描的查询,那么这条SQL查询语句可能需要扫描多个索引页,也就是需要进行多次的IO操作.在每扫描一个页并等待其完成后在进行下一次扫描,这是没必要的.

  • 用户可以在发出一个IO请求后立即再发出另一个IO请求,当全部IO请求发送完毕后,等待所有IO操作的完成,这就是AIO.

  • AIO另一个优势是可以进行IO Merge操作,也就是将多个IO合并为1个IO,这样可以提高IOPS的性能.如用户需要访问的(space,offset)为(8,6),(8,7),(8,8),每个页的大小为16KB,那么同步IO则需要进行3次IO操作.而AIO会判断到这三个页是连续的(可以通过(space,offset)得知).因此AIO底层会发送一个IO请求,从(8,6)开始,读取48KB的页.

  • AIO是通过内核级别AIO来支持的,称为Native AIO.通过参数innodb_use_native_aio来控制禁用和启用 Native AIO.Mac OSX系统未提供Native AIO支持.

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_use_native_aio';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | innodb_use_native_aio | OFF   |
    +-----------------------+-------+
    2 rows in set (0.01 sec)
    • 官方测试启用Native AIO速度可以提高75%.在InnoDB存储引擎中,read ahead方式的读取都是通过AIO完成,脏页的刷新,即磁盘的写入操作则全部由AIO完成.

刷新邻接页

  • InnoDB存储引擎还提供了Flush Neighbor Page(刷新邻接页)的特性,其工作原理为:当刷新一个脏页时,InnoDB存储引擎会检测该页所在区的所有页,如果是脏页,那么一起进行刷新.这样做的好处显而易见,通过AIO可以将多个IO写入操作合并为一个IO操作,故该工作机制在传统磁盘下有显著的优势.

  • 但需要考虑两个问题

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

  • 所以InnoDB存储引擎在1.2.x版本开始提供参数innodb_flush_neighbors来控制是否启用该特性.

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_flush_neighbors';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_flush_neighbors | 0     |
    +------------------------+-------+
    1 row in set (0.00 sec)
    • 对于传统机械硬盘建议启用该特性(即设置参数为1),而对于固态硬盘有着超高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 参数默认值为1,表示不需要完成full purge和merge insert buffer操作,但是缓冲池中的一些脏页数据还是会刷新回磁盘.
    2 表示不完成full purge和merge insert buffer操作,也不将缓冲池中的数据脏页写回磁盘,而是将日志都写入日志文件.这样会有任何事务的丢失,但是下次MySQL启动时,会进行恢复操作. 
    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_fast_shutdown';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | innodb_fast_shutdown | 1     |
    +----------------------+-------+
    1 row in set (0.01 sec)

  • 若MySQL没有被正常关闭如使用kill命令关闭数据库或者在MySQL运行过程中重启服务器等,将参数innodb_fast_shutdown设置为2时,但是下次MySQL启动时,会对存储引擎为InnoDB的表进行恢复操作.

  • 参数innodb_force_recovery影响了整个InnoDB存储引擎恢复的状态.其值可以设置如下:

    参数值 说明
    0 默认值,代表当发生需要恢复时,进行所有的恢复操作,当不能进行有效恢复时,如数据页发生了corruption,MySQL数据库可能发生宕机(crash),并把错误写入错误日志中去.
    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操作是不允许的.
    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_force_recovery';
    +-----------------------------+-------+
    | Variable_name               | Value |
    +-----------------------------+-------+
    | innodb_force_recovery       | 0     |
    +-----------------------------+-------+
    2 rows in set (0.00 sec)