How are SQL statements executed in MySQL?

The content of this article is about how to execute SQL statements in MySQL? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

This article will analyze the execution process of the next sql statement in MySQL, including how the sql query flows within MySQL and how the update of the sql statement is completed.

Before analyzing, I will take you to look at the infrastructure of MySQL. Knowing which components MySQL is composed of and what the functions of these components are can help us understand and solve these problems.

A MySQL infrastructure analysis

1.1 MySQL basic architecture overview

The following figure is a brief architecture diagram of MySQL. From the figure below, you can easily Clearly see how the user's SQL statement is executed inside MySQL.

Let’s briefly introduce the basic functions of some components involved in the picture below to help everyone understand this picture. The functions of these components will be introduced in detail in Section 1.2.

Connector: Identity authentication is related to permissions (when logging in to MySQL).

Query cache: When executing a query statement, the cache will be queried first (removed after MySQL 8.0 version, because this function is not very practical).

Analyzer: If the cache is not hit, the SQL statement will go through the analyzer. To put it bluntly, the analyzer must first see what your SQL statement is doing, and then check your SQL statement. Is the syntax correct?

Optimizer: Execute according to the solution that MySQL considers to be the best.

Executor: Execute the statement and return data from the storage engine.

Simply put, MySQL is mainly divided into Server layer and storage engine layer:

Server layer: mainly includes connectors and queries Cache, analyzer, optimizer, executor, etc. All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, functions, etc. There is also a general log module binglog log module.

Storage engine: Mainly responsible for data storage and reading. It adopts a replaceable plug-in architecture and supports multiple storage engines such as InnoDB, MyISAM, and Memory. Among them, the InnoDB engine has its own The log module redolog module. The most commonly used storage engine now is InnoDB, which has been used as the default storage engine since MySQL version 5.5.5.

1.2 Introduction to the basic components of the Server layer

1) Connector

The connector is mainly related to functions related to identity authentication and permissions, just like a very high level Like the doorman.

Mainly responsible for user login database, user identity authentication, including verification of account password, permissions and other operations. If the user account password has passed, the connector will query all the permissions of the user in the permissions table, and then The permission logic judgment in this connection will rely on the permission data read at this time. In other words, as long as the connection is not disconnected, even if the administrator modifies the user's permissions, the user will not be affected.

2) Query cache (removed after MySQL 8.0 version)

The query cache is mainly used to cache the SELECT statement we execute and the result set of the statement.

After the connection is established, when executing the query statement, the cache will be queried first. MySQL will first verify whether the sql has been executed and cache it in the memory in the form of Key-Value. Key is the query estimate and Value is Result set. If the cache key is hit, it will be returned directly to the client. If there is no hit, subsequent operations will be performed. After completion, the result will be cached to facilitate the next call. Of course, when the cache query is actually executed, the user's permissions will still be verified to see whether there are query conditions for the table.

It is not recommended to use cache for MySQL queries, because query cache failures may be very frequent in actual business scenarios. If you update a table, all query caches on this table will be cleared. For data that is not updated frequently, it is still possible to use caching.

So, generally we do not recommend using query cache in most cases.

The cache function was deleted after MySQL version 8.0. Officials also believed that this function had few practical application scenarios, so they simply deleted it.

3) Analyzer

If MySQL does not hit the cache, it will enter the analyzer. The analyzer is mainly used to analyze what the SQL statement is for. The analyzer will also be divided into several categories. Step:

The first step, lexical analysis, a SQL statement consists of multiple strings, first of all, extract the keywords, such as select, propose the query table, propose the field name, propose Query conditions and so on. After completing these operations, you will enter the second step.

The second step, syntax analysis, is mainly to determine whether the sql you entered is correct and conforms to the syntax of MySQL.

After completing these 2 steps, MySQL is ready to start execution, but how to execute it and how to achieve the best result? At this time, the optimizer needs to come into play.

4) Optimizer

The function of the optimizer is to execute what it considers to be the optimal execution plan (sometimes it may not be optimal. This article involves an in-depth explanation of this part of knowledge), For example, how to choose an index when there are multiple indexes, how to choose the association order when querying multiple tables, etc.

It can be said that after going through the optimizer, it can be said that the specific execution of this statement has been determined.

5) Executor

After selecting the execution plan, MySQL is ready to start execution. First, it will check whether the user has permission before execution. If there is no permission, an error will be returned. Information, if it has permission, it will call the engine's interface and return the result of the interface execution.

2 Statement Analysis

2.1 Query Statement

Having said so much, how is a sql statement executed? In fact, our sql can be divided into two types, one is query and the other is update (add, update, delete). Let’s first analyze the query statement. The statement is as follows:

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

Combined with the above description, we analyze the execution process of this statement:

