Completely master the MySQL principles and InnoDB storage engine architecture design

This article brings you relevant knowledge about the InnoDB storage engine architecture design in the mysql principle. I hope it will be helpful to you.

InnoDB component structure:

1. buffer pool: buffer pool, cache disk data

2. redo log buffer: Record the operations on the buffer pool, and write to the disk according to the policy to prevent data loss due to downtime but the transaction has been submitted

3. undo log: When the data in the buffer pool is processed When making modifications, you can roll back the transaction before it is committed. The old values ​​are written to the undo log file to facilitate rollback. At this time, the data in the buffer pool is inconsistent with that in the disk and is dirty data

1. Buffer Pool

Suppose there is an update statement now:

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

Needs to be updated to the database, what operations will InnoDB perform?

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

2. undo log file

Assume that id = 1 and the original value of this data name is name = 'zhangsan', now We want to update to name = 'lisi', then we need to write the old value name='zhangsan' and id=1 to the undo log file.

For students who are familiar with databases, they all understand the concept of transactions. Before the transaction is submitted, all operations may 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, start updating this entry in the memory data. Update name = 'zhangsan' with id = 1 to name = 'lisi'. At this time, the data in the memory has been updated, but the data on the disk has not changed. At this time, inconsistent dirty data appears.

You may have a question at this time. If the transaction is submitted, but the MySQL service is down, and the data in the memory has not been written to the disk, will it happen? Causes data loss and causes inconsistency in SQL execution data?

4. redo log buffer

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

#But at this time, the redo log buffer only exists in the memory, and data recovery after MySQL is down cannot be realized.

5. If the transaction is not submitted, will there be any impact if the database goes down?

In fact, it has no impact. The transaction was not submitted, which means that the execution was not successful. Even after MySQL crashes or goes down, the modified data in the buffer pool and redo log buffer in the memory will be lost, and there will be no impact. Does not affect the consistency of data. If the transaction commit fails, the data in the database will not change.

6. Submit the transaction, redo log configuration strategy

When submitting the transaction, the redo log will implement redo according to the strategy Logs are written to disk from the redo log buffer. The policy is configured via innoDB_flush_log_at_trx_commit.

1. The parameter of innoDB_flush_log_at_trx_commit is 0. Even after the transaction is committed, the redo log will not be written to the disk. After MySQL crashes, the data in the memory will be lost.

1. ## The parameter of innoDB_flush_log_at_trx_commit is 1. After the transaction is submitted, the redo log will be flushed from the memory to the disk, as long as the transaction is submitted successfully. , the redo log must exist on the disk.

Even if the buffer pool data is not flushed to the disk at this time, you can still know which data has been modified from the redo log, and MySQL shuts down and restarts Afterwards, the modified data can be recovered from the redo log.

1. The parameter of innoDB_flush_log_at_trx_commit is 2. After the transaction is submitted, the redo log only stays in the os cache and has not been flushed to the disk, in case the service goes down at this time. Then the data in the os cache will also be lost. Even if the transaction is submitted successfully, data will be lost.

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

7. The final submission of the transaction, binlog

binlog is actually a log file belonging to MySQL Server, and it is proposed here because it has a huge relationship with redo log association.

1) The difference between biglog and redo log

• redo log: records redo logs with partial physical properties, such as "which data page What records have been recorded and what modifications have been made?”

• binlog: biased toward logical logs, such as: “Do a row of data with id=10 in the users table After the update operation, what is the value after the update?"

2) When submitting the transaction, write the binlog at the same time

While executing the update, innoDB and The executor is constantly interacting, including loading data into the buffer pool, writing undo log files, updating memory data, writing redo logs, and flushing to disk. The writing to binlog is also performed by the executor.

Steps 1, 2, 3, and 4 are what you do when you execute the update statement, and steps 5 and 6 are what you do when you commit the transaction.

3) Binlog log flushing strategy analysis

sync_binlog parameter controls binlog flushing strategy

1. The default value of sync_binlog is 0, after the transaction is submitted , the binlog log will be stored in the os cache. After MySQL is down, the data in the os cache will be lost.

2. The sync_binlog value is 1. After the transaction is submitted, the binlog log will be flushed directly to the disk. middle.

4) Complete transaction submission based on binlog and redo log

After the binlog is written to the disk, the location and file name of the binlog log file will be written to redo log file, and write a commit mark in the redo log file.

5) What is the significance of commit tag?

The commit mark means keeping the redo log and binlog log consistent. If transaction submission starts in step 5 or step 6, MySQL is down, and there is no commit mark in the redo log, the transaction submission is considered failed.

means that the commit mark is that the transaction was finally submitted successfully.

8. Buffer pool Dirty data is flushed to the disk

Dirty data is flushed to the disk randomly by the background IO thread.

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

9. Summary

After executing the analysis through the update statement, we learned that the InnoDB storage engine contains buffer pool buffer pool, redo log buffer buffer and other cache data. Log files such as undo and reod log, as well as MySQL Server log files.

When executing the update statement, the buffer pool, writing undo log file, writing redo log buffer and other operations will be modified; when the transaction is submitted, the redo log will be flushed, the binlog will be flushed, and the binlog file name will be written. and position, write the commit mark, and finally wait for the IO thread to randomly flush the dirty data in the buffer pool.

Recommended learning: mysql video tutorial

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