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

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语句的执行顺序和底层原理，基本的sql查询会分为六大步骤。本文结合具体事例，给出了每一步骤的详细结果，这样会对其执行的底层原理有更加深刻的认识。

到此这篇关于sql查询的底层运行原理深入分析的文章就介绍到这了,更多相关sql查询底层运行原理内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！