MySQL Innodb key features insert buffer

What is insert buffer?

Insert buffer, also known as insert buffer, is one of the key features of the InnoDB storage engine. We often understand insert buffer as a part of the buffer pool. This understanding is one-sided. Part of the information of the insert buffer is in the memory, and the other part exists in the physical page like the data page.

In InnoDB, we know that if a table has an auto-incrementing primary key, then the default insertion of this table is very fast. Note that the primary key here is auto-incrementing. If it is not auto-incrementing, then the insertion will become random, which may bring the overhead of data page splitting. In this way, the insertion will not be sequential and will become slower. There is another situation, that is, if the ID we insert is not sequential but random, then even if there is an auto-increment primary key, the insertion speed will not be particularly fast.

If we define a table with a primary key and a non-clustered index as follows:

create table t(

a int auto_increment,

b varchar(30),

primary key(a),

key (b)

);

When we insert data according to the primary key a, the insertion of the non-clustered index, also known as the secondary index b, is not sequential, and the insertion performance will inevitably decrease.

Innodb storage engine has designed Insert Buffer for this situation. For the insertion or update operation of non-clustered index, it does not insert into the index page every time, but first determines whether the inserted non-clustered index page is in the buffer pool. If it is, it is directly inserted. If not, it is first put into an insert buffer to tell the database that this non-clustered index has been inserted into the leaf node. In fact, it is not inserted, but stored in another location. Then, the Insert buffer and auxiliary index leaf node merge operation are performed at a certain frequency and situation. In this case, it is often possible to merge the insertion of multiple records into one operation, which greatly improves the performance of discrete insertion of non-clustered indexes.

What are the trigger conditions for insert buffer?

The insert buffer needs to meet two conditions to be used. First, the index is a secondary index, and second, the index is not unique. When the above two conditions are met, the insert buffer can be used to improve the insert operation performance of the database.

It should be noted here that if the MySQL database crashes when the program is performing a large number of operations, there must be a large number of insert buffers that have not been merged into the actual non-clustered index, and recovery may take a long time.

Why can't it be a unique index?

The reason why unique indexes are not supported is that if the auxiliary index is a unique index, then the uniqueness needs to be verified during insertion. When verifying the uniqueness, discrete reads will occur, which will increase the overhead, so the insert buffer will not be worth the cost.

We can view the usage of insert buffer through show engine innodb status, as follows:

mysql--root@localhost:dms_alimetadata 20:35:24>>show engine innodb status\G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len ​​0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

The size represents the number of merged record pages, the free list len ​​represents the length of the free list, and the seg size shows that the current insert buffer size is 2*16KB

Introducing the concept of Change Buffer

The latest MySQL5.7 already supports change buffer. In fact, it has been introduced in innodb 1.0.x. This change buffer can be understood as an upgrade of insert buffer, that is, it can buffer common DML languages, including insert, delete and update, corresponding to insert buffer, delete buffer and purge buffer respectively.

Of course, the objects used by the change buffer are still non-unique auxiliary indexes.

Here we take the update operation as an example. The update process can be divided into two parts:

The first part is to mark the record’s delete_mask as deleted. If you don’t know about delete_mask, you can read about it in the April 9th ​​article. The second part is to actually delete the record.

The delete buffer corresponds to the first process of update, and the purge buffer corresponds to the second part.

In innodb, we can enable various buffer options through the parameter innodb_change_buffering. The optional values ​​of this parameter are inserts, deletes, purges, changes, all, none, etc. Among them, inserts, deletes and purges are the situations discussed above, changes means turning on inserts and deletes, and all means turning on all. The default parameters are as follows:

mysql--root@localhost:dms_alimetadata 21:13:37>>show variables like '%buffering%';
        +-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_change_buffering | all |
+-------------------------+-------+
1 row in set (0.01 sec)

We can also control the maximum amount of memory used by change_buffer through innodb_change_buffer_max_size. The default value of this parameter is 25, which is 1/4. The example is as follows:

mysql--root@localhost:dms_alimetadata 21:20:52>>show variables like '%innodb_change_buffer_max_size%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
+-------------------------------+-------+
1 row in set (0.00 sec)

In the output of the show engine innodb status command above, merged operation and discarded operation are displayed, where insert indicates the number of insert buffer operations, delete mark indicates the number of delete buffer operations, and delete indicates the number of purge buffer operations. The discarded operation indicates that when the change buffer is merged, the table has been deleted and no merge is required.

How to implement Insert Buffer?

The data structure of the insert buffer is a B+ tree. Similar to the clustered index, there is only one insert buffer B+ tree globally, which is responsible for insert buffering all tables. This B+ tree is placed in the shared table space, that is, the ibdata1 file. Therefore, when trying to restore table data through the ibd file, the check table may fail because the data in the auxiliary index of the table may still be in the insert buffer. Therefore, after restoring the file through the ibd file, the repair table operation is required to rebuild the auxiliary index on the table.

Since the insert buffer is a tree, it must have leaf nodes and non-leaf nodes. The non-leaf nodes store the search key values ​​of the query. Its structure is as follows:

+---------+------------+-------+
| space | marker | Value |
+---------+------------+-------+

This structure occupies a total of 9 bytes, where space represents the table space id of the table where the record to be inserted is located. This id is a unique id that each table must have. Space occupies 4 bytes, marker occupies 1 byte to be compatible with the old version of insert buffer, and offset occupies 4 bytes to indicate the offset of the page.

Insertion process of auxiliary index?

When an auxiliary index is to be inserted into a data page, if the data page is not in the buffer pool, InnoDB will construct a search key according to the rules, and then insert the record into the B+ tree of the insert buffer. During the insertion process, the record needs to be constructed in some ways. The final result of the insertion is a record similar to the following:

+---------+------------+-------+------------+------+-------+------+------+
| space | marker | Value | metadata | | | | |
+---------+------------+-------+------------+------+-------+------+------+

It can be found that there is an additional metadata field and four other fields at the end. Let’s talk about the metadata field first. It occupies 4 bytes and is used to sort the order in which each record enters the insert buffer. Starting from the 5th column, it is the value of each field of the actual inserted record. Therefore, compared with a simple data record, the insert buffer requires an additional 13 bytes of overhead.

In order to ensure that each merge insert buffer is successful, a special data page needs to be set to mark the available space of each auxiliary index page. The type of this data page is insert buffer bitmap, which can track the available space of many auxiliary index pages. Let's take a brief look at it here, and its usage will be explained below.

When to Merge Insert Buffer?

We already know that when the auxiliary index page for inserting records is not in the buffer pool, the auxiliary index record needs to be inserted into this B+ tree, and then it will be merged from the insert buffer to the real auxiliary index. So when will the merge be performed?

1. When the auxiliary index page is read into the buffer pool

2. When the insert buffer Bitmap tracks that there is not enough free space on the auxiliary index page, the general threshold is 1/32 of the auxiliary index page space.

3. The master thread performs the merge insert buffer operation once per second

The above is the details of the insert buffer, a key feature of MySQL Innodb. For more information about the insert buffer feature of Innodb, please pay attention to other related articles on 123WORDPRESS.COM!