当前位置:网站首页>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
边栏推荐
- Codejock Suite Pro v20.3.0
- 一刷314-剑指 Offer 09. 用两个栈实现队列(e)
- Multi level cache usage
- Upgrade MySQL 5.1 to 5.66
- IronPDF for . NET 2022.4.5455
- 导入地址表分析(根据库文件名求出:导入函数数量、函数序号、函数名称)
- 怎么看基金是不是reits,通过银行购买基金安全吗
- 实现缺省页面
- s16.基于镜像仓库一键安装containerd脚本
- Open source project recommendation: 3D point cloud processing software paraview, based on QT and VTK
猜你喜欢
5分钟,把你的Excel变成在线数据库,神奇的魔方网表excel数据库
API IX JWT auth plug-in has an error. Risk announcement of information disclosure in response (cve-2022-29266)
王启亨谈Web3.0与价值互联网“通证交换”
CAP定理
[AI weekly] NVIDIA designs chips with AI; The imperfect transformer needs to overcome the theoretical defect of self attention
Neodynamic Barcode Professional for WPF V11.0
Codejock Suite Pro v20.3.0
WPS brand was upgraded to focus on China. The other two domestic software were banned from going abroad with a low profile
MetaLife与ESTV建立战略合作伙伴关系并任命其首席执行官Eric Yoon为顾问
C language --- advanced pointer
随机推荐
R语言中实现作图对象排列的函数总结
一文读懂串口及各种电平信号含义
js正則判斷域名或者IP的端口路徑是否正確
Go language, array, pointer, structure
VIM specifies the line comment and reconciliation comment
ICE -- 源码分析
字符串排序
Multi level cache usage
Partitionby of spark operator
【递归之数的拆分】n分k,限定范围的拆分
Load Balancer
API IX JWT auth plug-in has an error. Risk announcement of information disclosure in response (cve-2022-29266)
MySQL集群模式与应用场景
一文掌握vscode远程gdb调试
[AI weekly] NVIDIA designs chips with AI; The imperfect transformer needs to overcome the theoretical defect of self attention
单体架构系统重新架构
Implement default page
腾讯Offer已拿,这99道算法高频面试题别漏了,80%都败在算法上
vim指定行注释和解注释
C#,贝尔数(Bell Number)的计算方法与源程序