ChangeBuffer is a special data structure in the InnoDB cache. When users perform SQL to make changes to non-unique indexes, InnoDB does not load disk data directly into the cache data page if the corresponding data page is not in the cache. Instead, InnoDB caches the changes. These change operations may be triggered by insert, update, or delete operations (DML). Changes to the cache are merged into the corresponding cache data page when disk data is loaded into the cache by other reads.

ChangeBuffer

The official schematic of InnoDB ChangeBuffer is shown below, showing the following information:

  1. ChangeBuffer is used to store SQL change operations, such as Insert/Update/Delete SQL statements.
  2. Each change operation in ChangeBuffer has a corresponding data page that is not loaded into the cache;
  3. When the data page corresponding to the change operation in ChangeBufferd is loaded into the cache, InnoDB merges the change operation into the data page.
  4. InnoDB periodically loads data pages corresponding to ChangeBuffer operations into the cache and merges change operations.

Based on my personal understanding and reference to the official ChangeBuffer sample diagram, I drew the following more intuitive ChangeBuffer sample diagram:

The role of ChangeBuffer

We know that InnoDB recommends using auto-increment primary keys, which are incremented when inserted and can be accessed sequentially. Unlike clustered indexes, secondary indexes are usually not unique and are inserted in a relatively random order. Similarly, updates and deletions of secondary indexes often affect non-adjacent secondary index data pages in the index tree.

For random access due to secondary index data changes, disk I/O each time will obviously affect database performance. Therefore, InnoDB does not immediately perform a change operation if the data page is not in the secondary index of the cache. Instead, InnoDB caches the change operation first and then merges all the changes on a data page into that data page at some point. Through the ChangeBuffer, InnoDB can merge a large number of random access I/O on the same data page.

ChangeBuffer workflow

When are changes put into ChangeBuffer

Not all operations in the database go into ChangeBuffer. Database statements that meet the following conditions do not modify the data page during execution, but instead go into ChangeBuffer.

  1. SQL modifies the data in the database;
  2. SQL statement does not involve unique key verification;
  3. SQL statements do not need to return changed data;
  4. The data page involved is not in the cache;

ChangeBuffer is merged into the original data page

ChangeBuffer buffers changes that need to be merged into data pages in the database. The Merge process is called Merge. Under what circumstances does ChangeBuffer Merge occur?

  1. Access the data page corresponding to the change operation;
  2. InnoDB background Merge periodically;
  3. Database BufferPool space insufficient;
  4. When the database is normally shut down;
  5. RedoLog is full;

Why does ChangeBuffer only cache non-unique index data

ChangeBuffer only works if the changed data is not a unique index. If the changed data is a unique index or primary key, InnoDB cannot cache the changed data into ChangeBuffer. Why?

Take a user table as an example, the user table contains the primary key ID, age, name and gender four fields, where age added non-unique index, the initial data and table construction sentence as follows:
The user ID 1 2 3 4 5 6 7 8 9
The name Chen Er A loose Lee thought The king dance Zhao Liu Sun period Zhou l ‘envoi Wu wine The history of zheng
gender male male female female male male male female male
age 5 10 20 28 35 56 25 80 90 
create table user_info
(
    id   int primary key,
    age  int not null,
    name varchar(16),
    sex  bool,
    key(age)
)engine=InnoDB;
Copy the code

Non-unique index updates

Update user_info set age=6 where id=1 where age=6 update user_info set age=6 where id=1 where age= 1

  • If the age index page and row data page need to be changed are in the cache, directly update the data in the cache and mark the data page as dirty;
  • If you need to change the age index page and row data page that is not in the cache, put the SQL statement directlyupdate user_info set age=6 where id=1Store to ChangeBuffer;

Unique index update

SQL statement update user_info set id=2 where id=1 change user ID to 2.

  • If the clustered index and row data pages that need to be changed are in the cache, the data in the cache is directly updated and the data pages are marked as dirty.
  • If the clustered index page and row data page to be changed are not in the cache, the corresponding data page needs to be loaded into the cache to check whether the ID meets the unique key constraint after modification, and then modify the data in the cache.

As can be seen, unique indexes need to be checked for uniqueness. Therefore, when updating unique indexes, the corresponding data pages must be loaded into the cache for verification, resulting in the invalidation of ChangeBuffer.

A normal index or a unique index

Based on the above analysis, we know that unique indexes cannot use ChangeBuffer, so should we use normal indexes or unique indexes in actual use?

From the perspective of equivalent query performance:

  • After finding the first data that meets the condition, a normal index needs to continue to search for the data that meets the condition.
  • Unique indexes do not need to look back after finding the first data that meets the condition, because indexes are unique.

There is only one record difference between the two. How much difference does one record make in performance? The answer is, very little. Since the InnoDB engine reads data on a per-page basis, it often reads neighboring data into memory as well, so there is little difference in performance from multiple queries.

From an index modification perspective:

Because ChangeBuffer cannot be used for non-unique indexes, changes to indexes can cause a lot of disk I/O, affecting database performance.

In summary, it is better to use a normal index rather than a unique index if the business does not require the database to perform a unique check on a field.

ChangeBuffer application scenario

When does ChangeBuffer get a big performance boost?

  1. Most database indexes are non-unique;
  2. The business is to write more and read less, or not to read immediately after writing;

Scenarios that are not suitable for using ChangeBuffer are:

When is it not suitable?

  1. Databases are unique indexes;
  2. Data is read immediately after being written.

ChangeBuffer parameters

  • innodb_change_buffer_max_size: Configures the size of the write buffer. The default value is 25%, and the maximum value is 50%.

You need to increase this value only when you write more and read less.

  • innodb_change_buffering: Configures which write operations enable write buffers, which can be all/ None /inserts/ Deletes, etc.

I am the god of the royal Fox. Welcome to follow my wechat official account: Wzm2ZSD

This article was first published to wechat public account, all rights reserved, reprint prohibited!