Analyze how a SQL query statement is executed in MySQL

1. Overview of MySQL Logical Architecture

The most important and distinctive feature of MySQL is its pluggable storage engine architecture, which is designed to separate query processing and other system tasks from the storage/retrieval of data. Let’s look at the explanation on the official website:

The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements.

Basically, the MySQL pluggable storage engine architecture enables developers to choose a specialized storage engine for specific application needs without having to manage any specific application coding requirements. That is, although different storage engines have different capabilities, applications are not affected by these differences.

If application changes bring about the need to change the underlying storage engine, or if one or more storage engines need to be added to support new requirements, no major coding or process changes are required to make things work. The MySQL server architecture shields applications from the underlying complexity of storage engines by providing a consistent and easy-to-use API across storage engines.

The logical architecture diagram of MySQL is as follows, refer to "High Performance MySQL - 3rd Edition":

We can roughly divide the logical architecture of MySQL into the server layer and the storage engine layer:

1) Most of MySQL's core service functions are in the Server layer, including connections, query parsing, analysis, optimization, caching, and all built-in functions (for example, date, time, math, and encryption functions). All cross-storage engine functions are implemented in this layer: stored procedures, triggers, views, etc.

It is worth mentioning that the top service of Server is the connector, which has the functions of managing MySQL connections and authority verification. Obviously this is not unique to MySQL; most network-based client/server tools or services have a similar architecture.

2) The second layer is the storage engine (supports multiple storage engines such as InnoDB, MyISAM, Memory, etc.). The storage engine is responsible for storing and retrieving data in MySQL and responding to requests from upper-level servers. Each storage engine naturally has its advantages and disadvantages. Different storage engines cannot communicate with each other, so we need to choose the appropriate storage engine according to different scenarios.

The server communicates with the storage engine through an API. These interfaces shield the differences between different storage engines, making these differences transparent to the upper-level query process. The storage engine API consists of dozens of low-level functions for performing operations such as "start a transaction" or "fetch a row of records based on the primary key".

It should be noted that in MySQL 5.1 and earlier versions, MyISAM is the default storage engine, and after MySQL 5.5.5, InnoDB becomes the default storage engine.

2. Connector

The official documentation for MySQL 5.7 describes the connector as follows:

MySQL Connectors provide connectivity to the MySQL server for client programs.

The MySQL Connector provides client programs with connectivity to a MySQL server. To be more specific, the connector actually does two things: one is managing MySQL connections, and the other is permission verification. Let’s explain each one in turn.

First, to connect to the MySQL server, we usually need to provide the MySQL username and password, and if the server is running on a machine other than the one we are logged in from, we also need to specify a host name such as host. So the connection command is generally like this:

shell> mysql -h host -u user -p

Enter password: ********

Of course, if you are logged in on the same machine running MySQL, you can omit the host name and just use the following:

shell>mysql -u user -p

You should all be familiar with the above command.

OK, after completing the classic TCP three-way handshake to establish a connection through the above command, the connector will authenticate your identity based on the username and password you entered:

1) If the username or password is incorrect, you will receive an "Access denied for user" error and the client program will terminate execution.

2) If the username and password authentication is successful, you will see the following string of content:

mysql> is telling you that MySQL is ready and you can start entering SQL statements!

Of course, the connector does more than just compare the username and password; it also verifies whether the user has permission to execute a particular query (for example, whether the user is allowed to execute a SELECT statement on the Country table in the world database). After that, all permission judgment logic in this connection will depend on the permissions read at this time.

This means that once a user successfully establishes a connection, even if you modify the permissions of this user using an administrator account on another terminal, it will not have any impact on the permissions of the existing connection.

That is to say, after the user permissions are modified, only newly created connections will use the new permission settings.

When a connection is established, if you do not take any subsequent action, the connection is in idle state (Sleep).

In fact, for a MySQL connection (or a thread), there is a state at any time, which indicates what MySQL is currently doing. There are many ways to view the current status. The simplest is to use the SHOW FULL PROCESSLIST command (the Command column in the result returned by the command indicates the current status).

During the life cycle of a query, the state changes many times. I won’t list them in detail here. The Sleep state in the figure above means that the current connection is waiting for the client to send a new request, and Query state means that the current connection is executing a query or sending the result to the client.

In the default settings of MySQL, if a connection is in Sleep state for 8 hours (that is, it is not used for more than 8 hours), the server will disconnect the connection and all subsequent operations on the connection will fail. This time is controlled by the parameter wait_timeout :

Query Cache

OK, after the connection is established, we can enter the select statement for query. The execution logic comes to the second step: query cache.

The official documentation explains Query Cache as follows:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

That is, the query cache stores the text of the SELECT statement and the corresponding results responded to the client. That way, if the server receives the same SELECT statement later, the server retrieves the results from the query cache first, rather than parsing and executing the statement again. The query cache is shared between sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

If the current query happens to hit the query cache, MySQL will check the user permissions once before returning the query results. There is still no need to parse the query SQL statement, because the table information that the current query needs to access is already stored in the query cache.

