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>
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 杭州

SQL查询的底层运行原理深入分析

扫一扫手机访问