Summary of MySQL InnoDB architecture

introduction

As a back-end programmer, we have to deal with databases almost every day. There are many databases on the market, such as: Mysql, Oracle, SqlServer, etc. So how do our programs connect to the database? That is the database driver. Different databases correspond to different database drivers. When we connect to the database, we first register the database driver, and then establish a connection with the database based on the database address, user name, password and other information. If you use Maven to manage your project, you will generally see the following configuration:

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.24</version>
</dependency>

As above, import the MySQL driver jar package through Maven, and then you can operate the database through SQL statements in the project. So how does the database execute after receiving the request? Next I will explain in detail through the MySQL database.

1. Overall architecture of MySQL database

Generally, we know that after the web project is developed, the project files can be packaged into a war package, and then published through the Tomcat container, and finally users can access our system. We all know that Tomcat supports concurrent access. When multiple requests need to operate the database at the same time, do multiple requests have to grab a database connection? That is definitely not the case, otherwise the efficiency would be very low. Would it be necessary to establish a connection for each request and destroy the connection after the request is completed? That is definitely not the case. Frequent establishment and destruction of connections will definitely affect performance. So how does Tomcat solve this problem? Remember the "pooling" idea we mentioned when talking about thread pools? Yes, there is a database connection pool in Tomcat, so there is also a corresponding database connection pool in the same database server. The general structure is shown in the figure below

SQL Interface

When the request reaches the database, it will be discovered by the listening thread, and then the request will be transferred to the SQL interface for processing. The SQL interface is specifically used to execute SQL statements such as add, delete, modify and query.

Parser

Although SQL statements are relatively easy for us to understand, they cannot be directly understood by the MySQL system, so the SQL interface will transfer the SQL statements to the parser. The query parser is responsible for parsing the SQL statements, that is, parsing the SQL statements according to the established SQL syntax and understanding the operations to be completed by the SQL.

Optimizer

After the parser understands the operations that the SQL statement needs to complete, it then uses the optimizer to select a path that it considers to be the best. Generally speaking, there is more than one path to achieve a certain result. For example, to query the values ​​of two fields f1 and f2 in table T that meet condition C, there are at least two possible paths:

1. First, filter out all data rows that meet condition C in table T, and then select the values ​​of fields f1 and f2 as the result set;
2. First select all the values ​​of f1 and f2, and then filter out the data rows that meet the condition according to condition C to form a result set.
   

The optimizer will obtain the query path it considers to be optimal based on different strategies.

Actuator

When the optimizer selects the optimal query path, it cannot get the result we ultimately want, so we still need to use an executor. The role of the executor is to generate an execution plan based on the optimal query path selected by the optimizer, and then continuously call the interface provided by the database storage engine to complete the execution plan of the SQL statement.

Storage Engine

Databases generally store data in two places: memory or disk. So if we query data, does the executor need to query the disk or the memory? How is memory queried? How is the disk searched? The memory capacity is limited. What should we do when there is no extra space in the memory? The solution to a series of problems is the storage engine. MySQL provides multiple storage engines: InnoDB, MyISAM, MEMORY, etc. The more common ones are InnoDB and MyISAM. You can view the storage engine of the current MySQL database through the show engines command. This series will mainly analyze the InnoDB storage engine.

In summary, a complete set of SQL statement execution flow is shown in the following figure

2. InnoDB storage engine architecture

If a SQL statement now goes through the above process and reaches the interface where the executor calls the InnoDB storage engine, how does the InnoDB storage engine work?

Memory buffer pool

First, let’s introduce the first important component of the InnoDB storage engine—the memory buffer pool, or Buffer Pool. This is an area in the memory that stores a large amount of data to facilitate operations such as query and update. The purpose of doing this is to improve the execution efficiency of SQL statements, so we must make it clear that our query, update and other operations are all completed in the Buffer Pool (regardless of whether the data exists in the Buffer Pool, if it exists, operate directly, if not, load it from the disk to the Buffer Pool before operating).

Undo log log file

Students who are familiar with databases know that when we update data, we usually do it in a transaction. Transactions have four major characteristics: ACID, where A stands for atomicity, that is, the operations are either completely successful or completely failed. If successful, the transaction is committed, and if failed, it is rolled back. The rollback is achieved through undo log. (I was asked about this once, but I was so nervous that I didn’t remember it. It took me a while to realize it...).

Generally, MySQL database will enable automatic transaction commit by default, so we don't need to do any additional operations. We can turn off automatic transaction commit by set autocommit = 0 and turn on automatic transaction commit by set autocommit. If you are interested, you can try it to feel it.

redolog log file

As we have introduced before, the update operation is completed in the Buffer Pool, that is, in the memory. If MySQL crashes after the operation, the modified data in the memory will inevitably be lost. In order to solve this problem, the redo log is designed in the InnoDB architecture to record what data you have modified. If MySQL crashes, you can use the redo log to recover data after restarting. However, the redo log is first written to the redo log buffer in the memory and is not persisted to the disk, so the risk of data loss still exists. Therefore, InnoDB provides several redo log flushing strategies. You can set the flushing strategy through innodb_flush_log_at_trx_commit. For example, innodb_flush_log_at_trx_commit=1 means that the transaction commit log is flushed to disk immediately. In this way, there is no risk of data loss, but performance will definitely be affected. Generally, you can set policies based on business needs.

binlog log file

Binlog is also called archive log. Different from redo log, it is for MySQL server, not specific to InnoDB. Generally, users restore data at a certain point in time, master-slave synchronization, etc., while redo log users recover from failures. Generally, archive logs are also submitted when a transaction is submitted. There are also several disk flushing strategies for archive logs. Sync_binlog is used to control the disk flushing after several transaction commits. The special sync_binlog=0 means that the flushing timing is controlled by the operating system, not MySQL.

InnoDB Execution Process

After introducing several components of the InnoDB storage engine, suppose you need to update a piece of data. What should the execution process in InnoDB be like? as follows:

1. If the data does not exist in the Buffer Pool, random I/O reads the data from the disk and puts it into the Buffer Pool;
2. Write undo log to roll back data;
3. Update the data in the Buffer Pool;
4. Write redo log to redo log buffer for failure recovery data;
5. Prepare to commit the transaction, and flush the redo log to disk based on the policy;
6. Prepare to commit the transaction, and flush the binlog log to disk based on the policy;
7. Write the binlog file and commit mark to the redo log file;
8. Commit the transaction;
9. The background IO thread inputs the dirty data in the Buffer Pool to the disk. (Because only the logs in the Buffer Pool were modified in the early stage, and the data in the disk was not modified, the data in the Buffer Pool is dirty data for the disk data)
   

The process is shown in the figure below:

The above is the detailed summary of MySQL InnoDB architecture. For more information about MySQL InnoDB architecture, please pay attention to other related articles on 123WORDPRESS.COM!