SQL查询的底层运行原理深入分析
前言
SQL语言无处不在。SQL已经不仅仅是技术人员的专属技能了，似乎人人都会写SQL，就如同人人都是产品经理一样。如果你是做后台开发的，那么CRUD就是家常便饭。如果你是做数仓开发的，那么写SQL可能占据了你的大部分工作时间。我们在理解SELECT语法的时候，还需要了解SELECT执行时的底层原理。只有这样，才能让我们对SQL有更深刻的认识。本文分享将逐步分解SQL的执行过程，希望对你有所帮助。
数据准备
本文旨在说明SQL查询的执行过程，不会涉及太复杂的SQL操作，主要涉及两张表：citizen和city,具体数据如下所示：
CREATETABLEcitizen(
nameCHAR(20),
city_idINT(10)
);
CREATETABLEcity(
city_idINT(10),
city_nameCHAR(20)
);
INSERTINTOcity
VALUES
(1,"上海"),
(2,"北京"),
(3,"杭州");
INSERTINTOcitizen
VALUES
("tom",3),
("jack",2),
("robin",1),
("jasper",3),
("kevin",1),
("rachel",2),
("trump",3),
("lilei",1),
("hanmeiei",1);
查询执行顺序
本文所涉及的查询语句如下，主要是citizen表与city表进行join，然后筛掉city_name!="上海"的数据，接着按照city_name进行分组，统计每个城市总人数大于2的城市，具体如下：
查询语句
SELECT city.city_nameAS"City", COUNT(*)AS"citizen_cnt" FROMcitizen JOINcityONcitizen.city_id=city.city_id WHEREcity.city_name!='上海' GROUPBYcity.city_name HAVINGCOUNT(*)>=2 ORDERBYcity.city_nameASC LIMIT2
执行步骤
上面SQL查询语句的书写书序是：
SELECT...FROM...WHERE...GROUPBY...HAVING...ORDERBY...
但是执行顺序并不是这样，具体的执行顺序如下步骤所示：
- 1.获取数据(From,Join)
- 2.过滤数据(Where)
- 3.分组(Groupby)
- 4.分组过滤(Having)
- 5.返回查询字段(Select)
- 6.排序与分页(Orderby&Limit/Offset)
尖叫提示：本文旨在说明通用的SQL执行底层原理，对于其优化技术不做考虑，比如谓词下推、投影下推等等。
执行的底层原理
其实上面所说的SQL执行顺序就是所谓的底层原理，当我们在执行SELECT语句时，每个步骤都会产生一张虚拟表(virtualtable），在执行下一步骤时，会将该虚拟表作为输入。指的注意的是，这些过程是对用户透明的。
你可以注意到，SELECT是先从FROM这一步开始执行的。在这个阶段，如果是多张表进行JOIN，还会经历下面的几个步骤：
获取数据(From,Join)
- 首先会通过CROSSJOIN求笛卡尔积，相当于得到虚拟表vt1-1；
- 接着通过ON条件进行筛选，虚拟表vt1-1作为输入，输出虚拟表vt1-2；
- 添加外部行。我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2的基础上增加外部行，得到虚拟表vt1-3
过滤数据(Where)
经过上面的步骤，我们得到了一张最终的虚拟表vt1，在此表之上作用where过滤，通过筛选条件过滤掉不满足条件的数据，从而得到虚拟表vt2。
分组(Groupby)
经过where过滤操作之后，得到vt2。接下来进行GROUPBY操作，得到中间的虚拟表vt3。
分组过滤(Having)
在虚拟表vt3的基础之上，使用having过滤掉不满足条件的聚合数据，得到vt4。
返回查询字段(Select)
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段。首先在SELECT阶段会提取目标字段，然后在DISTINCT阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2。
排序与分页(Orderby&Limit/Offset)
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDERBY阶段，得到虚拟表vt6。最后在vt6的基础上，取出指定行的记录，也就是LIMIT阶段，得到最终的结果，对应的是虚拟表vt7
详细执行步骤分析
Step1:获取数据(From,Join)
FROMcitizen JOINcity
该过程的第一步是执行From子句中的语句，然后执行Join子句。这些操作的结果是得到两个表的笛卡尔积。
| name | city_id | city_id | city_name |
|---|---|---|---|
| tom | 3 | 1 | 上海 |
| tom | 3 | 2 | 北京 |
| tom | 3 | 3 | 杭州 |
| jack | 2 | 1 | 上海 |
| jack | 2 | 2 | 北京 |
| jack | 2 | 3 | 杭州 |
| robin | 1 | 1 | 上海 |
| robin | 1 | 2 | 北京 |
| robin | 1 | 3 | 杭州 |
| jasper | 3 | 1 | 上海 |
| jasper | 3 | 2 | 北京 |
| jasper | 3 | 3 | 杭州 |
| kevin | 1 | 1 | 上海 |
| kevin | 1 | 2 | 北京 |
| kevin | 1 | 3 | 杭州 |
| rachel | 2 | 1 | 上海 |
| rachel | 2 | 2 | 北京 |
| rachel | 2 | 3 | 杭州 |
| trump | 3 | 1 | 上海 |
| trump | 3 | 2 | 北京 |
| trump | 3 | 3 | 杭州 |
| lilei | 1 | 1 | 上海 |
| lilei | 1 | 2 | 北京 |
| lilei | 1 | 3 | 杭州 |
| hanmeiei | 1 | 1 | 上海 |
| hanmeiei | 1 | 2 | 北京 |
| hanmeiei | 1 | 3 | 杭州 |
在FROM和JOIN执行结束之后，会按照JOIN的ON条件，筛选所需要的行
ONcitizen.city_id=city.city_id
| name | city_id | city_id | city_name |
|---|---|---|---|
| tom | 3 | 3 | 杭州 |
| jack | 2 | 2 | 北京 |
| robin | 1 | 1 | 上海 |
| jasper | 3 | 3 | 杭州 |
| kevin | 1 | 1 | 上海 |
| rachel | 2 | 2 | 北京 |
| trump | 3 | 3 | 杭州 |
| lilei | 1 | 1 | 上海 |
| hanmeiei | 1 | 1 | 上海 |
Step2:过滤数据(Where)
获得满足条件的行后，将传递给Where子句。这将使用条件表达式评估每一行。如果行的计算结果不为true，则会将其从集合中删除。
WHEREcity.city_name!='上海'
| name | city_id | city_id | city_name |
|---|---|---|---|
| tom | 3 | 3 | 杭州 |
| jack | 2 | 2 | 北京 |
| jasper | 3 | 3 | 杭州 |
| rachel | 2 | 2 | 北京 |
| trump | 3 | 3 | 杭州 |
Step3:分组(Groupby)
下一步是执行Groupby子句，它将具有相同值的行分为一组。此后，将按组对所有Select表达式进行评估，而不是按行进行评估。
GROUPBYcity.city_name
| GROUP_CONCAT(citizen. name ) | city_id | city_name |
|---|---|---|
| jack,rachel | 2 | 北京 |
| tom,jasper,trump | 3 | 杭州 |
Step4:分组过滤(Having)
对分组后的数据使用Having子句所包含的谓词进行过滤
HAVINGCOUNT(*)>=2
Step5:返回查询字段(Select)
在此步骤中，处理器将评估查询结果将要打印的内容，以及是否有一些函数要对数据运行，例如Distinct，Max，Sqrt，Date，Lower等等。本案例中，SELECT子句只会打印城市名称和其对应分组的count(*)值，并使用标识符“City”作为city_name列的别名。
SELECT city.city_nameAS"City", COUNT(*)AS"citizen_cnt"
| city | citizen_cnt |
|---|---|
| 北京 | 2 |
| 杭州 | 3 |
Step6:排序与分页(Orderby&Limit/Offset)
查询的最后处理步骤涉及结果集的排序与输出大小。在我们的示例中，按照字母顺序升序排列，并输出两条数据结果。
ORDERBYcity.city_nameASC LIMIT2
| city | citizen_cnt |
|---|---|
| 北京 | 2 |
| 杭州 | 3 |
总结
本文主要剖析了SQL语句的执行顺序和底层原理，基本的SQL查询会分为六大步骤。本文结合具体事例，给出了每一步骤的详细结果，这样会对其执行的底层原理有更加深刻的认识。
到此这篇关于SQL查询的底层运行原理深入分析的文章就介绍到这了,更多相关SQL查询底层运行原理内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。