An article to understand the execution process of MySQL query statements

Preface

We need to retrieve certain data that meets the requirements from the database. It is easy for us to write SQL such as Select ABC FROM T WHERE ID = XX. So what does the database do when we send such a request to the database?

Today we take MYSQL as an example to reveal the query process of the MySQL database and let everyone know some parts of the database.

MYSQL Architecture

mysql architecture

MySQL can be mainly divided into the server layer and the storage engine layer.

The server layer includes connectors, query caches, analyzers, optimizers, executors, 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, the binlog log module;

The storage engine layer is responsible for storing and retrieving data. Its architecture mode is plug-in-based and supports multiple storage engines such as InnoDB, MyISAM, and Memory. The most commonly used storage engine now is InnoDB (which supports transactions), which has become the default storage engine since MySQL version 5.5.5.

Connectors

The connector is mainly responsible for user login to the database and user identity authentication, including verification of account passwords, permissions and other operations.

If the user password is incorrect, you will receive an "Access denied for user" error and the client program will terminate execution.

If the user account password has been approved, the connector will query all permissions of the user in the permission table. All subsequent permission logic judgments in this connection will rely on the permission data read at this time. In other words, as long as the connection is not disconnected, the user will not be affected even if the administrator modifies the user's permissions.

Query cache

After the client establishes a connection with the server, MySQL will first query the cache when executing a query statement to verify whether this SQL has been executed before. Previously executed statements and their results are cached directly in memory in the form of key-value pairs. The key is the query statement and the value is the query result. If your query can find the key directly in this cache, then the value will be returned directly to the client. If there is no hit, subsequent operations need to be performed, and the results will be cached after completion to facilitate the next call.

After seeing this, will your eyes light up? Will you have the urge to make good use of this great function?

In fact, it is not recommended to use query cache here. The query cache expires very frequently. As long as a table is updated, all query caches on this table will be cleared. Therefore, it is very likely that you have gone to great lengths to save the results, but they are cleared by an update before you can use them. For databases with heavy update pressure, the query cache hit rate will be very low. Unless it is a table that will not be updated for a long time, such as a system configuration table, but wouldn’t it be better for us to put this kind of system configuration on the configuration platform?

The query cache function has been deleted in MYSQL8.0. The official also believes that this function has few actual application scenarios, so it was simply deleted.

Analyzer

If Mysql does not hit the query cache, it will enter the analyzer, which is mainly used to analyze what the SQL statement is for. The analyzer is mainly divided into the following two steps:

• Lexical analysis: A SQL statement consists of multiple strings. First, we need to extract keywords, such as select, propose the query table, propose the field name, propose the query conditions, and so on.
• Syntax analysis: Based on the results of lexical analysis, syntax analysis mainly determines whether the SQL statement you entered is correct and whether it conforms to the MYSQL syntax. If your statement is incorrect, you will receive an error reminder "You have an error in your SQL syntax".
  

The lexical analyzer breaks down the entire query statement into various tokens, and the syntax analyzer converts the "various tokens" into combinations that are meaningful to MySQL based on the defined system language. Finally, the system generates a syntax tree (AST), which is the data structure that the optimizer relies on.

Optimizer

After the analyzer, MySQL knows what you are going to do. Before execution begins, it must first be processed by the optimizer.

Why do we need an optimizer?

• The optimizer contains many complex optimization techniques, which are often more than the best programmers know. Automatic optimization of the system is equivalent to making these optimization technologies available to everyone.
• The optimizer can obtain many statistical information from the data dictionary, such as the number of rows in the table, the distribution of each column in the table, and so on. Optimizers can consider hundreds of different execution plans, while programmers can generally only consider a limited number of possibilities;
• An effective execution plan can be selected based on this information, but it is difficult for user programs to obtain this information;
  

In short, the optimizer modifies the shape of the syntax analysis tree, converts the syntax analysis tree into a query tree, and determines the execution plan.

Actuator

MySQL knows what you want to do through the analyzer, and knows how to do it through the optimizer, so it enters the executor stage and starts executing the statement.

When starting execution, it is necessary to first check whether the user has the permission to execute the query. If not, an error message indicating that the user does not have permission will be returned. If there is permission, the engine's interface will be called and the result of the interface execution will be returned.

Statement Analysis

Let's analyze the execution process of MYSQL query with the following real SQL query statement:

select id,name,sex,phoone from user t where t.age='26' and t.account='javadaily'

• First, the client needs to connect to the database. If the account and password are incorrect, an error message will be returned directly. If they are correct, proceed to the next step.
• Before MYSQL8.0, the query cache will be searched first, and this SQL statement will be used as the key to query whether there is a result in the memory. If there is, it will first determine whether there is permission. If there is permission, it will return to the client, otherwise an error will be reported; if there is no hit from the query cache, it will proceed to the next step
• The analyzer performs lexical analysis to extract the key elements of the SQL statement. For example, the statement above is a select query, the table name to be queried is user, the columns to be queried are id, name, sex, phoone, and the query conditions are age=26 and account=javadailly. Then determine whether there are any syntax errors in the SQL statement, such as whether the keywords are correct, etc. If the check is OK, proceed to the next step.
• The above SQL has two execution plans. The optimizer selects plan a with the highest execution efficiency based on its own optimization algorithm (inaccurate statistical information may cause the optimizer to select the wrong execution plan). After determining the optimization plan, it starts execution.
  

a. First query the user with account=javadaily, then check whether age is equal to 26 b. First find the user with age=26, then query the user with account=javadaily

• Perform permission check. If there is query permission, call the database engine interface to return the execution result; otherwise, report an error.

Summarize

This is the end of this article about understanding the execution process of MySQL query statements. For more relevant MySQL query execution process content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!