14.2.13.2. Insert Buffering
It is a common situation in database applications that the
primary key is a unique identifier and new rows are inserted in
the ascending order of the primary key. Thus, the insertions to
the clustered index do not require random reads from a disk.
On the other hand, secondary indexes are usually non-unique, and
insertions into secondary indexes happen in a relatively random
order. This would cause a lot of random disk I/O operations
without a special mechanism used in InnoDB.
If an index record should be inserted to a non-unique secondary
index, InnoDB checks whether the secondary
index page is in the buffer pool. If that is the case,
InnoDB does the insertion directly to the
index page. If the index page is not found in the buffer pool,
InnoDB inserts the record to a special insert
buffer structure. The insert buffer is kept so small that it
fits entirely in the buffer pool, and insertions can be done
very fast.
Periodically, the insert buffer is merged into the secondary
index trees in the database. Often it is possible to merge
several insertions to 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 inserting transaction has been
committed. In fact, it may continue to happen after a server
shutdown and restart (see Section 14.2.8.1, “Forcing InnoDB Recovery”).
The 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 14.2.12, “Implementation of Multi-Versioning”).
