当前位置:网站首页>MySQL - execution process of MySQL query statement
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 .
1、MYSQL framework
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 .
4、 analyzer
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 .
5、 Optimizer
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 .
6、 actuator
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 areid,name,sex,phoone
, The query condition isage=26
andaccount=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 queryaccount=javadaily
Users of , And then determineage
Is it equal to 26 b. Find out firstage=26
Users of , The query againaccount=javadaily
Users of -
Verify authority , If you have query permission, call the database engine interface to return the execution result ; Otherwise, the report will be wrong .
版权声明
本文为[Q.E.D.]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231548425692.html
边栏推荐
- Tencent offer has been taken. Don't miss the 99 algorithm high-frequency interview questions. 80% of them are lost in the algorithm
- Open source project recommendation: 3D point cloud processing software paraview, based on QT and VTK
- One brush 312 - simple repetition set - Sword finger offer 03 Duplicate number in array (E)
- Go language, condition, loop, function
- C language --- string + memory function
- Basic greedy summary
- 为啥禁用外键约束
- 一刷313-剑指 Offer 06. 从尾到头打印链表(e)
- Calculate the number of occurrences of a character
- Spark 算子之partitionBy
猜你喜欢
Why disable foreign key constraints
Application of Bloom filter in 100 million flow e-commerce system
山寨版归并【上】
Multi level cache usage
WPS brand was upgraded to focus on China. The other two domestic software were banned from going abroad with a low profile
一刷314-剑指 Offer 09. 用两个栈实现队列(e)
C language --- advanced pointer
APISIX jwt-auth 插件存在错误响应中泄露信息的风险公告(CVE-2022-29266)
One brush 314 sword finger offer 09 Implement queue (E) with two stacks
Metalife established a strategic partnership with ESTV and appointed its CEO Eric Yoon as a consultant
随机推荐
Basic concepts of website construction and management
为啥禁用外键约束
Spark 算子之filter使用
Spark 算子之coalesce与repartition
王启亨谈Web3.0与价值互联网“通证交换”
【自娱自乐】构造笔记 week 2
Configuration of multi spanning tree MSTP
Upgrade MySQL 5.1 to 5.611
String sorting
Large factory technology implementation | industry solution series tutorials
One brush 312 - simple repetition set - Sword finger offer 03 Duplicate number in array (E)
vim指定行注释和解注释
字符串最后一个单词的长度
C#,贝尔数(Bell Number)的计算方法与源程序
Go language, condition, loop, function
For examination
大厂技术实现 | 行业解决方案系列教程
Cookie&Session
Upgrade MySQL 5.1 to 5.69
pgpool-II 4.3 中文手册 - 入门教程