A brief discussion on the mysql execution process and sequence

Preface: MySQL is something we face almost every day in our development. As the source of data in development, MySQL is responsible for storing data and reading and writing data. Because learning and understanding MySQL is crucial, what kind of process does it go through when we initiate a SQL statement on the client and detailed query data appears? How does the MySQL server process requests and execute SQL statements? This blog will explore this issue:

1:mysql execution process

The overall execution process of mysql is shown in the following figure:

1.1: Connectors

The main responsibilities of a connector are:

① Responsible for communicating with the client, it is half-duplex mode, which means that at a fixed time, only the client can request the server or the server can send data to the client, but not at the same time. MySQL is connected to the client through TC/IP

②Verify whether the account and password of the requesting user are correct. If the account and password are incorrect, an error will be reported: Access denied for user 'root'@'localhost' (using password: YES)

③If the user's account and password are verified, the current user's permissions will be queried in the MySQL's own permission table:

There are four tables in MySQL that control permissions,分別為user表，db表，tables_priv表，columns_priv表, The verification process of the MySQL permission table is as follows:

1: User表: stores user account information and global level (all database) permissions, which determines which users from which hosts can access the database instance

• Db table: stores數據庫級別permissions, which determines which users from which hosts can access this database
• Tables_priv table:存放表級別的權限, which determines which users from which hosts can access this table in the database
• Columns_priv table:存放列級別的權限, which determines which users from which hosts can access this field in the database table
• Procs_priv table:存放存儲過程和函數level permissions

2: First, determine whether the connected IP, username, and password exist from the three fields Host, User, and Password in the user table. If they exist, the verification is passed.

3: After identity authentication, permissions are assigned and verified in the order of user, db, tables_priv, and columns_priv. That is, first check the global permission table user. If the corresponding permission in user is Y, then the permission of this user on all databases is Y, and db, tables_priv, and columns_priv will no longer be checked. If it is N, check the specific database corresponding to this user in the db table and obtain the permission Y in db. If db is N, check the specific table corresponding to this database in tables_priv and obtain the permission Y in the table, and so on.

4: If the permission verification fails in any process, an error will be reported

1.2: Cache

The main function of MySQL cache is to improve query efficiency. The cache is stored in the form of a hash table of key and value. The key is a specific SQL statement and the value is a collection of results. If the cache cannot be hit, it will continue to the analyzer step. If the cache is hit, it will be returned directly to the client. However, it should be noted that after version 8.0 of MySQL, the cache was officially deleted. The reason for deletion is that the query cache is invalidated very frequently. If in an environment with more writes than reads, the cache will be frequently added and invalidated. For some databases with heavy update pressure, the query cache hit rate will be very low. In order to maintain the cache, MySQL may have certain scalability issues. Currently, it has been disabled by default in version 5.6. A more recommended approach is to put the cache on the client, which will improve performance by about 5 times.

1.3: Analyzer

The main function of the analyzer is to analyze the SQL statements sent by the client, which will include preprocessing and parsing. At this stage, the semantics of the SQL statements will be analyzed, and keywords and non-keywords will be extracted and parsed to form a parse tree. Specific keywords include but are not limited to the following: select/update/delete/or/in/where/group by/having/count/limit, etc. If a syntax error is found during analysis, an exception will be thrown directly to the client: ERROR: You have an error in your SQL syntax.

For example: select * from user where userId =1234;

In the analyzer, the keywords such as select from where are extracted and matched through the semantic rule generator. MySQL will automatically determine the keywords and non-keywords, and identify the user's matching fields and custom statements. Some checks will also be done at this stage: for example, checking whether the user table exists in the current database. If the userId field does not exist in the User table, an error will also be reported: unknown column in field list.

1.4: Optimizer

Being able to enter the optimizer stage means that the SQL statement complies with the standard semantic rules of MySQL and can be executed. This stage mainly optimizes the SQL statement, makes the best choice based on the execution plan, matches the appropriate index, and selects the best execution plan. For example, a typical example is this:

Table T, create a joint index for columns A, B, and C. When querying, when the SQL query result is: select xx where B=x and A=x and C=x. Many people think that the index is not used, but in fact it is used. Although the index must comply with the leftmost principle to be used, in essence, the optimizer will automatically optimize this SQL to: where A=x and B=x and C=X. This optimization will enable the underlying layer to match the index. At the same time, at this stage, it is automatically preprocessed according to the execution plan. MySQL will calculate the best time for each execution method and finally determine an executed SQL to be handed over to the final executor.

1.5: Actuator

In the executor stage, the storage engine API is called. The API calls the storage engine. There are mainly the following storage engines, but MyISAM and InnoDB are commonly used:

