MySQL 技术内幕
首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我

【MySQL技术内幕】05-插入缓冲

转
 2023-03-23
©版权
原文作者：一直不懂 原文地址：https://blog.csdn.net/shenchaohao12321/article/details/82798275

1、Insert Buffer

Insert Buffer 可能是 InnoDB 存储引擎关键特性中最令人激动与兴奋的一个功能。不过这个名字可能会让人认为插入缓冲是缓冲池中的一个组成部分。其实不然， InnoDB 缓冲池中有 Insert Buffer 信息固然不错，但是 Insert Buffer和数据页一样，也是物理页的一个组成部分。

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

    CREATE TABLE t ( 
       a INT AUTO INCREMENT ,
       b VARCHAR(30) , 
       PRIMARY KEY ( a ) 
    );

其中 a 列是自增长的，若对 a 列插入 NULL 值，则由于其具有 AUTO INCREMENT 属性，其值会自动增长。同时页中的行记录按 a 的值进行顺序存放。在一般情况下，不需要随机读取另一个页中的记录。因此，对于这类情况下的插入操作，速度是非常快的。 注意 并不是所有的主键插入都是顺序的。若主键类是 UUID 这样的类，那么插入和辅助索引一样，同样是随机的。即使主键是自增类型，但是插入的是指定的值，而不是 NULL 值，那么同样可能导致插入并非连续的情况。

但是不可能每张表上只有一个聚集索引，更多情况下，一张表上有多个非聚集的辅助索引（ secondary index ）。比如，用户需要按照 b 这个字段进行查找，并且 b 这个字段不是唯一的，即表是按如下的 SQL 语句定义的：

    CREATE TABLE t (
    a INT AUTO INCREMENT , 
    b VARCHAR ( 30 ) , 
    PRIMARY KEY ( a ) , 
    key ( b ) 
    );

在这样的情况下产生了一个非聚集的且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键 a 进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。当然这并不是这个 b 字段上索引的错误，而是因为 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来查看插入缓冲的信息:

202303232330175351.png

seg size显示了当前 Insert Buffer的大小为11336×16KB,大约为177MB; free list len代表了空闲列表的长度;size代表了已经合并记录页的数量。而黑体部分的第2行可能是用户真正关心的,因为它显示了插入性能的提高。 Inserts代表了插入的记录数;merged recs代表了合并的插入记录数量; merges代表合并的次数,也就是实际读取页的次数。 merges: merged recs大约为1:3,代表了插入缓冲将对于非聚集索引页的离散IO逻辑请求大约降低了2/3。
正如前面所说的,目前 Insert Buffer存在一个问题是:在写密集的情况下,插入缓冲会占用过多的缓冲池内存( innodb buffer pool),默认最大可以占用到1/2的缓冲池内存。以下是 InnoDB存储引擎源代码中对于 insert buffer的初始化操作:

202303232330181312.png
这对于其他的操作可能会带来一定的影响。 Percona上发布一些 patch来修正插入缓冲占用太多缓冲池内存的情况,具体可以到 Percona官网进行查找。简单来说,修改IBUF_POOL_SIZE_PER_MAX_SIZE就可以对插入缓冲的大小进行控制。比如将IBUF_POOL_SIZE_PER_MAX_SIZE改为3,则最大只能使用1/3的缓冲池内存。

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适用的对象依然是非唯一的辅助索引。
对一条记录进行 UPDATE操作可能分为两个过程:

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

因此 Delete Buffer对应 UPDATE操作的第一个过程,即将记录标记为删除。 PurgeBuffer对应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最大使用内存的数量:

    mysql> show variables like 'innodb_change_buffer_max_size';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | innodb_change_buffer_max_size | 25    |
    +-------------------------------+-------+
    1 row in set (0.05 sec)

innodb_change_buffer_max_size值默认为25,表示最多使用1/4的缓冲池内存空间。
而需要注意的是,该参数的最大有效值为50在 MySQL5.5版本中通过命令 SHOW ENGINE INNODB STATUS,可以观察到类似如下的内容:

202303232330186703.png

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

3、Insert Buffer的内部实现

通过前一个小节读者应该已经知道了 Insert Buffer的使用场景,即非唯一辅助索引的插入操作。但是对于 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(键值),其构造如图所示：

202303232330193654.png

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+树叶子节点的记录,并不是直接将待插入的记录插入,而是需要根据如下的规则进行构造:

202303232330200335.png

space、 marker、 page no字段和之前非叶节点中的含义相同,一共占用9字节。第4个字段 metadata占用4字节,其存储的内容如下表所示：

202303232330206896.png

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个区(Extent)。每个 Insert Buffer Bitmap页都在16384个页的第二个页中。
每个辅助索引页在 Insert Buffer Bitmap页中占用4位(bit),由下表中的三个部分组成：

202303232330214927.png

4、Merge Insert Buffer

通过前面的小节读者应该已经知道了 Insert/Change Buffer是一棵B+树。若需要实现插入记录的辅助索引页不在缓冲池中,那么需要将辅助索引记录首先插入到这棵B+树中。但是 Insert Buffer中的记录何时合并( merge)到真正的辅助索引中呢?这是本小节需要关注的重点，概括地说, Merge Insert Buffer的操作可能发生在以下几种情况下:

  • 辅助索引页被读取到缓冲池时;
  • 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_capactiy的百分比来决定真正要合并多少个辅助索引页。但 InnoDB存储引擎又是根据怎样的算法来得知需要合并的辅助索引页呢?
在 Insert Buffer B+树中,辅助索引页根据(space, offset)都已排序好,故可以根据(space, offset)的排序顺序进行页的选择。然而,对于 Insert Buffer页的选择,InnoDB存储引擎并非采用这个方式,它随机地选择 Insert Buffer B+树的一个页,读取该页中的 space及之后所需要数量的页。该算法在复杂情况下应有更好的公平性。同时,若进行 merge时,要进行 merge的表已经被删除,此时可以直接丢弃已经被 Insert/Change Buffer的数据记录。

阅读全文
文章标签： MySQL