Mysql存储-InnoDB关键特性

站长

2023年08月17日 00:23 · 阅读数 10

Mysql存储-InnoDB关键特性

一、插入缓冲（Insert Buffer）

1.1 Insert Buffer

Insert Buffer 和数据页一样，是物理页的一个组成部分。

通常在使用InnoDB的过程中，主键是表中一行数据的唯一标识，通常是按照主键递增的顺序插入的。所以，插入主键或者说聚集索引是顺序的，不需要磁盘的随机读取。页中的记录会随着主键的顺序逐个记录，这时候的速度还是很快的。

注意：如果插入的是随机数据，比如UUID，那么无论主键是否自增，都将导致数据的写入是随机的，并非连续的。

通常情况每张表会有除聚集索引以外的其他辅助索引，这时聚集索引的存放仍然是顺序的，但是在辅助索引的叶子节点存储的数据则是离散的，由于随机读取的操作而导致了写入性能的下降。

在某些特定情况下，非聚集索引的插入仍然可以保证是顺序的，例如订单表的下单时间字段。

为了提高非聚集索引的写入性能，InnoDB开创性的设计了Insert Buffer：对于非聚集索引的插入和更新操作，会先去查询缓冲池，看非聚集索引的页是否在缓冲池中，若存在，则直接写入；若不存在，则先存放到一个Insert Buffer对象中，然后以一定的频率使Inser Buffer和辅助索引叶子结点merge（合并）。这是通常是多个写入操作合并到一个操作中（在一个索引页中），这样就大大提高了非聚集索引插入的性能。

使用条件：

索引是辅助索引
索引不是唯一的，插入时，数据库不判断数据的唯一性。

1.2 Change Buffer

Insert Buffer的升级，对于Insert，Update，Delete都进行缓冲，他们分别是：Insert Buffer、Delete Buffer、Purge Buffer。

仍然作用于非唯一的辅助索引。

对数据Update分为两个步骤：

1）将数据标为已删除。对应Delete Buffer。

2）执行删除。对应Purge Buffer。

通过以下命令控制:

sql
复制代码
mysql> show variables like 'innodb_change_buffering';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+
1 row in set (0.00 sec)

默认是all，表示启动所有。此外还有inserts，deletes，purges，changes，none；changes表示启用inserts和deletes；none表示都不启用。

通过以下参数控制最大使用内存量：

sql
复制代码
mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.00 sec)

表示最多使用1/4的缓冲池空间，最大为50.

使用以下命令可以查看change buffer的状态：

sql
复制代码
mysql> show engine innodb status\G;

-------------------------------------
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

seg size：表示当前Inser Buffer的大小2*16k

free list len：表示空闲列表的长度。

size：表示已合并页数

merged operations：表示每个change Buffer操作的次数

insert：表示Insert Buffer

delete mark：表示Delete Buffer

delete：表示Purge Buffer

discarded operations：表示当Change Buffer进行merge时，表进行了删除。此时无需将记录合并到辅助索引中了。

1.3 Insert Buffer 的实现

Insert Buffer的数据结构是一棵B+树。

在mysql4.1版本之前，每张表都有一棵B+树，在其之后，全局只有一个用于Insert Buffer的B+树，负责对所有表的辅助索引进行insert buffer。

这颗B+树存放在共享表空间当中，默认是ibdata1。因此，如果想通过独立表空间恢复数据，往往会导致check table失败。因为辅助索引的数据可能还在Inser Buffer中，即共享表空间中，在通过ibd文件恢复后，还需要进行repair table来重建表上的所有辅助索引。

Insert Buffer的非叶子节点存储的是查询的key（search key），其构造如下：

Mysql存储-InnoDB关键特性

searck key一共9个字节，space表示要待插入记录所在表的表空间id，每个表都有一个唯一的space id。marker一个字节，用来兼容老版本的Insert buffer。offset表示页所在偏移量，4个字节。

当一个辅助索引要插入到页时，首先查询缓冲池，如果不在，那么存储引擎会构造一个search key，查询Inser buffer 这棵B+树，再将记录插入到这个树的叶子节点当中。

1.4 Merge Insert Buffer

哪些情况可能发生合并？如下所示：

辅助索引页被读取到缓冲池时；当辅助索引页被读取到缓冲池时，检查Insert Buffer Bitmap页，确认该辅助索引页是否有数据存放于Insert Buffer 的B+树中，有则将该数据插入到辅助索引页中。使原本多次操作通过一次合并插入到了辅助索引页中，提高了性能。
Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时； Insert Buffer Bitmap用来追踪辅助索引页在缓冲区中可用的空间。并且至少有1/32的空间。如果小于1/32，则进行合并。
Master Thread。每一秒或10秒都进行合并。

二、两次写（Double Write）

Insert Buffer带给InnoDB的是性能上的提升，而Double Write带给InnoDB的则是数据页的可靠性。

