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

First, there is a user_info table with an id field in it. Execute the following query statement:

Select * from user_info where id = 1

The returned result is:

Schematic diagram of mysql basic architecture:

Generally speaking, MySQL is divided into two parts: Server layer and storage engine layer.

The Server layer includes connectors, query caches, parsers, executors, and so on, as well as all built-in functions (such as date, time, mathematical and cryptographic functions, etc.) and cross-storage engine functionality (such as stored procedures, triggers, views).

The storage engine layer is responsible for data storage and extraction, and supports multiple storage engines such as InnoDB, MyISAM, Memory and so on. After MySQL version 5.5.5, the default storage engine is InnoDB.

Connector (Connector)

Before querying the SQL statement, be sure to establish a connection to the MySQL, which is done by the connector. Connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections. The connection command is:

Mysql-h$ip-P$port-u$user-p

After entering the password and passing the verification, the connector will find out the permissions you have in the permission table, and then the permission judgment logic in the connection will depend on the permissions read at this time. After a user successfully establishes a connection, even if the administrator makes changes to the permissions of this user, it will not affect the permissions of the existing connections. After the modification, only the newly created connections will use the new permission settings.

After the connection is complete, if you have no subsequent action, the connection is idle, which you can see in the show processlist command. The results are as follows:

If the client does not move for too long, the connector will automatically disconnect it; this time is controlled by the parameter wait_timeout, and the default value is 8 hours. If the client sends a request again after the connection is disconnected, it will receive an error reminder: Lost connection to MySQL server during query

Long connection and short connection

In the database, persistent connection means that after a successful connection, the client always uses the same connection if there are persistent requests from the client. A short connection means that the connection is disconnected after a few queries are executed, and a new one is established next time.

The process of establishing a connection is usually complicated. It is recommended that the action of establishing a connection should be minimized and long connections should be used as much as possible. However, after using all persistent connections, sometimes the memory consumed by MySQL increases particularly fast, because the memory temporarily used by MySQL during execution is managed in the connection object.

These resources are not released until the connection is disconnected. Therefore, if the long connection accumulates, it may lead to too much memory consumption and be forcibly killed by the system (OOM). From the phenomenon, it is that MySQL restarts abnormally.

How to solve this problem? The following two options can be considered:

Disconnect the long connection regularly. Use it for a period of time, or after it is judged in the program that a large memory-consuming query has been executed, disconnect, and then query and reconnect. MySQL version 5.7 or later, you can reinitialize the connection resources by executing mysql_reset_connection after each large operation. This process does not require reconnection and redo permission verification, but restores the connection to the state it was when it was just created.

Query cache (Query Cache)

After the connection is established, the select statement is executed, and the cache is first queried before execution.

After MySQL gets the query request, it first queries the cache to see if the statement has been executed. Executed statements and their results are stored in a certain area of memory in the form of key-value pairs. Key is the statement of the query and value is the result of the query. If your query can find key directly in this cache, then this

The value is returned directly to the client.

If the statement is not in the query cache, the execution phase continues later. After the execution is completed, the execution results are stored in the query cache. If the query hits the cache, MySQL does not need to perform the following complex operations to return the results directly, which improves efficiency.

However, query cache invalidation is very frequent, as long as there is an update to a table, all query caches on that table will be emptied. For databases with high update pressure, the hit rate of the query cache will be very low. If a static table is required in the business, it will take a long time to update.

For example, a system configuration table, then the query on this table is suitable for using query caching. MySQL provides this on-demand approach. You can set the parameter query_cache_type to DEMAND, and query caching will not be used for default SQL statements. For the statement for which you are sure you want to use the query cache, you can specify it explicitly with SQL_CACHE, as follows:

Mysql > select SQL_CACHE * from user_info where id = 1

MySQL version 8.0 removes the query cache function.

Analyzer (Analyzer)

If the query cache misses, the execution of the statement begins. First, MySQL needs to parse the SQL statement.

The analyzer will do lexical analysis first. The SQL statement is made up of multiple strings and spaces, and MySQL needs to identify what the strings are and what they represent. MySQL is identified by the keyword select you typed, which is a query statement. It also recognizes the string user_info as a table name and the string id as a column name. After that, we have to do a grammatical analysis. According to the results of lexical analysis, the parser determines whether the input SQL statement satisfies the MySQL grammar according to the grammar rules.

If your SQL statement is incorrect, you will receive an error reminder from You have an error in your SQL syntax, such as the following statement from is written as form.

Mysql > select * form user_info where id = 1bot 1064-You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form user_info where id = 1' at line 1

General syntax errors will indicate the first place where the error occurs, so focus on the content immediately following the use near.

Optimizer (Optimizer)

After the lexical analysis and syntax analysis of the analyzer, it is also processed by the optimizer.

The optimizer decides which index to use when there are multiple indexes in the table, or the join order of each table when a statement has multiple table associations (join). For example, you execute the following statement, which executes the join of two tables:

Mysql > SELECT * FROM order_master JOIN order_detail USING (order_id) WHERE order_master.pay_status = 0 AND order_detail.detail_id = 1558963262141624521

You can first extract the order_id value of the record with pay_status = 0 from the table order_master, then associate it to the table order_detail according to the order_id value, and then judge whether the detail_id value in order_detail is equal to 1558963262141624521.

You can also first extract the order_id value of the record with detail_id = 1558963262141624521 from the table order_detail, then associate it to order_master according to the order_id value, and then determine whether the value of pay_status in order_master is equal to 0.

The logical results of the two execution methods are the same, but the efficiency of execution will be different, and the role of the optimizer is to decide which scheme to use. After the optimizer phase is complete, the execution plan for this statement is determined and then enters the executor phase.

Actuator (Actuator)

MySQL knows what to do through the parser and what to do through the optimizer, so it enters the executor phase and starts executing the statement.

When you start execution, you need to determine whether you have permission to execute a query on the table user_info. If not, an error without permission will be returned, as shown below (if the query cache hits the query cache, permission verification will be done when the query cache returns the result. The query also calls precheck to verify permissions before the optimizer.

Mysql > select * from user_info where id = 1 * error 1142 (42000): SELECT command denied to user 'wupx'@'localhost' for table' user_info'

If you have permission, open the table and continue execution. When the table is opened, the executor uses the interface provided by the engine according to the engine definition of the table. For example, in the table user_info in our example, the id field does not have an index, then the execution flow of the executor is as follows:

1. Call the InnoDB engine API to get the first row of the table to determine whether the id value is 1. If not, skip it. If so, store the row in the result set.

2. Call the engine interface to fetch the next row and repeat the same judgment logic until the last row of the table is fetched.

3. The executor returns the recordset composed of all the rows that meet the criteria in the above traversal process to the client as a result set.

For indexed tables, the interface for the first row that meets the condition is called for the first time, and then the interface for the next row that meets the condition is looped.

There is a rows_examined field in the database's slow query log, indicating how many rows were scanned during the execution of this statement. This value is accumulated each time the executor calls the engine to get the data row. In some scenarios, the executor calls once and scans multiple lines inside the engine, so the number of rows scanned by the engine is not exactly the same as rows_examined.

Summary

By explaining the complete execution process of a SQL statement, this paper introduces the logical architecture of MySQL. MySQL mainly includes connector, query cache, analyzer, optimizer and executor.

These are the details of analyzing how SQL query statements are executed. Please pay attention to other related articles for more information.

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.