MySQL关键字的执行顺序分析

桃扇骨 2024-04-08 11:52 100阅读 0赞

#### 文章目录 ####

*  现实遇到的问题
 *  SQL查询
 *   *  常用关键字
     *  书写顺序
     *  执行顺序
 *  关键字常见疑问
 *   *  1. on和join执行顺序
     *  2. cross join on执行顺序
     *  3. on和where的区别
     *  4. where和having的区别
     *  5. group by、having使用别名
     *  6. inner join与笛卡尔积的区别
     *  7. 通过关键字对sql性能的优化
 *  关键字优化
 *   *  1. 优先选择on，而不是where
     *  2. 优先选择left join、right join，而不是inner join、cross join

--------------------

## 现实遇到的问题 ##

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

**错误的写法：**

SELECT
    	`month` 
    FROM
    	article 
    GROUP BY
    	DATE_FORMAT( create_time, '%Y%m' ) `month`

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

**正确的写法：**

写法1

SELECT
    	DATE_FORMAT( create_time, '%Y%m' ) `month`
    FROM
    	article 
    GROUP BY
    	DATE_FORMAT( create_time, '%Y%m' )

写法2

SELECT
    	DATE_FORMAT( create_time, '%Y%m' ) `month`
    FROM
    	article 
    GROUP BY
    	`month`

写法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

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

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

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

mysql5.5对笛卡尔积的优化可参考文章：[https://blog.csdn.net/qq\_36330228/article/details/125168628][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。

[https_blog.csdn.net_qq_36330228_article_details_125168628]: https://blog.csdn.net/qq_36330228/article/details/125168628