MySQL架构（一）SQL 查询语句是如何执行的？

在了解 MySQL 架构之前，我们先看几个 SQL 语句，当我们知道了 SQL 语句的执行流程，再学习 MySQL 架构简直手到擒来。

SQL 查询执行流程

SELECT * FROM user WHERE id=1; 

当我们在客户端执行这个查询语句时，会得到一条 user 表中 id 为 1 的数据。但这整个过程我们并不知道，你可以先想一下，如何去拿到 id=1 的数据。

我们往下看，SQL 查询过程的具体流程如下图。

• 客户端可以是数据库可视化软件（Navacat、DBeaver 等）包括 JDBC 连接工具，主要发送 SQL 语句的执行请求。
• 服务端可以分为 Server 层和存储引擎层两部分

  • Server 层包括连接器、查询缓存、分析器、优化器、执行器，MySQL 大多数核心服务功能都在这一层中，提供了包括权限控制、用户认证、MySQL 内置函数（如数学函数、字符串函数等）以及跨存储引擎的功能（存储过程、触发器、视图等）。

  • 存储引擎层负责数据的存储和检索。其架构模式是插件式的，MySQL 支持 InnoDB（最常用，MySQL5.5.5 开始成为默认存储引擎）、MyISAM、Memory 等存储引擎。如果需要更改存储引擎，我们可以通过指定存储引擎的类型来选择别的引擎（在 create table 中使用 engine=memory，指定内存引擎来创建表）。

连接器

首先，使用 MySQL 第一步需要连接上 MySQL，这就需要连接器建立与客户端的链接，并维护这个链接，包括权限认证、链接保持与管理。

假设我们有一台远程服务器， IP 为：110.110.110.110，MySQL 端口为 33060，MySQL 用户名为 root，密码为 123。

则可以通过以下命令连接 MySQL。

mysql -h 110.110.110.110 -P 33060 -u root -p 123;

注意：u 与 root 之间、p 和 123 可以不用加空格，其它也一样。如果不输入 -h、-P 则默认是 localhost:3306。

我们可以看到，上诉连接命令中 mysql 就是上文所说的客户端工具，用来跟服务端建立连接。如果你的电脑没有配置 MySQL 的环境变量或者不在 MySQL 的安装路径下，可能提示 'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

在与 msyql（客户端） 完成 TCP 握手后，连接器就会开始通过输入的用户名和密码认证登录者身份。

• 如果输入的用户名或密码错误，会提示 "Access denied for user" 错误，并且客户端程序会结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出登录用户所拥有的权限。后续这个的连接里面的所有权限判断逻辑，都将依赖于此时读到的权限。

连接器还会维持和管理连接，若客户端 8 小时没有发起请求，连接器就会断开这个连接，这个时间是由参数 wait_timeout 控制的 (默认 8 小时)。断开后，客户端再发送请求，则会收到 Lost connection to MySQL server during query。 错误提示，此时需要再次建立新连接。

我们可以通过下述命令查看连接、 wait_timeout 值。

# 查看数据库的所有连接状态 
show processlist; 
#查看wait_timeout值 
SHOW VARIABLES LIKE 'wait_timeout';

查询缓存

查询缓存在 MySQL 8.0 版本中已经被移除。在 MySQL5.7版本，连接后会查询缓存，即查询该语句是否执行过。

具体流程为 MySQL 在接收到查询请求后，先去查询缓存，看之前是否已经执行过该条查询语句。

若之前执行过的该语句，其查询结果会以 key-value （键值对）形式缓存在内存中。后续同样的查询请求能够直接在缓存中找到 key，并返回 value 值给客户端。

若是该查询语句不在查询缓存中，就会执行后面的阶段。待执行完成后，查询结果会被存入查询缓存中。

我们可以看到，若是查询语句在缓存中，就不需要执行后续的复杂操作，可以高效率的获取查询结果。

但是查询缓存有很多问题，并不建议使用，且在 MySQL 8.0 版本中查询混村已经被移除了。

• 若是数据库更新频繁，查询缓存的命中率就非常低。
• 查询缓存适合静态表，即数据很长时间才会更新一次，甚至不更新。

若是需要用到查询缓存，建议采用按需使用方式。即将参数 query_cache_type 设置成 DEMAND，只有对带 SQL_CACHE 的查询语句才采用查询缓存策略，而对于默认的 SQL 语句都将不使用查询缓存。

