Three key features of the InnoDB

The three key features of the INNODB storage engine are insert buffers (insert buffer), two write (double write), and adaptive hash Indexes (Adaptive Hash Index).

1. The principle of inserting buffer (insert buffer):

Inserts and updates for nonclustered indexes are not inserted directly into the index page every time, but are first judged whether the inserted nonclustered index page is in the buffer pool, or, if so, inserted directly, or put in an insert buffer first. It's like a cheat database. This nonclustered index has been inserted into the leaf node, and then the merge operation of the insert buffer and nonclustered index page child nodes is performed at a certain frequency, which can often be combined into one operation, which greatly improves the performance of insert and modify operations on nonclustered indexes.

Conditions for inserting buffer use:

1, index is a secondary index;

2, the index is not unique;

2, two writes (double write)
Two times addressed to InnoDB is the reliability that is primarily used to resolve partial write failures (partial page write). Doublewrite has two parts, part of the doublewrite buffer in memory, 2M in size, and the other part is a contiguous 128 pages in the shared table space on the physical disk, two extents, the same size as 2M. When the expansion of the buffer pool refreshes, not directly write the hard disk, but through the memcpy function to copy the dirty page into memory doublewrite buffer, and then through the doublewrite buffer two times write, each write 1M to the shared table space on the physical disk, Then call the Fsync function immediately and synchronize the disk. As shown

The following command can be used to view doublewrite usage.

Mysql> show global status like ' innodb_dblwr% ';

Slave can improve performance by setting the Skip_innodb_doublewrite parameter to turn off two write functions, but master must turn on this feature to ensure data security.

3. Adaptive Hash Index (adaptive hash)
Because InnoDB does not support hash index, but in some cases, the efficiency of the hash index is very high, so there is the adaptive hash of the index function, the INNODB storage engine will monitor the table of indexes on the lookup, if you observe the establishment of a hash index can improve performance, The hash index is automatically established. The use of the adaptive Hasi Index can be viewed through show engine InnoDB status\g. You can use Innodb_adaptive_hash_index to disable and enable hash indexing, which is turned on by default.

Three key features of the InnoDB