怎么理解MySQL change buffer

本篇内容介绍了“怎么理解MySQL change buffer”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

change buffer是MySQL5.5加入的新特性，change buffer是insert buffer的加强，insert buffer只针对insert有效，change buffer对insert、delete、update(delete+insert)、purge都有效。当修改一个索引块(secondary index)时的数据时，索引块在buffter pool中不存在，修改信息就会被cache在change buffer中，当通过索引扫描把需要的索引块读取到buffer pool时，会和change buffer中修改信息合并，再择机写回disk。目的还是为了减少随机IO带来性能损耗，说明白了：把随机IO尽量变成顺序IO。
Change buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。
change buffer是存放二级索引的没有在buffer pool的变更页的缓存区，变更的buffer是由insert，update,delete等操作导致的。等页被加载进buffer pool中后会将change buffer中的页合并。 
二级索引通常是非唯一的，插入也是很随机的顺序，更新删除也都不是在邻近的位置，所以change buffer就避免了很多的随机io的产生。puge操作会在系统空闲或慢关闭的时候定时将变更页写入到磁盘上去。 
change buffer合并在有大量的二级索引页更新或有很多影响行的情况下会花费很长的时间。change buffer会占用innodb buffer pool的部分空间，在磁盘上，change buffer会占用系统表空间，所以在数据库重启后，索引变更仍然被缓存。在change buffer中被缓存的数据可以使用innodb_change_buffering控制，我们也可以调整innodb_change_buffer_max_size配置change buffer的大小。

索引对insert的影响

　　1、表insert，对应表上的所有索引都需要insert；

　　2、假设这些索引不常使用，容易产生物理读；

　　3、索引的顺序和表的顺序完全不一致；

　　原则：一个表上的索引最好不超过6个

将对索引的更新记录存入insert buffer中，而不是直接调入索引页进行更新；择机进行merge insert buffer的操作，将insert buffer中的记录合并(merge)到真正的辅助索引中。
解决了insert表数据产生过多物理读的问题。
Insert Buffer 何时 merge
有2种情况innodb会merge Insert Buffer 到磁盘 
1)，master loop 线程主动merge
若过去1s之内发生的I/O，小于系统I/O能力的5%，则主动进行一次Insert buffer的merge操作。Merge的页面数为系统I/O能力的5%，每10s，必定触发一次insert buffer merge动作.
Merge的页面数仍旧为系统I/O能力的5% (系统能力指innodb_io_capacity)

2),读取数据发现该page在Insert Buffer还没有被merge,那么innodb首先会做一个merge操作，所以此时读取的速度会降低

在系统负载较高时，merge操作会很少，而在系统比较空闲时，merge操作会非常频繁，所以当你的系统突然负载变低时，DB还有可能会进行很长一段时间的IO操作。

merge insert buffer的操作可能发生在什么情况下：
　　在merge insert buffer之前，insert buffer数据是存在内存中，为了防止数据库意外宕机导致数据丢失，系统会周期性将insert buffer数据写入共享表空间中。
　　1、辅助索引页被读取到buffer pool中
　　　　例如这在执行正常的select查询操作，索引页被调入内存，该索引页对应在insert buffer中的索引更改记录就会发生merge操作。
　　2、insert buffer bitmap页追踪到该辅助索引页已无可用空间时
　　　　存于ibd文件中(表数据文件)
　　　　记录每一个索引页在insert buffer中对应的行数

如何看insert buffer的效果
　　1、insert buffer所占空间，占比太高就影响缓冲性能
　　2、每次merge处理的数据量
　　　　1)、merges如果很高，说明insert buffer调小了，也说明索引建多了；
　　　　2)、对表进行批量IDU的时候，可能会导致insert buffer迅速增加。
关注change buffer在innodb buffer pool中的占比
mysql> show variables like '%change_buffer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set (0.01 sec)
　　1、innodb_change_buffer_max_size：表示change buffer在buffer pool中的最大占比，默认25%，最大50%
　　2、innodb_change_buffering：表示索引列merge对象，all表示对IDU索引列都起作用，都进行merge，如果只想对insert索引列进行merge，就把all改为inserts。

调整建议：
　　1、如果系统中有严重的insert、update并且还有活跃的delete时，就增大max_size；
　　2、针对不更改数据的纯报表系统，可以减小该参数值。

“怎么理解MySQL change buffer”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！