Key features of the InnoDB Storage Engine

1. Insert Buffer-It brings performance to the InnoDB Storage Engine
Like Data Pages, insert buffering is an integral part of a physical page.
(1) The primary key is the unique identifier of the row. In the application, the row records are inserted in ascending order of the primary key.-> the insert clustered index is generally sequential, random disk reading is not required.
(2) secondary indexes of non-clustered indexes are not unique. During the insert operation, the insertion of leaf nodes of non-clustered indexes is not sequential. Depreciation requires discrete access to the non-clustered index page, low insertion performance (B + tree determines the discretization of non-clustered indexes)
Insert buffer-> for non-clustered index insertion or update operations, instead of directly inserting each time, you should first determine whether the inserted non-clustered index page is in the buffer pool, and then insert it directly; otherwise, insert a buffer first, and then merge the inserted buffer and non-clustered index leaf nodes at a certain frequency.
Mysql> show engine innodb status;
...
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 7545, free list len 3790, seq size 11336,
8075308 inserts, 7540969 merged recs, 2246304 merges
...
7545 used, 3790 idle, 11336 inserted buffer size = 7545 + 3790,807 5308 inserted records, 7540969 merged pages, 2246304 merged times, 7540969: 2246304 ≈
Note: by default, the inserted buffer pool can occupy a maximum of 1/2 of the buffer pool memory. You can modify IBUF_POOL_SIZE_PER_MAX_SIZE to control the buffer pool size.
Insert buffer conditions:
(1) Secondary Indexes;
(2) The index is not unique.
2. Double Write twice -- providing data reliability for the InnoDB Storage Engine
Partially failed partial page write: When the database is down, the database is writing a page and only writes a part of it, leading to data loss.> the root cause is that the page size of mysql is inconsistent with the page size of the system file. As a result, when writing data, the system does not write the entire buffer pool page to the disk at one time. (For example, for 16 K pages, only the first 4 K is written)
Redo log: record physical operations on the page, such as offset 800, write 'aaa' record, that is, if the page itself is damaged, it makes no sense to redo it.
Write double write twice: before applying the apply redo log, we need a copy of the page. When the write failure occurs, the page is restored through the copy before redoing, this is double write.
Recovery Method: If the write doublewrite buffer itself is invalid, the data will not be written to the disk, innodb will load the original data from the disk, and then calculate the correct data through log files, write the data to the doublewrite buffer again. If writing to the disk fails, write the data in the buffer again.
Shows the doublewrite architecture:

Doublewrite:
The doublewrite buffer in the memory. The size is 2 MB;
128 consecutive pages in the shared tablespace on the physical disk. The size is 2 MB (2 MB = 2*1 MB = 2*64*16 KB = 2*64 pages ).
Process description:
When dirty pages of the buffer pool are refreshed, flush dirty page is used to copy dirty pages to the doublewrite buffer in the memory instead of directly writing to the disk. Then, the doublewrite buffer is used twice, write 1 MB each time to the physical disk of the shared tablespace, and then immediately call the fsync function to synchronize the disk to avoid buffer write problems.
-> In this process, because the doublewrite page is continuous, the entire process is sequential write, with a low overhead. After writing the doublewrite page, write the pages in the doublewrite buffer to the tablespace files. The write operations are discrete.
Innodb_dblwr_pages_written: Innodb_dblwr_writes ≈ 64: 1 (64 dirty pages can be refreshed at a time). When the system write pressure is not very high, it is much less than 64: 1.
In the master/slave master-slave replication structure, the master server must enable the write operation twice. The slave server should disable the write operation twice.
3. Adaptive Hash Index
Hash-> very fast search method O (1), often used in join operations (hash connections between SQL Server and Oracle)
Design Philosophy: database self-optimization-> the InnoDB Storage engine monitors the query of table indexes. If we observe that the creation of hash indexes can speed up, create a hash index-> Create a hash index through the B + tree structure of the buffer pool. Therefore, it is fast to create and does not need to create a hash index for the entire table, the InnoDB Storage engine automatically creates a hash index for certain pages based on the Access frequency and mode. It can only be used to search for equivalent queries, such as select * from table where index_col = 'xxx '.
After the adaptive hash index is enabled, the read/write speed can be increased by 2 times, and the connection operation performance of the secondary index can be increased by 5 times.
Mysql> show engine innodb status;
...
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 7545, free list len 3790, seq size 11336,
8075308 inserts, 7540969 merged recs, 2246304 merges
Hash table size 4980499, node heap has 1246 buffer (s)
1640.60 hash searches/s, 3709.46 non-hash searches/s
...
1640.60: 3709.46 ≈. 26

Reference: <MySQL technology insider InnoDB Storage engine>