This article will introduce you to the process of executing a SQL update statement. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.
Previously we systematically understood the execution process of a query statement and introduced the processing modules involved in the execution process. I believe you still remember that the execution process of a query statement generally goes through functional modules such as connectors, analyzers, optimizers, and executors, and finally reaches the storage engine.
So, what is the execution flow of an update statement?
Before, you may have often heard from DBA colleagues that MySQL can be restored to its state at any second within half a month. While you are amazed, you may also be curious about how this is done. ?
Let’s start with an update statement of a table. The following is the creation statement of this table. This table has a primary key ID and an integer field c:
mysql> create table T(ID int primary key, c int);
If you want to add 1 to the value of the row ID=2, the SQL statement will be written like this:
mysql> update T set c=c+1 where ID=2;
I have introduced you to the basic execution link of the SQL statement before. Here I will Bring that picture over again. You can also briefly look at this picture to review it. First of all, it can be said with certainty that the same set of processes for query statements and update statements will also be followed.
#You must connect to the database before executing the statement. This is the job of the connector.
We said before that when there is an update on a table, the query cache related to this table will become invalid, so this statement will clear all cached results on table T. This is why we generally do not recommend using query caching.
Next, the analyzer will know that this is an update statement through lexical and syntactic analysis. The optimizer decides to use the index ID. The executor is then responsible for actually executing, finding this row, and updating it.
Unlike the query process, the update process also involves two important log modules, which are the protagonists we are going to discuss today: redo log (redo log) and binlog (archive log). If you come into contact with MySQL, you will definitely not be able to avoid these two words, and I will continue to emphasize them to you in the following content. But having said that, there are many interesting aspects in the design of redo log and binlog, and these design ideas can also be used in your own programs.
I don’t know if you still remember the article "Kong Yiji". The hotel manager has a pink board specially used to record the credit records of guests. If there are not many people who pay on credit, then he can write the customer's name and account on the board. But if there are too many people with credit accounts, there will always be times when the fan board can't keep track of them. At this time, the shopkeeper must have a ledger specifically for recording credit accounts.
If someone wants to make a credit or repay a debt, the shopkeeper generally has two methods:
When the business is booming and the counter is very busy, the shopkeeper will definitely choose the latter, because the former is too troublesome to operate. First, you have to find the record of this person's total credit account. Think about it, there are dozens of densely packed pages. To find the name, the shopkeeper may have to put on reading glasses and search slowly. After finding it, he will take out the abacus to calculate, and finally write the result back into the ledger.
This whole process is troublesome to think about. In contrast, it is easier to write it down on the pink board first. Think about it, if the shopkeeper doesn't have the help of the pink board, he has to turn over the ledger every time he records accounts. Isn't the efficiency unbearably low?
Similarly, this problem also exists in MySQL. If every update operation needs to be written to the disk, and then the disk must find the corresponding record and then update it, the IO cost and search cost of the entire process will be high. Very high. In order to solve this problem, the designers of MySQL used an idea similar to the hotel shopkeeper's pink board to improve update efficiency.
The whole process of the cooperation between the pink board and the ledger is actually the WAL technology often mentioned in MySQL. The full name of WAL is Write-Ahead Logging. Its key point is to write the log first and then write to the disk. Just write the pink board first, and then write the account book when you are not busy.
Specifically, when a record needs to be updated, the InnoDB engine will first write the record to the redo log (pink board) and update the memory. At this time, the update is completed. At the same time, the InnoDB engine will update the operation record to the disk at the appropriate time, and this update is often done when the system is relatively idle, just like what the shopkeeper does after closing.
If there are not many credit accounts today, the shopkeeper can wait until after closing to sort out. But what should we do if there are a lot of credit accounts on a certain day and the pink board is full? At this time, the shopkeeper had to put down his work, update some of the credit records on the pink board to the ledger, and then erase these records from the pink board to make room for new accounts.
Similar to this, InnoDB's redo log has a fixed size. For example, it can be configured as a set of 4 files, each file is 1GB in size. Then this "pink board" can record a total of 4GB operations. Start writing from the beginning, and then return to the beginning to write in a loop, as shown in the picture below.
write pos is the position of the current record. It moves backward (clockwise) while writing. After writing to the end of file No. 3, it returns to the beginning of file No. 0. The checkpoint is the current position to be erased, and it also moves forward and loops. Before erasing the record, the record must be updated to the data file.
The space between write pos and checkpoint is the empty part of the "pink board" that can be used to record new operations. If the write pos catches up with the checkpoint, it means that the "pink board" is full. At this time, no new updates can be performed. You have to stop and erase some records first to advance the checkpoint.
With redo log, InnoDB can ensure that even if the database restarts abnormally, previously submitted records will not be lost. This capability is called crash-safe.
To understand the concept of crash-safe, you can think of our previous credit record example. As long as the credit record is recorded on the pink board or written on the ledger, even if the shopkeeper forgets it later, such as suddenly suspending business for a few days, he can still clarify the credit account through the data in the ledger and pink board after resuming business.
As we said before, MySQL as a whole actually has two parts: one is the Server layer, which mainly does things at the functional level of MySQL; There is also an engine layer, which is responsible for specific matters related to storage. The pink board redo log we talked about above is a log unique to the InnoDB engine, and the Server layer also has its own log, called binlog (archived log).
I think you will ask, why are there two logs?
Because there was no InnoDB engine in MySQL at the beginning. MySQL's own engine is MyISAM, but MyISAM does not have crash-safe capabilities, and binlog logs can only be used for archiving. InnoDB was introduced to MySQL in the form of a plug-in by another company. Since relying only on binlog does not have crash-safe capabilities, InnoDB uses another log system, that is, redo log, to achieve crash-safe capabilities.
The two logs have the following three differences.
With a conceptual understanding of these two logs, let's look at the internal processes of the executor and the InnoDB engine when executing this simple update statement.
Here I give the execution flow chart of this update statement. The light box in the figure indicates that it is executed inside InnoDB, and the dark box indicates that it is executed in the executor.
You may have noticed that the last three steps seem a bit "circular". The writing of redo log is split into two steps: prepare and commit. This is" Two-phase commit".
Why is there necessary "two-phase submission"? This is to make the logic between the two logs consistent. To explain this problem, we have to start with the question at the beginning of the article: How to restore the database to the state of any second within half a month?
As we said before, binlog will record all logical operations and adopt the form of "append writing". If your DBA promises that it can be restored within half a month, then the backup system will definitely save all the binlogs in the last half month, and the system will regularly back up the entire database. The "regular" here depends on the importance of the system, which can be once a day or once a week.
When you need to restore to a specified second, for example, at two o'clock in the afternoon one day, you find that a table was accidentally deleted at noon, and you need to retrieve the data, then you can do this:
In this way, your temporary database will be the same as the online database before the accidental deletion. Then you can take the table data out of the temporary database and restore it to the online database as needed.
Okay, now that we have finished talking about the data recovery process, let’s come back and talk about why the log needs “two-phase submission”. Here we might as well use proof by contradiction to explain.
Still using the previous update statement as an example. Assume that in the current row with ID=2, the value of field c is 0, and suppose that during the execution of the update statement, after the first log is written, a crash occurs before the second log is written. What will happen?
It can be seen that if "two-phase commit" is not used, the state of the database may be inconsistent with the state of the library restored using its log.
You may say, is this probability very low? There are no situations where the temporary library needs to be restored at any time?
Actually no, this process is not just needed to recover data after misoperation. When you need to expand the capacity, that is, when you need to build more backup databases to increase the system's read capacity, the common practice now is to use full backup and apply binlog to achieve this. This "inconsistency" will cause your There is an inconsistency between the master and slave databases online.
Simply put, both redo log and binlog can be used to represent the commit status of a transaction, and two-phase commit is to keep the two states logically consistent.
Related recommendations: "mysql tutorial"
The above is the detailed content of How to execute an SQL update statement?. For more information, please follow other related articles on the PHP Chinese website!