MySQL Query execution process

MySQL Query execution process MySQL Query execution path

1. the client sends a query to the server;

2. the server first checks the query cache. if the cache is hit, the results stored in the cache are immediately returned. Otherwise, the system enters the next stage;

3. the server performs SQL parsing and preprocessing, and then the optimizer generates the corresponding execution plan;

4. MySQL calls the storage engine API for query based on the execution plan generated by the optimizer;

5. return the result to the client.

Query cache)

Before parsing a query statement, if the query cache is opened, MySQL will first check whether the query hits the data in the query cache. This check is implemented through a case-sensitive hash query. Queries in the query and cache do not match the cached results even if they have only one byte. in this case, the query enters the next stage.

If the current query hits the query cache, MySQL checks the user permissions before returning the query results. This still does not require parsing and Querying SQL statements, because the query cache contains the information about the tables to be accessed by the current query. If the permission is correct, MySQL will skip all other stages and get the result from the cache and return it to the client. In this case, the query will not be parsed, and the execution plan will not be generated or executed.

Cache configuration parameters:

Mysql> show variables like 'query _ cache % ';

+ ------------------------------ + --------- +

| Variable_name | Value |

+ ------------------------------ + --------- +

| Query_cache_limit | 1048576 |

| Query_cache_min_res_unit | 4096 |

| Query_cache_size | 0 |

| Query_cache_type | ON |

| Query_cache_wlock_invalidate | OFF |

+ ------------------------------ + --------- +

5 rows in set (0.01 sec)

Query_cache_limit: the maximum result that MySQL can cache. if the value exceeds, the value of Qcache_not_cached is added and the query result is deleted.

Query_cache_min_res_unit: minimum unit size for memory block allocation

Query_cache_size: the total memory space used by the cache, in bytes. The value must be an integer multiple of 1024, otherwise, the actual allocation of MySQL may be different from this value (I think this should be related to the blcok size of the file system)

Query_cache_type: whether to enable cache OFF: Disable ON: always enable

Query_cache_wlock_invalidate: if a data table is locked and the data is still returned from the cache, the default value is OFF, indicating that the data can still be returned.

Syntax parser and pre-processor

First, MySQL parses SQL statements using keywords and generates a corresponding "resolution tree ". The MySQL parser uses MySQL syntax rules to verify and parse queries. For example, it verifies whether incorrect keywords are used, whether the order of keywords is correct, or whether quotation marks can be correctly matched.

The pre-processor further checks whether the parsing tree is valid according to some MySQL rules. for example, the pre-processor checks whether data tables and data columns exist, and resolves the names and aliases to see if they are ambiguous.

Next, the pre-processor will verify the permissions, which is usually very fast unless there are a lot of permission settings on the server.

Query optimizer

The syntax tree is now considered valid and is converted into an execution plan by the optimizer. A query can be executed in many ways, and the same results are returned at the end. The Optimizer is used to find the best execution plan.

MySQL uses the cost-based optimizer, which will try to predict the cost of a query using a certain execution plan, and select one with the lowest cost. Initially, the minimum unit of cost was the cost of randomly reading a 4 K data page. later, the cost calculation formula became more complex and introduced some "factors" to estimate the cost of some operations, for example, the cost of executing a where condition comparison. You can query the last_query_cost value of the current session to find out the cost of the current query calculated by MySQL.

The MySQL optimizer may choose an incorrect execution plan for many reasons, such:

1. the statistical information is inaccurate.

2. the cost estimation in the execution plan is not the same as the actual execution plan cost.

3. the optimum of MySQL may be different from what you want.

4. MySQL never considers other concurrent queries, which may affect the current query speed.

5. MySQL is not always cost-based optimization, and sometimes it is based on some fixed rules.

6. MySQL will not consider the cost not controlled by it, such as the cost of executing stored procedures or user-defined functions.

MySQL Query optimization uses many optimization policies to generate an optimal execution plan. There are two types of optimization policies: static optimization and dynamic optimization. Static optimization can analyze the resolution tree directly and complete optimization. For example, the optimizer can convert the where condition into another equivalent form through some simple algebraic transformations. Static optimization does not rely on special values, such as some constants brought in by the where condition. Static optimization is always effective after the first time it is completed, and does not change even when different parameters are used for repeated queries. it can be considered as a "compile-time optimization ".

On the contrary, dynamic optimization is related to the query context. It may also be related to many other factors, such as the values in the where condition and the number of data rows corresponding to the entries in the index. these factors need to be re-evaluated during each query, it can be considered as "Runtime Optimization ".

Below are some optimization types that MySQL can handle:

1. reschedule the order of joined tables

2. Convert external connections into internal connections

3. use equivalent conversion rules

4. optimized count (), min (), and max ()

5. estimate and convert it into a constant expression

6. covering index scanning

7. subquery optimization

8. early query termination

9. equivalent propagation

10. Comparison of list in ()

Query execution engine

In the parsing and optimization phase, MySQL generates an execution plan for the query, and the query execution engine of MySQL completes the query according to the execution plan. The execution plan here is a data structure, rather than generating the corresponding bytecode as many other relational databases do.

Compared with the query optimization stage, the query execution stage is not so complex: MySQL is simply executed based on the instructions given in the execution plan. In the process of gradual execution according to the execution plan, a large number of operations need to be completed by calling the interfaces implemented by the storage engine. these interfaces are called "handler API" interfaces. In fact, MySQL creates a handler instance for each table during the optimization phase. The optimizer can obtain table information based on the interface of these instances, including all column names and index statistics of the table.

Return the result to the client

The last stage of query execution is to return the result to the client. Even if the query does not need to return results to the client, MySQL returns some information about the query, such as the number of rows affected by the query.

If the query can be cached, MySQL stores the results in the query cache at this stage.

MySQL returns the result to the client incrementally. For example, in the joined table operation, once the server finishes processing the last joined table and starts to generate the first result, MySQL can start to gradually return the result set to the client.

There are two advantages: the server does not need to store too many results, so it will not consume too much memory because it will return too many results. In addition, such processing also allows the MySQL client to obtain the returned results immediately.

Each row in the result set is sent in a packet that meets the MySQL client/server communication protocol, and then transmitted through the TCP protocol. during The TCP transmission process, mySQL packets may be cached and then transmitted in batches.