关于java: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 子句。这些操作的后果是失去两个表的笛卡尔积。

在 FROM 和 JOIN 执行完结之后，会依照 JOIN 的 ON 条件，筛选所须要的行

ON citizen.city_id = city.city_id

Step 2: 过滤数据 (Where)

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

WHERE city.city_name != '上海'

Step 3: 分组 (Group by)

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

GROUP BY city.city_name

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"

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

查问的最初解决步骤波及后果集的排序与输入大小。在咱们的示例中，依照字母程序升序排列，并输入两条数据后果。

ORDER BY city.city_name ASC
LIMIT 2

总结

本文次要分析了 SQL 语句的执行程序和底层原理，根本的 SQL 查问会分为六大步骤。本文联合具体事例，给出了每一步骤的具体后果，这样会对其执行的底层原理有更加粗浅的意识。

公众号『大数据技术与数仓』，回复『材料』支付大数据资料包