MySQL学习（一）---->SQL查询执行流程

桓徵

MySQL查询执行流程

架构总览
下面就是MySQL 的逻辑架构，sql layer主要负责如下功能：权限判断、sql解析、执行计划优化、query cache的处理等操作，这些操作都是在数据库系统处理底层数据之前的工作；
Storage Engine Layer主要负责底层数据存取的实现，由多种存储引擎共同组成。

SQL Layer 中包含了多个子模块，
结构图如下

1、初始化模块
顾名思议，初始化模块就是在mysql Server 启动的时候，对整个系统做各种各样的初始化操作，比如各种buffer，cache 结构的初始化和内存空间的申请，各种系统变量的初始化设定，各种存储引擎的初始化设置，等等。
2、核心API
核心API 模块主要是为了提供一些需要非常高效的底层操作功能的优化实现，包括各种底层数据结构的实现，特殊算法的实现，字符串处理，数字处理等，小文件I/O，格式化输
出，以及最重要的内存管理部分。核心API 模块的所有源代码都集中在mysys 和strings文件夹下面，有兴趣的读者可以研究研究。
3、网络交互模块
底层网络交互模块抽象出底层网络交互所使用的接口api，实现底层网络数据的接收与发送，以方便其他各个模块调用，以及对这一部分的维护。所有源码都在vio 文件夹下面。
4、Client & Server 交互协议模块
任何C/S 结构的软件系统，都肯定会有自己独有的信息交互协议，MySQL 也不例外。MySQL的Client & Server 交互协议模块部分，实现了客户端与MySQL 交互过程中的所有协议。当然这些协议都是建立在现有的OS 和网络协议之上的，如TCP/IP 以及Unix Socket。
5、用户模块
用户模块所实现的功能，主要包括用户的登录连接权限控制和用户的授权管理。他就像MySQL 的大门守卫一样，决定是否给来访者“开门”。
6、访问控制模块
造访客人进门了就可以想干嘛就干嘛吗？为了安全考虑，肯定不能如此随意。这时候就需要访问控制模块实时监控客人的每一个动作，给不同的客人以不同的权限。访问控制模块实现的功能就是根据用户模块中各用户的授权信息，以及数据库自身特有的各种约束，来控制用户对数据的访问。用户模块和访问控制模块两者结合起来，组成了MySQL 整个数据库系统的权限安全管理的功能。
7、连接管理、连接线程和线程管理
连接管理模块负责监听对MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责MySQL Server 与客户端的通信，接受客户端的命令请求，传递Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的cache 等。
8、Query 解析和转发模块
在MySQL 中我们习惯将所有Client 端发送给Server 端的命令都称为query，在MySQLServer 里面，连接线程接收到客户端的一个Query 后，会直接将该query 传递给专门负责
将各种Query 进行分类然后转发给各个对应的处理模块，这个模块就是query 解析和转发模块。其主要工作就是将query 语句进行语义和语法的分析，然后按照不同的操作类型进行分类，然后做出针对性的转发。
9、Query Cache 模块
Query Cache 模块在MySQL 中是一个非常重要的模块，他的主要功能是将客户端提交给MySQL 的Select 类query 请求的返回结果集cache 到内存中，与该query 的一个hash 值做一个对应。该Query 所取数据的基表发生任何数据的变化之后，MySQL 会自动使该query 的Cache 失效。在读写比例非常高的应用系统中，Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
10、Query 优化器模块
Query 优化器，顾名思义，就是优化客户端请求的query，根据客户端请求的query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个query 语句的结果。
11、表变更管理模块
表变更管理模块主要是负责完成一些DML 和DDL 的query，如：update，delte，insert，create table，alter table 等语句的处理。
12、表维护模块
表的状态检查，错误修复，以及优化和分析等工作都是表维护模块需要做的事情。
13、系统状态管理模块
系统状态管理模块负责在客户端请求系统状态的时候，将各种状态数据返回给用户，像DBA 常用的各种show status 命令，show variables 命令等，所得到的结果都是由这个模块返回的。
14、表管理器
这个模块从名字上看来很容易和上面的表变更和表维护模块相混淆，但是其功能与变更及维护模块却完全不同。大家知道，每一个MySQL 的表都有一个表的定义文件，也就是*.frm文件。表管理器的工作主要就是维护这些文件，以及一个cache，该cache 中的主要内容是各个表的结构信息。此外它还维护table 级别的锁管理。
15、日志记录模块
日志记录模块主要负责整个系统级别的逻辑层的日志的记录，包括error log，binarylog，slow query log 等。
16、复制模块
复制模块又可分为Master 模块和Slave 模块两部分， Master 模块主要负责在Replication 环境中读取Master 端的binary 日志，以及与Slave 端的I/O 线程交互等工作。
Slave 模块比Master 模块所要做的事情稍多一些，在系统中主要体现在两个线程上面。一个是负责从Master 请求和接受binary 日志，并写入本地relay log 中的I/O 线程。另外一个是负责从relay log 中读取相关日志事件，然后解析成可以在Slave 端正确执行并得到和Master 端完全相同的结果的命令并再交给Slave 执行的SQL 线程。
17、存储引擎接口模块
存储引擎接口模块可以说是MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天MySQL 可插拔存储引擎的特色。
查询执行流程

