Mysql关键字执行顺序-深入解析

mysql关键字执行顺序如下：

from
on/using
join
where
group by
having
select
distinct
union
order by
limit

一、解释关键字执行顺序

这些关键字涉及的sql笔者这里就不一一去写了。

Mysql会先执行from，然后根据on关键字去筛选目标表，筛选出的结果再进行join或者using，这样就会形成一个临时表。然后去执行where条件去筛选这个临时表，这样基本就筛选出需要的数据了。然后就可以对数据进行group by进行分组，同时若是有必要就会再执行having对数据进行进一步筛选，这里执行完数据基本就是一定定型了，下面就需要select去筛选目标列了，完事之后需要使用distinct进行去重这样一个表的查询基本就结束了。若是需要多表查询则还需要使用union或者union all来进行连接多表的结果。然后就是对数据进行排序的order by ，排完顺序自然就是取分页了。这样就会将一个完整的sql执行完毕了。

二、关于执行顺序可能会有的疑问

对于上面这个执行顺序，都是无需置疑的，不过对于为什么是这样一个顺序笔者曾经也是困惑过的，下面笔者总结了一下可能会产生困惑点，这里列下。

1 on和join 为什么先执行on

我最开始一直认为没有使用join形成一个临时表，那是如何对数据使用on进行条件筛选呢一直不太明白。后来才知道其实在使用left join 或者 right join 时临时表的主体数据本来就是确定的，比如使用left join 那么主表就是左表，那主表已经确定了，所以可以先执行on条件去筛选另一个表的数据，筛选完成后再将两个结果拼接形成一个临时表，mysql这么做就说的通了。rigint join 与left join类似，这里就不重复说了。

上面是用left join 说的例子。那cross join 与 inner join 好像都是会形成笛卡尔积的（mysql5.5以后已经不会有全表的笛卡尔积了）。所以若是两个表在inner join执行前是不知道临时表的数据的，那怎么来使用on条件进行筛选呢？这种情况就不对了啊。所以根据这个点很多人可能会认为应该限制性join 再执行on，其实则不然。

对于inner join 和 cross join 在他们后面使用on条件时，mysql都会将on后面的条件优化到where里，也就是说其实mysql在执行cross join 或者 inner join 时是不会有 on的，这里写on其实写的就是where。所以说cross join 和 inner join 里不存在on语句的，自然就没有先用on条件筛选的场景了。使用 inner join 或者 corss join 无论在on里写什么条件都会被翻译成where语句。所以到这里应该就会明白mysql先执行on关键字再执行 join 关键字是没有问题的了。

下面展示下inner join on 被优化前后的sql对比。

原始sql：

select a.user_id from sys_user  a 
inner join sys_user_role  b on a.user_id = b.user_id; 

被执行优化器优化后的sql（mysql真正执行的语句）：

