MySQL各部分的执行顺序

前几天参加了一个公司的面试，到了后面面试官出了一个SQL相关的题目：

数据的形式类似于以下这样（表名为views）：

当时为了稳妥起见，我的第一反应是使用窗口函数，

然后面试官问：“还有没有什么简便的方法么？”

很明显他的意思是要用传统的groupby来完成这个查询，确实我之前的查询又是用窗口函数又是加了distinct确实是复杂一些。

于是我用group by再写了一遍。

看完我的查询之后，面试官又问了一句：“可以不需要使用嵌套查询吗？”

当时我的回答是”应该不行，如果不使用嵌套而直接在group by后面加having的话sql会报错，就和where如果使用别名查询就会报错一样“

后面面试完想了一下，发现自己当时回答得不好，不是正确的但也不完全错，不是正确的原因是按照sql的规则having后面是可以拿聚合函数做判断的，但是不完全错的原因是如果having用的是像我之前设置的别名来判断的话，确实是会出错的。（虽然mysql在5.6之后基于sql的规则对group by进行拓展，支持这种写法。但在其它sql上面用别名having还是不行的）

我们可以从SQL运行时各部分的执行顺序来进行分析，当我们选择执行一个SQL语句的时候，它会按照以下的顺序来进行 *** 作，

这个执行顺序的设计是很巧妙的，我说一下我自己对于上述顺序的理解，

1. FROM

顾名思义，当执行查询语句的时候，首先需要知道的是它需要哪些表，正如我们去一个地方需要知道它的具体位置一样。如果需要多个表的话在这一部分也需要按照一定的顺序进行表的join *** 作。

2. WHERE

当确定我们需要读取哪一张表（或者多张表）的数据之后，我们就需要进行where的filter *** 作，根据filter尽量减少读取的数据数量。

那么问题来了，为什么where的优先级要比group by，having, select之类的要高呢？

第一个原因是可以减少不必要的查询量，加快执行语句的速度，类似于Apache Spark在对查询语句进行逻辑优化时需要用到的谓词下推类似的道理。举个栗子，比如我们可能需要userid从100到300的用户对于某一个页面的浏览次数，那么如果先执行group by再执行where的话，userid小于100的用户的数据也会被汇总进去，但实际上这些部分的数据是完全不需要的，计算它们完全是浪费系统资源（而且group by *** 作本身就是很耗资源的 *** 作）

3. GROUP BY

在完成where *** 作的过滤之后，如果语句中有group by的话则会对过滤后的数据进行聚合 *** 作，聚合 *** 作是多对一的转换，因此在聚合 *** 作过后，除了用于group by的字段之外，其它字段的原始数据将会丢失，只能得到它们相应的聚合结果（比如sum（）， avg（）这样）

在完成聚合 *** 作之后，参与group by的字段以及其它字段对应的聚合值已经处于已知状态，后续的 *** 作可以直接使用它们。

4. HAVING

HAVING *** 作主要做的是对group by之后的分组结果进行过滤，可以根据参与group by的字段进行过滤，也可以根据其它字段的聚合值进行过滤。（因为聚合值在这里已经算是已知数据）因此这里是可以拿聚合函数做判断的，比如最开始的那个查询的例子，可以直接写成以下的形式，

HAVING并不是一定要和group by成对出现的，它也可以单独存在，在没有group by的时候，此时默认只有一个组，但是需要注意的是这时having里面参与过滤的字段需要在select里面存在，不然having会不知道这是分组里面的内容而导致报错。

5. SELECT

选取结果集中相对应的字段，在select中为字段设置的别名在此阶段及之后的 *** 作中生效。

6. DISTINCT

去重 *** 作，放在select之后有个原因是去重 *** 作是要根据select里面所选字段来进行的。

7. ORDER BY

对得到的结果按照特定字段顺序进行排列，这里可以使用别名

8. LIMIT

设置显示结果集中的几条数据

通过分析MySQL中各部分的执行顺序，我们就不难理解为什么where不能有别名，而having可以用聚合函数来判断的原因，而且借此机会重新温习一遍SQL各部分对应的功能，加深理解，可以说是一举两得。

如果查询缓存没有命中，那么SQL请求会进入分析器，分析器是用来分辨SQL语句的执行目的，其执行过程大致分为两步：

表1 语法分析关键字然后再通过语法规则解析，判断输入的SQL 语句是否满足MySQL语法，并且生成图5的语法树。由SQL语句生成的四个单词中，识别出两个关键字，分别是select 和from。根据MySQL的语法Select 和 from之间对应的是fields 字段，下面应该挂接username；在from后面跟随的是Tables字段，其下挂接的是userinfo。

