How is an SQL query executed?

How to solve this problem? There are two options you can consider.

1. Disconnect the long-term connection periodically. Use for a period of time, or in the program to determine the execution of a large memory – consuming query, disconnect, then query and reconnect.

2. If you are using MySQL 5.7 or later, you can re-initialize the connection resource by executing mysql_reset_connection after each large operation. This process does not require reconnection and re-authentication of permissions, but restores the connection to the state it was in when it was created.

The query cache

After the connection is established, you can execute the SELECT statement. The execution logic leads to the second step: query 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 key-value 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 I would advise you 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 the results, and an update wiped them out before you even used 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.

Fortunately, MySQL also provides this “on demand” approach. You can set the query_cache_type parameter 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 specify it explicitly with SQL_CACHE, as in the following statement:

Mysql > select SQL_CACHE * from T where ID=10;

Note that MySQL 8.0 directly removed the entire query cache function, meaning that this function has been completely removed from MySQL 8.0.

analyzer

If the query cache is not hit, it is time to actually execute the statement. First, MySQL needs to know what you are going to do, so it needs to parse SQL statements.

The parser first does a “lexical analysis”. You are entering an SQL statement consisting of multiple strings and Spaces. MySQL needs to figure out what the strings are and what they represent.

MySQL knows from the keyword “select” that you typed in. This is a query. It also recognizes the string “T” as “table name T” and the string “ID” as “column ID.”

Once these identifications are done, “parsing” is done. Based on the results of the lexical analysis, the parser will determine whether the SQL statement you entered meets the MySQL syntax based on the syntax rules.

If your syntax is not correct, You will receive an error warning “You have an error in your SQL syntax”, such as the following statement select missing the starting letter “s”.

mysql> elect * from t where ID=1;

ERROR 1064 (42000): 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 ‘elect * from t where ID=1’ at line 1

A common grammar error will indicate where the first error occurred, so focus on the words immediately following “use near”.

The optimizer

After a profiler, MySQL knows what you need to do. Before execution can begin, it needs to be processed by the optimizer.

The optimizer determines which index to use when there are multiple indexes in the table. Or determine the order in which tables are joined when a statement has multiple joins. For example, if you execute a statement like the following, this statement performs a join on two tables:

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

It can first fetch the ID value of the record c=10 from table T1, then associate it with table T2 according to the ID value, and then judge whether the value of D in T2 is equal to 20.

It can also take the ID value of the record d=20 from table T2, and then associate it with T1 according to the ID value, and then judge whether the value of C in T1 is equal to 10.

The logical result of the two execution methods is the same, but the efficiency of the execution will be different, and the role of the optimizer is to decide which one to use.

After the optimizer phase is complete, the execution plan of the statement is determined, and then the executor phase is entered. If you have any questions about how the optimizer chose the index, or whether it might have chosen the index incorrectly, I’ll explain the optimizer separately in a later article.

actuator

MySQL knows what you want to do from the parser, it knows how to do it from the optimizer, so it goes to the executor phase and starts executing statements.

If you do not have permission to perform a query on T, an error will be returned, as shown below.

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user ‘b’@’localhost’ for table ‘T’

If you have permission, open the table and continue. When a table is opened, the executor uses the interface provided by the table engine according to its definition.

For example, in table T of our example, the ID field has no index, then the execution flow of the executor is like this:

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

2. Invoke the 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 recordset consisting of all rows that meet the preceding conditions as a result set to the client.

At this point, the statement is complete.

You’ll see a ROWS_EXAMINED field in the database’s slow query log that shows how many rows were scanned during this statement execution. This value is accumulated each time the executor calls the engine to retrieve the row.

In some scenarios, the executor is called once and multiple rows are scanned inside the engine, so
The number of lines scanned by the engine is not exactly the same as ROws_EXAMINED. The internal mechanics of the storage engine will be explained in detail in a later article.

The same logic is performed for indexed tables. The first call is the “fetch the first row that meets the condition” interface, and then the “next row that meets the condition” interface is iterated, all of which are already defined in the engine.

summary

Today I have introduced you to the logical architecture of MySQL, hopefully giving you an idea of the stages of a complete SQL statement execution process. Due to space constraints, I just used a query example to go through each link. Don’t worry if you still have questions about the details of how each section unfolded, I’ll cover them later in the field section.