01 Basic Structure How a SQL Query Sentence Is Executed

01 Basic Structure How a SQL Query Sentence is Executed #

This is the first article in the series, and I want to talk to you about the basic infrastructure of MySQL. We often say that when looking at something, don’t immediately dive into the details. You should first have an overview of the whole to help you understand the problem from a high-dimensional perspective. Similarly, learning MySQL is the same. Usually, when we use a database, we usually see it as a whole. For example, if you have a simplest table with only one ID field, and when executing the following query statement:

mysql> select * from T where ID=10;

What we see is just inputting a statement and returning a result, but we don’t know the execution process of this statement internally in MySQL.

So today I want to break down MySQL with you to see what “components” are inside. Through this process of breaking down, I hope to provide you with a deeper understanding of MySQL. So when we encounter any abnormalities or problems with MySQL, we can directly hit the core and quickly locate and solve the problem.

Below is a basic architecture diagram of MySQL, from which you can clearly see the execution process of an SQL statement in various functional modules of MySQL.

img

MySQL’s logical architecture diagram

Generally speaking, MySQL can be divided into two parts: the Server layer and the storage engine layer.

The Server layer includes the connection, query cache, parser, optimizer, executor, and so on, covering most of MySQL’s core service functions, as well as all built-in functions (such as date, time, mathematical, and encryption functions, etc.). All cross-storage engine functionalities are implemented in this layer, such as stored procedures, triggers, views, etc.

The storage engine layer is responsible for data storage and retrieval. Its architecture is plugin-based, supporting multiple storage engines such as InnoDB, MyISAM, and Memory. The most commonly used storage engine now is InnoDB, which has become the default storage engine since MySQL version 5.5.5.

In other words, when you execute the create table command, if you do not specify an engine type, the default engine used is InnoDB. However, you can also specify a different engine by specifying the engine type, such as using engine=memory in the create table statement to specify the creation of a table using the memory engine. Different storage engines have different ways of storing and retrieving table data, and support different functionalities. In the subsequent articles, we will discuss the selection of engines.

From the diagram, it is not difficult to see that different storage engines share one Server layer, which is the part from the connector to the executor. You can have a general impression of the name of each component, and next, I will guide you through the entire execution process, step by step, with the SQL statement mentioned at the beginning, to see the roles of each component.

Connector #

First, you will connect to the database, and the one who receives you is the connector. The connector is responsible for establishing a connection with the client, obtaining permissions, and maintaining and managing the connection. The connection command is generally written as:

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

After entering the command, you need to enter the password in the interactive dialogue. Although the password can also be directly written after -p in the command line, doing so may expose your password. If you are connecting to a production server, it is strongly recommended not to do this.

The mysql in the connection command is a client tool used to establish a connection with the server. After completing the classic TCP handshake, the connector begins to authenticate your identity, using the username and password you entered.

  • If the username or password is incorrect, you will receive an “Access denied for user” error, and the client program will end.
  • If the username and password authentication is successful, the connector will look up the permissions you have in the permission table. Afterwards, the permission judgment logic in this connection will rely on the permissions read at this time.

This means that after a user successfully establishes a connection, even if you modify the permissions of this user with an administrator account, the existing connection’s permissions will not be affected. After the modification is completed, only newly established connections will use the new permission settings.

After the connection is established, if you have no further actions, the connection will be in the idle state, which you can see in the show processlist command. In the text, the row with “Sleep” displayed in the Command column represents an idle connection in the system.

If the client is inactive for too long, the connector will automatically disconnect it. This time is controlled by the wait_timeout parameter, with a default value of 8 hours.

If the client sends another request after the connection is disconnected, it will receive an error message: “Lost connection to MySQL server during query”. In this case, if you want to continue, you need to reconnect and then execute the request.

In the database, a long connection refers to using the same connection if the client continues to send requests after a successful connection. A short connection, on the other hand, refers to disconnecting after executing a few queries and establishing a new connection for the next query.

The process of establishing a connection is usually complex, so I recommend minimizing the actions of establishing connections during use, in other words, try to use long connections as much as possible.

However, if you use long connections exclusively, you may find that MySQL’s memory usage increases rapidly. This is because the temporary memory used by MySQL during the execution process is managed within the connection object. These resources are released only when the connection is disconnected. Therefore, if long connections accumulate, it may cause excessive memory usage and be forcibly killed by the system (OOM), resulting in abnormal restart of MySQL.

How do you solve this problem? You can consider the following two solutions.

  1. Disconnect long connections regularly. Use them for a period of time, or if your program judges that it has executed a memory-intensive query, disconnect the connection and reconnect when querying is needed again.
  2. If you are using MySQL 5.7 or a newer version, you can reinitialize connection resources by executing mysql_reset_connection after executing a relatively large operation. This process does not require reconnection or re-authorization, but it will restore the connection to the state just after it was created.

Query Cache #

After the connection is established, you can execute select statements. The execution logic will come to the second step: query cache.

After receiving a query request, MySQL checks the query cache first to see if the statement has been executed before. Previously executed statements and their results may be directly cached in memory as key-value pairs. The key is the query statement, and the value is the query result. If your query can find the key directly in the cache, the corresponding value will be returned to the client directly.