The engine was previously called: Table Processor (actually, I think this name better expresses the meaning of its existence). It is responsible for operating specific data files, analyzing the semantics of SQL such as select or update, and performing specific operations. After execution, the specific operation will be recorded in binlog. One thing to note is that select will not be recorded in binlog, only update/delete/insert will be recorded in binlog. The update will use a two-phase commit method, and the records are all in the redolog

2: Execution status

You can use the command: show full processlist to display all processing processes, which mainly include the following status, indicating the status of the server processing the client. The status includes the process from the client initiating the request to the background server processing, including the locking process, statistical storage engine information, sorting data, searching intermediate tables, sending data, etc. It covers all the states of MySQL, the specific meanings of which are as follows:

Three: SQL execution order

In fact, SQL is not executed from front to back or from left to right in the order we write it. It is parsed in a fixed order. Its main function is to return the results from the previous stage for use in the next stage. SQL will have different temporary intermediate tables during execution, generally in the following order:

Example: select distinct s.id from T t join S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2 order by s.create_time limit 5;

3.1: from

The first step is to select the table following the from keyword, which is also the first step of SQL execution: indicating which table to execute from the database.

Example description: In this example, we first find table T from the database

3.2: join on

join indicates the table to be associated, and on is the condition for the connection. The database tables T and S that need to be executed are selected through from and join on, a Cartesian product is generated, and a temporary intermediate table Temp1 that merges T and S is generated. on: Determine the binding relationship of the table and generate a temporary intermediate table Temp2 through on.

Example description: Find table S, generate temporary intermediate table Temp1, then find the parts of table T with the same id as S to form table Temp2, which contains all the data with the same id as T.

3.3:where

Where means filtering, filtering is performed according to the conditions after where, and filtering the required data from the temporary intermediate table Temp2 according to the values ​​of the specified fields (if there is an and connector, joint filtering will be performed). Note that if no data is found at this stage, it will be returned directly to the client and will not proceed further. This process will generate a temporary intermediate table Temp3. Note that you cannot use aggregate functions in where. Aggregate functions are mainly (min\max\count\sum and other functions)

Example description: Find the data of table T with name="Yrion" in the temp2 temporary table collection. After finding the data, it will become a temporary intermediate table Temp3, which contains all the table data with name column "Yrion"

3.4: group by

Group by is to group, and the temporary table Temp3 after filtering by the where condition is grouped according to fixed fields to generate a temporary intermediate table Temp4. In this process, only the order of the data changes, and the total amount of data does not change. The data in the table exists in the form of groups.

Example description: Group mobile in the temp3 table data, find the same data of mobile, and then put them together to generate the temp4 temporary table.

3.5:Having

Aggregate the temporary intermediate table Temp4, which can be counted, and then generate the intermediate table Temp5. At this stage, you can use the alias in the select

Example description: Find the data with more than 2 entries in the temp4 temporary table. If it is less than 2, it will be discarded directly, and then a temporary intermediate table temp5 will be generated.

3.6:select

Select the data to be queried from the grouped and aggregated table. If it is *, it will be parsed as all data, and an intermediate table Temp6 will be generated.

Example description: At this stage, the id in the S table of the temp5 temporary aggregation table is filtered to generate Temp6. At this time, temp6 only contains the id column data of the s table and name="Yrion". The data with a number greater than 2 in the mobile grouping

3.7:Distinct

Distinct removes duplicates from all data. If there are min and max functions, the field function calculation will be performed, and then a temporary table Temp7 will be generated.

Example description: In this stage, the data in temp5 is deduplicated. The engine API will call the deduplication function to filter the data, and finally only retain the data where the id appears for the first time, and then generate a temporary intermediate table temp7

3.8:order by

It will be sorted in order or in reverse order according to Temp7, and then inserted into the temporary intermediate table Temp8. This process is more resource-consuming.

Example description: This will sort all data in the temp7 temporary table by creation time (create_time), and there will be no column or row loss in this process

3.9:limit

Limit paginates the intermediate table Temp8, generates a temporary intermediate table Temp9, and returns it to the client.

Example description: sort the data in temp7, then take the first five records and insert them into the temporary table Temp9, and finally return them to the client

ps: In fact, this process is not absolutely like this. MySQL will have some optimizations in the middle to achieve the best optimization effect, such as selecting the found data set

IV. Summary

This blog summarizes the execution process of MySQL and the execution order of SQL. Understanding these will help us optimize SQL statements and understand the trajectory of SQL statements in MySQL from writing to final execution. This will help us have a deeper and more detailed understanding of SQL and improve our ability to understand databases. At the same time, it will have a certain degree of significance for the execution process and writing of complex SQL.

This is the end of this article about the MySQL execution process and sequence. For more relevant MySQL execution process and sequence content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!