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

This article mainly explains "the example analysis of the execution process of MySQL query sentence". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the example analysis of the execution process of MySQL query sentence".

1. MYSQL architecture

MySQL can be divided into Server layer and storage engine layer.

The Server layer includes connector, query cache, analyzer, optimizer, executor, etc. All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, functions, etc., as well as a general logging module, binlog logging module.

The storage engine layer is responsible for data storage and extraction. Its architecture mode is plug-in, supporting multiple storage engines such as InnoDB, MyISAM, Memory and so on. The most commonly used storage engine today is InnoDB (which supports transactions), which has become the default storage engine since the MySQL5.5.5 version.

2. Connector

The connector is mainly responsible for user login database, user identity authentication, including checking account password, permissions and other operations.

If the user's password is incorrect, you will receive a "Access denied for user" error and the client program finishes execution.

If the user account password has been passed, the connector will query all the permissions of the user in the permission table, and then the permission logic judgment in the connection will rely on the permission data read at this time, that is to say, as long as the connection continues to open, the administrator will not be affected as soon as the administrator modifies the user's permissions.

3. Query cache (Query cache)

After the client establishes a connection with the server, MySQL will first query the cache when executing the query statement to verify whether the SQL has been executed before. Previously executed statements and their results are directly cached in memory in the form of key-value pairs. Key is the statement of the query and value is the result of the query. If your query can find key directly in this cache, then the value will be returned directly to the client. If there is no hit, the subsequent operation needs to be performed, and the result will be cached after completion to facilitate the next call.

See here, will everyone shine in front of their eyes, and will they have the impulse to make good use of this function?

In fact, the use of query cache is not recommended here. Query cache invalidation is very frequent. As long as there is an update to a table, all query caches on that table will be emptied. So it's possible that you struggled to save the results and were completely emptied by an update before you could use them. For databases with high update pressure, the hit rate of the query cache will be very low. Unless it is the kind of table that will not be updated for a long time, such as the system configuration table, but is it not good for us to put this system configuration on the configuration platform?

Query caching has been deleted in MYSQL8.0, and officials believe that this feature is relatively rare in actual application scenarios, so it is simply deleted.

4. Analyzer

If Mysql misses the query cache, it goes to the parser, which is mainly used to analyze what the SQL statement is for. The analyzer is mainly divided into the following two steps:

Lexical analysis: a SQL statement is composed of multiple strings, first of all, it is necessary to extract keywords, such as select, propose the query table, propose the field name, put forward the query conditions and so on.

Syntax analysis: according to the results of lexical analysis, the main purpose of syntax analysis is to judge whether the SQL statement you entered is correct and whether it conforms to MYSQL grammar. If your sentence is not correct, you will receive an error reminder of "You have an error in your SQL syntax".

The lexical analysis program decomposes the whole query sentence into all kinds of tags, and the syntax analysis converts "all kinds of tags" into meaningful combinations for MySQL according to the defined system language. Finally, the system generates a grammar tree (AST), which is the data structure that the optimizer depends on.

5. Optimizer

After going through the analyzer, MySQL knows what you're going to do. It has to be processed by the optimizer before starting execution.

Why do you need an optimizer?

The optimizer contains many complex optimization techniques that often outnumber the best programmers. The automatic optimization of the system is equivalent to making everyone have these optimization techniques.

The optimizer can get a lot of 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 optimizer can consider a hundred different execution plans, while programmers generally can only consider a limited number of possibilities

An effective execution plan can be selected based on this information, but it is difficult for user programs to obtain this information

In a word, the optimizer modifies the shape of the parsing tree, turns the parsing tree into a query tree, and determines the execution plan.

6. Actuator

MySQL knows what you want to do through the parser and what to do through the optimizer, so it enters the executor phase and starts executing the statement.

At the beginning of execution, it is necessary to verify whether the user has permission to execute the query. If not, an error without permission will be returned. If you have permission, it will call the interface of the engine and return the result of the execution of the interface.

7. Sentence analysis

We use the following real SQL query statement to analyze the execution process of the MYSQL query

Select id,name,sex,phoone from user t where t.agedating / 26' and t.javadaily`

First of all, the client needs to connect to the database. If the account password is wrong, the error message will be returned directly. If it is correct, proceed to the next step.

Before MYSQL8.0, you will first query the cache and use this SQL statement as key to query whether there is a result in memory. If so, you will first determine whether you have permission, and return to the client if you have permission. Otherwise, you will report an error. If you are not hit from the query cache, proceed to the next step.

Through the parser for lexical analysis, extract the key elements of the sql statement, for example, extract the above statement is query select, extract the name of the table to be queried is user, the query is listed as id,name,sex,phoone, and the query conditions are age=26 and account=javadailly. Then determine whether the sql statement has syntax errors, such as whether the keywords are correct, etc., and if there is no problem with the check, proceed to the next step.

The above SQL has two execution schemes, the optimizer chooses the most efficient scheme an according to its own optimization algorithm (incorrect statistical information may cause the optimizer to choose the wrong execution scheme), and starts to execute after determining the optimization scheme.

a. First query the users of account=javadaily, and then determine whether age is equal to 26 b. First find out the users of age=26, then query the users of account=javadaily.

Check the permissions, and call the database engine API to return the execution result if you have the query permission; otherwise, an error is reported.

Thank you for your reading, the above is the content of "instance analysis of the execution process of MySQL query". After the study of this article, I believe you have a deeper understanding of the analysis of the execution process of MySQL query, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.