I believe that before you understand mysql, there must be no small doubt about the execution of SQL statements! I used to naively think that mysql server was only able to judge data and write it into a table. After learning, I realized the logic behind SQL statements
Let’s learn how to execute mysql today
Software architecture
- Client: We usually connect through JDBC driver or use graphical tools, which are essentially tools for transferring SQL statements (DLL, DML) and communicating with mysql server on the server side (Mysql four communication modes), in the learning stage, our mysql installation address is generally installed locally, that is, the client and server are installed on the same machine. So they use Unix sockets to communicate directly between local computer processes (Two ways for processes to communicate)
- Server: Before a server can receive a client SQL statement, they previously need to establish a connection (Long connection or short connection) –The connector. After the connection is successful, the client can communicate with the server. When the server receives an SQL statement, it checks the syntax and semantics of the statement.This field includes whether the table exists in the database) –The parser, SQL compliance will be optimized to get the execution plan, select index —The optimizer, the execution engine calls the API provided by the storage engine to execute statementsactuator. These different components interpret and optimize the execution of a statement, one layer down in the Server layerThe storage engineAn SPI can be implemented by different vendors, or we can customize the implementation. MyISAM, InnoDB, MEMORY, MERGE
The connector
The connector is responsible for user login to the database and user identity authentication, including account password verification and permission verification. If the user password is denied, you will receive an error saying “Access denied for user”, and the client program will stop executing. If the user account password has passed, the connector will query the user to access list all permissions, in this connection the permissions after logic are will depend on the read permissions on the data at this time, that is, subsequent as long as the connection open, instant administrator changed the user permissions, the user is not affected.
Query cache
After the connection between the client and server is established, MySQL queries the cache before executing the query statement to verify whether the SQL statement has been executed before. Previously executed statements and their results are cached directly in memory as key-value pairs. Key is the query statement and value is the query result. If your query can find the key directly in the cache, the value will be returned directly to the client. If there is no hit, subsequent operations need to be performed, and the results are cached for the convenience of the next call. Is it going to be a little bit of a shock, is it going to be a good impulse to take advantage of it. The use of the query cache is not recommended here. The query cache invalidates very frequently, and whenever a table is updated, all the query cache on that table will be cleared. So it’s likely that you struggled to save the results, only to have them all wiped out by an update before you could use them. For databases under pressure to update, the hit ratio of the query cache can be very low. Unless it is a table that will not be updated for a long time, such as the system configuration table, but this system configuration we put on the configuration platform is not good? The query caching feature was removed from MYSQL8.0, but it was not used in many practical scenarios. If Mysql does not hit the query cache, it enters the parser, which is mainly used to analyze the SQL statement for what purpose. The analyzer is divided into the following two steps:
Lexical analysis: an SQL statement consists of multiple strings. The first step is to extract keywords, such as SELECT, propose the table to be queried, propose the field name, propose the query conditions, and so on. Syntax analysis: According to the result of syntax analysis, the main purpose of syntax analysis is to determine whether the SQL statement You entered is correct, whether it conforms to the MYSQL syntax. If your statement is incorrect, You will receive an error message “You have an error in your SQL syntax”.
The lexical analyzer breaks the entire query into flags, and the syntax analyzer converts “flags” into combinations that make sense for MySQL based on the system language defined. Finally, the system generates a syntax tree (AST), which is the data structure on which the optimizer depends.
The optimizer
After a profiler, MySQL knows what you need to do. Before execution can begin, it needs to be processed by the optimizer. Why do you need an optimizer?
The optimizer contains many complex optimization techniques, often more than the best programmers can master. Automatic optimization of the system is equivalent to making these optimization techniques available to everyone.
The optimizer can obtain many statistics from the data dictionary, such as the number of rows in the table, the distribution of each column in the table, and so on. Optimizer Optimizers can consider hundreds of different execution plans, whereas programmers can generally consider only a limited number of possibilities;
An effective execution plan can be selected based on this information, which is difficult for user programs to obtain;
In short, the optimizer is to modify the syntax analysis tree morphology, change the syntax analysis tree into a query tree, determine the execution plan.
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. At the start of the query, verify whether the user has the permission to execute the query. If not, an error is returned indicating that the user has no permission. If it has permission, it calls the engine’s interface and returns the result of the interface’s execution. Above reference author: Misty Jam link: juejin.cn/post/684490…
The storage engine
Different storage engines have the same method entry, which makes the logic code of the server layer executor highly reusable
The next article updates the details of the various execution engines
I am a new blogger, if there are mistakes above welcome to correct