Mysql Essay – How is an SQL query executed

Advice, I write blog is to sort out the learning materials, if there is a mistake, please give advice, if installed force, please roll off.

Generally speaking, Mysql is divided into Server layer and storage engine layer. As shown in the figure:Server layer including connector, query cache, analyzer, the optimizer, actuators, etc, covering most of the core service Mysql function, and all of the built-in functions (such as date, time, mathematics, and encryption function, etc.), all across the storage engine functions are realized in this layer, such as stored procedures, triggers, views, etc. The storage engine layer is responsible for data storage and extraction. The architectural pattern is plug-in.

The connector

Connectors are responsible for creating links to clients, obtaining permissions, and maintaining and managing links. Links can be long links and short links. Mysql is very time-consuming and complex to create links. So long links are recommended. However, using long links can cause mysql memory to explode. Since mysql manages these long links in memory, there are several solutions to these problems, and these solutions also depend on the company configuration and business form. Any speculation of leaving the business is hooliganism. 1, regularly disconnect long links, use for a period of time, or the program to determine the execution of a large query occupying memory, disconnect the link, and then query and re-link. 2. If you use Mysql5.7 or later, you can re-initialize the link resource by executing mysql_reset_connection after each large operation. This process does not require reconnection and re-authentication of permissions, but restores the link to the state it was in when it was created.

The query cache

Cache as the name suggests in mysql is actually a kv form of storage in memory. Each time mysql receives a query request, it checks the cache first. But caching is not recommended in most cases. Because cache invalidation is frequent, updating a table will clear the cache of all queries on that table. Caching was removed after mysql8.0, which means it is no longer available.

analyzer

If the cache is not hit, it is time to actually execute the statement. First, mysql will parse the SQL statement, and check the syntax.

The optimizer

The optimizer determines which index to use when there are multiple indexes in the table. Or determine the order in which tables are joined when a statement has multiple joins. For example, if you execute a statement like the following, this statement performs a join on two tables:

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 take the ID value of the record d=20 from table T2, and then associate it with T1 according to the ID value, and then judge whether the value of C in T1 is equal to 10. The logical result of the two execution methods is the same, but the efficiency of the execution will be different, and the role of the optimizer is to decide which one to use. After the optimizer phase is complete, the execution plan of the statement is determined, and then the executor phase is entered. If you have any questions, such as how the optimizer chooses the index, I haven’t figured it out yet and I don’t have time to write.

actuator

MySQL knows what you want to do from the parser, it knows how to do it from the optimizer, so it goes to the executor phase and starts executing statements. SQL > select * from table T; select * from table T; select * from table T; select * from table T; The query also calls Precheck to verify permissions before the optimizer). The executor returns the recordset of all rows that meet the criteria during the traversal as a result set to the client.

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
Copy the code

If you have permission, open the table and continue. When a table is opened, the executor uses the interface provided by the table engine according to its definition. For example, in table T of our example, the ID field has no index, then the execution flow of the executor is like this:

  1. Call InnoDB engine interface to fetch the first row of the table, check whether the ID value is 10, if not skip, if yes, save the 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. At this point, the statement is complete.