MySQL基础架构之一条SQL查询语句是如何执行的？

本篇文章给大家分享《MySQL基础架构之一条SQL查询语句是如何执行的？》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

前言

MySQL数据库作为优秀的关系型开源数据库，受到所有人的青睐，我们日常工作中，和其打交道的次数十分频繁，那么你知道我们在写sql的时候，MySQL内部是如何执行的吗？接下来我们就一起探讨一下吧。

MySQL的架构

以

select * from T where ID=10;

这条sql语句为例：
当我们执行该查询语句时，会收到MySQL给我们返回的结果，那么其在MySQL中是如何一步步执行的呢？
首先我们了解一下MySQL的架构图，看一下MySQL服务的组织结构：

如上图所示：
MySQL大致可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，以及所有内置的函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，如存储过程、触发器、视图等。
存储引擎层则负责数据的存取。架构是插件式的，支持 InnoDB、MyISAM、Memory等多个存储引擎。MySQL 5.5.5版本之后，其默认存储引擎为InnoDB。
所有的存储引擎共用一个Server层。

预埋问题：memory 引擎和 redis 两者的区别

连接器

平时我们命令行连接MySQL数据库时的命令为：

mysql -h127.0.0.1 -P6293 -uroot -p

这条命令就是走到了连接器去验证用户身份。
其中命令中的

mysql

是客户端工具，用来和服务端建立连接。在完成TCP握手之后，连接器开始使用输入的参数信息进行身份认证。

• 如果用户名或密码不正确，会有一个

  Access Denied for user

  的错误，客户端中断。
• 如果验证通过，连接器会到权限表里面查出用户权限并存在变量中。之后该连接里面的权限判断逻辑，都将依赖于此时读取到的权限。

此时我们应该就会理解，为什么我们给某个用户修改了权限之后，已经存在的该用户的连接不会变更为新的权限。要想新的权限生效，只能新建该用户的连接。

连接完成后，若没有后续的动作，该连接就处于空闲状态，可通过

show processlist

命令查看所有连接状态。其中

Command

列显示为

Sleep

表示系统中有一个空闲连接。

客户端如果长时间处于Sleep，连接器会将其自动断开，断开时间基于配置的

wait_timeout

参数，默认值是8小时。

如果连接断开之后，客户端再次发送请求的话，就会收到错误：

Lost connection to MySQL server during query

。此时，继续执行请求就需要重连。

MySQL中，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很小的几次查询就断开连接，下次查询重建新连接。

建立连接的过程一般是比较复杂的，所以我们应该尽量使用长连接。
但是并不是说长连接就没有问题，因为MySQL执行过程中临时使用的内存是管理在连接对象中的，所以每次执行完请求，都会增加连接的内存占用，这些资源一直不释放会导致内存爆满，被系统强行kill掉，从现象上看就是MySQL异常重启了。

那么解决这个问题的方案就需要考虑以下两点：
1、定期断开长连接，或在程序中判断某个连接内存占用过大，就将其关闭，之后使用再连接即可。
2、MySQL 5.7之后的版本，有一个 mysql_reset_connection 的函数可以重新初始化连接资源，也就是释放该连接占用的内存。这个过程不需要重连和重新做权限验证就会将连接恢复到刚刚创建完时的状态。

查询缓存

连接建立完成之后，我们就可以执行 select 语句了，此时就会来到执行逻辑的第二步：查询缓存。

这一步不做过多说明，因为MySQL 8.0版本直接将查询缓存的整块功能删掉了。

查询缓存我们往往不需要使用，因为其命中率实在是低下。对一个表就行更新操作就会使表上的所有查询缓存清空。

分析器

分析器的执行逻辑就是，内建解析树，对其进行词法语法检查，生成新的解析树，语义检查（字段，表是否存在）等。

首先进行词法分析：主要是根据MySQL的关键字进行验证和解析以及识别表名和列名。

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

之后进行语法分析：在词法分析的基础上，判断sql语句是否合法。
若语句不正确（如字段不存在），就会收到报错信息：
如果我们执行这个语句：其中表T中字段k不存在，则会在分析器阶段就报错

select * from T where k=1

Unknown column ‘k’ in ‘where c...

之后会进行一个权限检查，这也就是如果用户没有T表的权限，且sql中字段k页不存在，却报错为无权限的原因。

优化器

经过分析器之后，MySQL已经知道我们要做什么了，在开始执行sql之前，还要经过优化器的处理。

简单来说，优化器就是优化sql执行计划。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候（join）的时候，决定各个表的连接顺序。
简单来说：优化器就是将前面生成的解析树转换成执行计划，并进行最优选择。

如执行以下语句：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

