MySQL learning summary: a preliminary understanding of the architectural design of the InnoDB storage engine

1. Storage Engine

In the last section, we mentioned that the SQL execution plan is completed by the executor component calling the storage engine interface.
Then we can understand that: the MySQL database management system relies on the storage engine to interact with the disk files that store data.

So what storage engines does MySQL have?

The main ones are MyISAM, InnoDB, Memory and so on. Now, the InnoDB storage engine is basically used on the Internet, so next I will briefly summarize my learning about the InnoDB storage engine and briefly introduce the components in the InnoDB storage engine.

Buffer Pool

We all know now that database data is stored in disk files.
So, do we add, delete, modify, and query the table directly in the disk file every time?

Answer: No!

Because the random reading and writing performance of disk files is very poor, if all operations are performed on the disk, there will be no such thing as high-performance MySQL, MySQL will not be able to support high concurrency, and it will not be so popular on the Internet.

At this time, the most important component of the InnoDB storage engine is緩沖池（Buffer Pool） , which is a very important memory structure. It is in the memory, and thanks to the extremely high performance of memory reading and writing, MySQL can support high concurrency.

The usage principle of the buffer pool:

Let's first review the process of MySQL receiving requests.

①. The MySQL worker thread specifically monitors the connection of the database connection pool, and obtains the SQL statement in the connection if there is a connection.
② Then the SQL statement is handed over to SQL 接口for processing, and the following series of processes will be carried out in the SQL interface.
③.查詢解析器parses the SQL statement into something that MySQL can understand.
④.查詢優化器then develops an optimal execution plan for the SQL statement.
⑤.執行器will call the storage engine interface according to the execution plan.

The above is a summary of the previous article. So how does the storage engine interface perform additions, deletions, modifications, and queries? Take the update operation as an example, and the others are similar.
First, the storage engine will determine whether the data row corresponding to the update SQL is in緩沖池（Buffer Pool） . If it exists, the data is updated directly in緩沖池（Buffer Pool） and then returned; if it does not exist, the data is read from the disk file into緩沖池（Buffer Pool） , and then the update operation is performed, and finally the result is returned.

3. Undo log files

We all know that in a transaction, data updates can be rolled back at any time before the transaction is committed. So what do you rely on to do this?

Relying on undo 日志文件.

The usage principle of undo log file:

Take updating data as an example:
If you update a row of data with id=100 and change the field name from "Zhang San" to "Li Si", then the two key information "id=10" and "name=Zhang San" will be written into undo 日志文件.
When you need to roll back a transaction before committing it, you will find these two keywords in undo 日志文件and then roll back the update operation.

4. redo log buffer

As mentioned above, all add, delete, modify and query operations are actually performed in the buffer pool, so the changes to the data are not immediately implemented in the disk files.

So here comes a question: What if MySQL crashes before the dirty data in the buffer pool is flushed back to the disk file?
At this time, the InnoDB storage engine provides a very important component, the redo log buffer component. It is also a buffer in the memory.

The usage principle of redo log buffer:

Taking the update operation above as an example, when the data is updated, the key information of the data update will be recorded, which corresponds to the redo log and then written to redo log buffer .

But there is still a problem. As mentioned above, redo log buffer is also in memory. When MySQL crashes, all data in the memory will be lost, so the dirty data in the buffer pool and the logs in redo log buffer will also be lost.
This will result in a situation where the client receives the update success message, but the data in the database is still not updated successfully.

Therefore, redo log buffer also has a disk flushing strategy. Normally, when a transaction is committed, redo 日志in redo log buffer are flushed back to the disk, so there is no need to worry about the problem that the transaction is successfully committed but the updated data may be lost. Even if MySQL crashes before the dirty data in緩沖池（Buffer Pool） is flushed back to disk, the data will not be lost because when MySQL restarts, it can restore all previous updates of dirty data based on redo 日志on disk.

Summarize

The above is the summary of MySQL learning that the editor introduced to you, a preliminary understanding of the architectural design of the InnoDB storage engine. I hope it will be helpful to you!