I was exploring MySQL internals and found something clever called the change buffer - it is an interesting optimization to avoid random disk I/O on index updates. Here’s how it works…
When you insert, update, or delete a row, MySQL (InnoDB) needs to update all the secondary indexes too. But what if that index page is not loaded in the buffer pool (cache)?
Instead of fetching that page from disk immediately, InnoDB records the change in a special structure called the change buffer. This buffer itself lives in the buffer pool (cache) and acts as a deferred work queue.
The actual merge happens later in two ways:
- when a query eventually needs that index page and loads it into memory, InnoDB applies all the buffered changes at once
- a background thread periodically flushes and merges these changes during idle time
This batching is powerful, and it saves many random disk writes - jumping around to update scattered index pages - becoming sequential operations. For write-heavy workloads with multiple secondary indexes, this is a massive performance win.
Of course, there is a trade-off. If the secondary indexes are frequently read (meaning those pages are almost always in the buffer pool anyway), the change buffer provides little benefit since the pages are already in memory.
By the way, this can be tuned with a parameter innodb_change_buffering. For read-heavy workloads, you should disable it entirely. For write-heavy workloads with rarely-read indexes, setting this makes things efficient.
Sorry, I find weird things interesting. But, hope this helps :)