查询执行的流程：
1.连接
　　1.1客户端发起一条Query请求，监听客户端的‘连接管理模块'接收请求
　　1.2将请求转发到‘连接进/线程模块'
　　1.3调用‘用户模块'来进行授权检查
　　1.4通过检查后，‘连接进/线程模块'从‘线程连接池'中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求
2.处理
　　2.1先查询缓存，检查Query语句是否完全匹配，接着再检查是否具有权限，都成功则直接取数据返回
　　2.2上一步有失败则转交给‘命令解析器'，经过词法分析，语法分析后生成解析树
　　2.3接下来是预处理阶段，处理解析器无法解决的语义，检查权限等，生成新的解析树
　　2.4再转交给对应的模块处理
　　2.5如果是SELECT查询还会经由‘查询优化器'做大量的优化，生成执行计划
　　2.6模块收到请求后，通过‘访问控制模块'检查所连接的用户是否有访问目标表和目标字段的权限
　　2.7有则调用‘表管理模块'，先是查看table cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件
　　2.8根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理
　　2.9上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中
3.结果
　　3.1Query请求完成后，将结果集返回给‘连接进/线程模块'
　　3.2返回的也可以是相应的状态标识，如成功或失败等
　　3.3‘连接进/线程模块'进行后续的清理工作，并继续等待请求或断开与客户端的连接
一图小总结

接下来再走一步，让我们看看一条SQL语句的前世今生。
首先看一下示例语句

1. SELECT DISTINCT
2. < select_list >
3. FROM
4. < left_table > < join_type >
5. JOIN < right_table > ON < join_condition >
6. WHERE
7. < where_condition >
8. GROUP BY
9. < group_by_list >
10. HAVING
11. < having_condition >
12. ORDER BY
13. < order_by_condition >
14. LIMIT < limit_number >

复制代码

然而它的执行顺序是这样的

1. FROM <left_table>
2. ON <join_condition>
3. <join_type> JOIN <right_table>
4. WHERE <where_condition>
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
7. SELECT
8. DISTINCT <select_list>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>

复制代码

既然如此了，那就让我们一步步来看看其中的细节吧。
准备工作
1.创建测试数据库

1. create database testQuery

复制代码

2.创建测试表

1. CREATE TABLE table1
2. (
3. uid VARCHAR(10) NOT NULL,
4. name VARCHAR(10) NOT NULL,
5. PRIMARY KEY(uid)
6. )ENGINE=INNODB DEFAULT CHARSET=UTF8;
8. CREATE TABLE table2
9. (
10. oid INT NOT NULL auto_increment,
11. uid VARCHAR(10),
12. PRIMARY KEY(oid)
13. )ENGINE=INNODB DEFAULT CHARSET=UTF8;

复制代码

3.插入数据

1. INSERT INTO table1(uid,name) VALUES('aaa','mike'),('bbb','jack'),('ccc','mike'),('ddd','mike');
3. INSERT INTO table2(uid) VALUES('aaa'),('aaa'),('bbb'),('bbb'),('bbb'),('ccc'),(NULL);

复制代码

4.最后想要的结果

1. SELECT
2. a.uid,
3. count(b.oid) AS total
4. FROM
5. table1 AS a
6. LEFT JOIN table2 AS b ON a.uid = b.uid
7. WHERE
8. a. NAME = 'mike'
9. GROUP BY
10. a.uid
11. HAVING
12. count(b.oid) < 2
13. ORDER BY
14. total DESC
15. LIMIT 1;

复制代码

现在开始SQL解析之旅吧！
1. FROM
当涉及多个表的时候，左边表的输出会作为右边表的输入，之后会生成一个虚拟表VT1。
(1-J1)笛卡尔积
计算两个相关联表的笛卡尔积(CROSS JOIN) ，生成虚拟表VT1-J1。

