We write SQL statements almost every day. When we write a query statement, we get a query result. But what is the execution process inside MySQL?

mysql> select * from T where Id = 10;
Copy the code

Let’s take a look at the basic architecture of MySQL.



As a whole, MySQL is divided intoServer layerandStorage engine layerTwo parts.

The Server layer includes connectors, query caches, analyzers, optimizers, actuators, etc. It contains most of the core functions, as well as all the built-in functions (such as date, time, math and encryption functions, etc.). All functions that involve cross-storage engines are implemented in this layer, such as stored procedures, triggers, views, etc

The storage engine layer is responsible for storing and extracting data. Its architecture mode is plug-in, support InnoDB, MyISAM, Memory, etc. The most we use is InnoDB, as it is the default storage engine for MySQL after 5.5.5.

As you can see from the figure, the various storage engines share a Server layer, which runs from the connector to the actuator.

The connector

First, you connect to the database, and that’s where the connector comes in. Connectors establish connections to clients, obtain permissions, and maintain and manage connections. Connection command:

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

After entering the command, you need to enter the password.

Mysql is the client tool used to establish a connection with the server. After the classic TCP handshake, the connector authenticates the identity, that is, after we enter the username and password. If the user name or password is incorrect, the error “Access denied for user” will be displayed, which will end the client execution. If the user name and password are authenticated, the connector will go to the permission table to find out what permissions you have, and then the permission logic in the link will depend on the permissions you read at that point.

This means that when a user successfully establishes a connection, the permissions of the user are changed in a timely manner, and the current permissions are not affected. The new permissions are used only when a new connection is created.

After the connection is completed, if there is no other operation, the connection is idle, as shown in the figure:



If the client does not operate for a long time, the connector automatically disconnects. This time can be determined by parameterswait_timeoutControl, default is 8 hours.

A long connection in the database means that after a successful connection, the same connection is used all the time if the client continues to have requests. A short connection is when a connection is disconnected after a few queries are executed and then re-established the next time a query is executed. It is recommended to use a long connection because the process of establishing a connection is complicated.

The query cache

After the connection is established, you can execute the SELECT statement. Namely: query cache.

When MySQL receives a query request, it first checks the query cache to see if the statement has been executed before. If executed, the result is cached directly in memory as a key-value pair. If the key can be found in the cache, the corresponding value is returned directly to the client.

If it is not in the query cache, subsequent execution phases continue. After the execution is complete, the results are stored in the query cache.

While using query caching is efficient, it is not intrinsically good to use this approach. Because the query cache invalidates so frequently, whenever a table is updated, all the query cache on that table is cleared. So it’s possible to save a lot of results and get an update that clears them before they’re used…

analyzer

If the query cache is not hit, it is time to actually execute the statement to query.

First, mysql needs to know what the statement is doing, so it needs to parse the SQL statement. First the parser does a “lexical analysis”. MySQL needs to recognize what the multiple strings and Spaces we enter represent. MySQL recognizes that this is a query from the input “SELECT” keyword, “T” identifies as the table name “T”, “ID” identifies as “column ID”. Then “parsing”. The parser determines whether the entered SQL meets the MySQL syntax based on the syntax rules. You have an error in your SQL syntax.

The optimizer

After the analyzer analysis, MySQL knows what to do. Before execution can begin, the optimizer’s processing needs to be done.

The optimizer determines which index to use when there are multiple indexes in a table. Or, if a statement has multiple table associations, determine the join order of the tables. For example, if we have a join where two tables are associated:

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
Copy the code
  • 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 fetch the ID value of the record d=20 from table T2, and then associate it with table T1 according to the ID value, and then judge whether the value of C in T1 is equal to 10

The results of the two execution methods are the same, but the efficiency of the execution may be different. The role of the optimizer is to choose a more efficient way to execute.

After the optimizer phase is complete, the statement execution scheme is determined and the execution phase begins.

actuator

To start execution, determine whether the current user has permission to query table T. If not, an error with no permission is returned:



If permission is granted, the table is opened and execution continues. When a table is opened, the executor uses the interface provided by that engine based on the table’s engine definition.

For example, in table T, where the ID field has no index, the execution flow of the actuator would look like this:

  1. Call InnoDB engine interface to take the first row of the table and determine whether the ID value is 10. If not, skip it. If so, save this row in the result set.
  2. Call 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 of all rows that meet the criteria in the above traversal as a result set to the client.

This statement is done at this point.

For indexed tables, the execution logic is similar. The first call is the “fetch the first row that meets the condition” interface, and the next row that meets the condition interface is iterated, which is already defined in the engine.

The ROws_EXAMINED field, seen in the slow query log for the database, shows how many flights were scanned during this statement execution. This value is accumulated each time the executor calls the execution engine to retrieve the row.