mysql repire_mysql 原理 ~ change buffer

一 简介:今天咱们来聊聊mysql的change buffer

二 详细说明

1 +-change Buffer和数据页一样，也是物理页的一个组成部分，数据结构也是一颗B+树，这棵B+树放在共享表空间中，默认ibdata1中。change buffer 写入系统表空间机制应该和普通表的脏页刷新到磁盘是相同的机制--Checkpoint机制

2  具体查看

show engines inndo status 过滤

Ibuf: size 1, free list len 14316, seg size 14318(插入缓冲区的总大小 页的数量X16KB), 466174 merges(已经合并的meregs数量)

merged operations: insert 547399(插入记录被merge的次数), delete mark 42008(删除操作被merge的次数), delete 32055(更新操作被merge了多少次)

如果merges/merged的值等于3/1,则代表插入缓冲对于非聚集索引页的IO请求大约降低了3倍

三 具体过程

通用操作

1 判断目标数据页是否在内存中 -> 在, 直接操作数据页(更新,插入)

-> 不在,将操作缓冲到change buffer中>在下次进行操作这个数据页时,数据页读入内存,进行操作数据页(更新,插入)->写事务日志

2 merge 触发条件 >定期刷新 change buffer到ibdata中进行物理保存

1 当目标数据页被读取到内存时进行操作

2 master thread 每隔10s会进行操作

3 当mysql关闭时会触发进行操作

3 change buffer备份

change buffer虽然是内存的空间,也会定期刷新到磁盘,保证数据安全

4 change buffer补充

1 插入更新删除操作的辅助索引数的维护操作

2 本质是一个全局性的B+树,存在于共享表空间中,也即是ibdata

3 当通过ibd文件恢复表数据时,如果插入缓冲还在ibdata中，可能导致check_table失败.需要手动执行repire tablle

5 change buffer的相关参数

1 innodb_change_buffering 默认是all支持所有DML操作

2 innodb_change_buffer_max_size，默认是25，即缓冲池的1/4。最大可设置为50,采用默认即可

6 目的

减少了从内存读取硬盘的随机读IO(数据页)操作,redo log减少了随机写log操作

四 现实例子

1 举个现实中的例子来做说明，我们去图书馆还书，对应图书馆来说，他是做了insert(增加)操作，管理员在1小时内接受了100本书，这时候他有2种做法把还回来的书归位到书架上

1)每还回来一本书，根据这本书的编码(书柜区-排-号)把书送回架上

2)暂时不做归位操作，先放到柜面上，等不忙的时候，再把这些书按照书柜区-排-号先排好，然后一次性归位

用方法1，管理员需要进出(IO)藏书区100次，不停的登高爬低完成图书归位操作，累死累活，效率很差。

用方法2，管理员只需要进出(IO)藏书区1次，对同一个位置的书，不管多少，都只要爬一次楼梯，大大减轻了管理员的工作量。

所以图书馆都是按照方法2来做还书动作的。而且如果暂时不归还,需要相应的柜子进行保存,相当于数据库的insert buffer

2 为什么对于非聚集索引(非唯一)的插入和更新有效?

还是用还书的例子来说，还一本书A到图书馆，管理员要判断一下这本书是不是唯一的，他在柜台上是看不到的，必须爬到指定位置去确认，这个过程其实已经产生了一次IO操作，相当于没有节省任何操作。

所以这个buffer只能处理非唯一的插入，不要求判断是否唯一。聚集索引就不用说了，它肯定是唯一的，mysql现在还只能通过主键聚集。

3 前面说到管理员图书归位的时候，他会选择在“不忙的时候”再去做，优先处理前台退书操作，这个在MYSQL里面是这样体现的：

1)每1秒，如果IO次数小于5，合并插入缓冲。

2)每10秒，IO次数小于200，合并最多5个插入缓冲。

五 适用场景

事务进行操作后不会立刻查看的场景(写多读少),因为如果立刻查看会频繁的触发merge

六 补充

1 看完了上面的优化措施,我们应当明白了一点,为什么mysql不提倡建立很多无用的索引,因为每个辅助索引都需要成本来维护,虽然有了change buffer,还应该尽量避免建立无序索引

2 对于innodb的恢复机制应该加上change buffer 最后为 change buffer+redo log+binlog

3 change buffer本身一个缺点是可能会造成更多的内存占用,导致整个buffer_pool增大