An in-depth analysis of the execution process of MySQL query statements

This article brings you relevant knowledge about mysql, which mainly introduces the execution process of query statements. Taking MYSQL as an example, it reveals the query process of MySQL database and allows everyone to I have some understanding of some parts in the database, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

Need to retrieve certain data that meets the requirements from the database, we can easily write Select A B C FROM T WHERE ID = XX Such SQL, then when we send such a request to the database, what exactly does the database do?

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

1. MYSQL architecture

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

Server layer includes connectors, query caches, analyzers, optimizers, executors, etc. All cross-storage engine functions are implemented in this layer, such as stored procedures and triggers. , views, functions, etc., there is also a general log module binlog log module;

Storage engine layer is responsible for data storage and extraction. Its architectural model is plug-in 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.

2. Connector

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

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

If the user account password has been passed, the connector will query all the permissions of the user in the permission table. Subsequent permission logic judgments in this connection will rely on the permission data read at this time. That is to say, as long as the connection is not disconnected, even if the administrator modifies the user's permissions, the user will not be affected.

3. Query cache (Query cache)

After the client establishes a connection with the server, MySQL will first query the cache when executing the query statement to verify whether this SQL has been executed before. Pass. Previously executed statements and their results will be 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.

When you see this, your eyes will light up and you will have the urge to make good use of this function.

In fact, it is not recommended to use the query cache here. The query cache fails very frequently. As long as there is an update to a table, all query caches on this table will be cleared. So it's possible that you took the trouble to save the results, only to have them wiped out by an update before you even used them. For databases with heavy update pressure, the hit rate of the query cache will be very low. Unless it is the kind of table that will not be updated for a long time, such as the system configuration table, but shouldn't we put this kind of system configuration on the configuration platform?

The query cache function has been deleted in MYSQL8.0. Officials also believe that this function has relatively few actual application scenarios, so they simply deleted it.

4. Analyzer

If Mysql does not hit the query cache, it will enter the analyzer. The analyzer 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, keywords must be extracted, such as select, and proposed Query the table, propose field names, propose query conditions, etc.

• Grammar analysis: Based on the results of lexical analysis, grammar analysis is mainly to determine whether the SQL statement you entered is correct and conforms to MYSQL syntax. If your statement is incorrect , you will receive the error reminder "You have an error in your SQL syntax".

The lexical analysis program decomposes the entire query statement into various types of tokens, and the syntax analysis converts the "various types of tokens" into meaningful combinations for 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.

5. Optimizer

After passing the analyzer, MySQL will know what you want to do. Before execution begins, it must be processed by the optimizer.

Why do we need an optimizer?

• The optimizer contains many complex optimization techniques, which are often more complex than those of the best programmers. The automatic optimization of the system is equivalent to making these optimization technologies available to everyone.

• The optimizer can obtain a lot of statistical information from the data dictionary, such as the number of rows in the table, the distribution of each column in the table, etc. Optimizer The optimizer can consider hundreds of different execution plans, while programmers generally can only consider a limited number of possibilities;

• can select an effective execution plan based on this information. , and it is difficult for user programs to obtain this information;

In short, the optimizer modifies the shape of the parse tree, changes the parse tree into a query tree, and determines the execution plan .

6. Executor

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

When starting execution, first verify whether the user has the permission to execute the query. If not, an error of no permission will be returned. If it has permission, it will call the engine's interface and return the result of the interface execution.

7. Statement analysis

We use the following real SQL query statement to analyze the execution process of the MYSQL query

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

• First of all, the client needs Connect to the database. If the account password is incorrect, an error message will be returned directly. If it is correct, go to the next step.

• Before MYSQL8.0, it would first go to the query cache and use this SQL statement as the key to query whether there is a result in the memory. If there is, it will first determine whether there is permission and permission. Then return to the client, otherwise an error will be reported; if there is no hit from the query cache, go 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 extracted Query select, extract the table name to be queried

  user, the columns to be queried are id,name,sex,phone, the query conditions are age=26 and account=javadailly. Then determine whether the SQL statement has grammatical errors, such as whether the keywords are correct, etc. If there is no problem, proceed to the next step.

• The above SQL has two execution plans. The optimizer selects plan A with the highest execution efficiency according to its own optimization algorithm (inaccurate statistical information may cause the optimizer to select the wrong execution plan) , start executing the optimization plan after determining it.

  a. First query the users of
  account=javadaily, and then determine whether age is equal to 26 b. First find the users of age=26, and then query User account=javadaily performs permission verification. If there is query permission, the database engine interface is called to return the execution result; otherwise, an error is reported.

• Recommended learning:

  mysql video tutorial

The above is the detailed content of An in-depth analysis of the execution process of MySQL query statements. For more information, please follow other related articles on the PHP Chinese website!