select `sunacwyselect `sunacwy_cockpit`.`a`.`user_id` AS `user_id` from `sunacwy_cockpit`.`sys_user` `a` 
join `sunacwy_cockpit`.`sys_user_role` `b` where (`sunacwy_cockpit`.`a`.`user_id` = `sunacwy_cockpit`.`b`.`user_id`)

通过上面的例子，就可以验证上面笔者说的内容了。有兴趣的同学可以在测试下非等值连接，自然连接等其他场景。

2 on和where 有什么区别，哪些场景是相同的

当on使用在left join 和 right join 后面时，会对连接数据先进行筛选，然后才会把符合on条件的数据进行连接。连接完成后才会执行where后面的筛选条件。

当on使用在cross join 和 inner join 后面时，就不一样了。此时on和where效果一致，因为这里的on会被sql执行优化器，优化成where，所以这里的on和where是相同的。这一点上面的例子中已经证明了，这里就不重复写了。

3 where和having 的区别

where 和 having在一定程度上可以做一些相同的事，那他们有什么不同呢，其实having最主要的目的是用来执行分组后的聚合函数的，因为where关键字在group by之前执行，所以没法在where后面使用聚合函数，所以才有了having关键字。当然having关键字不仅可以执行聚合函数，同时也可以做与where一样的条件筛选。不过这里需要说明的事，若是非聚合函数的筛选条件一般不要出现在having里，这样会影响sql性能，这种数据筛选一般在where阶段就应该过滤掉，而不应该是执行到having了再去过滤这部分数据。

三、关键字对于sql优化的启示

我们知道更多的数据在越早的阶段被过滤掉越好，sql性能也越高。所以根据这一原则，我们根据sql关键字的执行顺序可以总结出这么一句话：

1.能写在on里的条件，不要写在where里，能写在where里的条件不写在having里

这个很好理解根据各个关键字的执行顺序，很容易就可以看出来先执行on再执行where最后执行haing，所以条件越靠前月有利于sql的执行。不过这里还是需要区分场景的，我们可以从内连和外连两个角度分析这个问题。

1）内连时

内连时，我们写的on条件其实都会解析成where条件，所以我们将条件写在on或者where里是没有区别的，所以此时是on=where>having的关系。

2）外连时

外连时，我们无论以哪个表为基表，那么都是对基表进行全表扫描，所以外连里的on并不会对数据筛选，只有where才会对连接的结果进行筛选，那此时的on其实起不到数据过滤的作用，只有where和having才有过滤数据的作用。

3）总结来说，on起到的过滤作用只是在外连时过滤被连接的表有些作用，因为在内连里本质上没有on，外连时on其实不会过滤基表，只过滤了被连接的表。所以若是必须使用外连时，我们需要尽可能过滤被连接表，然后在where里加筛选条件即可。若是非必要使用外连，建议还是使用内连，因为内连不会全表扫描。

2.使用inner join 或者cross join 、left join 、right join、子查询 怎么选择

先建设一个场景有两个表我们需要关联查询,条件固定（详见下面的sql），需要建立的索引均已建立

①如果我们使用inner join，我们可能会像下面这么写：

select fl.* from tb_mdm_floor fl 
inner join tb_mdm_unit un on fl.unit_id = un.id 
where un.id in (
'16611347838871536931',
'16611347838881537131',
'16611386992314864604',
'16611386992304864504',
'16611386992294864404'
);

②如果我们使用left join，我们可能会像下面这么写：

select fl.* from tb_mdm_floor fl
left join  tb_mdm_unit un on fl.unit_id = un.id  
where un.id in (
'16611347838871536931',
'16611347838881537131',
'16611386992314864604',
'16611386992304864504',
'16611386992294864404'
);

③如果我们使用子查询，我们可能会像下面这么写：

select * from tb_mdm_floor where unit_id in 
(
select id from tb_mdm_unit where id in (
'16611347838871536931',
'16611347838881537131',
'16611386992314864604',
'16611386992304864504',
'16611386992294864404'
));

那这三种查询效率该怎么选择呢，我们分别使用explain看下三种写法扫描的行数就会有感觉了

第①种explaian的分析结果

第②种explaian的分析结果

第③种explaian的分析结果

这样的话效率的高低其实就很简单明了了，我们可以看到使用子查询和内连时数据的扫描行数最少，而使用外连时会扫描基表的所有行数，最后再使用where过滤基表的全部信息。所以我们可以知道需要优先使用内连和子查询。那内连和子查询我们怎么判定优先级呢？若是在mysql5.5以前，肯定是优先使用子查询的，因为mysql5.5以前使用内连会进行全表的笛卡尔积，全表的笛卡尔积是一件很恐怖的事情。但是mysql5.5以后对连接进行了优化，不会再进行全表的笛卡尔积了，使用了一种BNL的算法，进行了优化（未使用索引的情况下，使用索引会使用INL算法）。上面我们使用explain查看的其实是优化后的结果。所以暂时来看子查询与内连效率上没什么差别，但是理论上子查询会更好些。所以我们可以这么总结：子查询 > 内连 > 外连

3.关于连表查询时的笛卡尔的说明

首先我们需要明确的是mysql5.5以后，sql在执行的时候基本是不会做全表的笛卡尔积的，最多都是局部进行笛卡尔积。无论是inner、left、right等都有可能产生笛卡尔积。但现在肯定不会有全表的笛卡尔积了。关于为什么连表查没有全表的笛卡尔积，以及局部又是如何进行优化计算的，请看下这篇文章，这位道友写的还是很通俗易懂的：https://blog.csdn.net/qq_36330228/article/details/125168628