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

This article introduces the relevant knowledge of "what are the three brothers who escort you when writing operations in MySQL?". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

-mind map-

Two-phase submission

You should have heard this term many times. I'd like to introduce this old friend here.

The so-called two-phase commit, literally, should have two steps to constrain. As a matter of fact, it is. The protagonists in these two steps are two of the important characters we are going to talk about today: binlog and redo log.

When it comes to two-phase commit, the execution process of the SQL statement cannot be bypassed. There is nothing I can do. Although I have mentioned it many times, I still have to pull it out again. It's just that this time the focus will be a little different from the previous one.

When it comes to the operation flow, it goes like this:

When the SQL of a write operation is performed, the engine updates the row of data to memory and records the corresponding operation to the redo log, and then in the prepare state. And inform the actuator of the completion information.

The executor generates the binlog for the corresponding operation and writes the binlog to disk. Then call the engine's commit transaction interface and change the redo log state to commit so that the operation is complete.

Well, now that we know the two-phase commit, let's take a look at the true face of these log files.

Redo log (redo log)

The first to come out is redo log, which is located in the storage engine layer, which is a physical log file used to record what changes have been made on the data page.

WAL technology

When it comes to redo log,WAL technology, it is inevitable to get around it. Its full name is Write-Ahead Logging. That is, the log is written before synchronizing the disk, and then the system synchronizes the records in the log to the disk according to a certain policy.

The necessity of existence

From the above two-phase commit process, we can see the usage scenario of WAL technology. I don't know if you are wondering why you have to write redo log in the middle and synchronize the update results to disk directly. Silly boy, synchronizing to disk means that every write operation has to produce random write disk operation, how slow it is.

Witty you may say, can I synchronize from memory to disk after a certain period of time? Come on, I'll give you a head crash first. Think about it. My service has been restarted. Do you still have these data? Memory is easy to lose, and I don't know what abnormal situation will lead to data loss. So at this time, we need an intermediate file that can be persisted, which acts as a "buffer", and the writing speed is not slow.

Then redo log came into being. Although it is also stored on disk, sequential writing is not affected in terms of speed (confused students can understand the difference between random and sequential reading and writing on disk).

Of course, redo log can not only play the role of "delay" to synchronize disk files, but also can be used to recover data when the database server goes down.

Writing opportunity

When it comes to the timing of writing, is it more puzzling that you don't write to the redo log file after updating the memory? The answer is no, because there is also a redo log buffer in the middle. Each time Mysql executes a statement, the record is first written to redo log buffer, and then the subsequent commit operation is written to the redo log file (on disk) at a certain time.

It is worth noting that the data in redo log buffer is written to the redo log file when the commit operation is performed.

As for the timing of writing, it is controlled by the following parameters:

(the picture is from the Internet)

Writing mode

Now that you know the timing of writing, here is a brief introduction to the way to write. In Innodb, the size of the redo log is fixed, so it can only be written in a circular manner. If I currently have 4 files, write from the first file until the last file is full, and then go back to the beginning to synchronize the data to the file and erase it and continue to write.

The write pos in the figure represents the location of the current record, which gradually moves backward as it is written. When you write to the ib_logfile_3 file, the entire redo log is full. At this point, the update operation is blocked. The system erases some records according to the check point flag bits (provided, of course, they are synchronized to disk).

All in all, the way redo log is written is a process of constantly writing, erasing and writing after being full.

Binary log (binlog)

After talking about redo log, let's take a look at another binary log file, binlog, which is located in the service layer. The role played by this big brother is to store the logical log, which means that whatever has been modified will be recorded.

For example: update the field with id = 1.

Of course, in addition to recording the operation process, it also has the ability to support master-slave synchronization and data exception recovery.

Write mode

There are three write modes in binlog. Let's take a look at the differences and the corresponding advantages and disadvantages:

(the picture is from the Internet)

Writing mode

Unlike redo log loop writes, binlog writes are appended, and when one file is written to a certain size, it switches to another.

Association with redo log

In the above two-phase commit, we mentioned that after writing the binlog, the commit transaction interface of the engine will be called to change the redo log state to commit. So how does it find the corresponding records, or in other words, how do they relate to each other?

The answer is through a common field XID, not only in transaction commit, but also in crash recovery if you encounter a redo log that only writes prepare but not commit, you can also find the corresponding transaction through XID.

Review the writing process

At this point, it is necessary to review the operation of writing the process, taking updating a field as an example:

Roll back the log (undo log)

At this point, you may wonder, there is no shadow of undo log in the whole article, you scumbag!

Don't worry, it's coming!

Literally, you should be able to guess what it does. To roll back is to give you a chance to regret. When the data is modified, the undo log is recorded at the same time, that is, the logical log of the opposite operation is also recorded. You can understand that when operating update, write a corresponding update record, and when operating delete, write a corresponding insert record.

When the transaction is rolled back. After reading the corresponding logical record from undo log, you can roll back.

Summary

Two-phase submission

In the process of two-phase commit, the corresponding operation is recorded in redo log while the memory is updated, and the generated binlog is written to disk to commit the transaction.

Redo log

Redo log is a physical log located at the storage engine layer, which is used to record the physical log file on "what changes have been made to the data page". Write in a loop to record what the data looks like after it has been modified. It also provides the ability to recover data.

Binary log

Binlog is a logical log located at the service layer, which is used to record "what changes have been made to the data" log file. Unlike redo log, append writes can be made all the time. At the same time, it also provides master-slave synchronization and data abnormal recovery ability.

Roll back the log

When the data is modified, the undo log is recorded at the same time to ensure that the data is restored during the transaction rollback operation.

This is the end of the content of "what are the three brothers to escort when writing operations in MySQL"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.