Mysql查询语句的执行过程原理及研究

最近在思考mysql查询语句优化的一些方方面面，网上可以看到的有很多，例如，加索引啊，去看explain执行过程看有没有走索引，看执行的步骤，然后还有一些是进行分库分表等等反正思路有很多，但是，真正要说优化mysql查询的话，我相信，最基本的你还是得知道他查询语句是如何去执行的原理，这样才能去从sql语句的角度去分析，去看如何能进行优化。

一般来讲，在目前时长比较主流的关系型数据库来说，比较多用的是mysql，而mysql的查询语句执行的大体过程一般如下：

客户端通过连接器连接到MySQL服务器。
连接器权限验证通过之后，先查询是否有缓存，如果有缓存（之前执行过该条语句）则返回缓存数据，如果不存在缓存数据则进入分析器
分析器会对查询语句进行语法分析和词法分析，以判断sql语法是否正确，如果查询语句语法错误直接返回客户端错误信息，如果正确则进入优化器
优化器会对查询语句进行优化处理，比如一个表中存在多个索引，优化器会比较判别哪个索引性能更好
优化器执行完就进入执行器，执行器就开始时执行查询语句进行查询比对了，直到查询到满足条件的所有数据，然后返回数据。

以上说的都是比较概念性的执行过程，下面我们来看一条实例，来具体讲解一条SQL语句如何执行。

首先，创建一张student表，字段有自增主键id，学生姓名name，学科subject，成绩grade。

DROP TABLE IF EXISTS student;
CREATE TABLE `student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `subject` varchar(10) DEFAULT NULL,
  `grade` double(4,1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

然后来看下这条语句:

SELECT 
  `name`, 
  COUNT(`name`) AS num 
FROM 
  student 
WHERE 
  grade < 60 
GROUP BY 
  `name` 
HAVING 
  num >= 2 
ORDER BY 
  num DESC, 
  `name` ASC 
LIMIT 
  0, 2;

可以看到几乎涵盖了一条查询语句会涉及到的语法点，我直接按照执行的顺序排序吧:

select
where
group
having
order by
limit

下面我们就来细节的解析下:

1，一条查询的sql语句先执行的是 FROM student 负责把数据库的表文件加载到内存中去.

紧接着，会去执行where中的条件把其中的数据进行过滤，取出符合条件的，生成一张临时表。
如果有运用到group by的话，那么会把前面第一张临时表的数据划分切割成若干临时表，在内存中。上面的条件是group by “name”,那么就会按照name阻断属性，去切割。

可以看到因为有4个不同的名字，所以会切割为4张。
紧接着看上一步,如果没有group by的情况呢？

这时候又会分为两种:

1.无group by，则会根据where中的条件，在临时表种筛选去读取。

2.如果有，则会在内存中，对所有的临时表分别执行select，而且只取临时表中的第一条记录，然后形成新的临时表，怎么说呢？字面意思，大家好好体会体会。然后我们可以得到一些东西:

查询sql使用GROUP BY的场景下，SELECT后面跟的一般是参与分组的字段和聚合函数，否则查询出的数据要是情况而定。（聚合函数就是指having后面跟着的一些函数包括: max() \count() \ sub() 、。。。）。

另外聚合函数中的字段可以是表中的任意字段，需要注意的是聚合函数会自动忽略空值。

HAVING num >= 2对上图所示临时表中的数据再次过滤，与WHERE语句不同的是HAVING 用在GROUP BY之后，WHERE是对FROM student从数据库表文件加载到内存中的原生数据过滤，而HAVING 是对SELECT 语句执行之后的临时表中的数据过滤，所以说column AS otherName ,otherName这样的字段在WHERE后不能使用，但在HAVING 后可以使用。但HAVING的后使用的字段只能是SELECT 后的字段，SELECT后没有的字段HAVING之后不能使用。HAVING num >= 2语句执行之后生成一张临时表

6.倒数第二个，order by 会对之前几个执行条件过滤之后的临时表在进行排序

limit 0，2 取后排的前两个。

以后就是一条普通的sql查询具体的语法，内部真正的执行过程，仅限于mysql哈，其他的暂不算在里面。

后面会继续研究一些查询优化的内容。

参考自:
Mysql查询语句执行过程及运行原理