MySQL storage engine detailed explanation of InnoDB architecture

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about the storage engine InnoDB architecture. InnoDB is the default engine of MySQL, a storage engine that supports transaction security. , let’s take a look at it, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

The current MySQL8.x version database already supports many storage engines, but generally we There are only a few commonly used ones. It is easy to form a fixed mindset and not easily adopt other storage engines, thus missing many functions of optimizing storage. Therefore, it is worth learning to have a clear understanding of the functions of the nine currently supported database storage engines. This article clearly explains the functions, functions and usage scenarios of these eight database storage engines.

This series of articles will be included in my column - Quickly Learn Various SQL Database Operations, which basically covers all aspects of using SQL to handle daily business, conventional query database analysis, and complex operations. From the basic steps of building databases and tables to handling various complex database operations, as well as professional explanations of common SQL functions, a lot of time and effort have been spent on creation. If you have friends who need to engage in data analysis or data development, I recommend subscribing to the column. Learn the most practical and commonly used knowledge in the first time. This blog is quite long and deserves careful reading and practice. I will select the best parts and explain the practice in detail. The blogger will maintain the blog post for a long time. If you have any errors or doubts, you can point them out in the comment area. Thank you for your support.

1. Supported storage engines

Enter the MySQL database to view the storage engine and you can see all the storage engines supported by the MySQL database:

SHOW ENGINES

Currently there is an engine Federated does not support, we only need to be clear about the other eight database storages.

Common database engines in MySQL include MyISAM, InnoDB, and Memory. So let’s first understand these three engines.

2. InnoDB engine

InnoDB is the default engine of MySQL, a storage engine that supports transaction security. Data in MySQL is stored on the physical disk, and the actual data processing is performed in memory. Since the read and write speed of the disk is very slow, if the disk is frequently read and written for each operation, the performance will be very poor.

In order to solve the above problems, InnoDB divides the data into several pages, using pages as the basic unit of interaction between disk and memory. The general page size is 16KB. In this case, at least 1 page of data is read into memory or 1 page of data is written to disk at a time. Improve performance by reducing the number of interactions between memory and disk.

This is essentially a typical cache design idea. Generally, cache design is basically considered from the time dimension or space dimension:

• Time dimension: If a piece of data is being used, it will most likely be used again in the next period of time. It can be considered that hotspot data caching belongs to the implementation of this idea.

• Spatial dimension: If a piece of data is being used, there is a high probability that the data stored near it will also be used soon. InnoDB's data pages and operating system's page cache are the embodiment of this idea.

The following is the official InnoDB engine structure diagram, which is mainly divided into two parts: memory structure and disk structure.

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

1.Buffer Pool

Buffer Pool consists of data, index, insert buffer, adaptive hash index, lock information and data dictionary. Buffer pool, referred to as BP. BP is based on Page, with a default size of 16K. The bottom layer of BP uses a linked list data structure to manage Pages. When InnoDB accesses table records and indexes, they will be cached in the Page page. Later use can reduce disk IO operations and improve efficiency.

The buffer pool is simply a memory area that uses the speed of memory to compensate for the impact of slow disk speed on database performance. When reading a page in a database, the page read from the disk is first stored in the buffer pool. This process is called "FIX" the page in the buffer pool. The next time the same page is read, first determine whether the page is in the buffer pool. If it is in the buffer pool, the page is said to be hit in the buffer pool. Read the page directly. Otherwise the page on disk is read. For the modification operation of pages in the database, the pages in the buffer pool are first modified, and then refreshed to the disk at a certain frequency. What needs to be noted here is that the operation of flushing pages from the buffer pool back to the disk is not triggered every time the page is updated, but is flushed back to the disk through a mechanism called Checkpoint. Again this is to improve the overall performance of the database.

Traditional LUR algorithm

The buffer pool is managed through the LRU (Latest Recent Used, least recently used) algorithm, that is, the most frequently used pages At the front of the LRU list, and the least used page is at the end of the LRU list. When the buffer pool cannot store the newly read page, the page at the end of the LRU list is first released:

(1) page If it is already in the buffer pool, then only the action of "moving" to the LRU head is performed, and no page is eliminated;

(2) The page is not in the buffer pool, except for "putting" the page into the LRU head. Action, but also to "eliminate" the LRU tail page;

But InnoDB's LUR algorithm is not a traditional LUR algorithm.

There are two problems here:

(1) Pre-reading failure;

(2) Buffer pool pollution;