# MySQL 配置文件（安装路径下的 my.cnf 或 my.ini）中设置缓存按需使用
[mysqld] 
query_cache_type = DEMAND

# CLI方式设置缓存按需使用
SET GLOBAL query_cache_type = DEMAND;

# 只有带 SQL_CACHE 的查询会查询缓存。
SELECT SQL_CACHE * FROM user WHERE id = 1;

分析器

分析器判断语句是否合法，首先会进行词法分析，提取语句的关键字，即 SELECT 关键字识别为查询语句，user 识别成表名，id 识别成列的 id。这一阶段从 information_schema 中获取表的结构信息。

完成词法分析后，还需要进行语法分析，根据语法规则，判断查询语句是否满足 MySQL 语法，如果语法不满足，会提示 You have an error in your SQL syntax 错误，并会指出出错位置（to use near 的后续部分）。

优化器

通常情况下，一条查询语句有多种查询方案，优化器的作用就是在基于这多个查询方案中找出效率最高的方案。譬如，user 表中有多个索引，由优化器决定使用哪个索引。又或是查询语句使用多表关联（join），由优化器决定多表的连接顺序。

SELECT * FROM t1 JOIN t2 USING(id) WHERE t1.a=1 AND t2.b=2;

在 sql/92标准中 using 可以代替 on，即上面语句等价于 SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.a=1 AND t2.b=2;

在使用 using 时，还需要注意：查询必须时等值连接；等值连接的列必须具有相同名称和数据类型。

等值连接：从 t1 表中取出每一条记录，去 t2 表中与所有的记录进行匹配，匹配必须是某个条件在 t 表中与 t2 表中相同最终才会保留结果，否则不保留。

上诉语句就有两种查询方式

执行器

到了执行器这一步，开始执行查询语句，在执行之前还需要判断下登录用户是否具有查询这个表的权限，若是没有权限则返回权限限制的错误提示 ERROR 1142 (42000): SELECT command denied to user ...。

执行器的运作流程：打开表后，执行器依据表的存储引擎定义，使用其存储引擎提供的接口，执行如下操作。

当 MySQL 将结果集返回给客户端，查询语句就已经执行完了。

补充

权限检查：分析器的语法分析过程会做权限预检查（precheck），包括检查用户对数据库、表的权限。

执行器检查权限则是因为一些过程只能在执行时才最终确认，precheck 无法对执行阶段涉及的表做权限检查。

总结

MySQL 架构可分为 Server 层和存储引擎层，其中 Server 层和存储引擎层是相互独立的两个模块。

Server 层是 MySQL 的核心部分，负责处理用户的连接请求、权限管理、查询解析、查询优化、执行计划生成、缓存管理等功能。Server 层将用户请求解析为具体的 SQL 操作并将其转发给存储引擎层执行。

存储引擎层负责数据的存储和读写操作。MySQL 支持多种存储引擎，包括 InnoDB、MyISAM、Memory 等。每个存储引擎都有自己的特点和适用场景。存储引擎层负责将数据存储在磁盘上，并提供相应的索引、事务处理和并发控制等功能。用户可以根据需求选择合适的存储引擎。

Server 层和存储引擎层之间通过 API 进行通信。API 定义了存储引擎层与 Server 层之间的接口规范，使得不同存储引擎可以与 Server 层进行无缝衔接。用户可以根据需要选择不同的存储引擎，从而实现对数据的不同操作和存储方式的灵活选择。

Server 层

• 处理 SQL 语句、解析、优化、缓存等。
• 权限管理、用户认证等。
• 提供了复制、备份、恢复等功能。
• 提供了各种 SQL 函数和存储过程。
• Server 层的日志系统，称为 binlog（归档日志）。
  • binlog 记录了所有修改数据库数据的 SQL 语句（如 INSERT、UPDATE、DELETE 等）的信息，但不包括 SELECT 和 SHOW 这类查询语句。
  • binlog 主要用于复制和恢复操作。

存储引擎层

• 处理数据的存储和检索。
• MySQL 支持多种存储引擎，如 InnoDB、MyISAM、Memory 等，每种引擎都有其特点和适用场景。
• InnoDB 是 MySQL 默认存储引擎（MySQL 5.5.5 版本开始），支持事务、行级锁定和外键约束。
• InnoDB 的日志系统，称为 redo log（重做日志） 和 undo log（撤销日志）。
  • redo log 保证事务的持久性，在数据库崩溃后可以用来恢复数据。
  • undo log 支持事务的原子性和多版本并发控制（MVCC）。