Shulou(Shulou.com)05/31 Report--

This article mainly introduces how the execution process of SQL statement in MySQL is. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

Generally speaking, MySQL logical architecture can be divided into two parts: server layer and storage engine layer. This article documents the execution process of SQL statements, mainly what happens at the server layer.

The server layer mainly includes most of the functions of the MySQL database, after all, most of the implementation of SQL is in this layer ^ _ ^. This layer can be subdivided into: query cache (query cache, sometimes referred to as QC), parser can also be called parser, preprocessor (which can also be classified as parser), query optimizer, and so on.

The main work of the storage engine layer is to store and extract data. At present, the main types of storage engines are: InnoDB,MyISAM,tokuDB and so on. The differences between them are as follows:

Among the two storage engines, InnoDB and MyISAM, InnoDB,MyISAM has been completely abandoned after version 8.0 because it does not support transactions and row locks. Starting with the MySQL5.5 version, InnoDB has become the default storage engine for MySQL databases.

Let's first come to the conclusion that after a SQL statement is initiated from the client to the MySQL database, it will first perform a series of verification (such as query / update permissions), then to the query cache, and then to the parser, query optimizer, and complete the operation of executing the statement to call the results from the storage engine and return them to the client.

1. Connect & & verify permissions

There are two main ways to connect to MySQL, one is initiated locally from the server, through root users, log in using socket, and the other is a remote connection that we initiate from the client. Generally speaking, there are several elements that you usually need to enter to connect to the database remotely: user name, password, port number, IP address.

First of all, the connection verifies whether the password is correct and whether the current user has the permission to log in in the current IP. After the login is completed, the login user will have the permissions currently assigned to him (addition, deletion, modification, query, etc.).

Second, query cache

After the verification in the previous step is completed, SQL execution will go to the query cache (query cache) to see if the SQL statement has been executed to cache the SQL execution result. If so, the result will be directly fetched and returned to the client. If not, proceed to the next query.

Admittedly, if you can get the data in the query cache every time, this efficiency is very high. However, for practical applications, query caching is not recommended. Because the query cache can only be aimed at static data, not dynamic data, it is very likely that the cached data is not easily cached, in which some tables are updated, and the cached data is cleared out at once. This feature has also been completely abandoned after version 8.0, and it is generally recommended that query caching be turned off in versions before 8.0.

3. Parser

If you fail to get or miss the query cache in step 2, you will first perform SQL parsing, including syntax, semantic parsing: whether to query or update, whether the statement is written with no problem, and so on. Generate a parsing tree and submit it to the next step.

4. Preprocessor (in fact, it can also be classified as a parser)

Preprocessing the parsed tree in the third step will make a deeper judgment than the third part, such as whether the table / column exists or not, and will process the parsing tree into a more balanced tree structure.

Query optimizer

After the preprocessing is completed, we go to the query optimizer, where we decide, such as which index to go, transform the previous balanced tree into an execution plan, and select an execution plan that the database considers to be the best for SQL execution.

VI. SQL implementation

The required data is fetched from the storage engine layer and returned to the client, at which point the process is over.

These are all the contents of the article "what is the execution process of SQL statements in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.