MySQL关键字的执行顺序分析

---

现实遇到的问题

最近在使用mysql日期分组查询的时候，遇到需要将datetime类型日期按yyyyMM分组。

错误的写法：

SELECT
	`month` 
FROM
	article 
GROUP BY
	DATE_FORMAT( create_time, '%Y%m' ) `month`
1
2
3
4
5
6

注意：GROUP BY后面不能定义别名

正确的写法：

写法1

SELECT
	DATE_FORMAT( create_time, '%Y%m' ) `month`
FROM
	article 
GROUP BY
	DATE_FORMAT( create_time, '%Y%m' )
1
2
3
4
5
6

写法2

SELECT
	DATE_FORMAT( create_time, '%Y%m' ) `month`
FROM
	article 
GROUP BY
	`month`
1
2
3
4
5
6

写法2最终其实就是按写法1的方式去执行的。MySQL对group by进行了优化，所以支持使用select中定义的别名。

• 执行顺序为 group by => order by => select中的赋值语句

• 在MySQL中，group by和order by可以使用别名，相当于把别名的语句放在group by 和order by中，而where中不能使用别名

• group by和order by中使用别名时，赋值语句不会重复执行，只是将赋值语句的赋值顺序提前触发了

上面遇到的问题，成功勾起了我对MySQL关键字的执行顺序好奇性，本文主要记录一下对MySQL关键字执行顺序的相关分析学习。

SQL查询

常用关键字

SQL查询常用的关键字：

• select
• distinct
• from
• join（left join、 right join、…）
• on
• where
• group by
• having
• union（union all）
• order by
• limit

书写顺序

SQL查询关键字书写顺序：

1. select
2. distinct
3. from
4. join（left join、 right join、…）
5. on
6. where
7. group by
8. having
9. union（union all）
10. order by
11. limit

执行顺序

SQL查询关键字执行顺序：

1. from
2. on
3. join（left join、 right join、…）
4. where
5. group by
6. having
7. select
8. distinct
9. union（union all）
10. order by
11. limit

执行顺序详细分析：

1. from
   指定表。了解需要通过哪些表来检索数据。

   1. 单表，直接进入下一步：执行where；

   2. 隐式内连接（table1,table2或者cross join），求笛卡尔积，生成虚拟表v1。
      如果使用table1,table2，下一步骤为where

      如果使用cross join on，下一步骤为on

   3. 显示内连接（[inner] join），直接进入下一步：执行on；

   4. 左外连接（left [outer] join）），直接进入下一步：执行on；

   5. 右外连接（right [outer] join）），直接进入下一步：执行on；

2. on
   筛选条件。配合join使用，用于多表查询，根据on的条件筛选出满足条件的行，生成虚拟表 v2。

   注意：当使用inner join时，无论在on里写什么条件都会被翻译成where语句。此时，使用on其实就是使用where。

3. join（left join、 right join、…）
   连接方式。用于多表查询：

   • inner join：不作操作，继续下一步骤。
   • left join：会把左表在第2步中过滤掉的的添加进来，生成虚拟表 v3
   • right join：会把右表在第2步中过滤掉的行添加进来，生成虚拟表 v3

   注意：如果from子句中的表数目多于两个表，那么就将虚拟表v3和第三个表连接，重复1-3的步骤，最终得到一个新的虚拟表v3。

4. where
   筛选条件。根据where指定的条件，对虚拟表进行再次筛选，生成虚拟表v4。

   where与on的区别：

   • 先执行on，后执行where；
   • on是建立关联关系，在生成临时表时候执行，where是在临时表生成后对数据进行筛选的
   • on能更好的提高效率，应优先选择使用on

5. group by
   分组条件。进行分组操作，通过group by子句指定的列或列函数进行分组，生成虚拟表v5。

   注意：

   • select子句中的列名必须为分组列或列函数。
   • 后面的所有步骤都只能得到的v5的列或者是列的聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组保留一行。

6. having
   分组筛选条件。对分组后的结果进行再次筛选，生成虚拟表v7。条件中经常包含聚集函数，having筛选器是唯一一个对已分组数据的筛选器。

7. select
   返回数据列表。处理select子句。将v7中的在select中出现的列筛选出来。生成虚拟表v8 。

