Do you know what it takes to execute a simple SELECT statement?

First of all, mysql is mainly composed of two parts: server layer and storage layer. The Server layer consists of connectors, query caches, analyzers, optimizers, and actuators. Storage layer is mainly used to store and query data. Common storage engines include InnoDB and MyISAM. After MySQL 5.5.5, InnoDB is used as the default storage engine.

The connector

The connector is responsible for connecting the mysql client to the server. After the connection is successful, the connector obtains the permission of the current user. The permissions obtained here are valid for the entire connection. Once the connection is successful, if you change the permissions of the user using the administrator account, the permissions in the current connection remain unchanged and will be updated only after the next connection is reconnected.

The query cache

SQL > select (select); SQL > select (select); SQL > select (select);

If there is no cache, the execution continues and the results and statements are stored in the cache.

Note that the query cache is no longer available after mysql8 because it is very easy to flush and the hit ratio is low. Every time you update a table, all of the cache on that table is cleared, so the content you just cached is cleared by another update before you can use it.

analyzer

Since the cache is not found, you need to start executing the SQL statement, which must be parsed before execution. The analyzer mainly analyzes the syntax and semantics of SQL statements, checks for spelling errors, and checks for the existence of tables or fields to be queried.

If the parser detects an error, it returns an error message like “You have an error in your SQL “and terminates the query.

The optimizer

After passing the parser, mysql understands what you want to do. Generally, there may be multiple execution schemes in mysql for the same SQL statement, such as which index to select when there are multiple indexes, and how to confirm the join order of each table when multiple tables are associated with query.

The execution results of these schemes are the same, but the execution efficiency is different, so mysql needs to try to find an optimal scheme before execution, which is the main work of the optimizer. However, mysql can sometimes choose the wrong scheme. I won’t go into details here, but I’ll explain why later.

actuator

After the optimizer selects a scenario, the executor executes the SQL statement according to the scenario selected. As we mentioned earlier, in the connector, permissions are read for the current user. In the connector, permissions are only obtained, and permissions are not judged or verified.

Therefore, in the executor, the permission is determined before the statement is executed, and if there is no corresponding permission, it is directly returned with a message indicating that there is no related permission.

Here you may ask, why not in the connector will directly determine permissions, here I think probably because the table is not necessarily limited to mysql to query in the SQL statement literally those tables, sometimes takes after analyzer and optimizer to determine exactly how to perform, so permission to check in the actuator is justified.

Note that if the cache is found in the previous query cache, permissions are checked before the result is returned.

After permissions are verified, the table is opened and the interface provided by the storage engine is called to query and return result set data.

At this point, a query SQL statement completes execution. Each step of the process is very complex to implement in mysql. We will talk about the underlying implementation principle of index.