Thresh

InnoDB is used as the default engine since MySQL 5.5. It specializes in transactions and has automatic crash recovery features. The following is the official InnoDB engine architecture diagram, which is mainly divided into memory structure and disk structure.

InnoDB memory structure

The memory structure mainly includes Buffer Pool, Change Buffer, Adaptive Hash Index and Log Buffer.

Buffer Pool: Buffer Pool, BP for short.

BP takes Page Page as the unit and the default size is 16K. The bottom layer of BP uses linked list data structure to manage Page. When InnoDB accesses table records and indexes, they are cached in the Page Page, which can be used to reduce disk I/O operations and improve efficiency.

Page management Mechanism Page can be classified into three types based on the status:

Clean Page: Used page, data is not modified. Dirty Page: Used page, data is modified. Data on the page is inconsistent with data on the diskCopy the code

InnoDB maintains and manages the three page types through three linked list structures

Free list: indicates the free buffer, and manages free page Flush list: indicates the buffers that need to be flushed to disks, and manages dirty pages. Internal pages are sorted by modification time. Dirty pages exist in both the FLUSH list and the LRU list, but the two do not affect each other. The LRU list manages page availability and release, while the Flush list manages the flush of dirty pages. Lru list: indicates the buffer in use, manages clean page and Dirty Page buffers based on MIDpoint, the front list is called new list area stores frequently accessed data, accounting for 63%; The next linked list, called the Old list area, holds less used data, at 37%.Copy the code

Improved LRU algorithm maintenance

• Normal LRU:

In the end elimination method, new data is added from the head of the list and eliminated from the end when space is freedCopy the code

• Modified LRU:

The linked list is divided into two parts: new and old. When an element is inserted, it is not inserted from the table head, but from the middle midpoint. If the data is accessed quickly, the page moves to the head of the new list.Copy the code

• Every time a new page is read into the buffer pool, the InnoDb engine determines if there are free pages and if there are enough, then it removes the free page from the free List and puts it into the LRU list. If there are no free pages, the default pages of the LRU linked list are weeded out according to the LRU algorithm and memory space is freed to allocate to the new pages.

Buffer Pool configuration parameters

show variables like '%innodb_page_size%'; Show variables like '%innodb_old%'; Show variables like '%innodb_buffer%'; Suggestion: Set Innodb_buffer_pool_size to 60%-80% of the total memory size. Innodb_buffer_pool_instances can be set to multiple sizes to avoid cache contention.Copy the code

Change Buffer: Write Buffer, CB for short.

During DML operations, if BP does not have its corresponding Page data, disk pages are not immediately loaded into the buffer pool. Instead, CB records the buffer changes and then merges the data back into BP when future data is read. The ChangeBuffer occupies 25% of the BufferPool space by default and 50% by maximum. The value can be adjusted based on read and write services.

Parameter innodb_change_buffer_max_size;Copy the code

When a record is updated, it exists in the BufferPool and is modified directly in the BufferPool, a memory operation. If the record does not exist in the BufferPool (no hit), a memory operation is performed in ChangeBuffer, avoiding disk data query and avoiding disk I/O. The next time the record is queried, it is advanced disk read, and then it is merged from ChangeBuffer and loaded into BufferPool.

Write buffer, only for non-unique ordinary index pages, why?

If the index is set to be unique, InnoDB must perform uniqueness checks before making changes, so it must query the disk and perform an IO operation. Records are queried directly into BufferPool and then modified in BufferPool, not ChangeBuffer.

Adaptive Hash Index: an Adaptive Hash Index used to optimize queries for BP data.

The InnoDB storage engine monitors table index lookups, and if it sees a speed increase by creating a hash index, it creates a hash index, so it’s called adaptive. The InnoDB storage engine automatically hashes certain pages based on the frequency and mode of access.

Log Buffer: indicates the Log Buffer

The log buffer is used to store data to be written to the log file (Redo/Undo) on disk. The contents of the log buffer are periodically flushed to the log file on disk. When the log buffer is full, it is automatically flushed to disk, and when large transactions such as BLObs or multi-line updates occur, adding the log buffer can save disk I/O. The LogBuffer is used to log InnoDB engine logs. During DML operations, Redo and Undo logs are generated. When the LogBuffer space is used up, it is automatically written to disk. You can increase the innodb_log_buffer_size parameter to reduce disk I/O frequency. The innodb_flush_log_at_trx_COMMIT parameter controls the log refresh behavior. The default value is 1

0: write log files and flush disks every second (write log files LogBuffer >OS cache, flush disk files OS cache > disk files). Data will be lost for a maximum of one second. 1: Commit transactions. Transaction commit, write log file immediately, flush every 1 secondCopy the code

InnoDB disk structure

InnoDB disks contain Tablespaces, InnoDB Data Dictionary, Doublewrite Buffer, Redo Log, and Undo Logs.

Tablespaces

Used to store table structures and data. Table Spaces are divided into system table Spaces, independent table Spaces, general table Spaces, temporary table Spaces, Undo table Spaces and so on.

The System Tablespace contains The storage area of InnoDB data dictionary, Doublewrite Buffer, Change Buffer, Undo Logs. The system table space also contains, by default, any table data and index data created by the user in the system table space. A system table space is a shared table space because it is shared by multiple tables. The data files in this space are controlled by the innodb_data_file_path parameter. The default value is IBDATA1 :12M:autoextend(file name ibdata1, 12MB, autoextend).

File-per-table Tablespaces are enabled by default. An independent tablespace is a single-table tablespace created in its own data File rather than in the system tablespace. When innodb_FILe_per_TABLE is enabled, tables will be created in the tablespace. Otherwise, InnoDB will be created in the system tablespace. Each table file table space is represented by an. Ibd data file, which is created by default in the database directory. Table files for table Spaces support dynamic and commpressed row formats.

General Tablespaces are shared Tablespaces created using the CREATE TABLESPACE syntax. A generic table space can be created in a table space other than the mysql data directory, can hold multiple tables, and supports all row formats.

CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; TABLESPACE ts1 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; // Add the table to the TS1 tablespaceCopy the code

An Undo Tablespaces consists of one or more Undo log files. Before MySQL 5.7, Undo occupied the System Tablespace share. Since 5.7, Undo has been separated from System Tablespace. The undo tablespace used by InnoDB is controlled by the Innodb_undo_TABLespaces configuration option, which defaults to 0. If the parameter value is 0, the system tablespace ibDATA1 is used. If the value is greater than 0, the undo tablespace undo_001 and undo_002 is used.

Temporary Tablespaces are classified into session Temporary Tablespaces and global Temporary TABLESPACE.

Session temporary tablespaces store temporary tables created by users and those on disks. Global TEMPORARY TABLESPACE Stores rollback segments of user temporary tables. Temporary table Spaces are removed when the mysql server shuts down properly or terminates abnormally and are recreated each time it is started.Copy the code

Redo Log

A redo log is a disk-based data structure used to correct data written by incomplete transactions during crash recovery. MySQL writes to the redo log file in a circular manner, logging all changes to the Buffer Pool in InnoDB. When an instance failure (such as a power outage) causes data not to be updated to the data file, the database must redo data to the data file during restart. Redo logs are generated during write/write transactions. By default, redo logs are physically represented on disk by two files named IB_logFile0 and ib_logFILe1.

Undo Logs

The undo log is a backup of the modified data saved before the transaction begins and is used to roll back the transaction in exceptional cases. Undo logs are logical logs, logged on a per-row basis. Undo logs exist in system table Spaces, undo table Spaces, and temporary table Spaces.