A detailed introduction to the MySQL execution process and query cache

This article brings you a detailed introduction to the MySQL execution process and query cache. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. .

MySQL executes a query process:
When we send a request to MySQL, what exactly does MySQL do:

1. Client Send a query to the server
2. The server first checks the query cache. If the cache is hit, it immediately returns the results stored in the cache. Otherwise go to the next stage.
3. The server performs SQL parsing and preprocessing, and then the optimizer generates the corresponding execution plan.
4.MySQL calls the API of the storage engine to execute the query based on the execution plan generated by the optimizer.
5. Returns the result to the client.

mysql is mainly composed of two parts: the server layer and the storage layer.
The server layer mainly includes connectors, query cache, analyzers, optimizers, and executors.
The storage layer is mainly used to store and query data. Commonly used storage engines include InnoDB and MyISAM.

(1) MySQL client/server communication protocol

MySQL client and The communication protocol between the server is "half-duplex", which means that at any moment, either the server is sending data to the client, or the client is sending data to the server. These two actions cannot occur at the same time. . So we cannot and do not need to cut a message into small pieces and send them independently.

Advantages and Disadvantages:
This protocol makes MySQL communication simple and fast, but it also limits MySQL in many places.
One obvious limitation is that this means no flow control. Once one end starts sending a message, the other end must receive the entire message before it can respond to it. It's like the game of retrieval: at any moment, only one person can control the ball, and only the person controlling the ball can throw the ball back (send a message).

(2).Connector

The MySQL client establishes a connection with the server and obtains the permissions of the currently connected user

(3)Query cache
Before parsing a query statement, if the query cache is turned on, MySQL will check the cache to see if it hits the data in the query cache. This check is implemented via a case-sensitive hash lookup.
Even if there is only one byte difference between the query and the query in the cache, it will not match the cached result. In this case, the query
will enter the next stage of processing.

If the current query happens to hit the query cache, MySQL will check the user permissions once before returning the query results. This still does not require parsing the query SQL statement, because the table information that the current query needs to access is already stored in the query cache. If there is no problem with permissions, MySQL will skip all other stages, get the result directly from the cache and return it to the client. In this case, the query will not be parsed, no execution plan will be generated, and it will not be executed.

ps: Note that the query cache function is no longer available after mysql8, because this cache is very easy to be cleared. The hit rate is relatively low.

(3). Analyzer

Since the cache is not found, it is necessary to start executing the sql statement. The sql statement must be parsed before execution.

The analyzer mainly performs syntactic and semantic analysis of sql statements, checks whether words are spelled incorrectly, and checks whether the table or field to be queried exists

(4) Query optimization

Query The next step in the life cycle is to convert a SQL into an execution plan, and MySQL then interacts with the storage engine according to this execution plan. This includes multiple sub-phases: parsing SQL, preprocessing, optimizing SQ execution plan.

Any errors in this process (such as syntax errors) may terminate the query.

2. About query cache

(1)

The method for MySQL to determine cache hits is very simple: the cache is stored in a reference table and referenced through a hash value.

MySOL query cache saves the complete results returned by the query. When a query hits the cache, MySQL will return the results immediately, skipping the parsing, optimization and execution phases.

The query cache system will track each table involved in the query. If these tables change, then the query cache system will track the results with this table. All relevant stored data will be invalid.

The efficiency of this mechanism seems relatively low, because it is very likely that the query results will not change when the data table changes, but the cost of this simple implementation is very small, and this is very important for a very busy system. Very important.

The query cache system is completely transparent to applications. Applications do not need to care whether MySQL returns results from the query cache or actual execution. In fact, the results of these two methods are exactly the same. In other words, there is no need to use any syntax to query the cache. Whether MYSQL query caching is turned on or off, it is transparent to the application.

(2) Determine cache hit

When determining whether cache hit, MySQL will not parse, "normalize" or parameterize the query statement, but directly use SQL statements and send them to the client Other original information that comes over has different characters, such as spaces and comments, and any differences will lead to cache failure.

When there is some uncertain data in the query statement, it will not be cached. For example, queries containing the function NOW() or CURRENT_DATE()
will not be cached.

Misunderstanding:
We often hear: "If the query contains an undefined function, MySQL will not check the query cache." This statement is incorrect.

Because the SQL statement has not been parsed when checking the query cache, MySQL does not know whether the query statement contains such a function.

Before checking the query cache, MySQL only does one thing, which is to see if the SQL statement starts with 5EL through a case-insensitive check.

The accurate statement should be: "If the query statement contains any uncertain function, then it is impossible to find the cached result in the query cache."

Note:
MySQL's query cache can improve query performance in many cases. There are some issues that require special attention when using it. First of all, turning on the query cache will bring additional consumption to both read and write operations:

1. The read query must first check whether it hits the cache before starting
2. If this read query can be cached, then After the execution is completed, if MySQL finds that the query does not exist in the query cache, it will store the results in the query cache, which will cause additional system consumption.
3. This will also have an impact on write operations, because when writing data to a table, MySQL must invalidate all caches of the corresponding table. If the query cache is very large or fragmented, this operation may cause large system consumption (when a lot of memory is set for the query cache)

If the query cache uses a large amount of memory, the cache will become invalid. The operation may become a very serious problem bottleneck
If a large number of query results are stored in the cache, the entire system may freeze for a while during the cache invalidation operation

Because this operation relies on a global lock Operation protection, all queries that need to perform the operation must wait for this lock,
and whether it is detecting whether it hits the cache or cache invalidation detection, it needs to wait for this global lock.

(3) Under what circumstances can query cache play a role?
Theoretically, you can determine whether you need to enable query by observing the system efficiency when query cache is turned on or off.

Opponent queries that consume a lot of resources are usually very suitable for caching.
For example, some summary calculation queries are specific, such as COUNT(), etc. Generally speaking, query caching can be used for complex SELECT statements.
For example, sorting and paging are required after multi-table JOIN. This type of query consumes a lot of money each time it is executed, but the result set returned is very small. , very suitable for query caching.

However, it should be noted that there are very few UPDATE, DELETE and INSERT operations on the involved tables compared to SELECT.

The direct data to determine whether the query cache is effective is the hit rate. It is the ratio of results returned by the query cache to the total query

However, the cache hit rate is a value that is difficult to judge. What is a good hit rate? Specific situation, specific analysis.

As long as the efficiency improvement brought by query caching is greater than the additional consumption caused by query caching, even a 30% hit rate will have great benefits in improving system performance. In addition, which queries are cached is also very important. For example, the cached query itself consumes a lot of money, so even if the cache hit rate is very low, it will still be beneficial to system performance.

Cache misses may be as follows Several reasons:

1. The query statement cannot be cached, perhaps because the query contains an uncertain function (such as CURREN_DATE) or the query result is too large to be cached. This will cause the status value Cache not cached to increase.
2.MySQL has never processed this query, so the results have never been cached.

3. Another situation is that although the query results have been cached before, because the memory of the query cache is used up, MySQL needs to "evict" some caches, or the cache becomes invalid because the data table is modified.

If there are a large number of cache misses on your server, but in fact most of the queries are cached, then the following situations must have occurred:

1. Query caching has not been completed yet Warm up. In other words, MySQL has not had the opportunity to cache all query results.
2. The query statement has never been executed before. If your application does not repeatedly execute a query statement, there will still be many cache misses even after warm-up is completed
3. Too many cache invalidation operations.

(4) How to configure and maintain the query cache

query_cache_type

Whether to turn on the query cache. Can be set to 0FN or DEMAND. DEMAND means that only statements that clearly indicate SQL_CACHE in the query statement are placed in the query cache. This variable can be session-level or global-level

query_cache_size

The total memory space used by the query cache, in bytes. This value must be an integer multiple of 1024, otherwise the actual data allocated by MySQL will be slightly different from what you specified.

query_cahce_min_res_unit

The minimum unit when allocating memory blocks in the query cache.

query_chache_limit

The maximum query result that MySQL can cache. If the query result is larger than this value, it will not be cached. Because the query cache starts trying to cache the data when the data is generated, only when all the results are returned will it know whether the query results exceed the limit

If it exceeds, MySQL will increase the status value Cache_not_cached and will The results are deleted from the query cache. If you know in advance that there are many such situations, it is recommended to add

to the query statement (5) Alternative

The principle of MySQL query cache work is: execution The fastest way to query is not to execute it, but the query still needs to be sent to the server, and the server still needs to do a little work. What will happen if there is no need to communicate with the server for some queries? At this time, the client's cache can help you to a large extent share the pressure on the MySQL server

Summary:

Exactly the same When a query is executed repeatedly, the query cache can return the results immediately without having to re-execute it in the database. According to our experience, querying the cache in a high concurrency pressure environment will lead to a decrease in system performance or even freeze.

If you must use the query cache, do not set too much memory, and only use it when the benefits are confirmed.

How to judge whether query cache should be used? It is recommended to use Percona server., observe more detailed logs, and do some simple calculations. You can also look at the cache hit ratio (not always useful), the "NSERTS to SELECT ratio" (this parameter is also not intuitive), or the "hit to write ratio" (this reference is more meaningful).

The query cache is a very convenient cache that is completely transparent to the application and does not require any additional coding. However, if you want higher caching efficiency, we recommend using cache or other similar solutions.

【Related recommendations: MySQL video tutorial】

The above is the detailed content of A detailed introduction to the MySQL execution process and query cache. For more information, please follow other related articles on the PHP Chinese website!