可以先从t1表中取出c=10的ID值，再根据ID值关联到t2表，判断t2表里面d的值是否为20；
也可以从t2表中取出d=20的ID值，再根据ID值关联到t1表，判断t1里面c的值是否等于10。

以上两种执行方法的逻辑结果是一样的，但是执行效率可能存在不同，优化器的作用就是决定选择使用哪一种方案。

优化器执行完之后，就会进入执行器阶段。

预埋问题：优化器怎么选择索引？会不会选错？

执行器

MySQL通过分析器知道了我们要做什么，通过优化器知道了该怎么去做，到了执行器阶段，就开始执行逻辑了。

简单来说，执行器就是检查权限，打开表，处理数据。

执行sql时，会判断sql中的表以及操作涉及到的其他表（如更新一张表触发更新另一个表的触发器操作）在当前连接上有没有相关权限，若没有，则会返回权限错误；若有则继续处理数据

注：并非在执行器才执行权限检查，事实上，在命中查询缓存时，会在缓存返回结果时，做权限验证。且在优化器之前也会调用

precheck

验证权限。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如

select * from T where ID=10;

这条语句，表T中的ID字段没有索引，那么执行的流程就是：
1、调用 InnoDB 引擎接口取表的第一行，判断ID值是否为10，不是则跳过，是则将该行信息存在结果集中。
2、调引擎接口继续获取下一行，重复第1步判断，直至最后一行数据。
3、执行器将结果集返回给客户端。

对于有索引的表，第一次调用的是引擎中取满足条件的第一行的接口，之后循环取满足条件的下一行。这些接口都是引擎中已经定义好的。

之后我们可以在数据库的慢查询日志中看到一个

rows_examined

的字段，表示这个语句执行过程中扫描了多少行。该值就是在执行器每次调用引擎获取数据行的时候累加的。

在某些场景下，执行器调用一次，在引擎内部则会扫描多行，因此引擎扫描行数和

rows_examined

并不是完全相同的。

在开启查询缓存时，此时的结果集会更新到查询缓存中。

问题

1、在执行器阶段为什么要判断对表是否具有执行查询的权限，而不是在分析器去判断？根据 MySQL 中的 information_schema 库的存储信息，理论上可以在分析器阶段判断是否有权限。
有时sql语句要操作的表不只是sql字面上的那些，比如有个触发器，得在执行器阶段才能确定，所以优化器前判断权限有时候不完整。

2、创建一个没有 select 权限的用户，执行

select * from T where k=1;

（表T中无k字段），报错信息为：

select command denied

而不是：

unknown column

是否可以说明在打开表之后才判断读取的列不存在？
并不能说明，为了安全起见，返回无权限信息肯定是最好的

总结

客户端通过连接器连接到服务端，获取到权限等信息，然后在连接的有效时长内（interactive_timeout 和 wait_timeout 参数控制，5.7版本会断开自动重连）对客户端请求进行处理。

判断sql是select/update/delete/insert 中的哪一种，若是 select 判断查询缓存是否开启。（此时判断的select是简单判断，其他解析并没有做，所以分析器会再次判断并解析）
若查询缓存开启，则
若命中，则在数据返回时会判断查询权限，权限通过则返回数据。
若未命中，则进入分析器。
若未开启直接进入分析器。

分析器进行词法分析，语法分析，检查sql的语法顺序等生成解析树，然后预处理器对解析树进一步分析，验证对表是否具有相应权限以及数据表，字段是否存在，验证通过后更新解析树，交给优化器处理。

优化器对sql的执行计划进行最优选择，将最优执行计划交给执行器。

执行器再次验证相应权限，验证通过后，调用引擎接口获取数据并将结果返回给客户端，若查询缓存开启，则更新缓存。若是update/delete/insert请求，则删除查询缓存。

一些容易漏掉的知识：
1、连接器是从权限表里面查询用户权限并保存在一个变量里面以供查询缓存、分析器、执行器在检查权限的时候使用。
2、sql在执行的过程中，可能会有触发器这种在运行时才能确定是否有相应权限的操作，分析器工作结束后的

precheck

操作是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查。另外也是因为有precheck这个步骤，才会在报错时显示为用户无权，而不是字段k不存在，此举是为了不向用户暴露表结构。
3、词法分析阶段是从information_schema里面获得表的结构信息的。
4、可使用连接池的方式，将短连接变为长连接
5、mysql_reset_connection 是MySQL为各个语言提供的api，非sql语句。
6、wait_timeout 是非交互式连接的空闲超时，interactive_timeout 是交互式连接的空闲超时。执行时间不计入空闲时间。通过客户端连接的为交互式，通过程序连接的是非交互式

本篇关于《MySQL基础架构之一条SQL查询语句是如何执行的？》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！