8. distinct
   去重。一般用于一个字段；若有多个字段时，则会对多个字段进行组合去重，只有多个字段组合起来的值是相等的才会被去重，生成虚拟表v9。

   事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。

9. union（union all）
   数据合并。union（union all）在order by执行之前执行。在用union组合查询时，只能使用一条order by子句，它必须出现在最后一条 select语句之后。

   对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条order by子句。

   注意：子查询除外，存在子查询的情况，子查询里面也可以有order by。

10. order by
    排序条件。使用order by子句。指定排序字段对v9进行排序，此时返回的一个游标，而不再是虚拟表。

    sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by。

11. limit
    行数限制。使用limit，对结果的行数进行限制，生成v10，返回结果给请求者。

关键字常见疑问

1. on和join执行顺序

on为筛选，join为连接。

• 先确定主表（驱动表）：join 主表为数据量比较小的表、left join主表为左表、right join主表为右表
• 根据on筛选条件去筛选另一个表的数据得到临时表（虚拟表）数据
• 若为left join，会把左表中被过滤掉的数据添加进临时表；若为right join，会把右表中被过滤掉的数据添加进临时表

2. cross join on执行顺序

cross join on的执行顺序和其它join有所不同。

其它join都是先通过on筛选得到临时表数据，但cross join on是先对多表进行笛卡尔积得到临时表数据，通过on对临时表数据进行筛选得到新的零时表数据。

3. on和where的区别

on先于where执行，先根据on的筛选条件进行筛选，得到临时表数据后，再根据where条件进行筛选。

所以优先是用on，性能更好。

特殊情况：当在inner join和cross join中使用on条件时，mysql都会将on后面的条件优化到where里。此时on和where效果一样。

4. where和having的区别

where和having都是对数据做筛选。

having最主要的目的是用来执行分组后的聚合函数的，因为where关键字在group by之前执行，所以没法在where后面使用聚合函数，所以才有了having关键字。

当然having关键字不仅可以执行聚合函数，同时也可以做与where一样的条件筛选。

但是若是非聚合函数的筛选条件一般不要出现在having里，这样会影响sql性能，这种数据筛选一般在where阶段就应该过滤掉，而不应该是执行到having了再去过滤这部分数据。

5. group by、having使用别名

group by关键字已经其之后执行的关键字都可以使用select中定义的别名。

虽然关键字的执行顺序是group by --> having --> select，貌似select之前，别名还没有被定义，不能使用才对。但是MySQL做了一定的优化，group by使用select定义的别名时，会提前触发别名的赋值语句。

示例可参考上文：现实遇到的问题

6. inner join与笛卡尔积的区别

举例：表table1有m条数据，表table2有n条数据

笛卡尔积：
笛卡尔积是两个表直接关联，得到的临时数据条数为m*n。

select * from table1,table2 where xxx

select * from table1 cross join table2 where xxx
1
2
3

上面两种都执行的笛卡尔积

inner join：
[inner] join、left join、right join都会先根据on筛选条件筛选，得到满足条件的临时表数据，数据条数一般

需要注意：mysql5.5以后，SQL在执行的时候基本是不会做全表的笛卡尔积了，最多只会做局部的笛卡尔积。无论是inner、left、right等都有可能产生笛卡尔积。但都只是做局部的笛卡尔积。

mysql5.5对笛卡尔积的优化可参考文章：https://blog.csdn.net/qq_36330228/article/details/125168628

7. 通过关键字对sql性能的优化

关键字优化

在SQL查询的的过程中，数据在越早的阶段被过滤掉越好，SQL性能也就越高。因此可以通过下面几种关键字优化的方式来提高SQL性能。

1. 优先选择on，而不是where

根据关键字的执行顺序，先执行on筛选再执行where筛选，越靠前越有利于提高性能。

2. 优先选择left join、right join，而不是inner join、cross join

这种join方式的优先选择，最终也是考察的：优先选择on筛选，而不是where筛选。

• inner join、cross join中使用on其实就是使用where，因为mysql的执行优化器最后都会将on解析成where。这样就导致了本来想要在on中进行条件筛选的目的落空。

  而且inner join、cross join都会进行笛卡尔积，笛卡尔积对于数据量大的表则是一个灾难性的后果。

• left join、right join的on条件并不会被解析成where，这样就能达到数据提前筛选的目的（先执行on再执行join），也不会有笛卡尔积产生。所以建议优先选择left join、right join。