InnoDB Insert buffer (insert buffering)

InnoDB Insert buffer (insert buffering)

Each storage engine has its own characteristics (determining performance and higher reliability), while the key features of InnoDB are:

• Insertion buffer (insert buffer)--"Change buffer"
• Write two times (Double write)
• Adaptive Hash Index (Adaptive hash Page)
• Asynchronous io (async IO)
• Refresh adjacency page (Flush Neighbor page)

Today, let's talk about the role of insert buffer in the InnoDB storage engine.

Insert buffer is an important data change log, known as insert buffer prior to MySQL5.1, to optimize the read IO of the insert operation on level 2 non-unique indexes, renamed Change Buffer after MySQL5.5, Features are also extended to 2-level non-unique indexes for INSERT, delete, update, purge read IO optimizations. That is, the primary purpose of the change buffer is to cache the data operations on the two-level index to reduce the random IO of the two-level index and to achieve the effect of operation merging. It is worth mentioning that the change buffer is also part of the physical page.

The core idea of change buffer, when the database needs to modify the level 2 cache, is to cache these updates in memory, under certain conditions, the unified update these to the corresponding Level 2 index page, which can reduce the number of read Io, and adjacent pages of the read IO can be merged. Primary keys and unique indexes cannot be optimized because uniqueness judgments require reading data from the actual indexed page, which deviates from the intention of the insert Buffer to reduce the read IO. The name in the source code is still used ibuf.

structibuf_struct{ulint size; //Current number of ibuf btree pagesUlint max_size;//ibuf Maximum number of pagesUlint seg_size;//number of pages in the owning segmentIbool empty;//is emptyUlint Free_list_len;//idle linked list lengthUlint height;//Tree Heightdict_index_t* index;//Ibuf IndexUlint n_merges; //number of merged pagesUlint N_merged_ops[ibuf_op_count];//number of pages merged by various operationsUlint N_discarded_ops[ibuf_op_count];//The number of pages involved in the abort operation};

From the structure mentioned above,ibuf is actually a B + Tree index, which has exactly the same structure as the other B + trees in InnoDB. The record in the Ibuf tree is actually the information that contains the record itself and the page number where the record is located.

The point to note is that IBUF applies only to Non-unique secondary indexes, which means it can only be used on non-unique indexes for the following reasons:

1. Primary key is inserted in ascending order, and the exception is inserted into the clustered index in general order, non-random IO

2. Write a unique index to check whether a record exists, so before you modify a unique index, you must read the modified record-related index page to know is not unique, so ibuf is meaningless, to read out (random io) so only for non-unique index meaningful.

Ibuf can cache three types of operations: INSERT, Delete-mark, DELETE operations, the first two corresponding user thread operations, and the third is triggered by purge operations.

The user can control what the cache does with the parameter innodb_change_buffering:

Static Const Char* Innobase_change_buffering_values[ibuf_use_count] = {        "None",/*Ibuf_use_none*/        "Inserts",/*Ibuf_use_insert*/        "Deletes",/*Ibuf_use_delete_mark*/        "Changes",/*Ibuf_use_insert_delete_mark*/        "Purges",/*Ibuf_use_delete*/        " All"           /*Ibuf_use_all*/};

The default value of Ibuf is all, which means that all operations are cached. Note Because update operations on a level two index are always first Delete-mark and then insert new records, Update generates two IBUF entry.

Therefore, delete buffer corresponds to the first procedure of the update operation, and the record is marked for deletion. Purge buffer corresponds to the second procedure of the update operation, which is about to record the real deletion. The InnoDB storage engine also provides parameter innodb_change_buffering to enable the various buffer options. The optional values for this parameter are: Inserts, deletes, purges, changes, all, none. Inserts, deletes and purges are the three cases discussed earlier. Changes means that enabling inserts and Deletes,all means all is enabled, none means not enabled.

InnoDB Insert buffer (insert buffering)