Since cache is involved, the cache consistency issue cannot be avoided. Thankfully, querying the cache does not return stale data without any extra work on our part!

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

When a table is modified, any related entries in the query cache are flushed. Note that flushed here translates to clearing rather than refreshing.

Looks pretty good? The invalidation cache can be automatically cleared without our manual operation.

Unfortunately, due to this feature, starting from MySQL 5.7.20, the official no longer recommends the use of query cache, and directly deleted the query cache in MySQL 8.0!

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

In fact, it is not difficult to understand. For example, for a forum project with a large amount of traffic, the demand for querying the post table exists all the time, and the posts are increasing almost every moment. As long as this table is updated, all query caches on this table will be cleared. You can imagine the great pressure on the MySQL database. I took great pains to save the query results, but before I could use them, they were cleared out by an update.

For versions prior to MySQL 8.0, you can set the parameter query_cache_type to DEMAND so that all SQL statements will no longer use the query cache. For statements that you are sure to use query caching, you can explicitly specify it with SQL_CACHE , like the following statement:

mysql> select SQL_CACHE * from t1 where id = 1;

4. Parser

If there is no hit or the query cache is not enabled, the next thing the MySQL server does is to convert a SQL statement into an execution plan and then interact with the storage engine according to this execution plan. This includes multiple sub-phases: parsing SQL, preprocessing, and optimizing SQL execution plan. Any error during this process (such as a syntax error) may terminate the query.

Parsing SQL and preprocessing are what the parser does, and optimizing SQL execution plans is what the optimizer does. Here we first talk about the parser.

Here, the book "High Performance MySQL - 3rd Edition" divides it more finely. The parser is used to parse SQL, and the preprocessor is used for preprocessing. I will classify them all as parsers for now.

Parsing SQL means that MySQL parses SQL statements through keywords and generates a corresponding "parse tree" to verify whether the statement is correct according to grammatical rules. For example, it will verify whether the wrong keywords are used, or whether the keywords are used in the correct order, or whether the quotation marks are matched correctly.

The preprocessing will further check whether the parse tree is legal, for example, checking whether the data table and data columns exist, whether the table name and field name are correct, etc.

5. Optimizer

Now the parse tree is valid and MySQL knows what you are trying to do. However, a query can have many execution plans, and they all return the same results. So which execution plan should we choose?

Here is a simple example:

mysql> select * from t1 where id = 10 and name = "good";

For the statement above, you can first search for name = good and then search for id = 10, or you can first search for id = 10 and then search for name = good. The time costs of these two different execution plans may be different.

The role of the optimizer is to find the best execution plan among them. It should be noted that the execution plan here is a data structure, rather than generating corresponding bytecodes like many other relational databases.

In addition, the optimizer does not care what storage engine the table uses, but the storage engine does have an impact on optimizing queries. The optimizer will request the storage engine to provide capacity or cost information for a specific operation, as well as statistical information about table data.

When the optimizer phase is completed, the execution plan for the statement is finalized and the executor phase can begin.

6. Actuator

Just like hitting the query cache, before starting to execute the SQL statement, the executor will first determine whether the current user has the permission to execute the query on this table. If not, it will return an error indicating that the user does not have permission.

After the permission authentication is completed, MySQL will execute step by step according to the instructions given in the execution plan. In the process of step-by-step execution according to the execution plan, a large number of operations need to be completed by calling the interfaces implemented by the storage engine. These interfaces are what we call "handler API" interfaces.

Each table in the query is represented by an instance of handler. In fact, MySQL creates a handler instance for each table during the optimization phase. The optimizer can obtain relevant information about the table based on the interfaces of these instances, including all column names, index statistics, and so on.

For example:

mysql> select * from t1 where id = 10;

Assuming that we use the default InnoDB engine, the execution flow of the executor is roughly as follows (note that if id is not an index, a full table scan will be performed, searching row by row. If it is an index, the query will be made in the index organization table, which is more efficient. Here we take a non-index as an example):

1) Call the InnoDB engine interface to obtain the first row of records in this table, and determine whether the id value is 10. If it is, store this row of records in a set; if not, proceed to the next row of judgment until the last row of this table is obtained.

2) The executor returns the record set consisting of all rows that meet the conditions in the above traversal process as the result to the client

VII. Summary

Summarize the execution process of the next query statement:

1. A connection is established between the MySQL client and the server, and the client sends a query to the server;

2. The server first checks the query cache. If the cache is hit, the result stored in the cache is immediately returned; otherwise, it proceeds to the next stage;

3. The server performs SQL parsing and preprocessing to generate a legal parse tree;

4. The optimizer then generates the corresponding execution plan;

5.MySQL calls the corresponding storage engine API to execute according to the execution plan generated by the optimizer, and returns the execution result to the client.

The above is a detailed analysis of how a SQL query statement is executed in MySQL. For more information about how MySQL query statements are executed, please pay attention to other related articles on 123WORDPRESS.COM!