How to execute an SQL update statement?

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.

1. Introduction

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. ?

2. Statement analysis

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.

Important log module: redo log

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:

• One method is to directly turn out the account book and add the credit account or Deduct it;
• Another way is to write down the account on the pink board first, and then take out the account book after closing time.

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.

Important log module: binlog

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.

1. redo log is unique to the InnoDB engine; binlog is implemented by the Server layer of MySQL and can be used by all engines.
2. redo log is a physical log, which records "what modifications were made on a certain data page"; binlog is a logical log, which records the original logic of this statement, such as "give the line ID=2" Add 1 to the c field.
3. The redo log is written in a loop, and the space will always be used up; the binlog can be written additionally. "Append writing" means that after the binlog file reaches a certain size, it will switch to the next one and will not overwrite the previous log.

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.

1. The executor first looks for the engine to get the line ID=2. ID is the primary key, and the engine directly uses tree search to find this row. If the data page where the ID=2 row is located is already in the memory, it will be returned directly to the executor; otherwise, it needs to be read into the memory from the disk and then returned.
2. The executor gets the row data given by the engine, adds 1 to this value, for example, it used to be N, but now it is N 1, gets a new row of data, and then calls the engine interface to write this new row of data.
3. The engine updates this new row of data into the memory and records the update operation into the redo log. At this time, the redo log is in the prepare state. Then inform the executor that the execution is completed and the transaction can be submitted at any time.
4. The executor generates the binlog of this operation and writes the binlog to disk.
5. The executor calls the engine's commit transaction interface, and the engine changes the redo log just written to the commit state, and the update is completed.

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".

Two-phase submission

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:

• First, find the most recent full backup. If you are lucky, it may be a backup from last night, and restore it to the temporary library from this backup;
• Then, starting from the backup time point, restore The backup binlogs are taken out one by one and played back to the time before the table was accidentally deleted at noon.

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?

1. Write redo log first and then binlog. Suppose that the MySQL process restarts abnormally when the redo log is written but before the binlog is written. As we said before, after the redo log is written, even if the system crashes, the data can still be recovered, so the value of c in this line after recovery is 1. However, since the binlog crashed before it was finished, this statement was not recorded in the binlog at this time. Therefore, when the log is backed up later, this statement will not be included in the saved binlog. Then you will find that if you need to use this binlog to restore the temporary library, because the binlog of this statement is lost, the temporary library will not be updated this time. The value of c in the restored row is 0, which is the same as the value of the original library. different.
2. Write binlog first and then redo log. If a crash occurs after the binlog is written, since the redo log has not yet been written, the transaction will be invalid after the crash recovery, so the value of c in this line is 0. But the log "Change c from 0 to 1" has been recorded in the binlog. Therefore, when binlog is used to restore later, one more transaction will come out. The value of c in the restored row is 1, which is different from the value in the original database.

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!