MySQL - execution process of MySQL query statement
2022-04-23 15:49:00 【Q.E.D.】
You need to retrieve some data that meets the requirements from the database , It's easy for us to write Select A B C FROM T WHERE ID = XX In this way SQL, So when we send such a request to the database , What the database does ？
Today we use MYSQL For example , Reveal it MySQL Database query process , And let you know some parts in the database .
MySQL Mainly can be divided into Server Layer and storage engine layer .
Server layer Including connectors 、 The query cache 、 analyzer 、 Optimizer 、 Actuators etc. , All cross-storage engine functionality is implemented in this layer , Like stored procedures 、 trigger 、 View 、 Functions, etc , There is also a general logging module binlog Log module ;
Storage engine layer Responsible for data storage and extraction . Its architecture pattern is plug-in , Support InnoDB、MyISAM、Memory Wait for multiple storage engines . Now the most commonly used storage engine is InnoDB（ Support transactions ）, It is from MySQL5.5.5 Version starts to be the default storage engine .
2、 The connector
Connector is mainly responsible for user login database , Authentication of users , Including checking account password , Permission and other operations .
If the user's password is wrong , You'll get one "Access denied for user" Error of , Then the client program ends execution .
If the user account password has passed , The connector will query all permissions of the user in the permission table , Later, the logical judgment of permission in this connection will depend on the permission data read at this time , in other words , In the future, as long as the connection is kept open , The immediate administrator modified the user's permissions , The user is also unaffected .
3、 The query cache （ Query cache）
After the client establishes a connection with the server ,MySQL When the query statement is executed, the cache is queried first , Check this one SQL Was it executed before . Previously executed statements and their results will be written as key-value On the form of , Is directly cached in memory .key Is the statement of the query ,value Is the result of a query . If your query can be found directly in this cache key, So this value Will be returned directly to the client . If you don't hit , You need to perform subsequent operations , The result will also be cached after completion , Convenient for the next call .
If you see this, you'll see a bright future , Will there be an impulse to make good use of this function .
In fact, query caching is not recommended here , Query cache failures are very frequent , As long as there is an update to a table , All query caches on this table will be cleared . So it's likely that you're struggling to save the results , It was emptied by an update before it was used . For databases that are under pressure to update , The hit rate for the query cache will be very low . Unless it's a table that won't be updated for a long time , For example, the system configuration table , But this kind of system configuration we put in the configuration platform is not good ？
stay MYSQL8.0 Query caching has been removed in , The official also thinks that this function is less in the actual application scenarios , So just delete .
Mysql Miss query cache , So it goes into the analyzer , The analyzer is mainly used to analyze SQL What are the sentences for . The analyzer is mainly divided into the following two steps ：
Lexical analysis ： One SQL Statement has multiple strings , First, we need to extract keywords , such as select, A table to query , Propose field name , Put forward query conditions and so on .
Syntax analysis ： According to the result of lexical analysis , Grammar analysis is mainly to judge what you input SQL Is the statement correct , Compliance MYSQL grammar , If your statement is wrong , Will receive “You have an error in your SQL syntax” Error warning .
The lexical analysis program decomposes the whole query statement into various kinds of marks , Syntax analysis is based on the defined system language “ All kinds of signs ” Turn to right MySQL Meaningful combination . Finally, the system generates a syntax tree (AST), The syntax tree is the data structure that the optimizer depends on .
Through the analyser ,MySQL You know what you're gonna do . Before we start executing , It's also handled by the optimizer .
Why an optimizer is needed ？
The optimizer contains many Complex optimization techniques , These optimization techniques are often more than the best programmers can master . The automatic optimization of the system is equivalent to making everyone have these optimization technologies .
The optimizer can get many... From the data dictionary Statistics , For example, the number of rows in a table 、 The distribution of each column in the table, etc . The optimizer optimizer can consider hundreds of different execution plans , Programmers can only consider a limited number of possibilities ;
You can choose from this information Effective implementation plan , And it's hard for user programs to get that information ;
In short, the optimizer modifies the shape of the parse tree , Turn the parsing tree into a query tree , Determine the implementation plan .
MySQL I know what you are going to do through the analyzer , You know what to do with the optimizer , So it goes into the actuator stage , Start statement execution .
At the beginning of execution , First check whether the user has the right to execute the query , without , An error with no permissions is returned . If you have authority , Will call the engine's interface , Returns the result of the interface execution .
7、 analysis of sentences
Let's take the following real SQL Query statement for analysis MYSQL The execution of the query
select id,name,sex,phoone from user t where t.age='26' and t.account='javadaily'
First, the client needs to connect to the database , If the account password is wrong, the error message will be returned directly , If it's right, go to the next step .
stay MYSQL8.0 I'll go to the query cache first , With this SQL Sentence as key Query in memory for results , If yes, judge whether you have permission first , If you have permission, return to the client , Otherwise, the report will be wrong ; If there is no hit from the query cache, go to the next step
Lexical analysis by Analyzer , extract sql Key elements of the statement , For example, extracting the above statement is a query select, Extract the table name to be queried as
user, The columns to query are
id,name,sex,phoone, The query condition is
account=javadailly. And then judge this sql Is there any grammatical error in the statement , For example, whether the keywords are correct, etc , If the check is OK, go to the next step .
above SQL There are two implementation options , The optimizer chooses the most efficient one according to its own optimization algorithm a programme （ Inaccurate statistics may cause the optimizer to choose the wrong execution scheme ）, After the optimization plan is determined, it will be implemented .
a. First query
account=javadailyUsers of , And then determine
ageIs it equal to 26 b. Find out first
age=26Users of , The query again
Verify authority , If you have query permission, call the database engine interface to return the execution result ; Otherwise, the report will be wrong .
- utils. Deprecated in35 may be cancelled due to upgrade. What should I do
- The length of the last word of the string
- Tencent offer has been taken. Don't miss the 99 algorithm high-frequency interview questions. 80% of them are lost in the algorithm
- Go language, array, pointer, structure
- JS regular détermine si le nom de domaine ou le chemin de port IP est correct
IronPDF for .NET 2022.4.5455
Pgpool II 4.3 Chinese Manual - introductory tutorial
Modèle de Cluster MySQL et scénario d'application
New developments: new trends in cooperation between smartmesh and meshbox
Import address table analysis (calculated according to the library file name: number of imported functions, function serial number and function name)
[open source tool sharing] MCU debugging assistant (oscillograph / modification / log) - linkscope
大厂技术实现 | 行业解决方案系列教程
【开源工具分享】单片机调试助手（示波/改值/日志） - LinkScope
Accumulation of applet knowledge points
Tencent offer has been taken. Don't miss the 99 algorithm high-frequency interview questions. 80% of them are lost in the algorithm
Neodynamic Barcode Professional for WPF V11. 0
Upgrade MySQL 5.1 to 5.66
Config learning notes component
Mobile finance (for personal use)
Open source project recommendation: 3D point cloud processing software paraview, based on QT and VTK
Implement default page