优化器的作用是对SQL进行优化，生成最有的执行方案。如图6所示，前面提到的SQL解析器通过语法分析和语法规则生成了SQL语法树。这个语法树作为优化器的输入，而优化器（黄色的部分）包含了逻辑变换和代价优化两部分的内容。在优化完成以后会生成SQL执行计划作为整个优化过程的输出，交给执行器在存储引擎上执行。

所处的位置如上图所示，这节的重点在优化器中的逻辑变换和代价优化上。

逻辑变换也就是在关系代数基础上进行变换，其目的是为了化简，同时保证SQL变化前后的结果一致，也就是逻辑变化并不会带来结果集的变化。其主要包括以下几个方面：

这样讲概念或许有些抽象，通过图7 来看看逻辑变化如何在SQL中执行的吧。

如图7所示，从上往下共有4个步骤：

1. 针对存在的SQL语句，首先通过“否定消除”，去掉条件判断中的“NOT”。语句由原来的“or”转换成“and”，并且大于小于符号进行变号。蓝色部分为修改前的SQL，红色是修改以后的SQL。2. 等值传递，这一步很好理解分别降”t2.a=9” 和”t2.b=5”分别替换掉SQL中对应的值。3. 接下来就是常量表达式计算，将“5+7”计算得到“12”。4. 最后是常量表达式计算后的化简，将”9<=10”化简为”true”带入到最终的SQL表达式中完成优化。

代价优化是用来确定每个表，根据条件是否应用索引，应用哪个索引和确定多表连接的顺序等问题。为了完成代价优化，需要找到一个代价最小的方案。因此，优化器是通过基于代价的计算方法来决定如何执行查询的（Cost-based Optimization）。简化的过程如下：

这里将配置 *** 作的代价分为MySQL 服务层和MySQL 引擎层，MySQL 服务层主要是定义CPU的代价，而MySQL 引擎层主要定义IO代价。MySQL 5.7 引入了两个系统表mysql.server_cost和mysql.engine_cost来分别配置这两个层的代价。如下：MySQL 服务层代价保存在表server_cost中，其具体内容如下：

由上可以看出创建临时表的代价是很高的，尤其是内部的myisam或innodb临时表。MySQL 引擎层代价保存在表engine_cost中，其具体内容如下：

目前io_block_read_cost和memory_block_read_cost默认值均为1，实际生产中建议酌情调大memory_block_read_cost，特别是对普通硬盘的场景。MySQL会根据SQL查询生成的查询计划中对应的 *** 作从上面两张代价表中查找对应的代价值，并且进行累加形成最终执行SQL计划的代价。再将多种可能的执行计划进行比较，选取最小代价的计划执行。

当分析器生成查询计划，并且经过优化器以后，就到了执行器。执行器会选择执行计划开始执行，但在执行之前会校验请求用户是否拥有查询的权限，如果没有权限，就会返回错误信息，否则将会去调用MySQL引擎层的接口，执行对应的SQL语句并且返回结果。例如SQL：“SELECT * FROM userinfo WHERE username = 'Tom'“假设 “username“ 字段没有设置索引，就会调用存储引擎从第一条开始查，如果碰到了用户名字是” Tom“， 就将结果集返回，没有查找到就查看下一行，重复上一步的 *** 作，直到读完整个表或者找到对应的记录。需要注意SQL语句的执行顺序并不是按照书写顺序来的，顺序的定义会在分析器中做好，一般是按照如下顺序：

如果命中的记录比较多，应用会从MySql Server一批批获取数据

本文从MySQL中SQL语句的执行过程作为切入点，首先介绍了查询请求的执行流程，其中将MySQL的处理分为MySQL Server层和MySQL存储引擎层。通过介绍SQL语句的流转，引出了后面要介绍的5大组件，他们分别是：连接器、查询缓存、分析器、优化器、执行器。后面的内容中对每个组件进行了详细的介绍。连接器，负责身份认证和权限鉴别；查询缓存，将查询的结果集进行缓存，提高查询效率；分析器，对SQL语句执行语法分析和语法规则，生成语法树和执行计划；优化器，包括逻辑变换和代价优化；执行器，在检查用户权限以后对数据进行逐条查询，整个过程遵守SQL语句的执行顺序。

（1）一般而言在group by语句中，的结果显示只能是分组字段，如果有其他字段那么必须是带有聚合函数的，比如count,sum,max,min等，但是这里直接放个*就可以执行了？

（2）如果是在group by之前筛选，那么怎么count，是选择oid=5的进行count么？如果不进行分组，数据库怎么能够知道按照什么规则进行count.

个人建议，这样

select cuid,count(oid) from orders

group by cuid

having count(oid)=5

和

select cuid,count(oid) from orders

group by cuid

对比一下，我忘了mysql有没有minus

如果有的话，

select cuid,count(oid) from orders group by cuid

minus

select cuid,count(oid) from orders group by cuid having count(oid)=5

就应该能看出来了。

---