The execution of an update SQL statement

Ask questions

UPDATE student SET score = score + 1 WHERE uid = 666; This is a simple SQL update statement. To understand how the above SQL statement is executed, you need to understand the logical architecture of the MySQL database.

In the UPDATE statement above, select all rows where uid=666 and add the score field value to memory by 1. The following process is different from the query process of the query statement. The query statement only needs to return the query result, but the update statement needs to actually modify the data in the database, so the update statement is relatively more complicated.

When it comes to SQL updates, redo logs and archive logs play a significant role in MySQL, and the interaction between these two logs is also interesting. I’ll take a closer look at what these two types of logs do, how they work, and how they work together.

redo log

The redo log is a physical log that is used to store data in a database. The redo log is a physical log that is used to store data in a database. The crash-safe problem is very important for a database. In this way, data will not be lost after an abnormal crash.

The redo log is an InnoDB engine layer log that records what was changed on the page. WAL is often described in MySQL. WAL stands for Write Ahead Logging. WAL is Logging first. For example, InnoDB writes records to the redo log and then updates them to memory. Wait until the system is free to write to the disk. The redo log file size is fixed and is written in a loop.

The redo log ensures that InnoDB will not lose data in the event of an abnormal restart. This capability is also called crash-safe capability

binlog

The binlog is a logical log that records all SQL statements in the Server layer. It is used to restore the database as long as we have the last backup and the complete binlog during this period. Let’s take a quick look at the binlog file. I’m ubuntu, and this file is in /var/log/mysql.

As you can see from the above image, file names are incremented. Unlike redo logs, binlogs are appended. To see what a binlog SQL statement looks like, run the following command line, and refer to the official documentation for the contents of the binlog file.

Sudo mysqlbinlog /var/log/mysql/mysql-bin.000002 --base64-output=DECODE -- ROWS --verbose --verboseCopy the code

The result is as follows:

It is clear from the figure above what this update statement means.

So much has been said about the meanings of the two types of logs. The following is a brief summary of the differences between the two types of logs:

  • The redo log is a physical log that records changes made to the page, and the binlog is a logical log that records the original logic of the SQL statement.
  • The redo log is a fixed file size and is written in a loop, overwriting previous logs. Binlog appends and does not overwrite the previous log.
  • Redo log is InnoDB engine layer log, binlog is server layer log.

Some students will ask, why do we have two journals?

We know that the default MySQL engine is MyASIM, there is no crash-safe problem, binlog is only used for archiving. InnoDB was used as the default storage engine after MySQL5.5.5, so InnoDB has crash-safe capability. In the MySQL architecture, the engine exists in the form of plug-ins, InnoDB engine is not required for MySQL database. So it’s easy to understand that redo logs are not mandatory for MySQL databases.

It is easy to understand why there are two logs, one server layer and one engine layer, which are responsible for different functions and cooperate with each other.

So how exactly do these two logs work together? How do they keep the data consistent?

Two-phase commit

First, the specific process of the next two stages of submission:

  • The result of the UPDATE statement is written to memory and the redo log is written to the redo log, which is prepared and tells the executor that it is ready to commit at any time.
  • The executor generates a binlog of this operation and writes to the binlog.
  • The executor notifies you to change the prepare state to commit state, and the update is complete.

Two-phase commit ensures redo log and binlog consistency. Now let’s analyze what happens if it’s not a two-stage process?

Write redo log and then binlog

If redo log is written before binlog, no log is recorded in the binlog when a crash occurs. During a restart, the redo log restores the crash statement, but if the redo log is used to restore the database, the log is lost, and the data restored by the two logs is different.

Write binlog and redo log

If you write binlog first and then redo log, a crash occurs when writing binlog. The redo log file is invalid and cannot be restored. The redo log file is invalid and cannot be restored. When the redo log file is used to restore the database, one record is added to the database.

As you can see from the above, it is possible to have two log states that are inconsistent without a two-phase commit.