If the statement is not in the query cache, the execution continues to the subsequent stages. After the execution is completed, the result will be stored in the query cache. As you can see, if the query hits the cache, MySQL does not need to execute complex operations afterwards and can directly return the result, which greatly improves efficiency.

However, in most cases, I would advise against using the query cache. Why? Because the drawbacks of the query cache often outweigh the benefits.

The query cache is invalidated very frequently. Whenever there is an update to a table, all query caches on that table will be cleared. Therefore, it is very likely that you store the results with great effort, but they are cleared by an update before you can use them. For databases with high update pressure, the hit rate of the query cache will be very low. Unless your business involves a static table that is updated only rarely, such as a system configuration table, queries on this table are suitable for using the query cache.

Fortunately, MySQL also provides this “on-demand” approach. You can set the query_cache_type parameter to DEMAND so that the query cache is not used for default SQL statements. For statements where you are certain to use the query cache, you can explicitly specify it with SQL_CACHE, like the following statement: mysql> select SQL_CACHE * from T where ID=10;

Note that starting from MySQL 8.0, the entire query cache feature has been removed. In other words, this feature no longer exists from version 8.0 onwards.

Parser #

If the query cache is not hit, the statement execution process will begin. First, MySQL needs to know what you want to do, so it needs to parse the SQL statement.

The parser starts with “lexical analysis”. The SQL statement you input is composed of multiple strings and spaces, and MySQL needs to recognize what each string represents.

MySQL recognizes that you are entering a “select” keyword, indicating that it is a query statement. It also recognizes the string “T” as the “table T”, and the string “ID” as the “column ID”.

After completing this recognition, the “syntax analysis” is performed. Based on the results of lexical analysis, the syntax analyzer determines whether the SQL statement you entered complies with the MySQL syntax based on the syntax rules.

If your statement is incorrect, you will receive an error message saying “You have an error in your SQL syntax”. For example, the statement below is missing the initial letter “s” in “select”.

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

Syntax errors usually prompt the position of the first occurrence of the error, so you should pay attention to the content immediately after “use near”.

Optimizer #

After the analysis phase, MySQL knows what you want to do. Before starting execution, it needs to go through the optimizer.

When there are multiple indexes in a table, the optimizer determines which index to use. Likewise, when a statement involves multiple table joins, the optimizer decides the order of table joins. For example, consider the following statement that joins two tables:

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
  • The optimizer can first retrieve the ID values of the records in table t1 where c=10, then use those ID values to join with table t2, and finally check if the value of d in table t2 equals 20.
  • The optimizer can also first retrieve the ID values of the records in table t2 where d=20, then use those ID values to join with table t1, and finally check if the value of c in table t1 equals 10.

Both methods produce the same logical result, but the efficiency of execution can vary. The role of the optimizer is to determine which approach to choose.

Once the optimizer phase is completed, the execution plan for the statement is determined, and the execution phase begins. If you still have questions, such as how the optimizer selects indexes or the possibility of selecting the wrong index, don’t worry. I will explain the optimizer in detail in a separate article.

Executor #

MySQL knows what you want to do through the parser, and it knows how to do it through the optimizer. Consequently, it enters the executor phase and starts executing the statement.

At the beginning of execution, the executor checks whether you have the necessary privileges to perform the query on table T. If not, it returns an error indicating the lack of permission, as shown below (in terms of implementation, if the query cache is hit, permission verification is done when returning the result from the cache. Pre-checks for permissions are also done before the optimizer calls the query):

mysql> select * from T where ID=10;
 
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

If you have the necessary privileges, the executor proceeds to open the table and continue the execution. When opening the table, the executor uses the interface provided by the underlying storage engine, based on the table’s engine definition.

In the case of our example table T with no index on ID, the execution process of the executor is as follows:

  1. The executor calls the InnoDB engine interface to retrieve the first row of the table and checks whether the ID value is 10. If it’s not, the row is skipped. If it is, the row is added to the result set.
  2. The executor calls the engine interface to retrieve the “next row” that satisfies the conditions, repeating the same check logic until the last row of the table is reached.
  3. Finally, the executor returns the set of all rows that satisfy the conditions during the above iteration as the result set to the client.

At this point, the execution of the statement is complete.

For tables with indexes, the logic of execution is similar. The first call is made to the interface that retrieves the first row satisfying the conditions, followed by a loop calling the interface to retrieve the “next row” that satisfies the conditions. These interfaces are already defined by the underlying engine.

In the slow query log of the database, you will see a field called “rows_examined”, which shows the number of rows scanned during the execution of the statement. This value is incremented each time the executor calls the engine to retrieve a data row.

In some scenarios, one call from the executor may result in scanning multiple rows within the engine. Therefore, the number of rows scanned by the engine may not be exactly the same as the value of rows_examined. We will have a separate article in the future dedicated to explaining the internal mechanism of storage engines, where this will be explained in detail.

Summary #

Today, I introduced you to the logical architecture of MySQL, hoping that you now have a preliminary understanding of the various stages in the complete execution process of an SQL statement. Due to space limitations, I could only go through each stage with a single query example. If you have questions about the details of each stage, don’t worry, I will mention them again in the practical sections later.

Let me leave you with a question: If the column k does not exist in table T, and you execute the statement select * from T where k=1, it will definitely throw an error saying “Unknown column ‘k’ in ‘where clause’”. In your opinion, in which of the stages mentioned above is this error thrown?