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

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

InnoDB component structure:

Buffer pool: buffer pool, caching disk data

Redo log buffer: records the operation of the buffer pool and writes to disk according to policy to prevent data loss due to downtime but transactions have been committed

Undo log: when the data in the buffer pool is modified, it can be rolled back when the transaction is not committed. Write the old value to the undo log file to facilitate rollback. In this case, the data in the buffer pool is inconsistent with that in the disk, and it is dirty data.

1. Buffer Pool

Suppose you now have an update statement:

Update users set name = 'lisi' where id = 1

What does InnoDB do if you need to update to the database?

First of all, InnoDB will determine whether the data id = 1 exists in the buffer pool, and if it does not exist, it will be loaded into the buffer pool from disk, and an exclusive lock will be added to the row of data to prevent multiple sql from modifying it at the same time.

2. Undo log file

Suppose the id=1 data name has the original value name='zhangsan', and now we want to update it to name=' lisi', then we need to write the old values name='zhangsan' and id=1 to the undo log file.

For students who are familiar with the database, they all understand the concept of transaction. Before the transaction is committed, all operations can be rolled back, that is, name = 'lisi' can be rolled back to name =' zhangsan', so the value before the update is written to the undo log file.

3. Update buffer pool data

After the undo log file is written, the data in memory is updated. Update name = 'zhangsan' for id = 1 to name =' lisi'. At this time, the data in memory has been updated, but the data on the disk has not changed, at this time there is inconsistent dirty data.

At this point, there may be a question: if the transaction commit is completed, but the MySQL service goes down, and the data in memory has not been written to disk, will it result in data loss and inconsistent sql execution data?

4. Redo log buffer

In the InnoDB structure, there is a redo log buffer buffer to store the redo log, the so-called redo log, for example, changing id=1,name='zhangsan' to name='lisi' is a log.

But at this time, the redo log buffer only exists in memory, and the data recovery after MySQL downtime has not been realized.

5. If the transaction is not committed, will there be any impact after the database downtime?

In fact, there is no impact. The transaction is not committed, which means that the execution is not successful. Even if the MySQL crashes or goes down, the modified data of buffer pool and redo log buffer in memory will be lost, and the consistency of the data will not be affected. If the transaction commit fails, the data in the database will not change.

6. Commit transaction, configuration strategy of redo log

When a transaction is committed, the redo journal writes the redo log from redo log buffer to disk according to the policy implementation. Policies are configured through innoDB_flush_log_at_trx_commit.

The parameter innoDB_flush_log_at_trx_commit is 0, and even after the transaction is committed, the redo log is not written to disk. When MySQL goes down, the data in memory will be lost.

The parameter of innoDB_flush_log_at_trx_commit is 1. After the transaction is committed, the redo log will be brushed to the disk from memory. As long as the transaction is successfully committed, the redo log must be stored on the disk.

At this point, even if the data of buffer pool is not brushed into the disk, you can know what data has been modified from redo log. After MySQL is down and restarted, you can recover the modified data from the redo log.

The parameter of innoDB_flush_log_at_trx_commit is 2. After the transaction is committed, the redo log only stays in the os cache and has not been brushed into the disk, in case the service goes down. Then the data in the os cache will also be lost, even if the transaction commits successfully, resulting in data loss.

After reading these kinds, I believe that in order to ensure data security, a parameter of 1 is the best strategy.

7. The final commit of the transaction, binlog

Binlog is actually a log file of MySQL Server, and it is proposed here because it has a lot to do with redo log.

1) the difference between biglog and redo log

Redo log: records partial physical redo logs, such as "what changes have been made to what records in which data page"

Binlog: prefer logical logs, such as: "update a row of id=10 data in the users table, what is the value after the update"

2) write binlog when committing the transaction

While performing the update, innoDB has been interacting with the executor, including loading data into the buffer pool, writing undo log files, updating memory data, writing redo logs, and flushing to disk. Writing to the binlog is also performed by the executor.

Steps 1, 2, 3, and 4 do what they do to execute the update statement, while steps 5 and 6 start with the commit transaction.

3) Analysis of binlog log brushing strategy

The Brush Strategy of binlog controlled by sync_binlog parameters

The default value of sync_ binlog is 0. After the transaction is committed, the binlog log will be stored in os cache, and the data in os cache will be lost after MySQL downtime.

The sync_binlog value is 1, and after committing the transaction, the binlog log is brushed directly to disk.

4) complete transaction commit based on binlog and redo log

After binlog writes to disk, the location and file name of the binlog log file are written to the redo log log file, and a commit tag is written in the redo log log file.

5) what is the significance of commit markers?

The commit flag means to keep the redo log and binlog logs consistent. If the transaction commits at step 5 or 6 and the MySQL goes down and there is no commit flag in the redo log, the transaction commit fails.

It means that the commint flag indicates that the transaction was finally committed successfully.

8. Buffer pool dirty data is brushed to disk

Dirty data is brushed into the disk randomly by the background IO thread.

At this point, what if the MySQL goes down before flushing into the disk? At this time, the transaction has been successfully committed and there is a commit flag in redo log. Even if it is down, after restarting, the data will be updated to memory according to the redo log file, waiting for the IO thread to flush.

The above is all the content of the article "sample Analysis of InnoDB Storage engine Architecture 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.