摘要： SQL语句执行顺序及实际案例

关键词： 数据库、MySQL、SQL

整体说明

本文主要是以MySQL数据库为例，介绍SQL语句执行顺序及其实际案例，为平时编写SQL时，会遇到的比较迷惑的问题，寻找底层思路。

一、SQL顺序概况

平时我们写sql的时候有没有注意sql语句是按照什么顺序执行的呢？

本篇文章就来介绍下这个顺序，参考了《MySQL技术内幕 SQL编程》，有兴趣可以去学习一下。

如上图所示，SQL 的执行顺序是按照这个顺序执行的 总共11个步骤， 第一步是 FROM ，最后一步是 LIMIT ，每个操作都会产生一张虚拟表，这些虚拟表对用户不是透明的，只有最后一步生成的虚拟表才会返回给用户。 具体每一步的内容如下：

1. FROM： 对FROM子句中的左表和右表执行笛卡尔积，产生虚拟表VT1
2. ON： 对虚拟表VT1应用ON筛选，只有那些符合的行才被插入虚拟表VT2中
3. JOIN： 如果指定了OUTER JOIN（如LEFT OUTER JOIN ，RIGTH OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3，直到处理完所有的表为止
4. WHERE： 对虚拟表VT3应用VT3应用WEHRE过滤条件，只有符合的记录才被插入虚拟表VT4中
5. GROUP BY： 根据GROUP BY 子句中的列，对VT4中的记录进行分组操作，产生VT5
6. CUBE|ROLLUP： 对表VT5进行CUBE或ROLLUP操作，产生表VT6
7. HAVING： 对虚拟表VT6应用HAVING过滤器，只有符合的记录才被插入虚拟表VT7中
8. SELECT： 选定指定的列，插入到虚拟表VT8中
9. DISTINCT： 去除重复数据，产生虚拟表VT9
10. ORDER BY： 将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10
11. LIMIT： 取出指定行的数据，产生虚拟表VT11，并返回给查询用户

二、数据准备

2.1、数据表结构

CREATE TABLE `student` (
  `student_id` int(11) COMMENT "学生id",
  `student_name` varchar(255) COMMENT "学生姓名",
  `subject` varchar(255) COMMENT "学科名称"
) comment = "学生表";

CREATE TABLE `orders` (
  `student_id` int(11) COMMENT "学生id",
  `order_id` int(11) COMMENT "排序id"
) comment = "学生排序表";

2.2、数据表数据

INSERT INTO `student` VALUES (1, '张三', '语文');
INSERT INTO `student` VALUES (2, '李四', '数学');
INSERT INTO `student` VALUES (3, '王二', '语文');
INSERT INTO `student` VALUES (4, '权一', '音乐');
INSERT INTO `student` VALUES (5, '孙五', '语文');

INSERT INTO `orders` VALUES (1, 1);
INSERT INTO `orders` VALUES (2, 2);
INSERT INTO `orders` VALUES (3, 1);
INSERT INTO `orders` VALUES (4, 4);

三、关键字顺序举例

3.1、FROM

对FROM子句前后的两张表进行笛卡尔积操作，生产虚拟表VT1。 如果FROM子句前的表中包含a行数据，FROM子句后的表中包含b行数据，那么虚拟表VT1中将包含a*b行数据。

3.2、ON

SELECT查询一共有3个过滤过程，分别是ON，WHERE，HAVING。 ON是最先执行的过滤过程。根据上一个步骤产生的虚拟表VT1，应用ON进行过滤，产生虚拟表VT2。

3.3、JOIN

这一步只有在连接类型为OUTER JOIN时才发生，如LEFT OUTER JOIN，RIGHT OUTER JOIN，FULL OUTER JOIN。 虽然在大多数时候我们可以省略OUTER关键词，但OUTER代表的就是外部行。 LEFT OUTER JOIN把左表记为保留表，RIGHT OUTER JOIN把右表记为保留表，FULL OUTER JOIN把左右表都记为保留表。 添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表的数据被赋予NULL值，最后生成虚拟表VT3。 由于孙五没有排序信息，所以排序表相关信息赋值为NULL。

如果需要连接表的数量大于2，则对虚拟表VT3重做步骤1-步骤3，最后产生的虚拟表作为下一个步骤的输出

3.4、WHERE

对上一个步骤产生的虚拟表VT3进行WHERE条件过滤，只有符合 where 条件 的记录才会输出到虚拟表VT4中。 在当前应用WHERE过滤器时，有两种过滤是不被允许的：

• 由于数据还没有分组，因此现在还不能在WHERE过滤中使用where_condition=MIN(col)这类对统计的过滤。
• 由于没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如SELECT student_name as c FROM student WHERE c = "张三" 是不允许出现的。

