MySQL的逻辑架构

MySQL从逻辑架构的角度分为两部分：server层和存储引擎层。通常会说server层和引擎层。

所以就要说说，server层和引擎层的作用是什么？或者它们各自负责什么功能？

• Server 层可以划分为连接器、查询缓存、分析器、优化器、执行器等核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等）、存储过程、触发器、视图等。

除了数据库中数据的存储和获取，所有跨存储引擎的功能都在这一层实现。

• 存储引擎层负责数据的存储和提取、管理物理文件。存储引擎直接与文件交互，是通过 MySQL AB 公司提供的文件访问层抽象接口定制的一种文件访问机制（这种机制就叫存储引擎）。

存储引擎被设计为插件式的，因此可以根据需要选择合适的存储引擎。常用的存储引擎 MyISAM 、支持事务的 InnoDB、内存类型的 Memory、归档类型的 Archive、列式存储的 Infobright、以 RocksDB 为底层基础的 MyRocks 和 RocksDB、以分形树索引组织存储的 TokuDB 等。

不同的存储引擎共用一个 Server 层。

MySQL 5.5.5 版本之前，默认的存储引擎是 MyISAM，MySQL 5.5.5版本开始默认存储引擎为InnoDB。

下面是MySQL的简要的逻辑架构，以及各个组件的功能，说明了一条(查询)语句的执行过程：

create table 建表时，如果不指定引擎类型，默认使用的就是 InnoDB。

从一条SQL语句的查询看server层和引擎层的作用

如下，在建表语句中显式指定为内存类型的存储引擎，创建一个简单的表T：

 create table T(
    Id int(11) NOT NULL AUTO_INCREMENT
 ) ENGINE=memory;

以如下，最简单的一条根据Id查询记录的SQL为例，看看SQL的执行。

mysql> select * from T where ID=10；

注：如下的执行阶段并不是严格按照1、2、3、4按部就班执行的，各自功能调用的查询，实际的实现中相互间会有交错，尤其是权限部分。

如下只是方面理解上的逻辑划分。

1. 连接

第一步，连接到数据库。连接器用于与客户端建立连接、获取权限、维持和管理连接

客户端连接过程

如下，是命令行中的一条连接命令（执行SQL的第一步）：

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

回车后，需要在交互对话里面输入密码。（不建议在 -p 后明文写密码）。

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成 TCP 握手连接后，就会对输入的用户名和密码认证身份。

• 如果用户名或密码不对，就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

一个用户成功建立连接后，即使该用户被管理员账号修改了权限，也不会影响已经存在连接的权限。

也就是，修改权限后，需要重新登陆连接，才会使用新的权限。

连接建立后，会由Server层中的 SQL interface 接收发送过来的SQL语句。

连接状态

连接完成后，如果不进行任何操作，这个连接就处于空闲状态。

通过 show processlist 命令可以查看连接的线程。Command列显示为“Sleep”表示当前是一个空闲连接。

不同命令行工具查询的连接显示会有些许差异。

客户端如果太长时间没任何操作，连接器会自动将其断开。这个时间是由参数 wait_timeout 控制的，默认是 8 小时。

如果连接被断开之后，客户端再次发送请求就会收到一个错误提醒： Lost connection to MySQL server during query

还有一个类似的 Connect_timeout 参数，以及相同的 interactive_timeout 。

• Connect_timeout 指的是“连接过程中”的等待时间。

• wait_timeout指的是“连接完成后，使用过程中”没有任何操作的等待时间。

• wait_timeout 是客户端非交互式的连接时间。

• interactive_timeout 对应是软件程序连接MySQL Server时的交互连接。interactive_timeout和wait_timeout应尽量相同。

长连接可能导致的OOM

数据库中：

长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

通常为了减少重新建立连接的消耗和复杂处理，推荐使用长连接。

长连接的问题：

如果所有的连接都使用长连接，则有可能导致MySQL占用内存增长过快，原因是MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开的时候才会释放。

如果长连接不断积累，就会导致内存占用过高，被系统强行杀掉(OOM)，从现象看就是 MySQL 异常重启了。

