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

参考自：https://time.geekbang.org/column/article/115537

对于下面这个mysql查询语句相信任何接触过mysql的coder都不会陌生

mysql>select * from T where ID=10;

通过这个指定我们能够从数据表中获得一个结果，那么这个语句在MySQL内部执行的过程是什么样的呢？

首先给出MySQL的基本架构图，其中概况了SQL语句在MySQL的各个功能模块中的执行过程

大体上，MySQL分为Server层和存储引擎层两部分。

Server层负责MySQL的大多数核心服务功能，所有跨存储引擎的功能都在这一层实现，例如存储过程，触发器，视图等。其中包括了连接器，查询缓存（在8.0版本被取消），分析器，优化器，执行器，以及所有的内置函数（如日期、时间、数学和加密函数等）

存储引擎部分负责数据的存储和提取。其架构是插件式的，目前最常用的存储引擎是InnoDB，从5.5.5版本成为了默认存储引擎。从图中可以看出，不同的存储引擎是共用的一个Server层的。

连接器

我们操作MySQL的第一步当然是先连接到数据库，连接器负责跟客户端建立连接，获取权限，维持和管理连接。以下是一个连接命令：

mysql -h$ip -P$port -u$user -p

连接命令中的mysql是客户端工具，用于跟服务器mysql建立连接，其过程当然是经典的TCP三次握手了。

输入完命令后，就需要在交互对话里输入密码了，需要注意的是在 -p 后也可以直接写密码，但是生产环境下别这么干，很可能导致泄露密码。

如果用户名密码验证通过，连接器会到权限表中查出登陆用户的权限，之后，这个连接里面的所有权限判断逻辑，都将依赖于此时读到的权限。

也就是一个用户成功建立连接后，即便管理员账号对该用户的权限进行了修改，也不会影响此时已经存在连接的权限。修改完成后，只有新建的连接才能使用新的权限设置。

连接完成后，若没有后续操作，连接则处于空闲状态，可以通过show processlist命令进行状态查看

若为空闲状态则Command就会显示为Sleep。

若客户端长时间没有操作，连接器就会自动断开与客户端的连接。该参数由wait_timeout控制，默认8h。

数据库里连接又分为长连接和短连接。

• 长连接：指连接成功后，若客户端持续有请求，则一直使用同一个连接
• 短连接：指每次执行完很少的几次查询就断开连接，下次查询再重新建立连接。

建立连接的过程通常是比较复杂的，所以尽量减少建立连接的动作，尽可能使用长连接。

但是当所有连接都为长连接时，MySQL的内存会涨的很快，这是因为MYSQL在执行过程临时使用的内存是管理在连接对象里的。这些资源只有在连接断开的时候才会释放，所以如果长连接积累多了以后就会导致MYSQL因为内存占用过大被系统强行Kill的情况，也就是MYSQL的异常重启。

我们可以通过以下两个方案进行解决：

1. 定期断开长连接：在使用一段时间或程序里判断执行过一个占用内存的大查询后就断开连接。
2. 若是5.7版本以上，可以在每次执行一个大操作后，通过执行mysql_reset_connection重新初始化连接资源。该过程不需要重连和重新进行权限验证，但是会将连接恢复刚创建时的状态。

查询缓存

大多数情况下不建议使用查询缓存，因为每次更新后都会将查询缓存清空，且8.0版本MYSQL也取消了该功能，就直接带过。

分析器

建立连接后，就开始执行语句了，首先MYSQL需要通过解析SQL语句才能知道该语句需要做什么操作，其中的过程涉及了词法分析和语法分析，说白了就是识别命令中字符串的含义和判断语法规则是否正确。

优化器

经过分析后，MYSQL需要对执行的语句进行优化处理。当表中有多个索引的时候，由优化器决定使用哪个索引；或者在语句中存在多表关联join时，由优化器决定决定各个表的连接顺序。

执行器

经过优化后，就进入了执行器阶段，开始执行语句了。

开始执行时，需要先判断用户的操作是否符合其权限，若没有，就会返回没有权限的错误。若有权限，则打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去这个引擎提供的接口。

例如：

mysql> select * from T where ID=10;

表T中，ID字段没有索引，那么执行器的执行流程如下：
①调用InnoDB引擎接口取该表的第一行，判断ID值是否为10，不是则跳过，是则保存于结果集。
②调用接口取"下一行"，直到取到表的最后一行。
③将结果集返回给客户端

由此可见，没有索引的查询效率是蛮低的…

对于有索引的表，流程为：
①调用"取满足条件的第一行"这个接口
②循环取"满足条件的下一行"这个接口

我们可以在数据库中的慢查询日志中看到一个row_examined字段，表示该语句执行过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数与row_examined并非完全相同。

---

以上就是关于MYSQL的逻辑架构的一个简单分析