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 ...

但是执行顺序并不是这样，具体的执行顺序如下步骤所示：

  • 1.获取数据 (From, Join)
  • 2.过滤数据 (Where)
  • 3.分组 (Group by)
  • 4.分组过滤 (Having)
  • 5.返回查询字段 (Select)
  • 6.排序与分页 (Order by & Limit / Offset)

尖叫提示：本文旨在说明通用的SQL执行底层原理，对于其优化技术不做考虑，比如谓词下推、投影下推等等。

执行的底层原理

其实上面所说的SQL执行顺序就是所谓的底层原理，当我们在执行SELECT语句时，每个步骤都会产生一张虚拟表(virtual table），在执行下一步骤时，会将该虚拟表作为输入。指的注意的是，这些过程是对用户透明的。

你可以注意到，SELECT 是先从FROM 这一步开始执行的。在这个阶段，如果是多张表进行JOIN，还会经历下面的几个步骤：

获取数据 (From, Join)

  • 首先会通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt1-1；

  • 接着通过ON 条件进行筛选，虚拟表 vt1-1 作为输入，输出虚拟表 vt1-2；

  • 添加外部行。我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3

过滤数据 (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子句。这些操作的结果是得到两个表的笛卡尔积。

namecity_idcity_idcity_name
tom31上海
tom32北京
tom33杭州
jack21上海
jack22北京
jack23杭州
robin11上海
robin12北京
robin13杭州
jasper31上海
jasper32北京
jasper33杭州
kevin11上海
kevin12北京
kevin13杭州
rachel21上海
rachel22北京
rachel23杭州
trump31上海
trump32北京
trump33杭州
lilei11上海
lilei12北京
lilei13杭州
hanmeiei11上海
hanmeiei12北京
hanmeiei13杭州

在FROM和JOIN执行结束之后，会按照JOIN的ON条件，筛选所需要的行

ON citizen.city_id = city.city_id
namecity_idcity_idcity_name
tom33杭州
jack22北京
robin11上海
jasper33杭州
kevin11上海
rachel22北京
trump33杭州
lilei11上海
hanmeiei11上海

Step 2:过滤数据 (Where)

获得满足条件的行后，将传递给Where子句。这将使用条件表达式评估每一行。如果行的计算结果不为true，则会将其从集合中删除。

WHERE city.city_name != '上海'
namecity_idcity_idcity_name
tom33杭州
jack22北京
jasper33杭州
rachel22北京
trump33杭州

Step 3:分组 (Group by)

下一步是执行Group by子句，它将具有相同值的行分为一组。此后，将按组对所有Select表达式进行评估，而不是按行进行评估。

GROUP BY city.city_name
GROUP_CONCAT(citizen.name)city_idcity_name
jack,rachel2北京
tom,jasper,trump3杭州

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"
citycitizen_cnt
北京2
杭州3

Step 6:排序与分页 (Order by & Limit / Offset)

查询的最后处理步骤涉及结果集的排序与输出大小。在我们的示例中，按照字母顺序升序排列，并输出两条数据结果。

ORDER BY city.city_name ASC
LIMIT 2
citycitizen_cnt
北京2
杭州3

总结

本文主要剖析了SQL语句的执行顺序和底层原理，基本的SQL查询会分为六大步骤。本文结合具体事例，给出了每一步骤的详细结果，这样会对其执行的底层原理有更加深刻的认识。

公众号『大数据技术与数仓』，回复『资料』领取大数据资料包

posted @ 2020-08-22 15:25  大数据技术与数仓  阅读(338)  评论(0)  编辑  收藏  举报