Shulou(Shulou.com)06/01 Report--

Editor to share with you what the MySQL query process is, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

MySQL logical architecture

The MySQL logic architecture is divided into three layers, the top layer is the client layer, which is not unique to MySQL, such as connection processing, authorization authentication, security and other functions are handled in this layer.

Most of MySQL's core services are in the middle layer, including query parsing, analysis, optimization, caching, built-in functions (time, mathematics, encryption, etc.), and all cross-storage engine functions are also implemented at this layer: stored procedures, triggers, views, etc.

The lowest layer is the storage engine, which is responsible for data storage and extraction in MySQL. The middle service layer communicates with the storage engine through API. These API interfaces shield the differences between different storage engines.

MySQL query process

When sending a request to MySQL:

1. Client / server communication protocol

MySQL client / server communication protocol is "half-duplex": at any time, either the server sends data to the client, or the client sends data to the server, both actions cannot occur at the same time. Once one end starts to send a message, the other end has to accept the whole message before it can respond to it, so we cannot and do not need to cut a message into small pieces to send independently, and there is no way to control the flow.

The client sends the query request to the server in a separate packet, so when the query statement is very long, you need to set the max_allowed_packet parameter. However, it is important to note that if the query is too large, the server will refuse to accept more data and throw an exception.

In contrast, the server usually responds to a lot of data to the user, consisting of multiple packets. However, when the server responds to the client request, the client must fully accept the entire return result, instead of simply taking the first few results and asking the server to stop sending. Therefore, in the actual development, it is a very good habit to keep the query simple and return only the necessary data, and to reduce the size and number of data packets between communications, which is one of the reasons to avoid using SELECT * and add LIMIT restrictions in the query.

two。 Query cache

Before parsing a query statement, if the query cache is open, MySQL checks to see if the query statement hits the data in the query cache. If the current query happens to hit the query cache, the results in the cache are returned directly after checking the user permissions once. In this case, the query will not be parsed, nor will it generate an execution plan, let alone execute.

MySQL stores the cache in a reference table (a data structure similar to HashMap) through an index of hash values, which are calculated from the query itself, the database to be queried, the client protocol version number, and other information that may affect the result. So the difference between the two queries on any character (spaces, comments) will cause the cache to miss.

If the query contains any user-defined functions, storage functions, user variables, temporary tables, and system tables in the mysql library, the query results will not be cached. For example, the function NOW () or CURRENT_DATE () will return different query results for different query times, and for example, the query statements containing CURRENT_USER or CONNECION_ID () will return different results for different users. There is no point in caching such query results.

3. Cache invalidation

MySQL's query caching system tracks each table involved in the query, and if these tables (data or structure) change, all cached data associated with this table will be invalidated. Because of this, MySQL must set all caches of the corresponding table to invalidate during any write operation. If the query cache is very large or fragmented, this operation may cause a lot of system consumption, or even cause the system to freeze for a while. And the extra consumption of query caching on the system is not only for write operations, but also for read operations:

1. Any query statement must be checked before it starts, even if the SQL statement never hits the cache

two。 If the query results can be cached, the results will be cached after execution, resulting in additional system consumption

Based on this, we should know that querying cache does not always improve system performance, caching and invalidation will bring additional consumption, only when the resource savings brought by cache is greater than the resources consumed by itself, it will bring performance improvement to the system. However, it is very difficult to evaluate whether opening the cache will lead to a performance improvement. If the system does have some performance problems, you can try to open the query cache and make some optimizations in the database design: for example:

1. Replace a large table with multiple small tables, be careful not to overdesign

two。 Batch insertion instead of cyclic single insert

3. Reasonably control the size of the cache space. Generally speaking, it is appropriate to set the size to several tens of megabytes.

4. You can use SQL_CACHE and SQL_NO_CACHE to control whether a query statement needs to be cached

Do not easily open query caching, especially for write-intensive applications. If you can't help it, you can set query_cache_type to DEMAND, where only queries that join SQL_CACH will be cached, and other queries won't, so you can freely control which queries need to be cached.

4. Syntax parsing and preprocessing

MySQL parses the SQL statement with keywords and generates a corresponding parsing tree. This process parser validates and parses mainly through syntax rules. For example, whether the wrong keywords are used in SQL or whether the keywords are in the correct order, and so on. The preprocessing further checks whether the parsing tree is legal according to the MySQL rules. For example, check whether the data tables and columns to be queried exist, and so on.

5. Query optimization

After the syntax tree is considered legal, and an optimizer converts it into a query plan, in most cases, a query can be executed in many ways, and finally return the corresponding results. the role of the optimizer is to find the best execution plan.

MySQL's query optimizer is a very complex part that uses a lot of optimization strategies to generate an optimal execution plan:

1. Redefine the association order of tables (when multiple tables are associated with queries, they do not necessarily follow the order specified in SQL, but there are some tricks to specify the association order)

two。 Optimize the MIN () and MAX () functions (find the minimum value of a column, if the column has an index, only find the leftmost end of the B+Tree index, and vice versa, you can find the maximum value)

3. Terminate the query early (when using Limit, the query will be terminated as soon as a satisfied number of result sets are found)

4. Optimized sorting (in the old version of MySQL, two transfer sorting was used, that is, the row pointer and the fields that need to be sorted were sorted in memory, and then the data rows were read according to the sort results, while the new version used a single transfer sort, that is, all the data rows were read at once and then sorted according to the given columns)

6. Query execution engine

After completing the parsing and optimization phase, MySQL generates the corresponding execution plan, and the query execution engine executes the results step by step according to the instructions given by the execution plan. Most of the operation of the entire execution process is done by calling the interfaces implemented by the storage engine, which are called handler API. Each table in the query process is represented by a handler instance. In fact, MySQL creates a handler instance for each table in the query optimization phase, and the optimizer can obtain the relevant information of the table according to the interface of these instances, including all the column names of the table, index statistics, and so on. The storage engine interface provides a wealth of functions, but there are only dozens of interfaces at the bottom, which complete most of the operations of a query like tower building blocks.

7. Return the result to the client

The final stage of query execution is to return the results to the client. Even if the data cannot be queried, MySQL still returns information about the query, such as the number of rows affected by the query, the execution time, and so on.

If the query cache is turned on and the query can be cached, MySQL will also store the results in the cache.

The result set returning to the client is an incremental and step-by-step process. It is possible that when MySQL generates the first result, it begins to gradually return the result set to the client. In this way, the server does not need to store too many results and consumes too much memory, and it also allows the client to get the returned results the first time. It should be noted that each row in the result set is sent as a packet that meets the communication protocol described in ① and then transmitted through the TCP protocol. During the transmission process, the packets of MySQL may be cached and then sent in batches.

MySQL the entire query execution process

1. The client sends a query request to the MySQL server

two。 The server first checks the query cache and immediately returns the results stored in the cache if it hits the cache. Otherwise, go to the next stage.

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

According to the execution plan, 4.MySQL calls the API of the storage engine to execute the query

The above is all the contents of the MySQL query process, thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.