【mysql】InnoDB —— 插入缓存

╰+哭是因爲堅強的太久メ 2022-06-15 10:28 147阅读 0赞

# 背景： #

1.  什么是插入缓存，原理是什么？

2.  它存在的意义是什么？ 有什么好处？

3.  怎样利用与查看它？

# 内容： #

## 原理及意义： ##

插入缓存是InnoDB独有的优化模式。 我们都知道，InnoDB是基于主键的聚簇索引。通常应用程序中行纪录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读写。如下，假如我有这样一个表：

| test  | CREATE TABLE \`test\` (

\`id\` int(2) NOT NULL AUTO\_INCREMENT,

\`count\` int(2) DEFAULT NULL,

PRIMARY KEY (\`id\`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

那么当我不断插入时，若对id列插入null值，则由于其具有auto\_increment的属性，其值会自动增长。同时页中的行记录按照id的值进行存放。在一般情况下，不需要随机读取另一个页中的记录。因此，对于这类情况下的插入操作，速度是非常快的。（uuid除外）

[![003yQUOezy70r9dSubif4][]][003yQUOezy70r9dSubif4 1]

当我insert  into  test (count) values (12),(23),(56),(89) ...... ,左边的那页会随着主键顺序递增，它不会去考虑其他的页。 假如当我要插入100的时候，刚好左边那一页满了。那么很自然的就插入到下一页了，还是不需要去查询我应该插入在哪里。

我靠，那岂不是不需要插入缓存了，我这样多高效！ ！ ！

但通常情况下，我们的表结构还存在其他的非聚集的辅助索引的啊。那么假如我们的表结构是这样的呢？ 我给count也加了一个索引：

alter  table test  add  index  count (count);     这时候我们的表结构就变成了这样：

| test  | CREATE TABLE \`test\` (

\`id\` int(2) NOT NULL AUTO\_INCREMENT,

\`count\` int(2) DEFAULT NULL,

PRIMARY KEY (\`id\`),

KEY \`count\` (\`count\`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

在这样的情况下产生了一个非聚簇索引且不是唯一索引，在插入的时候，数据页的存放还是按照主键id进行顺序存放的。 但是！！  对于非聚簇索引的叶子节点的插入不再是顺序的了，这是就需要离散的访问非聚簇索引页，由于随机读取的存在而导致了插入操作的性能下降。   不理解？   看图：

[![003yQUOezy70rase6Uf87][]][003yQUOezy70rase6Uf87 1]

—— 图片摘自 《高性能MySQL》

上图中左侧为InnoDB的主键索引，以及二级索引的结构。右边是MyISAM的索引结构。我们把注意力放在左下角，你会发现。 InnoDB非聚簇索引的叶子节点的值不再是行，而是指向了主键索引！

当我再次重新向test插入插入时：   insert   into  test  （count） values (55) ;

这时，数据页的存放还是按照主键id进行顺序存放的，但是count的页节点就不是了。 count为55的值应该放在B+树的哪里，它得有一个判断的过程。 它不像主键id一样，可以直接放在末尾而不用寻找。   那么问题来了，假如每插入一次就得进行一次判断与结构的调整，那么成本是很高的。

所以， 插入缓存就诞生了，这就是它的存在意义：

对于非聚簇索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚簇索引页是否在缓冲池中，若在，则直接插入； 若不在，则先放入到一个Insert  Buffer对象中，好似欺骗。数据库这个非聚簇索引的已经插入到叶子节点中，而实际没有，只是存放在了另一个位置。然后再以一定的频率和情况进行insert Buffer和辅助索引叶子节点的merge操作，这样就可以把多个插入操作合并到一个操作中，大大提高对非聚簇索引的插入性能。

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

**1.  索引是辅助索引 （非主键索引）**

**2.  索引不是唯一索引**

为什么必须不能是唯一索引呢？  因为插入的时候，数据库并不去查找索引页来判断插入的记录的唯一性。 如果去查找肯定又会有离散的读取的情况发生，岂不是得不偿失，那Insert Buffer也失去了意义。

## 查看与调整： ##

我们已经粗略知道了 Insert  Buffer的基本概念，那么该如何去查看它呢？

mysql >  show engine  innodb  status \\G;

[![003yQUOezy70riSdnyn27][]][003yQUOezy70riSdnyn27 1]

如上图，有一部分标题为 插入缓存与自适应哈希索引的：

**size** 代表已经合并记录页的数量；

**free list len**代表了用于change buffer的空闲队列的长度；

**seg size**代表了当前change Buffer的大小为 4745 \* 16K = 74MB 。

**merges** ： change buffer 一共合并了多少次 。

**merged operations - insert** :  插入记录被merge的次数

**merged operations  - delete mark** : 删除操作被merge的次数

**merged options -delete**  : 更新操作被merge了多少次

上面的这些信息我们都可以在infoemation\_schema. innodb\_metrics 里面查到：

mysql> SELECT NAME, COMMENT FROM INFORMATION\_SCHEMA.INNODB\_METRICS WHERE NAME LIKE '%ibuf%'\\G

[![003yQUOezy70rm7yuI989][]][003yQUOezy70rm7yuI989 1]

我们也可以从information\_schema.innodb\_buffer\_page里面得到各种buffer page的信息，当然也包括change buffer.  例如，我们想得到change  buffer page占所有buffer page的百分比：

[![003yQUOezy70rqr9FoW96][]][003yQUOezy70rqr9FoW96 1]

细心的reader会发现，我们上面不再是insert buffer，而是change  buffer了，这是因为InnoDB后来觉得这种优化方式不错，所以给所有的DML操作（INSERT , DELETE, UPDATE） 都做了缓冲,他们分别是 Insert Buffer ,  Delete Buffer , Purge Buffer .

目前，change buffer存在一个问题，在写密集的情况下，插入缓存会占用过多的缓冲池内存（innodb\_buffer\_pool）, 默认是可以占到 1/2 的缓冲池大小。 我们可以通过下面这个参数来控制change buffer最大使用的内存占比：

[![003yQUOezy70rrtXPli4c][]][003yQUOezy70rrtXPli4c 1]

默认值是25，代表最多使用25%的innodb\_buffer\_pool。

到了这里，又产生了几个疑问：

1. Insert  Buffer在内存中它是怎么存在的，存在在哪里呢？

2. 它在什么时候会merge进入物理内存呢？

其实上述的问题涉及到InnoDB的insert buffer的实现原理。最好可以从数据库源码入手，就可以透彻的进行了解了。我也试着去入手这一部分的源码，但发现无从下手。代码能力还是有待提高，所以暂且搁置，以后继续补充。  这里我翻阅了一些书籍得到了一些解百纳概念：

Insert Buffer的数据结构式一颗B+树。 这颗B+树负责对所有的辅助索引进行插入缓存。 而这颗B+树存放在共享表空间中，也就是我们经常看到的ibdata1.

而Merge  Insert Buffer主要由于下面几种情况：

—— 辅助索引对应的物理页要被读取到缓冲池时：  假如我要查询该页的内容了，而你还没有实际插入物理也那肯定不行啊。

—— Insert Buffer Bitmap 追踪到辅助索引页的可用空间不大时。

—— Master Thread 它每隔10秒会进行一次Merge Insert Buffer，不同之处在于每次Merge操作的页的数量不同。

本文的阐述框架来自于 ——    《MySQL技术内幕—— Innodb存储引擎》

> ## 欢迎关注公众号： ##
> 
> # ![70][] #

[003yQUOezy70r9dSubif4]: /images/20220615/66cd8069d2704864abdacd6e8249f38c.png
[003yQUOezy70r9dSubif4 1]: http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy70r9dSubif4
[003yQUOezy70rase6Uf87]: /images/20220615/d91dcca5d4274d198ea2d6718286a9ef.png
[003yQUOezy70rase6Uf87 1]: http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy70rase6Uf87
[003yQUOezy70riSdnyn27]: /images/20220615/153649472beb4a47898c479679961d1b.png
[003yQUOezy70riSdnyn27 1]: http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy70riSdnyn27
[003yQUOezy70rm7yuI989]: /images/20220615/adb52c468b834acab7d0f08f46156cbf.png
[003yQUOezy70rm7yuI989 1]: http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy70rm7yuI989
[003yQUOezy70rqr9FoW96]: /images/20220615/7ecd5e852d9b4e9ba1910d1bc96cee36.png
[003yQUOezy70rqr9FoW96 1]: http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy70rqr9FoW96
[003yQUOezy70rrtXPli4c]: /images/20220615/6384fc065a8e496a81c58cb8b9e6e249.png
[003yQUOezy70rrtXPli4c 1]: http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy70rrtXPli4c
[70]: /images/20220615/e86506d99ee5407dbe49c4e2e0a9b3bb.png