1. mysql> select * from table1,table2;
2. +-----+------+-----+------+
3. | uid | name | oid | uid |
4. +-----+------+-----+------+
5. | aaa | mike | 1 | aaa |
6. | bbb | jack | 1 | aaa |
7. | ccc | mike | 1 | aaa |
8. | ddd | mike | 1 | aaa |
9. | aaa | mike | 2 | aaa |
10. | bbb | jack | 2 | aaa |
11. | ccc | mike | 2 | aaa |
12. | ddd | mike | 2 | aaa |
13. | aaa | mike | 3 | bbb |
14. | bbb | jack | 3 | bbb |
15. | ccc | mike | 3 | bbb |
16. | ddd | mike | 3 | bbb |
17. | aaa | mike | 4 | bbb |
18. | bbb | jack | 4 | bbb |
19. | ccc | mike | 4 | bbb |
20. | ddd | mike | 4 | bbb |
21. | aaa | mike | 5 | bbb |
22. | bbb | jack | 5 | bbb |
23. | ccc | mike | 5 | bbb |
24. | ddd | mike | 5 | bbb |
25. | aaa | mike | 6 | ccc |
26. | bbb | jack | 6 | ccc |
27. | ccc | mike | 6 | ccc |
28. | ddd | mike | 6 | ccc |
29. | aaa | mike | 7 | NULL |
30. | bbb | jack | 7 | NULL |
31. | ccc | mike | 7 | NULL |
32. | ddd | mike | 7 | NULL |
33. +-----+------+-----+------+
34. rows in set (0.00 sec)

复制代码

(1-J2)ON过滤
基于虚拟表VT1-J1这一个虚拟表进行过滤，过滤出所有满足ON 谓词条件的列，生成虚拟表VT1-J2。
注意：这里因为语法限制，使用了'WHERE'代替，从中也可以感受到两者之间微妙的关系；

1. mysql> SELECT
2. -> *
3. -> FROM
4. -> table1,
5. -> table2
6. -> WHERE
7. -> table1.uid = table2.uid
8. -> ;
9. +-----+------+-----+------+
10. | uid | name | oid | uid |
11. +-----+------+-----+------+
12. | aaa | mike | 1 | aaa |
13. | aaa | mike | 2 | aaa |
14. | bbb | jack | 3 | bbb |
15. | bbb | jack | 4 | bbb |
16. | bbb | jack | 5 | bbb |
17. | ccc | mike | 6 | ccc |
18. +-----+------+-----+------+
19. rows in set (0.00 sec)

复制代码

(1-J3)添加外部列
如果使用了外连接(LEFT,RIGHT,FULL)，主表（保留表）中的不符合ON条件的列也会被加入到VT1-J2中，作为外部行，生成虚拟表VT1-J3。
如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。

1. mysql> SELECT
2. -> *
3. -> FROM
4. -> table1 AS a
5. -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid;
6. +-----+------+------+------+
7. | uid | name | oid | uid |
8. +-----+------+------+------+
9. | aaa | mike | 1 | aaa |
10. | aaa | mike | 2 | aaa |
11. | bbb | jack | 3 | bbb |
12. | bbb | jack | 4 | bbb |
13. | bbb | jack | 5 | bbb |
14. | ccc | mike | 6 | ccc |
15. | ddd | mike | NULL | NULL |
16. +-----+------+------+------+
17. rows in set (0.00 sec)

复制代码

下面从网上找到一张很形象的关于‘SQL JOINS'的解释图

2. WHERE
对VT1过程中生成的临时表进行过滤，满足WHERE子句的列被插入到VT2表中。
注意：
此时因为分组，不能使用聚合运算；也不能使用SELECT中创建的别名；
与ON的区别：
如果有外部列，ON针对过滤的是关联表，主表（保留表）会返回所有的列；
如果没有添加外部列，两者的效果是一样的；
应用：
对主表的过滤应该放在WHERE；
对于关联表，先条件查询后连接则用ON，先连接后条件查询则用WHERE；

1. mysql> SELECT
2. -> *
3. -> FROM
4. -> table1 AS a
5. -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
6. -> WHERE
7. -> a. NAME = 'mike';
8. +-----+------+------+------+
9. | uid | name | oid | uid |
10. +-----+------+------+------+
11. | aaa | mike | 1 | aaa |
12. | aaa | mike | 2 | aaa |
13. | ccc | mike | 6 | ccc |
14. | ddd | mike | NULL | NULL |
15. +-----+------+------+------+
16. rows in set (0.00 sec)

复制代码

