九、MySQL基础架构概述

MySQL基础架构

SELECT * FROM student WHERE stu_id = 007;

这样一条简单的 SQL 语句，可以从 student 表中查询得到 stu_id 为 007 的数据，学会写 SQL 语句后，在使用数据库时，只能观察到数据库执行了 SQL 语句，并且返回了所需要的数据。那么，MySQL 在拿到 SQL 语句之后做了哪些操作呢？

这涉及到 MySQL 的基础架构设计，分为 Service 层和存储引擎层，Service 层负责处理与客户端通信、SQL语句的解析和所有内置函数等，而存储引擎层则提供数据的存储和读写接口，其中 Service 层又包含了连接器，缓存，优化器，分析器和执行器。存储引擎层，包含了不同的存储引擎，MySQL 默认是 InnoDB，此外还有 MyISAM 引擎。详见下图：

1. 连接器：负责接待客户端，验证权限
2. 缓存：查询时，若命中缓存则直接返回结果
3. 分析器：分析 SQl 语句的语法和词法
4. 优化器：确定 SQL 语句的最佳执行方案
5. 执行器：根据优化器的方案，操作存储引擎拿到结果并返回

连接器

连接器的工作，就是负责处理所有客户端的连接请求，在完成TCP握手后，验证账号密码，并且得到对应的权限信息，用于这个连接后续对数据库操作时的权限判断。

可以通过show processlist查看当前的连接数：

mysql> show processlist;
+----+------+-----------------+-------+---------+-------+----------+------------------+
| Id | User | Host            | db    | Command | Time  | State    | Info             |
+----+------+-----------------+-------+---------+-------+----------+------------------+
|  2 | root | localhost:3044  | NULL  | Sleep   | 16858 |          | NULL             |
|  3 | root | localhost:3052  | uccdb | Sleep   | 16788 |          | NULL             |
|  4 | root | localhost:39029 | NULL  | Query   |     0 | starting | show processlist |
+----+------+-----------------+-------+---------+-------+----------+------------------+
3 rows in set

Command 为Sleep的是空闲连接。

值得注意的是

1. 空闲连接在特定时间后会失去连接，这个时间参数是wait_timeout。

--临时修改wait_timeout，重启后失效
set  global wait_timeout=28800;--8小时

--也可以通过修改 my.ini 配置文件

2. 每一个连接执行操作后，使用的内存是在连接对象中的，直到连接断开才会释放，而重新连接的成本也不低，因此可以在执行大操作后，使用mysql_reset_connection（是API不是可执行的SQL）来重置资源。

缓存

在执行查询语句后，查询语句以及对应的结果集会被记录到缓存中，下次遇到同样的查询时，直接从缓存中返回即可。

缓存虽然看似可以提高查询效率，但是在实际应用中却没有什么作用，甚至反而降低了效率。因为只要某个表更新了数据，那么这个表相关的缓存就会被清除，如果是更新比较频繁的业务，那么缓存几乎没有任何作用，反而为了维护缓存做了更多的无用功。

通常建议关闭缓存，MySQL 8.0 中也已经移除了缓存这个机制。

分析器

分析器有两个作用，一个是词法分析，一个是语法分析。

MySQL要知道你给的 SQL 语句是什么意思，在分析器之前，SQL 语句只是一个字符串而已，分析器要做的首先就是词法分析，解析 SQL 语句，分析每个单词的含义，例如开头select的，是查询语句。

分析完单词的含义之后，接着是语法分析，也就是检查这句 SQL 语句有没有语法错误。

全部通过后，就将解析后的SQl语句交给优化器。

这里需要注意的是，分析器除了分析词法和语法，还会判断表、字段等是否存在。

优化器

优化器的作用就是要把这句 SQL 语句的执行方案定下来，当然，肯定是优化器认为的效率最高的那个方案（有的时候优化器并不靠谱）。

经过分析器分析后，MySQL 已经知道你想要做什么了，此时交给分析器来决定使用哪个索引，如果有多个表的连接查询，那么确定先连哪个表。

最后确定了执行计划后，交给执行器去实施这个计划。

执行器

执行器阶段首先要做的就是判断送来这个SQL语句的客户端有没有权限去执行。

确认有权限执行后，执行器会打开相应的表，根据引擎定义的接口去取数据。这里以开头的SQL语句为例（无索引）：

SELECT * FROM student WHERE stu_id = 007;

执行步骤为：

1. 打开表student
2. 调用引擎的读数据接口按顺序取一行数据
3. 判断stu_id是否为007，重复步骤2
4. 直到扫描完全表，返回所有符合的数据

至于存在索引的情况，是根据索引的规则来取数据，例如，索引是按stu_id排序的，那么可以二分查找快速找到对应数据，即合适的索引可以提高查询效率。