OOM，全称“Out Of Memory”，翻译成中文就是“内存用完了、超出内存”。

解决方案有两种：

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2. 如果是 MySQL 5.7 及以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection() 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到初始时的状态。

mysql_reset_connection()是MySQL中的 C API。无法直接在命令行中使用。

mysql_reset_connection() 返回值 0 表示成功，非0表示错误，它影响以下与会话状态相关的信息：

• 回滚活跃事务并重新设置自动提交模式
• 释放所有的表锁
• 关闭或删除所有的临时表（TEMPORARY table）
• 重新初始化会话的系统变量值
• 丢失用户定义的变量设置
• 释放prepared语句
• 关闭 HANDLER 变量
• 将 last_insert_id() 的值设置为0
• 释放 get_lock() 获取的锁
• 清空通过 mysql_bind_param() 调用定义的当前查询属性。

2. 查询缓存

查询缓存

执行逻辑就会进入到第二步：查询缓存。

MySQL按到查询请求后，会去查看“查询缓存”中是否存在。

查询缓存中存放着，之前执行过的语句及其结果，会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。

如果命中查询缓存，则直接将结果返回给客户端，效率非常高。

如果没有命中查询缓存（查询的语句不在查询缓存中），则执行后续操作。执行完成后的结果会被存入查询缓存中（如果开启了query_cache）

不推荐使用查询缓存

查询缓存往往弊大于利，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

而且，任何字符上的不同，例如空格、注释等都会导致缓存的不命中。

对于更新压力大的数据库来说，查询缓存的命中率会非常低。

除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL 中可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。

对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，如：

mysql> select SQL_CACHE * from T where ID=10；

默认查询缓存功能是关闭的。通过在MySQL服务器配置文件中添加query_cache_type=2，重启mysql服务启用。

MySQL 8.0 版本直接去掉了查询缓存的整块功能， 即 8.0 开始就没有了查询缓存功能。

几个查询缓存有关的参数：

1. query_cache_type=0（或者OFF）时表示关闭；1（或者ON）时表示打开；2（或者DEMAND）表示只有 select 中明确指定SQL_CACHE才缓存。

2. 查询 query_cache_type 变量：show variables like 'query_cache_type';。

3. have_query_cache 配置参数表示这个mysql版本是否支持查询缓存。 如MySQL8.0下，show variables like 'have_query_cache'; 返回 NO。

4. query_cache_limit 表示单个结果集所被允许缓存的最大值。

5. query_cache_min_res_unit 每个被缓存的结果集要占用的最小内存。

6. query_cache_size 用于查询缓存的内存大小。 query_cache_size=0表示不使用缓存，推荐query_cache_type=0关闭。

7. Qcache_free_memory 查询缓存目前剩余空间大小。

8. Qcache_hits 查询缓存的命中次数。

9. Qcache_inserts 查询缓存插入的次数。

3. 分析器

未命中查询缓存，或者未开启查询缓存，就会进入分析器对SQL语句做解析。

1. 分析器先做“词法分析”。一条 SQL 语句由多个字符串和空格组成，MySQL 需要识别出里面的字符串分别是什么，代表什么。

比如，从输入的"select"这个关键字，识别出是一个查询语句。同时，还要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”等。

1. 然后做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果语句存在语法错误，比如这个语句elect * from t where ID=1;，select少了开头的s，语法分析就会收到“You have an error in your SQL syntax”的错误。

语法错误会提示第一个出现错误的位置，错误信息的“use near”位置。

1. 语义分析（语义解析），将上面分析的结果（即生成的解析树），进一步分析字符串标识符的语义，检查表、列是否存在，别名是否有歧义等，通过后生成新的解析树。

分析器部分可以分为 解析器和预处理器。

• 解析器处理语法和解析查询, 生成一课对应的“解析树”。解析器将查询分解成一个个Identifier（词法分析），然后确保查询中的标识都是有效的，检查其中的语法错误（语法分析），比如标识符是否有效、语句是否闭合等。

