Mysql >select * from table where id=1; What is the specific execution process of such a query statement?

1. Schematic diagram of mysql basic framework

1.1. Client

The client can have various GUI tools on Windows, and mysql Client on Linux

1.2 Connector

Connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections. The connection command is as follows:

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

After you have typed the command, you will need to enter the password in the interactive dialog. Although it is possible to write the password directly after -p in the command, this may expose the password and is highly recommended against doing so. After completing the classic TCP three-way handshake, the connector authenticates your identity.

  • If the user name or password is wrong, you will receive oneAccess denied for userThe client program then terminates execution
  • If the username and password authentication is successful, the connector will check the list of permissions you have. After that, all the permission determination logic within the connection will depend on the permissions set read at this point.

When the connection is complete, you can view it by running the show processlist command. If the Command column displays “Sleep”, it indicates that this is a free connection.



Database connection

  • Persistent connection: After the connection is successful, if the client continues to receive requests, the same connection is used.
  • Short connection: the port is connected after a few queries are executed each time, and then a new one is established in the next query.

Note: The process of establishing a connection is usually more complex, so it is recommended to minimize the action of establishing a connection in use, that is, to try to use a long connection.

However, after using all persistent connections, you will find that mysql’s memory usage sometimes increases very quickly, because mysql temporarily uses memory during execution, which is also managed in the connection object. These resources are released when the connection is disconnected. (OOM) mysql restart (OOM) mysql restart (OOM) The solution

  1. Disconnect the long connection periodically. Use a period of time, or the program to determine the execution of a large memory size query, disconnect, after the query and reconnect.
  2. If you use themysql5.7Or newer versions can be executed at a time after a relatively large operationmysql_reset_connectionTo reproduce the initial connection resource. This process does not require reconnection or permission verification, but restores the connection to the state it was in when it was created.

The query cache

  • Once the connection is established, you can executeselectThe statement.mysqlWhen a query request is received, it goes to the query cache to see if the statement has been executed before. Previously executed statements and their results may be metkey-valueIn this case, it is cached directly in memory.keyIs the statement of the query,valueIs the result of a query. If your query can be found directly in the cachekey, thenvalueWill be returned directly to the client.
  • However, the use of the query cache is not recommended because the query cache is only updated for this table, and all the query caches on this table will be cleared. For databases that are heavily updated, the hit ratio of the query cache can be very low. And MySQL8.0 began to directly remove the entire query cache function.

analyzer

If the query cache is not hit, it is time to actually execute the statement. First, MySQL needs to know what to do, so it needs to parse the SQL statement. The analyzer starts with a lexical analysis. A command is a statement composed of multiple strings and Spaces. MySQL needs to identify what the strings are and what they stand for. MySQL recognizes that this is a query statement from the select keyword input. It also identifies the string TABLE as the table name and the string ID as the column ID. After these recognition, it is necessary to do lexical analysis. Based on the results of the lexical analysis, the parser will determine whether the SQL statement you entered meets the MySQL syntax based on the syntax rules. You have an error in your SQL syntax. For example, select is missing the letter ‘s’.

MariaDB [test]> elect * from table;    
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'elect * from table' at line 1
Copy the code

A common syntax error will indicate the first place where the error occurred, so focus on the content immediately following the use near.

The optimizer

After the profiler, MySQL will know what you want to do. Before the execution begins, it must be processed by the optimizer. The optimizer determines which index to use if there are multiple indexes in the table. Or when multiple tables are associated in a statement, determine the join order of each table. For example, you execute the following statement, which executes a join on two tables:

select * from table1 join table2 using(ID) where tables1.c=10 and table2.d=20;

  • It can first fetch the record ID value c=10 from Table1, and then manage the table table2 according to the ID value, and then judge whether the value of D in Table2 is equal to 20.
  • In table1, the value of c in table1 is equal to 10. In table1, the value of C is equal to 10.

The logical result of these two implementations is the same, but the efficiency of execution is different, and the optimizer’s role is to decide which one to use.

actuator

MySQL knows what to do through the profiler, it knows what to do through the optimizer, and so it goes to the executor phase and starts executing statements. If you do not have permission to perform a query on table T, you will return no permission. If you have permission, open the table and continue executing. When a table is opened, the actuator uses the interface provided by the engine according to the engine definition of the table. How about the following example:

select * from T where ID=10;

For example, in our example, table T, ID field has no index, then the execution process of the actuator is as follows:

  1. Call the InnoDB engine interface to fetch the first row of the table, check whether the ID value is 10, if it is not, skip, if it is, put the row in the result set;
  2. The call engine interface takes the “next row” and repeats the same judgment logic until it reaches the last row of the table.
  3. The executor returns the recordset of all the rows that satisfy the condition during the above traversal to the client as the result set.

At this point, the statement is executed. For tables with indexes, the logic is similar. The first call is the “get the first line that satisfies the condition” interface, and then the loop is the “get the next line that satisfies the condition” interface, which is defined in the engine. You’ll see a field in the slow query log of your database called rows_examined that shows how many rows were scanned during the execution of this statement. This value is added each time the actuator calls the engine to retrieve the data row. In some scenarios, when the executor is called once, multiple rows are scanned inside the engine, so the number of rows scanned by the engine is not exactly the same as rows_examined.