First check whether the statement has permissions. If there is no permission, directly An error message is returned. If you have permission, before MySQL8.0 version, the cache will be queried first, and this SQL statement will be used as the key to query whether there is a result in the memory. If there is a direct cache, if not, proceed to the next step.

Perform lexical analysis through the analyzer and extract the key elements of the sql statement. For example, the above statement is a query select. The table name to be queried is tb_student. All columns need to be queried. The query conditions are The id of this table is '1'. Then determine whether the SQL statement has syntax errors, such as whether the keywords are correct, etc. If there is no problem, proceed to the next step.

The next step is for the optimizer to determine the execution plan. The above SQL statement can have two execution plans:

  a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。
  b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

Then the optimizer selects execution efficiency according to its own optimization algorithm The best solution (the optimizer thinks that sometimes it may not be the best). Then after confirming the execution plan, you are ready to start execution.

Perform permission verification. If there is no permission, an error message will be returned. If there is permission, the database engine interface will be called and the engine execution result will be returned.

2.2 Update statement

The above is the execution process of a query sql, then let's see how an update statement is executed? The sql statement is as follows:

update tb_student A set A.age='19' where A.name=' 张三 ';

Let’s modify Zhang San’s age. The age field will definitely not be set in the actual database, otherwise it will be beaten by the technical person in charge. In fact, each statement will basically follow the process of the previous query, but the log must be recorded when executing the update. This will introduce the log module. MySQL’s own log module binlog (archive log ), all storage engines can be used, our commonly used InnoDB engine also comes with a log module redo log (redo log), we will discuss the execution of this statement in InnoDB mode process. The process is as follows:

First query the data of Zhang San. If there is a cache, the cache will also be used.

Then get the query statement, change the age to 19, and then call the engine API interface to write this row of data. The InnoDB engine saves the data in the memory and records the redo log at the same time. At this time, the redo log enters the prepare state. Then tell the executor that the execution is completed and can be submitted at any time.

After receiving the notification, the executor records the binlog, then calls the engine interface and submits the redo log as the submission status.

update completed.

Some students here will definitely ask, why do we need to use two log modules instead of one log module?

This is because MySQL did not work with InnoDB at the beginning. Engine (InnoDB engine is inserted into MySQL in the form of a plug-in by other companies). MySQL's own engine is MyISAM, but we know that redo log is unique to the InnoDB engine and is not available in other storage engines. This results in the lack of crash-safe capabilities. (With the crash-safe capability, even if the database restarts abnormally, previously submitted records will not be lost). Binlog logs can only be used for archiving.

It’s not that you can’t use only one log module, it’s just that the InnoDB engine supports transactions through redo log. Then, some students will ask, can I use two log modules, but not so complicated? Why does redo log introduce prepare pre-commit state? Here we use proof by contradiction to explain why we do this?

First write the redo log and submit it directly, then write the binlog. Suppose that after writing the redo log, the machine hangs and the binlog log is not written. Then after the machine is restarted, the machine will The data is restored through redo log, but bingog does not record the data at this time. When the machine is backed up later, this piece of data will be lost, and the master-slave synchronization will also lose this piece of data.

Write the binlog first, and then write the redo log. Assume that after writing the binlog, the machine restarts abnormally. Since there is no redo log, the machine cannot restore this record, but the binlog has Record, then the same reason as above will cause data inconsistency.

If the redo log two-stage submission method is used, it will be different. After writing the binglog, and then submitting the redo log will prevent the above problems and ensure the consistency of the data. So the question is, is there an extreme situation? Assume that the redo log is in the pre-commit state and the binglog has been written. What will happen if an abnormal restart occurs at this time?
This depends on the processing mechanism of MySQL. The processing process of MySQL is as follows:

Judge whether the redo log is complete. If it is judged to be complete, submit it immediately.

If the redo log is only pre-submitted but not in commit status, it will be judged at this time whether the binlog is complete. If it is complete, the redo log will be submitted. If it is incomplete, the transaction will be rolled back.

This solves the problem of data consistency.

Three Summary

MySQL is mainly divided into Server and Engine layers. The Server layer mainly includes connectors, query caches, analyzers, optimizers, and executors. At the same time There is also a log module (binlog), which can be shared by all execution engines. Redolog is only available in InnoDB.

The engine layer is plug-in type and currently mainly includes MyISAM, InnoDB, Memory, etc.

The execution process of the query statement is as follows: Permission verification (if the cache is hit)---"Query cache---"Analyzer---"Optimizer---"Permission verification---"Executor- --》Engine

The update statement execution process is as follows: Analyzer----》Permission verification----》Executor---》Engine---redo log(prepare status---》 binlog---》redo log (commit status) [Related recommendations: MySQL Tutorial]

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