MySQL InnoDB Insert Buffer

The three features of the InnoDB storage engine are very exciting, they are insert buffers, two writes, and adaptive hashes, and this article introduces the first feature-insertion buffer (insert buffer)

In the previous "MySQL-talking about InnoDB storage Engine", we can see that in the memory of InnoDB, there is a separate area called "Insert buffer", we will introduce it in detail below.

Non-clustered index write performance issues

To illustrate the problem of nonclustered index write performance, let's look at an example:

Mysql>create Table T (

ID int auto_increment,

Name varchar (30),

Primary key (ID));

We created a table where the primary key of the table is Id,id, that is, when the insert operation is performed, the ID column grows automatically, and the row records in the page are stored in the ID order, without having to randomly read the data from other pages. Therefore, in such a case (that is, a clustered index), the insertion operation is highly efficient.

However, in most applications, there is rarely a single clustered index in the table, and more often than not, there are multiple nonclustered secondary index (secondary indexes) on the table. For example, for the previous table T, the business also needs to be looked up by a name field that is not unique, and the table definition should read:

Mysql>create Table T (

ID int auto_increment,

Name varchar (30),

Primary key (ID),

Key (name));

At this point, in addition to the primary key aggregation index, also produces a Name column Secondary index, for the nonclustered index, leaf node insertion is no longer orderly, then need to discrete access to the nonclustered index page, insert performance is low.