INNODB Key Features

InnoDB Key features include:

Insertion buffer (insert buffer), two write (Double write), Adaptive Hash Index (Adaptive hash indexes),

Asynchronous io (async io), refreshes the adjacency page (Flush Neighbor page)

INSERT BUFFER may be one of the most exciting and exciting features of the InnoDB storage engine's key features. INSERT

Buffer is an integral part of the physical page as well as the data page.

Insert buffering is groundbreaking to solve this problem: for non-clustered index inserts or updates, not every time directly into the index page, first determine whether the index page in the buffer pool, provoked, then directly inserted, provoke not, then put in the insert buffer first, The merge operation (merge) that inserts the buffered and nonclustered index page sub-nodes at a certain frequency increases performance significantly.

Prerequisites for inserting buffers: NON unique,secondary INDEX.

Change buffer can be considered an upgrade of insert buffer, and the InnoDB storage engine can buffer DML operations---INSERT, UPDATE, delete, respectively: Insert buffer, delete buffer, Purge Buffer.

An update to a record can be done in two processes:

(1) Mark the record as deleted and (2) delete the record.

The purge buffer, which corresponds to the second procedure of the update operation, is about to record the actual deletion.

The InnoDB storage engine also provides the parameter innodb_change_buffering, which is used to open various buffer options. The optional value for this parameter is: Inserts,deletes,purges,changes,all,none.

Write two times (doublewrite)

If INSERT BUFFER brings a performance boost to the InnoDB storage engine, then Doublewrite brings the InnoDB storage engine the reliability of the data pages. This is because, when the database is down, the InnoDB storage engine may be writing a page to the table, and this time only writes a portion (such as a 16K page and writes only the first 4K), which is known as partial write invalidation (partial page write). You might be thinking about recovering with a redo log. This is a way. However, the redo log records the physical operation of the page, such as offset 800, which writes "AAAA" records. If the page itself has been damaged, it is not interesting to redo it. This is the need for a copy of the page before the redo log is applied, and when the write is invalidated, the page is restored by the duplicate of the page before being re-made.

Double WRITE consists of two parts, Doublewrite BUFFER in memory, 2MB in size, and 128 consecutive pages in the shared table space on the physical disk, 2 extents, and the same size as 2MB.

Adaptive Hash Index

Hashing is a very fast way to find, in general the time complexity of this lookup is 0 (1). The number of B + trees, depending on the height of the B + tree, in the spawning environment, the B + tree is typically 3-4 levels high and does not need to be queried 3-4 times. The InnoDB storage engine monitors queries on the index pages on the table. If the resume hash index is observed to increase speed, this CV hashes the index, called an Adaptive Hash Index (Adaptive hash, AHI). Ahi is constructed from the B + Tree page of the buffer pool. It is therefore very fast to build and do not build a hash index on the entire table. InnoDB Storage Yo inquiry will automatically build a hash index for some hot pages based on the frequency of the house and the strange.

Asynchronous IO (Asy io)

The system uses asynchronous IO (AIO). Prior to InnoDB 1.1.x, the implementation of AIO was simulated by the code in the InnoDB storage engine. But since then, support for kernel-level AIO has been provided, called native AIO. Native AIO requires an operating system support. Both Windows and Linux are supported, while Macs are not available. This is a factor to consider when selecting the operating system of the MySQL database server. MySQL can use the parameter Innodb_use_native_aio to decide whether to enable native AIO. In the InnoDB storage engine, read ahead reads are done through AIO, dirty page refreshes, and also through AIO.

Refresh adjacency Page
When a dirty page is refreshed by the InnoDB storage engine, all pages of the page's region (extent) are detected and refreshed together if the page is dirty. The benefit of this is that multiple IO writes can be combined into an IO operation through AIO. This working mechanism has a significant advantage over traditional mechanical disks. But there are two things to think about: a. Is it not a dirty page to write, and the page will soon become dirty page? B. Solid-state drives have high IOPS, do you still need this feature? For this reason, the InnoDB storage engine version 1.2.x starts with the parameter innodb_flush_neighbors to determine whether it is enabled. Recommended for traditional mechanical hard drives, and for SSDs can be turned off

INNODB Key Features