3.5、GROUP BY

在本步骤中根据指定的列对上个步骤中产生的虚拟表进行分组，最后得到虚拟表VT5

3.6、CUBE|ROLLUP

在本步骤中使用ROLLUP做分各个小组"小计" 进行汇总，得到VT6

3.7、HAVING

这是最后一个条件过滤了，之前已经分别应用了ON和WHERE过滤。 在该步骤中对于上一步产生的虚拟表应用HAVING过滤，HAVING是对分组条件进行过滤的筛选，得到VT7。

3.8、SELECT

虽然SELECT是查询中最先被指定的部分，但是直到这时才真正进行处理。 在这一步中，将SELECT中指定的列从上一步产生的虚拟表中选出，得到VT8。

3.9、DISTINCT

如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存中存放不下就放到磁盘上）。这张内存临时表的表结构和上一步产生的虚拟表一样，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来去除重复数据，得到VT9。

3.10、ORDER BY

根据ORDER BY子句中指定的列对上一个输出的虚拟表进行排列，返回新的虚拟表VT10。

3.11、LIMIT

在该步骤中应用LIMIT子句，从上一步骤的虚拟表选出从指定位置开始的指定行数据。 对于没有应用ORDER BY的LIMIT子句，结果同样可能是无序的，因此LIMIT子句通常和ORDER BY子句一起使用，得到VT11。

四、实际案例

4.1、LEFT JOIN ON 和 WHERE

4.1.1、LEFT JOIN ON

1. 查询SQL：

select *
from student t1
left join orders t2
on t1.student_id = t2.student_id
and t2.order_id = '1'
;

2. 执行结果：

3. 结果分析：

• 执行位置： ON 是在 FROM 之后 JOIN 之前执行。
• 处理方式： ON 是在关联数据之前，优先筛选右表 orders 数据。
• 查询1详细图解如下：

4.1.2、WHERE

1. 查询SQL：

select *
from student t1
left join orders t2
on t1.student_id = t2.student_id
where t2.order_id = '1'
;

2. 执行结果：

3. 结果分析：

• 执行位置： WHERE 是在 JOIN 之后执行。
• 处理方式： WHERE 是在关联数据之后，筛选数据，整个数据集都被筛选了。
• 查询2详细图解如下：

4.1.3、对比总结

ON 的条件会率先影响表数据，如果后面有关联，会按照 ON 筛选之后的结果关联。 WHERE 的条件是在关联之后才影响的，是影响整个关联结果集。

4.2、DISTINCT 和 GROUP BY

4.2.1、DISTINCT

1. 查询SQL：

select
distinct
student_id,student_name
from student

2. 执行结果：

3. 结果分析：

• 执行位置： DISTINCT 是在 SELECT 子句之后、ORDER BY 之前执行的。这意味着它是在所有之前的步骤（包括连接、过滤和聚合）完成后才开始工作。
• 处理方式： 数据库引擎会扫描整个结果集，构建一个内部临时表或使用哈希表来存储唯一的组合。对于每一行，检查是否已经存在于临时表中；如果存在，则丢弃该行；否则将其加入到临时表中。最终从这个临时表中提取唯一的数据行返回给用户。
• 性能考虑： 需要遍历所有记录，因此对于大规模数据集来说，这可能导致较高的 CPU 和内存消耗。如果没有合适的索引支持，可能会导致全表扫描，进一步降低性能。

4.2.2、GROUP BY

1. 查询SQL：

select student_id,student_name
from student
group by student_id,student_name

3. 执行结果：

4. 结果分析：

• 执行位置： GROUP BY 在 WHERE 子句之后、HAVING 和 SELECT 子句之前执行。这意味着它在过滤掉不需要的行后立即生效，可以利用索引加速分组操作。
• 处理方式： 数据库引擎会根据 GROUP BY 列对结果集进行排序或使用哈希算法进行分组。每个组内的数据可以通过聚合函数（如 COUNT, SUM, AVG 等）进行计算。只有在分组完成之后，才会选择最终输出的列（即 SELECT 子句），并且可以在 HAVING 子句中对分组后的数据进行进一步过滤。
• 性能考虑： 如果 GROUP BY 列上有适当的索引，数据库可以快速找到并组织相关记录，从而提高分组效率。分组操作可以并行化执行，特别是在多核处理器上，能够更好地利用硬件资源。使用聚合函数时，可以直接在分组过程中计算统计信息，减少了额外的处理步骤。

4.2.3、对比总结

DISTINCT 是在筛选数据 SELECT 之后执行，无法使用到索引，也无法利用多核处理性能去并行化处理，当数据量庞大的时候，性能很差，而 GROUP BY 是在 SELECT 之前执行，就能够利用这些优点。