Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of InnoDB storage engine in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Storage engine

The execution plan of SQL is accomplished by the executor component calling the interface of the storage engine.

Then we can understand that: MySQL this database management system relies on the storage engine to interact with the disk files that store the data.

So what storage engines does MySQL have?

There are mainly MyISAM, InnoDB, Memory and so on. Now in the Internet, basically use the InnoDB storage engine, so next I will briefly summarize my learning about the InnoDB storage engine, and briefly introduce the components of the InnoDB storage engine.

Second, buffer pool

As we all know now, the data in the database is stored in a disk file.

So, every time we add, delete, change and check the table, do we operate directly in the disk file?

Answer: no!

Because the random read and write performance of disk files is very poor, if all operations are done on disk, then there will be no high-performance MySQL, MySQL can not support high concurrency, nor will it be so popular on the Internet.

At this point, one of the most important components of the InnoDB storage engine is the buffer pool (Buffer Pool), which is a very important memory structure. It is in memory, and with very high performance of reading and writing in memory, MySQL can support high concurrency.

How the buffer pool (Buffer Pool) is used:

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

The worker thread of ① and MySQL listens to the connection of the database connection pool, and gets the SQL statement in the connection if there is a connection.

②, and then give the SQL statement to the SQL interface to be processed, and the following series of processes will be carried out in the SQL interface.

③, query parser parses SQL statements into things that MySQL can understand.

④, and then query the optimizer to develop an optimal execution plan for the SQL statement.

The ⑤ and the executor will call the interface of the storage engine according to the execution plan.

The above is the summary of the previous article, so how to add, delete, modify and check the interface of the storage engine? Take the update operation as an example, and the rest are the same.

First, the storage engine determines whether the data row corresponding to the update SQL is in the buffer pool (Buffer Pool). If it is, update the data directly in the buffer pool (Buffer Pool) and return it; if not, read the data from the disk file into the buffer pool (Buffer Pool), then update it, and finally return the result.

3. Undo log file

We all know that in a transaction, updates to the data can be rolled back at any time before the transaction is committed. So what does it depend on?

Rely on undo log files.

How undo log files are used:

Update data as an example:

If you update the data of a row of 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 to the undo log file.

When you need to roll back before the transaction is committed, you will find these two keywords in the undo log file and roll back the update operation.

IV. Redo log buffer

As mentioned above, all the additions, deletions, modifications and queries are actually carried out in the buffer pool, so the changes to the data are not immediately implemented in the disk file.

So there is a problem: what if MySQL goes down before the dirty data from the buffer pool is flushed back to the disk file?

At this point, the InnoDB storage engine provides a very important component, the redo log buffer component, which is also a buffer in memory.

How redo log buffer is used:

Take the update operation above as an example. When the data is updated, the key information of the data update will be recorded, corresponding to the redo log, and then written into the redo log buffer.

But there is still a problem. As mentioned above, redo log buffer is also in memory. When MySQL goes down, because all the data in memory will be lost, the dirty data of the buffer pool and the log of redo log buffer will still be lost.

This creates a situation in which the client receives the message that the update was successful, but in the end the data in the database is not updated successfully.

So, redo log buffer also has a flushing strategy. Normally, when a transaction commits, the redo log in redo log buffer is brushed back to disk, so you don't have to worry about the problem that the transaction commits successfully, but the updated data may be lost. Even if the MySQL goes down before the dirty data from the buffer pool (Buffer Pool) is flushed back to disk, the data will not be lost, because all previous updates to the dirty data can be restored according to the redo log on disk when MySQL restarts.

The above is all the content of the article "sample Analysis of InnoDB Storage engine in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.