2.1 存在的原因

当数据发生宕机的同时，数据正在写入，16k的页数据，可能只写入了4k数据，剩下的12k数据就丢失了。

当然可以通过重做日志进行恢复，但是如果被写入的页已经被损坏的话，那么这部分数据就无法写入了。

解决问题的方式是在重做日志进行重做之前，需要这个页的一个副本，当发生写入失效时，先通过这个副本恢复该页，之后在进行重做。这就是Double Write。

2.2 Double Write体系架构

Mysql存储-InnoDB关键特性

如上图所示：doublewrite分为两个部分，一部分是内存中的doublewrite buffer ，大小为2M，另一部分是磁盘上共享表空间中的连续128个页，分为两个区，大小也是2M。

当缓冲池对脏页刷新时，先将脏页通过memcpy（内存拷贝）函数赋值到doublewrite buffer中，然后doublewrite buffer分两次，每次1M将数据写入磁盘共享表空间的doublewrite中，完成后迅速调用fsync函数同步磁盘。doublewrite页是连续的，效率快，开销小。

通过下面的命令查看mysql的双写信息：

sql
复制代码
mysql> show status like "%InnoDB_dblwr%" ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Innodb_dblwr_pages_written | 22    |
| Innodb_dblwr_writes        | 6     |
+----------------------------+-------+
2 rows in set (0.00 sec)

Innodb_dblwr_pages_written：一共写入页数。 Innodb_dblwr_writes：实际写入页数。

通过如下参数控制开启和关闭：

sql
复制代码
mysql> show variables like 'innodb_doublewrite';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

三、自适应哈希索引（Adaptive Hash Index）

哈希是一种非常快的查找方法，在一般情况下，这种查找方式的时间复杂度为O(1)，即一次就能定位到数据。而B+树的查找次数根据其高度而定。通常是3或4层，也就是需要3到4次的查询。

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

建立AHI的要求：对这个也的连续访问必须是一样的。例如(a,b)这样的联合索引，其访问模式如下： where a = 1 where a = 1 and b = 1 如果以上两种查询交替进行，则不会对该页构造AHI。

另外两种建立AHI的条件：

1）同一访问模式访问100次 2）访问次数 = 数据记录数 * 1/16

AHI是数据库自优化的，不需要人为进行干预。使用下面的方式可以查看AHI的数据：

sql
复制代码
show engine innodb status\G;

-------------------------------------
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 276671, node heap has 27 buffer(s)
41.07 hash searches/s, 13.31 non-hash searches/s

每秒哈希查询41.07次，非哈希查询13.31次。

AHI只能用于指定条件的查询，如等于，对于范围查找不能自动建立。non-hash就显示了不是哈希查询的次数。两个参数对比可以查看哈希查询的效率。

使用如下参数可以控制AHI的状态,默认是开启的：

sql
复制代码
mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

四、异步IO（Async IO）

为了提高磁盘操作的性能问题，当前数据库都是通过异步IO（Async IO）的方式来操作磁盘，InnoDB也是如此。

与AIO对应的是Sync IO，每进行一次IO，就要等待此次完成后才能进行下一次。当用户发起一条查询请求，这个请求可能需要查询多个索引页，则需要进行多次的IO，使用Sync IO的话，需要等待每一次IO执行后在执行下一次。当时用AIO时，用户在发起一次IO请求后即可以发出下一次的IO请求，待所有请求完成后，会对所有的IO进行合并。

AIO的优势除了异步速度快，还有另外的优势就是其IO Merge（IO 合并），将多个IO合并为一个IO，可以提高性能。例如：如用户连续访问页的（space，pageNo）是（6,6），（6,7），（6,8），每个页的大小是16k，如果是Sync IO的话，需要串行发送三次IO请求。而AIO则会判断这三次的IO请求是否是连续的，如上面举例得知page_no是连续的，则AIO会发送一个从（6,6）开始的48k的页读取请求。

使用下面的参数控制AIO的开启,默认是ON：

sql
复制代码
mysql> show variables like 'innodb_use_native_aio';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_use_native_aio | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

目前InnoDB使用的AIO是基于操作系统内核级别支持的，使用的话需要libaio的支持，当前windows和linux都是支持的，而mac则暂时不支持。

五、刷新邻接页（Flush Neighbor Page）

当刷新一个脏页时，InnoDB会检测该页所在区（extent，关于extent的内容看这篇文章blog.csdn.net/fu_zhongyua…）的所有页，如果是脏页，那么一起刷新到磁盘。这样做通过AIO将多个IO操作合并为1个IO。在传统的机械磁盘下有显著的效果。

通过以下参数控制：

sql
复制代码
mysql> show variables like 'innodb_flush_neighbors';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_flush_neighbors | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

传统机械硬盘建议开启，固态硬盘建议关闭，即值设为0；