您好,登录后才能下订单哦!
今天就跟大家聊聊有关SQL查询的原理是什么,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。
前言
SQL 语言无处不在。SQL 已经不仅仅是技术人员的专属技能了,似乎人人都会写SQL,就如同人人都是产品经理一样。如果你是做后台开发的,那么CRUD就是家常便饭。如果你是做数仓开发的,那么写SQL可能占据了你的大部分工作时间。我们在理解 SELECT 语法的时候,还需要了解 SELECT 执行时的底层原理。只有这样,才能让我们对 SQL 有更深刻的认识。本文分享将逐步分解SQL的执行过程,希望对你有所帮助。
数据准备
本文旨在说明SQL查询的执行过程,不会涉及太复杂的SQL操作,主要涉及两张表: citizen 和 city ,具体数据如下所示:
CREATE TABLE citizen ( name CHAR ( 20 ), city_id INT ( 10 ) ); CREATE TABLE city ( city_id INT ( 10 ), city_name CHAR ( 20 ) ); INSERT INTO city VALUES ( 1, "上海" ), ( 2, "北京" ), ( 3, "杭州" ); INSERT INTO citizen 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_name AS "City", COUNT(*) AS "citizen_cnt" FROM citizen JOIN city ON citizen.city_id = city.city_id WHERE city.city_name != '上海' GROUP BY city.city_name HAVING COUNT(*) >= 2 ORDER BY city.city_name ASC LIMIT 2
执行步骤
上面SQL查询语句的书写书序是:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
但是执行顺序并不是这样,具体的执行顺序如下步骤所示:
尖叫提示:本文旨在说明通用的SQL执行底层原理,对于其优化技术不做考虑,比如谓词下推、投影下推等等。
执行的底层原理
其实上面所说的SQL执行顺序就是所谓的底层原理,当我们在执行SELECT语句时,每个步骤都会产生一张 虚拟表(virtual table) ,在执行下一步骤时,会将该虚拟表作为输入。指的注意的是,这些过程是对用户透明的。
你可以注意到,SELECT 是先从FROM 这一步开始执行的。在这个阶段,如果是多张表进行JOIN,还会经历下面的几个步骤:
获取数据 ( From, Join )
过滤数据 ( Where )
经过上面的步骤,我们得到了一张最终的虚拟表vt1,在此表之上作用where过滤,通过筛选条件过滤掉不满足条件的数据,从而得到虚拟表vt2。
分组 ( Group by )
经过where过滤操作之后,得到vt2。接下来进行GROUP BY操作,得到中间的虚拟表vt3。
分组过滤 ( Having )
在虚拟表vt3的基础之上,使用having过滤掉不满足条件的聚合数据,得到vt4。
返回查询字段 ( Select )
当我们完成了条件筛选部分之后,就可以筛选表中提取的字段,也就是进入到 SELECT 和 DISTINCT 阶段。首先在 SELECT 阶段会提取目标字段,然后在 DISTINCT 阶段过滤掉重复的行,分别得到中间的虚拟表 vt5-1 和 vt5-2。
排序与分页 ( Order by & Limit / Offset )
当我们提取了想要的字段数据之后,就可以按照指定的字段进行排序,也就是 ORDER BY 阶段,得到虚拟表 vt6。最后在 vt6 的基础上,取出指定行的记录,也就是 LIMIT 阶段,得到最终的结果,对应的是虚拟表 vt7
详细执行步骤分析
Step 1:获取数据 ( From, Join )
FROM citizen JOIN city
该过程的第一步是执行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条件,筛选所需要的行
ON citizen.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 | 上海 |
Step 2:过滤数据 ( Where )
获得满足条件的行后,将传递给Where子句。这将使用条件表达式评估每一行。如果行的计算结果不为true,则会将其从集合中删除。
WHERE city.city_name != '上海'
name | city_id | city_id | city_name |
---|---|---|---|
tom | 3 | 3 | 杭州 |
jack | 2 | 2 | 北京 |
jasper | 3 | 3 | 杭州 |
rachel | 2 | 2 | 北京 |
trump | 3 | 3 | 杭州 |
Step 3:分组 ( Group by )
下一步是执行Group by子句,它将具有相同值的行分为一组。此后,将按组对所有Select表达式进行评估,而不是按行进行评估。
GROUP BY city.city_name
GROUP_CONCAT(citizen. name ) | city_id | city_name |
---|---|---|
jack,rachel | 2 | 北京 |
tom,jasper,trump | 3 | 杭州 |
Step 4:分组过滤 ( Having )
对分组后的数据使用Having子句所包含的谓词进行过滤
HAVING COUNT(*) >= 2
Step 5:返回查询字段 ( Select )
在此步骤中,处理器将评估查询结果将要打印的内容,以及是否有一些函数要对数据运行,例如Distinct,Max,Sqrt,Date,Lower等等。本案例中,SELECT子句只会打印城市名称和其对应分组的count(*)值,并使用标识符“ City”作为city_name列的别名。
SELECT city.city_name AS "City", COUNT(*) AS "citizen_cnt"
city | citizen_cnt |
---|---|
北京 | 2 |
杭州 | 3 |
Step 6:排序与分页 ( Order by & Limit / Offset )
查询的最后处理步骤涉及结果集的排序与输出大小。在我们的示例中,按照字母顺序升序排列,并输出两条数据结果。
ORDER BY city.city_name ASC LIMIT 2
city | citizen_cnt |
---|---|
北京 | 2 |
杭州 | 3 |
看完上述内容,你们对SQL查询的原理是什么有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注亿速云行业资讯频道,感谢大家的支持。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。