(mysql.info.gz) InnoDB Insert buffering

Info Catalog

(mysql.info.gz) InnoDB physical structure

(mysql.info.gz) Table and index

(mysql.info.gz) InnoDB Adaptive hash

 
 15.14.2 Insert Buffering
 ------------------------
 
 It is a common situation in a database application 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.
 

Info Catalog

(mysql.info.gz) InnoDB physical structure

(mysql.info.gz) Table and index

(mysql.info.gz) InnoDB Adaptive hash