How SQL is executed in MySQL database

Today, let’s take a look at the execution process of an update statement with the mysql video tutorial column.

A set of execution processes for query statements and update statements will also go through the same step. Below we compare it with the previous article Let’s take a brief look at the picture:

" class="lazyload" src="https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d529960ec1dd496ca90860641bcaa093~tplv-k3u1fbpfcp-watermark.image?x-oss-process=image/resize,p_40" data-width="800" data- style="max-width:90%"/>

First of all, you need to connect to the database before executing the statement. This is the job of the connector in the first step. We also said before, When a table is updated, the query cache related to this table will become invalid, so we generally do not recommend October query cache.

Next, the analyzer will go through syntax analysis and lexical analysis. After knowing that this is an update statement, the optimizer decides which index to use, and then the executor is responsible for the specific execution. First find this line, Then do the update.

Different from the query statement update, the update process also involves two important logs. We have also introduced this in the previous article. If you are interested, you can find last week's article "MySQL's Two "A Log System", I won’t introduce it much here.

Let’s analyze the update operation process through a simple example.

We first create a table with a primary key ID and an integer field c:

mysql> create table demo T (ID int primarty ,c int);复制代码

Then add 1 to the value of the row with ID=2

mysql> update table demo set c = c + 1 where ID = 2;复制代码

Next, let’s take a look at the execution process of the update statement. The light box in the figure represents the execution in the storage engine, and the colored box represents the execution in the executor.

We can see that at the end, there are two steps when writing redolog, prepare and commit. This is what we often call "two-phase commit".

Why does the log need "two-phase submission"?

Since redo log and binlog are the logs of the storage engine and the executor respectively, they are two independent logics. If two-stage submission is not used, there will be some problems no matter which one is submitted first and which one is submitted later. Let's take a look at the example above. Assume that the current value of the row with ID=2 is 0. After the first log is written during the update process, a crash occurs while the second log is not written. What will happen? ?

• Write redolog first and then binlog. Assume that the redolog has been written but the binlog has not yet been written, and the MySQL process restarts abnormally. We know that after the redolog is written, the data can be restored even if the system crashes, so after MySQL is restarted, this row will be restored to 1. Since the binlog crashes before it is finished, there is no such statement in the binlog at this time. Therefore, when the log is backed up later, the saved binlog log does not have this statement. When we need to restore data through binlog, because binlog has lost this statement, the value of the restored row is 0, which is different from the value of the original database.
• Write binlog first and then redo log. If after writing the buglog, a crash occurs before the redo log is completed, and if the database crashes at this time, the transaction will be invalid after recovery, so the value of this line is still 0, but the update statement has been recorded in the binlog. Log, when you need to use binlog to restore data in the future, there will be an additional transaction. Execute this update statement to update the value from 0 to 1, which is different from the 0 in the original database.

We can see that if "two-phase commit" is not used, the state of the database will be inconsistent with the database restored using logs. Although the probability of using logs to recover data is relatively low, the most common use of logs is during capacity expansion, which is achieved through full backup and binlog. This may lead to inconsistencies between the online master-slave databases.

Related free learning recommendations: mysql video tutorial

The above is the detailed content of How SQL is executed in MySQL database. For more information, please follow other related articles on the PHP Chinese website!