Change buffer introduction

change buffer

Reference resources

mysql45 speak

MySQL Technology insider

Introduce and use scenarios

change buffer You can be Insert Buffer The upgrade . Change Buffer The applicable object is still Non unique auxiliary index .

Change Buffer The applicable scenario is ： Update operation of non unique index

When you need to update a data page , If the data page is in memory, update it directly . If not , Without affecting data consistency , Cache the update operation in change buffer in . When the next query needs to access this data page, it will read the data page into memory and then execute change buffer Operations related to this page in .

Why is a unique index not as applicable as a normal index change buffer

For a unique index , Update operation requires First, judge whether this operation violates the uniqueness constraint （ It is necessary to read data pages such as memory to see if they are unique ）, If they are all read into memory, there is no need to use change buffer 了 , Just modify it directly in memory

about For ordinary indexes , When updating, if the target memory page is not in memory , Record the update in change buffer, The execution of the statement is over

change buffer How to improve performance ？

Reading data from disk to memory involves Random IO The interview of , It is one of the most expensive operations of database .

change buffer Reduce random disk access , Improved the performance of the update

change buffer Applicable scenario

Ideal situation ：merge It is the time to update the data , The ideal situation is change buffer Record a lot of records before proceeding merge. This requires less reading operations , If you read a lot , Every time I finish change buffer It's about to be updated , Random IO No reduction

• Write less and read less. , Most suitable for change buffer