In the MySQL database system, the InnoDB storage engine maintains indexes for tables to speed up data retrieval. Indexes are essentially lookup tables that the database search engine can use to find data more quickly than by scanning the entire table.
In addition to primary indexes (which are usually based on the primary key of a table), InnoDB also supports secondary indexes. While primary indexes ensure data is physically stored in an order, secondary indexes are essentially pointers to the primary index, offering alternative ways to organize and access data.
The innodb_change_buffering variable governs how InnoDB handles changes (like inserts, deletes, and purges) to these secondary indexes:
- Inserts – When a new row is added to a table, any secondary indexes on that table will also need to be updated with the information about the new row.
- Deletes – When a row is deleted, the corresponding entries in the secondary indexes also need to be removed.
- Purges – InnoDB operates with a multi-version concurrency control (MVCC) model. When a row is deleted, InnoDB doesn't immediately remove it. Instead, it marks it for deletion and purges it later. The purge operation also affects secondary indexes.
Instead of immediately applying these changes to secondary indexes, InnoDB can buffer them. Buffering these changes means that they are held in memory and applied later in one go, which can be faster than applying each one individually as it happens. This approach can optimize disk I/O operations, as writing to disk is generally one of the slower operations in a database system.
The innodb_change_buffering MySQL system variable controls how the InnoDB storage engine buffers changes to secondary indexes. It is primarily intended to optimize disk I/O operations for these changes. Allowed Values
The innodb_change_buffering variable can have one of the following values:
- none – No operations are buffered.
- inserts – Only insert operations are buffered.
- deletes – Only delete operations are buffered.
- changes – Both insert and delete operations are buffered.
- purges – Only purge operations are buffered.
- all – All types of operations are buffered.
The default value for MySQL is all, meaning that, by default, InnoDB buffers all types of operations. The default value for MariaDB is none.