MySQL | 一条查询 SQL 是如何执行的

MySQL 分层

MySQL 大致可以分为两层，MySQL Server 层和 MySQL 存储引擎层，如图：

MySQL Server 层包括连接器、查询缓存、分析器、优化器、执行器等组件，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

可以在 create table 语句中使用 ENGINE=MyISAM，来指定使用引擎创建表。语句如下：

CREATE TABLE `article`(
  `id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '文章ID',
  `cover` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '封面图',
  `title` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '标题',
  `description` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '描述',
	`create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `modify_time` datetime DEFAULT NULL COMMENT '更新时间',
	 PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='文章表';

连接器 Connector

在执行 SQL 前，第一步客户端需要与 MySQL 服务建立连接。MySQL Server 层的连接器就是负责与客户端建立 TCP 长连接，然后会对用户进行权限验证。

可以通过如下命令连接 MySQL Server：

mysql -h {ip} -P {port} -u {user} -p

连接管理

在 MySQL 5.6 出现以前，MySQL 处理连接的方式是 One-Connection-Per-Thread，即对于每一个数据库连接，MySQL-Server都会创建一个独立的线程服务，请求结束后，销毁线程。这种方式在高并发情况下，会导致线程的频繁创建和释放。随着连接数暴增，导致需要创建同样多的服务线程，高并发线程意味着高的内存消耗，更多的上下文切换(cpu cache命中率降低)以及更多的资源竞争，导致服务出现抖动。

在 MySQL 5.7 中使用连接方式是 Thread-Pool，一个线程对应一个连接，Thread-Pool实现方式中，线程处理的最小单位是statement(语句)，一个线程可以处理多个连接的请求。这样，在保证充分利用硬件资源情况下(合理设置线程池大小)，可以避免瞬间连接数暴增导致的服务器抖动。

用户鉴权

在用户连接后，如果用户名或密码不对，就会收到一个Access denied for user的错误，然后客户端程序结束执行。

如果验证通过，MySQL 会查询用户所拥有的权限，在后续执行过程中，操作权限的检验都依赖于次。这就意味着在当前连接中，即使用户权限被修改了，也不会受到影响，只有在断开重连后才会生效。

连接成功后，若长时间未操作，默认 8 小时会自动断开连接，这个配置在 my.cnf 中 wait_timeout = 86400。此时再次发起请求时，会收到Lost connection to MySQL server during query的错误。

查询缓存 Query Cache

在建立完连接后，就可以执行 Select 语句了。如果开启了查询缓存，那么首先会查询缓存，缓存是以 key-value的形式存储在内存中，key是查询的语句，value是查询的结果。如果命中缓存，那么对应的value就会直接返回给客户端。否则继续后面的执行阶段，执行完成后，执行结果会被存入查询缓存中。

其中可以通过 query_cache_type 设置是否开启查询缓存：

OFF（0）：表示不使用查询缓存，默认为OFF。
ON（1）：表示使用查询缓存。
DEMAND（2）：表示按需使用查询缓存。通过 SQL_CACHE 显示指定使用，如 select SQL_CACHE * from T where id=15。

还可以通过 SHOW VARIABLES LIKE '%query_cache%'; 查看是否支持查询缓存。

在MySQL5.7中执行：

  mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |   # 表示这个mysql版本是否支持查询缓存
| query_cache_limit            | 1048576 |   # 表示单个结果集所被允许缓存的最大值
| query_cache_min_res_unit     | 4096    |   # 每个被缓存的结果集要占用的最小内存
| query_cache_size             | 1048576 |   # 用于查询缓存的内存大小
| query_cache_type             | OFF     |   # 控制查询缓存功能的开启关闭
| query_cache_wlock_invalidate | OFF     |   # 如果某表被其它的连接锁定，是否仍然可以从查询缓存中返回结果
+------------------------------+---------+
6 rows in set (0.01 sec)

在MySQL8.0中执行：

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | NO    |
+------------------+-------+
1 row in set (0.02 sec)

查询缓存在MySQL8.0版本中被废弃。原因是什么？因为在关系型数据库中，数据的变更是比较频繁的，一旦数据发生了修改，缓存就失效，所以缓存的命中率不高，缓存的意义也就不大了。

如果想要使用查询缓存的话，可以把 query_cache_type 设置为 DEMAND，按需查询一些不经常变或者不变的常量数据，如固定的配置、字典、地址库等。

分析器 Parser

当未命中查询缓存，会继续执行后续流程。通过分析器对 SQL 语句进行解析，解析过程分为词法分析和语法分析。

词法分析会将 SQL 拆解为不可再分的原子符号，称为 Token。并根据数据库所提供的字典，将其归类为关键字、表达式、字面量和操作符等。再通过语法分析将 SQL 转换为抽象语法树（AST）。

如以下 SQL：

SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18

解析之后的为抽象语法树见下图：

抽象语法树AST

抽象语法树中的关键字的Token用绿色表示，变量的Token用红色表示，灰色表示需要进一步拆分。

优化器 Optimizer

有了 AST 之后会通过优化器进行优化，一个 SQL 语句可能有不同的执行方式。比如，A 表 Join B 表关联查询，是先读取 A 表还是读取 B 表呢？表里面有多个索引，使用哪个索引呢？

MySQL 优化器会根据通用的规则进行自动选择最优执行计划，但这只能适配绝大部分的场景，因为有时候 MySQL 会有选错的情况，需要人工干预优化。

优化器通常分为两种方式：

RBO：基于规则的优化。按照硬编码在数据库中的一系列规则来决定SQL的执行计划。
CBO：基于成本的优化。通过根据优化规则对关系表达式进行转换，生成多个执行计划，然后CBO会通过根据统计信息(Statistics)和代价模型(Cost Model)计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案。

大部分情况下我们需要查询效率，所以通常是使用CBO基于成本的优化。

执行器 Executor

MySQL 通过分析器知道要做什么，通过优化器知道要怎么做，于是就进入执行阶段。

在执行 SQL 语句之前会根据连接器中获取的用户权限校验用户是否有该表的访问或操作权限。如果没有，就会返回没有权限的错误，如下所示：

mysql> SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18;
ERROR 1142 (42000): SELECT command denied to user 'msl'@'localhost' for table 't_user'

如果有权限，执⾏器会根据 SQL 语句中表的引擎定义，根据执行计划去调用对应的 API 接口。执行器会与底层存储引擎缓存或者物理⽂件的交互，得到查询结果，最终由 MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache，这时也会将 SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执⾏则直接返回结果。

执行器调用接口执行流程

如要查以下SQL：

select * from t_user where company_id = 100

如果 company_id 无索引，则执行流程如下：

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

如果 company_id 存在索引，则执行流程为：调用 InnoDB 引擎接口取这个表满足条件的第一行，之后循环取“满足条件的下一行”这个接口。

小结

本文通过一个查询 SQL 语句的执行过程，初步了解了 MySQL Server 的逻辑架构。

目录CONTENT