Database applications often insert new rows in the ascending order of the primary key. In this case, due to the layout of the clustered index in the same order as the primary key, insertions into an InnoDB table do not require random reads from a disk.
On the other hand, secondary indexes are usually nonunique, and
insertions into secondary indexes happen in a relatively random
order. In the same way, deletes and updates can affect data
pages that are not adjacent in secondary indexes. This would
cause a lot of random disk I/O operations without a special
mechanism used in InnoDB
.
When an index record is inserted, marked for deletion, or
deleted from a nonunique secondary index,
InnoDB
checks whether the secondary index
page is in the buffer pool. If that is the case,
InnoDB
applies the change directly to the
index page. If the index page is not found in the buffer pool,
InnoDB
records the change in a special
structure known as the insert
buffer. The insert buffer is kept small so that it fits
entirely in the buffer pool, and changes can be applied very
quickly. This process is known as
change buffering.
(Formerly, it applied only to inserts and was called insert
buffering. The data structure is still called the insert
buffer.)
Disk I/O for Flushing the Insert Buffer
Periodically, the insert buffer is merged into the secondary index trees in the database. Often, it is possible to merge several changes into the same page of the index tree, saving disk I/O operations. It has been measured that the insert buffer can speed up insertions into a table up to 15 times.
The insert buffer merging may continue to happen
after the transaction has been committed.
In fact, it may continue to happen after a server shutdown and
restart (see Section 13.6.7.2, “Forcing InnoDB
Recovery”).
Insert buffer merging may take many hours when many secondary
indexes must be updated and many rows have been inserted. During
this time, disk I/O will be increased, which can cause
significant slowdown on disk-bound queries. Another significant
background I/O operation is the purge thread (see
Section 13.6.10, “InnoDB
Multi-Versioning”).