Please go to DobbyKim’s Question of the Day for more questions

MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL

A:

SQL > select * from ‘MySQL’;

select * from T where ID = 10;
Copy the code

How is this statement executed on the MySQL client?

Let’s start with the MySQL client architecture:

MySQL can be divided into Server layer and storage engine.

Server layer:

  • The connector
  • Query cache (deprecated from MySQL 8.0)
  • analyzer
  • The optimizer
  • actuator

Storage engine:

  • Responsible for data storage and extraction
  • Plug-in, support a variety of storage engines, such as InnoDB, MyISAM, Memory
  • InnoDB is the default storage engine for MySQL since version 5.5.5

Next, let’s analyze the mental path of an SQL statement, starting with the connector

The connector

A connector serves two purposes: first, it validates permissions, and second, it maintains and manages connections.

We pass the command:

mysql -h$ip -P$port -u$user -p
Copy the code

If the password is authenticated, the connector will check the permission table for the current user you logged in to.

Another function of connectors is to maintain and manage connections.

If the client is inactive for too long, the connector will automatically disconnect. This time is controlled by the parameter “wait_timeout”

Use the following command:

show global variables like 'wait_timeout';
Copy the code

Displays the connection timeout time, in seconds, and the conversion result is 8 hours:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
Copy the code

After the connection is disconnected, we need to reconnect before we can send commands.

Long connection and short connection

A long connection means that the same connection is used all the time if the client keeps receiving requests. Short connections disconnect after a few queries and re-establish a connection the next time. As shown below:

Since establishing a single connection is usually complicated, we recommend using long connections as much as possible. If you are using a long connection, you may find that MySQL’s memory usage increases very quickly. This is because the long connection occupies resources, and the resources will be released after the connection is disconnected. As time goes by, the service will appear OOM. There are two solutions:

  1. Disconnect regularly
  2. After MySQL 5.7, you can set mysql_reset_connection to reset connections periodically to release resources periodically

The query cache

Once the connection is established, the execution logic goes to the second step: query the cache.

When MySQL gets a query request, it goes to the query cache to see if the query has been executed before. Previously executed statements and their results may be cached directly in memory as k-V pairs. Key is the query statement and value is the query result. If your query can find the key directly in the cache, the value will be returned directly to the client. If the statement is not in the query cache, subsequent execution phases continue. After the execution is complete, the results are stored in the query cache.

As you can see, if the query hits the cache, MySQL can return the result directly without having to perform further complicated operations, which can be very efficient. But in most cases it is recommended not to use query caching, why? Because query caching often does more harm than good. The query cache invalidates so frequently that whenever a table is updated, all the query cache on that table is cleared. So it’s very likely that you struggled to save your results, only to have them all wiped out by an update before you could use them. For databases under pressure to update, the hit ratio of the query cache can be very low. Unless your business has a static table, it only updates once in a while. For example, a system configuration table, the query on that table is suitable for query caching.

MySQL also provides this “on demand” approach. You can set the parameter “query_cache_type” to DEMAND so that query caching is not used for default SQL statements. For statements where you are sure to use the query cache, you can explicitly specify “SQL_CACHE”, as in the following statement:

select SQL_CACHE * from T where ID=10;Copy the code

Note that query caching is no longer available in MySQL 8.0.

analyzer

If there is no hit in the query cache, it comes to the parser.

Profilers do two things:

  • Lexical analysis
  • Syntax analysis

We enter an SQL statement consisting of multiple strings and Spaces. In “lexical analysis”, MySQL needs to recognize the meaning of strings, such as our query:

select * from T where ID = 10;
Copy the code

The analyzer will recognize the keyword “SELECT” as a query statement, “T” as a table name, “ID” as a query field, and so on.

Once this recognition is done, the parser does “parsing.”

The parser looks at the results of the lexical analysis to determine whether the SQL statement you entered meets the MySQL syntax.

The optimizer

From the parser’s judgment, MySQL knows what you need to do. However, before the SQL statement can be executed, the optimizer needs to process it.

The function of the optimizer is to optimize our SQL statement. When we have more than one index in the table, the optimizer will decide which index to use. Or, when there are multiple table joins in a statement, the optimizer determines the order in which the tables are joined to find the optimal solution.

After the optimization phase of the optimizer is complete, the execution plan of this SQL statement is determined.

actuator

MySQL knows what you need to do from the parser, it knows how to do it from the optimizer, and then it goes into the executor and executes the statement.

At the beginning of the execution, the executor must first determine whether you have the permission to execute the query on the table “T”, if not, will return no permission error (if the query cache hits, will do the permission verification when the query cache returns results).

If permission is granted, the table is opened and execution continues. When a table is opened, the executor uses the interface provided by the table engine according to its definition.

In our example table “T”, the ID field is not indexed, and the execution flow of the actuator is as follows:

  1. Call InnoDB engine interface to fetch the first row of the table, check whether the ID value is 10, if not skip, if yes, save the row in the result set;

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

  3. The executor returns the records of all the rows that meet the conditions during the above traversal as a result set to the client.