• 预处理器进一步检查“解析树”的合法性，解决解析器无法解析的语义。比如: 检查数据表和数据列是否存在、检查名字和别名保证没有歧义等。如果通过则生成新的解析树，再提交给优化器。

即，在优化器之前还需要进行预处理器的"语义分析"。

一个 Parser 的核心分为词法分析、语法分析和语义分析

如果表 T 中没有字段 k，执行语句 select * from T where k=1;, 在 分析器 的 预处理器 分析中，就会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。

4. 优化器

优化器是专门用于对查询进行优化的（包括所有类型的SQL语句：DDL和DML）。

优化器会根据解析树生成执行计划，同时，在表中有多个索引的时候，决定使用哪个索引；一个语句有多表关联(join)时，决定各个表的连接顺序。

优化器执行完，一个语句的执行方案就确定了下来。

5. 执行器（查询执行引擎）

MySQL 通过分析器知道SQL语句要做什么，通过优化器知道该怎么去做。剩下的就是进入执行器（或者执行阶段），依照“执行计划”开始执行。

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，会返回没有权限的错误。

权限错误的信息通常为：SELECT command denied to user 'xx'@'localhost' for table 'xx'

权限验证不仅仅在执行器这部分会做。

在分析器之后，也就是知道了该语句要“干什么”之后，也会先做一次权限验证。叫做 precheck （在工程实现上会这么处理，同时工程实现上，也会在查询缓存命中返回结果时，做权限验证）。

而precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。

在连接阶段，只是“获得权限信息”； 真正开始查询动作时，才判断“有没有操作这个表的权限”。

执行器阶段的权限验证，主要是判断一些关联操作，比如更新一行时，由触发器会再更新别的表的情况。

实际的执行和调用路径，执行器阶段都在mysql_parse函数里面调用的。

权限OK，就会打开表继续执行。

打开表的时候，执行器就会根据表的引擎定义，使用这个引擎提供的接口。

比如，这个查询语句的例子，T 表的 ID 字段没有索引，则执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

这样，一条SELECT语句完整的执行逻辑就结束了。

对于有索引的表，（如果能够命中索引）执行的逻辑基本相同，【开启了索引下推，默认都是开启，并且没必要关闭】。执行的逻辑为：第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，由引擎提供这些接口。

无索引时，执行器循环调用引擎接口，先调用“第一行”并过滤数据，之后循环调用“下一行”并过滤数据，一直到最后一行。过滤是在Server层完成的。

有索引时，执行器调用“满足条件的第一行”的引擎接口，之后调用“满足条件的下一行”的接口。有索引时在引擎层完成数据的过滤。

索引下推（index condition pushdown —— ICP）就是把数据筛选交由存储引擎层处理，减少Server层的负担，提高查询效率。

索引下推(Index Condition Pushdown) ICP 是 Mysql5.6 之后新增的功能，主要的核心点就在于把数据筛选的过程放在了存储引擎层去处理，而不是像之前一样放到Server层去做过滤。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化是非常好的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，可以减少存储引擎查询基础表的次数、降低回表，也可以减少MySQL服务器从存储引擎接收数据的次数、减少server层的过滤处理。

MySQL 慢查询日志中有 rows_examined 的字段，表示这个语句执行过程中“调用”存储引擎取数据行的次数。

rows_examined 会被笼统的看做语句执行过程扫描了多少行。但有时，执行器调用一次，引擎内部可能扫描了多行，因此引擎扫描行数跟 rows_examined 并不完全相同。但可以看做：执行器获取到的数据行数。

存储引擎的作用

存储引擎用于具体的组织、管理物理文件，包括二进制日志（binlog属于Server管理的文件，但也提供了存储引擎使用的接口）、数据文件、错误日志、慢查询日志、全日志、redo/undo 日志等。

除此之外，还有引擎程序运行的实例，包括功能结构、内存中数据的管理等（这就是具体引擎自己的功能实现了）。

不同的存储引擎有着不同的文件组织、管理的方式。

同时存储引擎提供Server层可以访问、存取数据的接口，用于 server 层的执行引擎获取或存入数据。

参考

主要参考自MySQL实战45讲、《高性能MySQL》，以及一些网上资料！