Let’s first understand what pre-reading is Read;

Read ahead

Disk reading and writing is not reading on demand, but reading by page, at least one page at a time Page data (usually 4K), if the data to be read in the future is in the page, subsequent disk IO can be omitted and efficiency improved. Data access usually follows the principle of "concentrated reading and writing". When using some data, there is a high probability that nearby data will be used. This is the so-called "locality principle", which shows that early loading is effective and can indeed reduce disk IO.

Read-ahead failure

Due to read-ahead (Read-Ahead), the page was put into the buffer pool in advance, but in the end MySQL did not Reading data from the page is called a read-ahead failure.

To optimize the read-ahead failure, the idea is:

(1) Let the page that failed to read-ahead stay in the buffer pool LRU for as short a time as possible;

( 2) Let the pages that are actually read be moved to the head of the buffer pool LRU;

to ensure that the hot data that is actually read stays in the buffer pool as long as possible.

The specific method is:

(1) Divide the LRU into two parts:

New generation (new sublist)

Old generation (old sublist) )

(2) The ends of the new and old generations are connected, that is: the tail of the new generation is connected to the head of the old generation;

(3) New page (for example, by When the pre-read page is added to the buffer pool, it is only added to the head of the old generation:

If the data is actually read (the pre-read is successful), it will be added to the head of the new generation

If the data has not been read, it will be eliminated from the buffer pool earlier than the "hot data pages" in the new generation

The improved version of LRU in the new and old generations still cannot solve the problem of buffer pool pollution.

2.Log Buffer

Log Buffer is used to cache redo logs.

InnoDB has two very important logs: undo log and redo log

(1) Through undo log, you can see earlier versions of data, implement MVCC, or rollback transactions and other functions.

(2) Use redo log to ensure transaction durability.

#The redo log buffer is a memory storage area used to save data to be written to the log file on disk. The log buffer size is defined by the innodb_log_buffer_size variable, and the default size is 16MB.

The contents of the log buffer are periodically flushed to disk. A larger log buffer allows large transactions to be run without redo log data being written to disk before the transaction commits. Therefore, if there are transactions that update, insert, or delete many rows, increasing the log buffer size can save disk I/O.

innodb_flush_log_at_trx_commit: Controls how the contents of the log buffer are written and flushed to disk.

innodb_flush_log_at_timeout: Control the log refresh frequency.

You need to observe transactions if disk I/O is causing performance issues, such as transactions involving many BLOB entries. The InnoDB log buffer is flushed to disk whenever it is full, so increasing the buffer size can reduce I/O.

The default number of log files is two: ib_logfile0 and ib_logfile1.

The log has a fixed size, and the default size depends on the MySQL version.

3.Adaptive Hash Index

Adaptive Hash IndexThe adaptive hash index is a key-value pair storage structure that stores the records where the hot pages are located. The InnoDB storage engine automatically creates hash indexes for certain pages based on the frequency and pattern of access.

#The above picture is the difference between B-tree index and adaptive hash index. Disable or enable this feature through the parameter innodb_adaptive_hash_index, which is enabled by default.

4.Change Buffer

Change Buffer: Data in MySQL is divided into two parts: memory and disk; cache hot data pages and index pages in the buffer pool to reduce disk reads; through change Buffer is a means to ease disk writing.

When a data page needs to be updated, update it directly if the data page is in memory. If the data page is not in memory. Without affecting data consistency, InooDB will cache these update operations in the change buffer, so that there is no need to read this data page from disk. When the next query needs to access this data page, read the data page into memory, and then perform operations related to this page in the change buffer. In this way, the correctness of the data logic can be ensured.

Although the name is called change buffer, it is actually data that can be persisted. In other words, the change buffer has a copy in memory and will also be written to disk (ibdata).

The process of merging the operations in the change buffer to the original data page and obtaining the latest results is called merge . The following situations will trigger merge:

• Access this data page;

• The background master thread will merge regularly;

• When the database buffer pool is not enough;

• When the database is shut down normally;

• When the redo log is full;

The change buffer means that when a non-unique ordinary index page is not in the buffer pool and a write operation is performed on the page, the record change buffer will be buffered first, and then the change buffer will be changed when the future data is read. The technology in the operation merge to the original data page. Before MySQL 5.5, it was called insert buffer, and it was only optimized for insert. Now it is also valid for delete and update, and it is called change buffer.

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL storage engine detailed explanation of InnoDB architecture. For more information, please follow other related articles on the PHP Chinese website!