3. GROUP BY
这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。
注意：
其后处理过程的语句，如SELECT,HAVING，所用到的列必须包含在GROUP BY中，对于没有出现的，得用聚合函数；
原因：
GROUP BY改变了对表的引用，将其转换为新的引用方式，能够对其进行下一级逻辑操作的列会减少；
我的理解是：
根据分组字段，将具有相同分组字段的记录归并成一条记录，因为每一个分组只能返回一条记录，除非是被过滤掉了，而不在分组字段里面的字段可能会有多个值，多个值是无法放进一条记录的，所以必须通过聚合函数将这些具有多值的列转换成单值；

1. mysql> SELECT
2. -> *
3. -> FROM
4. -> table1 AS a
5. -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
6. -> WHERE
7. -> a. NAME = 'mike'
8. -> GROUP BY
9. -> a.uid;
10. +-----+------+------+------+
11. | uid | name | oid | uid |
12. +-----+------+------+------+
13. | aaa | mike | 1 | aaa |
14. | ccc | mike | 6 | ccc |
15. | ddd | mike | NULL | NULL |
16. +-----+------+------+------+
17. rows in set (0.00 sec)

复制代码

4. HAVING
这个子句对VT3表中的不同的组进行过滤，只作用于分组后的数据，满足HAVING条件的子句被加入到VT4表中。

1. mysql> SELECT
2. -> *
3. -> FROM
4. -> table1 AS a
5. -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
6. -> WHERE
7. -> a. NAME = 'mike'
8. -> GROUP BY
9. -> a.uid
10. -> HAVING
11. -> count(b.oid) < 2;
12. +-----+------+------+------+
13. | uid | name | oid | uid |
14. +-----+------+------+------+
15. | ccc | mike | 6 | ccc |
16. | ddd | mike | NULL | NULL |
17. +-----+------+------+------+
18. rows in set (0.00 sec)

复制代码

5. SELECT
这个子句对SELECT子句中的元素进行处理，生成VT5表。
(5-J1)计算表达式 计算SELECT 子句中的表达式，生成VT5-J1
(5-J2)DISTINCT
寻找VT5-1中的重复列，并删掉，生成VT5-J2
如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT5是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。

1. mysql> SELECT
2. -> a.uid,
3. -> count(b.oid) AS total
4. -> FROM
5. -> table1 AS a
6. -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
7. -> WHERE
8. -> a. NAME = 'mike'
9. -> GROUP BY
10. -> a.uid
11. -> HAVING
12. -> count(b.oid) < 2;
13. +-----+-------+
14. | uid | total |
15. +-----+-------+
16. | ccc |  1 |
17. | ddd |  0 |
18. +-----+-------+
19. rows in set (0.00 sec)

复制代码

6.ORDER BY
从VT5-J2中的表中，根据ORDER BY 子句的条件对结果进行排序，生成VT6表。
注意：
唯一可使用SELECT中别名的地方；

1. mysql> SELECT
2. -> a.uid,
3. -> count(b.oid) AS total
4. -> FROM
5. -> table1 AS a
6. -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
7. -> WHERE
8. -> a. NAME = 'mike'
9. -> GROUP BY
10. -> a.uid
11. -> HAVING
12. -> count(b.oid) < 2
13. -> ORDER BY
14. -> total DESC;
15. +-----+-------+
16. | uid | total |
17. +-----+-------+
18. | ccc |  1 |
19. | ddd |  0 |
20. +-----+-------+
21. rows in set (0.00 sec)

复制代码

7.LIMIT
LIMIT子句从上一步得到的VT6虚拟表中选出从指定位置开始的指定行数据。
注意：
offset和rows的正负带来的影响；
当偏移量很大时效率是很低的，可以这么做：
采用子查询的方式优化，在子查询里先从索引获取到最大id，然后倒序排，再取N行结果集
采用INNER JOIN优化，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果

1. mysql> SELECT
2. -> a.uid,
3. -> count(b.oid) AS total
4. -> FROM
5. -> table1 AS a
6. -> LEFT JOIN table2 AS b ON a.uid = b.uid
7. -> WHERE
8. -> a. NAME = 'mike'
9. -> GROUP BY
10. -> a.uid
11. -> HAVING
12. -> count(b.oid) < 2
13. -> ORDER BY
14. -> total DESC
15. -> LIMIT 1;
16. +-----+-------+
17. | uid | total |
18. +-----+-------+
19. | ccc |  1 |
20. +-----+-------+
21. row in set (0.00 sec)

复制代码

至此SQL的解析之旅就结束了，上图总结一下：

 
 
 
出处：https://www.cnblogs.com/donleo123/本文如对您有帮助，还请多推荐下此文，如有错误欢迎指正，相互学习，共同进步。
来源:https://www.cnblogs.com/donleo123/p/17217850.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！