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 cityVALUES ( 1, "上海" ), ( 2, "北京" ), ( 3, "杭州" ); INSERT INTO citizenVALUES("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_nameHAVING COUNT(*) >= 2ORDER BY city.city_name ASCLIMIT 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 citizenJOIN 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_nameGROUP_CONCAT(citizen.name) | city_id | city_name |
|---|---|---|
| jack,rachel | 2 | 北京 |
| tom,jasper,trump | 3 | 杭州 |
Step 4:分组过滤 (Having)
对分组后的数据应用Having子句所蕴含的谓词进行过滤
HAVING COUNT(*) >= 2Step 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 ASCLIMIT 2| city | citizen_cnt |
|---|---|
| 北京 | 2 |
| 杭州 | 3 |
总结
本文次要分析了SQL语句的执行程序和底层原理，根本的SQL查问会分为六大步骤。本文联合具体事例，给出了每一步骤的具体后果，这样会对其执行的底层原理有更加粗浅的意识。
公众号『大数据技术与数仓』，回复『材料』支付大数据资料包