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

This article mainly explains the "introduction to the execution process of SQL statements". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "introduction to the execution process of SQL statements".

  did not see the whole cow in the past, which depends on its understanding of the internal skeleton structure of cattle, as well as for MySQL. Only a more comprehensive understanding of each process of SQL statement execution can better design and optimize SQL.

When   wants MySQL to run queries at higher performance, the best way is to figure out how MySQL optimizes and executes queries. Once you understand this, a lot of query optimization work actually follows some principles that can run in a reasonable way as expected.

  shows what MySQL does when sending a request to MySQL as shown in the following figure:

The client sends a query to the server.

The server first checks the query cache and immediately returns the results stored in the cache if it hits the cache. Or move on to the next stage.

The server carries out SQL parsing and preprocessing, and then the optimizer generates the corresponding execution plan.

According to the execution plan generated by the optimizer, MySQL invokes the storage engine's API to execute the query.

Return the result to the client.

Query cache during SQL statement execution

The   MySQL query cache saves the complete structure returned by the query. When the query hits the cache, MySQL returns the results immediately, skipping the parsing, optimization, and execution phases.

The   query caching system tracks each table involved in the query, and if these tables change, all cached data associated with that table will be invalidated.

  MySQL stores the cache in a reference table, referenced by a hash value that includes the following factors, that is, the query itself, the current database to be queried, the version of the client protocol, and other information that may affect the returned results.

When   determines whether the cache is hit, MySQL does not parse the query statement, but directly uses SQL statements and other raw information sent by the client. Therefore, differences in any character, such as spaces, comments, and so on, will cause the cache to miss.

  is not cached when there is some uncertain data in the query statement. For example, queries that contain functions NOW () or CURRENT_DATE () are not cached. Any user-defined functions, storage functions, user variables, temporary tables, system tables in the mysql database, or tables that contain any column-level permissions are not cached.

One thing to note about   is that MySQL does not check the query cache because it contains an uncertain function in the query, because MySQL does not parse the query statement before checking the query cache, so there is no way to know if there are uncertain functions in the statement.

The fact of   is that if the query contains any uncertain functions, its query results will not be cached because the corresponding cached results cannot be found in the query cache.

  's configuration for query caching is shown below.

Query_cache_type: whether to turn on query caching. Can be set to OFF, ON, and DEMAND. DEMAND means that only statements that explicitly specify SQL_CACHE in the query statement will be placed in the query cache.

Query_cache_size: the total memory space used by the query cache.

Query_cache_min_res_unit: the smallest unit when allocating memory blocks in the query cache. A smaller value reduces memory space waste caused by fragmentation, but results in more frequent memory block operations.

The maximum query result that query_cache_limit:MySQL can query. If the query result is greater than this value, it will not be cached. Because the query cache starts to cache the data when the data is generated, MySQL knows whether the query results exceed the limit when all the results are returned. The result is not deleted from the query cache until it is exceeded.

The optimization of query cache by   is an important part of database performance optimization. The judgment process is roughly shown in the following figure.

Flow chart of query cache judgment

The   cache hit ratio can be calculated by the following formula: Qcache_hits/ (Qcache_hits + Com_select).

Parsing and preprocessing

The   parser parses the SQL statement through keywords and generates the corresponding parsing tree. The MySQL parser will validate and parse the query using MySQL syntax rules.

The   preprocessor further checks whether the parsing book is legal according to some MySQL rules, such as checking the existence of data tables and data columns, and parsing names and aliases to see if they are ambiguous.

Query optimizer

The   query optimizer converts the parse tree into an execution plan. A query can be executed in multiple ways, all of which return the same result. The role of the optimizer is to find the best execution plan.

The process of generating an execution plan by   can be time-consuming, especially if there are many optional execution plans. If the final execution plan corresponding to a SQL statement is cached during the execution of the statement, when the similar statement is entered into the server again, the cached execution plan can be used directly, thus skipping the whole process of generating the execution plan of the SQL statement, thus improving the execution speed of the statement.

Execution plan cache

  MySQL uses a cost-based query optimizer (Cost-Based Optimizer,CBO). It attempts to predict the cost of a query when it uses some kind of execution plan and selects the one with the lowest cost.

The   optimizer will transform the relational expression according to the optimization rules, which means that one relational expression will generate another relational expression after optimizing the rules, while the original expression will be retained, and multiple execution plans will be generated after a series of transformations, and then CBO will calculate the Cost of each execution plan based on statistics and cost model (Cost Model) to select the execution plan with the minimum Cost. From the above, there are two dependencies in CBO: statistics and cost model. Whether the statistical information is accurate or not and whether the cost model is reasonable or not will affect the choice of the optimal plan by CBO.

The principle of optimizer in   is very complicated, so I won't explain it in detail here. You can learn it by yourself.

Query execution engine

In the parsing and optimization phase of  , MySQL will generate the corresponding execution plan for the query, and the query execution engine of MySQL will complete the entire query according to this execution plan. The execution plan here is a data structure, rather than generating the corresponding bytecode like other relational databases.

Return the result to the client

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

Returning the result set to the client by   MySQL is an incremental, step-by-step process. When the query generates the first result, MySQL can begin to gradually return the result set to the client.

Thank you for your reading, the above is the content of "introduction to the execution process of SQL statements". After the study of this article, I believe you have a deeper understanding of the introduction of the execution process of SQL statements, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.