Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of MySQL architecture components. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Overall architecture

one。 Connector

The    connector is mainly responsible for establishing connections with clients, verifying permissions, and managing connections. You can use the command show processlist to view connection information. When a user connection is successfully created, the permission information has been read into memory, and then the user's permissions are modified, which will not take effect if not refreshed.

   for a connection, if the instruction is not received for a long time (idle), then after a certain period of time, the connector will break the link. This time is controlled by the parameter wait_timeout, which defaults to 8 hours.

Connections in    connectors are divided into long connections and short connections:

Persistent connection: after a successful connection, the client requests to use the same connection

Short connection: the connection will be disconnected after each request is executed, and the connection will be re-established after another request.

  , in order to avoid the overhead of creating connections frequently and repeatedly, we usually use long-term connections, that is, to keep a connection open for a long time. Note, however, that a connection manages some of the memory it occupies during use and is released with the connection when it is disconnected. If the connection continues to open and does not deal with the accumulation for a long time, it may lead to excessive memory consumption and be forced by the system. There are generally two solutions:

Disconnect long connections periodically, disconnect every once in a while or after executing a memory-intensive query to free up memory and recreate the connection when a query is needed

Versions later than 5.7 can use mysql_reset_connection to reinitialize connection resources without reconnecting and validating permissions, but to restore the connection to the state it was in when it was created. There will also be some other effects, such as releasing table locks, clearing temporary tables, resetting variables set in the session, and so on.

Second, query cache

Note: query cache has been abolished since version 8.0.

After the    connection is successfully created, the SQL statement can be executed, but if query caching is turned on, it will be queried from the cache before the SQL is actually analyzed, and will be returned directly if the cache hits. The query cache is a Key-Value structure, Key is a SQL statement, and Value is the corresponding query result. If the cache misses, the subsequent query operation continues. After the query is completed, the results are stored in the query cache.

  , why is the query cache deleted? Because query caching usually does more harm than good. If you update a table, the query cache corresponding to that table will be emptied. For frequently updated tables, query cache invalidation will be very frequent, basically ineffective, and there is also the overhead of updating the cache. For those data tables that will remain basically unchanged, you can choose to use query caching, such as system configuration tables, which will have a higher cache hit rate and may have more advantages than disadvantages, but for this configuration, we can also use external caching.

   can configure the query cache through the parameter query_cache_type, which has three optional values, which are:

0: turn off query caching

1: enable query caching

2: use query cache when there are SQL_CACHE keywords in SQL, such as select SQL_CACHE * from t where xxx

III. Analyzer

   if the query cache does not hit, then the SQL needs to be really executed, and the SQL needs to be parsed before execution, which is mainly divided into two steps: lexical analysis and parsing.

Lexical analysis: extract keywords from SQL, such as select, from, table name, field name, etc.

Syntax analysis: check whether the SQL grammar is legal according to the results of lexical analysis and some grammar rules defined by MySQL, and eventually generate an abstract grammar tree (AST)

IV. Optimizer

The    optimizer takes the AST generated by the analyzer as the input, optimizes the SQL, generates the optimal execution scheme that the optimizer thinks, and gives it to the executor for execution. The optimization process includes logical conversion and cost calculation of SQL.

The    logic transformation is similar to the static compile-time optimization of Java, which makes some "simplification" of SQL to ensure that the results are consistent before and after the SQL transformation. For example, where 1 = 1 and a.id = 2, which can be equivalent to where a.id = 2.

The main purpose of    cost calculation is to choose the way in which SQL is executed, including whether to use index, which index to use, the order in which to join multiple tables, and so on. The cost is divided into service layer cost and engine layer cost. The service layer cost is mainly related to CPU, while the engine layer cost is mainly related to disk I and O. MySQL 5.7introduces two system tables, mysql.server_cost and mysql.engine_cost, to configure these two costs, which are the corresponding costs for various operations, such as temporary table creation, sorting, page reading, and so on.

The    optimizer calculates the final cost of a query plan based on the generated query plan and the above two cost configurations, and selects the one with the lowest cost among multiple query plans to be executed by the executor. However, it should be noted that the lowest cost sometimes does not necessarily mean that the execution time is the shortest.

V. Actuator

The    executor executes the SQL according to the query plan selected by the optimizer, verifies whether the request user has the corresponding query permissions before execution, and finally calls the interface provided by the MySQL engine layer, executes the SQL statement and returns the result. If query caching is turned on, the results are also stored in the query cache.

This is the end of this article on "sample Analysis of MySQL Architecture components". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.