Detailed explanation of the execution process of MySQL query statements

First, let's briefly review what MySQL does behind a query statement:

• The client sends a query to the server.
• The server first checks the query cache, and if a cache hit is found, it immediately returns the result stored in the cache. Otherwise proceed to the next stage.
• The server parses and preprocesses the SQL, and then the optimizer generates the corresponding execution plan.
• MySQL calls the storage engine API to execute the query based on the execution plan generated by the optimizer.
• Return the result to the client.

Next we will expand on these steps in this process in detail.

1. Communication method between client and server

The communication between the client and the server is a half-duplex communication, that is, only one party can send data to the other party at the same time. Therefore, after sending the query request, all the client can do is wait for the server to return the query results, and it needs to wait until all the returned data is received before proceeding to the next step. It cannot interrupt the sending or disconnect during the server sending process.

2. Query Cache

Before parsing a query statement, if the query cache is turned on, MySQL will first check whether the query hits the data in the query cache. This check is done using a case-sensitive hash table. When the query hits the cache, the result is directly obtained from the cache and returned to the client. MySQL will no longer perform the following operations, that is, the query statement will not be parsed, an execution plan will not be generated, and it will not be executed.

3. Query optimization processing

This link may be the most complicated link in the entire query execution process, which can be divided into three steps: SQL parsing, preprocessing and optimizing SQL execution plan.

(1) Syntax parser and preprocessing This process is to check the syntax of the SQL statement we pass in and verify the query permissions. Binghui generates a "parse tree".

(2) When the query optimizer reaches this step, it proves that there are no problems with the syntax of our statement. A query can have many execution plans that can return correct results. This step is to select the optimal execution plan.
MySQL's optimal execution plan is based on cost. MySQL sets a cost for each operation (such as performing a where comparison) and selects the one with the lowest "cost" from all execution plans.
We can use the following statement to view the cost of the previous query operation:

mysql> SHOW STATUS LIKE 'last_query_cost';

MySQL will return an execution cost data:

+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 0.549000 |
+-----------------+----------+

But it is worth noting that the minimum "cost" here does not mean the fastest query speed. That is, judging the quality of a query statement based on "cost" is sometimes unreliable.

The optimization strategies of the optimizer can be roughly divided into two types: static optimization and dynamic optimization.

Static optimization directly analyzes the previously generated parse tree. For example, the where condition can be converted into another equivalent form through some algebraic transformations. Static optimization remains in effect after the first completion and will not change even if the query is executed repeatedly with different parameters. It can be considered a kind of "compile (preprocessing) time optimization".

Dynamic optimization is related to the query context and needs to be re-evaluated every time a query is made. It can be considered a kind of "runtime optimization".

The following are some of the types of optimizations that MySQL can handle:

• Redefine the order of associated tables
  

Sometimes the order of the associated tables in the query statement we give may not be optimal for the query efficiency. At this time, MySQL can automatically help us adjust the order of the associated tables to improve efficiency.

• Convert outer join to inner join
  

Not all OUT JOIN statements must be executed as outer joins. MySQL is able to recognize this and rewrite the query so that it adjusts the order of the joins.

• Use equivalent transformation rules
  

Use some equivalent statements to reduce the number of comparisons and remove some conditions that are always true or not always true. For example, (5=5 AND a>5) will be rewritten as a>5; if (a5 AND b=c AND a=5.

• Optimizing COUNT(), MIN(), and MAX()
  

Indexes and column nullability can help optimize this type of expression. For example, when searching for the minimum value, you can use the index to directly find the leftmost record. This way, you don't need to query the entire table, but instead replace it with a constant.

• Covering Index Scan
  

When the columns in the index contain all the columns needed in the query, MySQL will use the index to return the required data without querying the corresponding data rows.

• Terminating a query early
  

MySQL can always terminate the query immediately when it finds that the query can meet the requirements. A typical example is when the LIMIT clause is used.

At this point, the MySQL server layer has given an optimal execution plan based on the given query statement. But we need to know that the series of operations we have performed so far are all performed at the server layer, and this layer is not where data is stored. Therefore, we need to take our optimal execution plan to the actual storage engine for search. This leads to our next step: obtaining the corresponding statistical information from the storage engine.

4. Query Execution Engine

Compared to the query optimization phase, the query execution phase is not that complicated. MySQL simply executes the instructions given in the execution plan step by step.

5. Return results to the client

The last stage of query execution is to return the results to the client. Even if the query does not need to return a result set to the client, MySQL will still return some information about the query, such as the number of rows affected by the query.
If the query can be cached, MySQL will put the query results into the query cache at this stage.
The process of returning results is a gradual and incremental process. That is, when the first result is obtained, it starts to be returned to the client. The advantage of doing this is that all data will not be returned at once, causing excessive memory usage, and the client can get the results as soon as possible. Each row in the result set is sent in a packet that meets the MySQL client/server communication protocol and then transmitted via the TCP protocol. During the TCP transmission process, the packets may be cached and then transmitted in batches.

The above is a detailed explanation of the execution process of MySQL query statements. For more information about MySQL query statements, please pay attention to other related articles on 123WORDPRESS.COM!