likes
comments
collection
share

MySQL 语句执行流程- Server 层

作者站长头像
站长
· 阅读数 47

如果想深入地学习 MySQL ，那么应该从宏观的架构上面着手，这一篇我们学习 MySQL 的整体架构及语句执行流程之 Server 层

MySQL 整体架构

整体上来说 MySQL 服务端可以分成两层

  • Server 层：负责 SQL 语句层面的处理，解析、优化等
  • 存储引擎层：负责数据的存储与读取，不同的存储引擎有不同的数据存储与查询的手段，存储引擎层给 Server 层提供了统一的接口访问数据

MySQL Server 层架构图

MySQL 语句执行流程- Server 层

连接处理器

连接处理器主要处理客户端的连接是否正确，是否具有相应数据操作的权限

解析器

解析器的作用是对客户端传来的 SQL 语句进行以下工作：

  • 语法解析：检查 SQL 语句的语法，括号、引号是否闭合等
  • 词法解析：把 SQL 语句中的关键词、表名、字段名拆分成一个个节点，最终得到一颗解析树

MySQL 语句执行流程- Server 层

预处理器

解析器主要是检查语法词法方面，但是如果语法词法都正确，但是表、字段是不存在的，那么这段 SQL 语句也是无法正确执行的。

所以预处理器的作用是：语义解析，判断解析树的语义是否正确，表、字段这些是否存在，预处理后会得到一颗新的解析树。

查询优化器

查询优化器结构

MySQL 语句执行流程- Server 层

在 MySQL 中一条 SQL 语句的执行方式有多种，虽然最终都会得到相同的结果，但是存在开销上的差异，具体选择哪一种执行方式是由查询优化器来决定的。比如说：

  • 表中有多个索引可以选择，具体选择哪一个索引
  • 当我们对多张表进行关联查询时，以哪一张表的数据为基准表

查询优化器是基于开销（cost）的优化器，它的工作原理是根据解析树生成的多种执行计划，会评估各种执行方式所需的开销（cost），最终会得到一个开销最小的执行计划作为最终方案。

但是这个开销最小的执行方式不一定是最优的执行方式，比如本该使用索引，却进行了全表扫描等。虽然查询优化器中有「优化」两个字，但是这个优化并不是万能的，很多时候更加需要考虑 SQL 语句书写得是否合理。

逻辑查询优化

逻辑查询优化主要负责进行一些关系代数对 SQL 语句进行优化，从而使 SQL 语句执行效率更高

逻辑查询优化我们可以使用几个案例来简单理解

  • 子查询合并

    合并前

    SELECT * FROM t1 WHERE a1<10 AND (
 EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR
 EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2)
);

    合并后

    SELECT * FROM t1 WHERE a1<10 AND (
 EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2)
);

    把多个子查询通过合并查询条件而合并查询，把多次连接操作减少为单次表扫描和单次连接

  • 等价谓词重写

    像我们熟悉的 like 模糊查询，% 写在条件后面才会进行索引范围查询，其实这是查询优化器的功劳

    假设使用的条件都是有建立索引的，重写前

    SELECT * FROM USERINFO WHERE name LIKE 'Abc%';

    重写后

    SELECT * FROM USERINFO WHERE name >= 'Abc' AND name < 'Abd';

    这就是为什么能进行索引范围查询的答案

  • 条件简化

    条件简化也是利用一些等式、代数关系来实现简化

    • 去除表达式中的冗余括号，减少语法分析时产生的AND和OR 树的层 次，比如 ((a AND b) AND (c AND d)) 简化为 a AND b AND c AND d
    • 常量传递，比如 col1 = col2 AND col2 = 3 简化为 col1 = 3 AND col2 = 3
    • 表达式计算，对于一些可直接求解的表达式会转换为最终的计算结果，比如 col1 = 1+2 简化为 col1 = 3

物理查询优化

物理查询优化主要做的工作是根据 SQL 语句分别对多种执行计划进行开销的评估

物理查询优化主要解决以下几个问题：

  1. 单表扫描中采用哪种方式是开销最小的（扫描索引+回表 or 全表扫描）
  2. 存在表连接的时候使用哪种连接方式是开销最小的

简单了解一下代价评估，代价评估是基于 CPU 代价和 IO 代价两个维度的

扫描方式代价评估公式
顺序扫描N_page * a_page_IO_time + N_tuple * a_tuple_CPU_time
索引扫描C_index + N_page_index * a_page_IO_time

上述参数说明如下：

  • a_page_IO_time， 一个数据页加载的IO耗时
  • N_page，数据页数量
  • N_tuple，元组数（元组理解为一行数据）
  • a_tuple_CPU_time，一个元组从数据页中解析的CPU耗时
  • C_index，索引的IO耗时
  • N_page_index，索引页数量

关于索引成本计算可以参考这篇文章：MySQL查询为什么选择使用这个索引？——基于MySQL 8.0.22索引成本计算

执行计划

执行计划是查询优化器的产物，最终会交给存储引擎进行执行。执行计划可以帮助我们得知 MySQL 会怎么执行这条 SQL 语句。

使用 explain 关键字查看 SQL 语句的执行计划，可以得到以下信息：

  • id：嵌套查询中查询的执行顺序
  • possible_keys：本次查询可能用到的索引
  • key：实际用到的索引
  • key_len：使用到的索引的字段长度
  • rows：得到结果大概要检索多少行数据
  • select_type多表之间的连接类型
  • extra：额外的信息，是否有索引覆盖、索引下推等

执行器与存储引擎

执行期负责拿着查询优化器生成的执行计划，对存储引擎调用统一的数据操作接口。

MySQL 服务端规定了数据如何存储、如何提取、如何更新的规范，这个规范由存储引擎来实现，不同的存储引擎的实现方式不同，所以不同的存储引擎会呈现其独特的功能和特点。其中最常用的存储引擎是 InnoDB 和 MyISAM ，简单说说这两款存储引擎的特点

InnoDB：

  • 支持外键、事务，保证了数据的完整性和一致性
  • 支持更细的锁粒度，对锁的控制更好，读写效率更高

MyISAM

  • 不支持事务，只支持行锁，适合数据只读的场景

存储引擎方面暂时先不展开，会在其他文章继续穿插他们的对比，以及会详细分析 InnoDB 更新数据的流程

Binlog

Binlog 是 MySQL Server 层自己维护的一个二进制日志，以事件的形式记录了所有 DDL 和 DML 语句

  • 日志写入的机制是顺序写，省去寻址时间，写入效率高
  • 日志采用追加写的模式，一份日志文件写到一定大小会切换到下一个

Binlog 中最重要的功能：数据恢复与主从同步

  • 数据恢复：因为 Binlog 记录了 DDL 和 DML 语句，可以通过 mysqlbinlog 工具来恢复数据
  • 主从同步：master 结点把它的 Binlog 传递给 slave 结点，slave 根据 master 中发生过的数据修改同步修改到自己库上

Binlog 开启后会有性能上的消耗，默认是关闭的，如果需要打开的话需要在 MySQL 配置文件 my.cnf 中的mysqld 区加入以下配置

[mysqld]
log-bin=/data/mysql-bin #日志路径
binlog_format=MIXED

Binlog 相关查询命令

-- 查看 Binlog 的开关状态、文件目录、索引文件目录等信息
show variables like '%log_bin%';
-- 查看 Binlog 文件信息
show binary logs;

Binlog 格式

  • row：记录数据被修改成什么样子。但是无法记录函数执行的结果，而且如果一条修改语句修改了大量的数据行或者 alter table 时，那么日志量会很大
  • statement：记录执行了的 SQL 语句。减少 Binlog 日志量，节省 IO，但是可能会出现同一条 SQL 语句在 master 结点和 slave 结点上结果不一致的情况
  • mixed：以上两种格式混合使用，MySQL 根据 SQL 语句自己选择最优的

MySQL 在事务提交时需要写入 Binlog，这一点在介绍存储引擎层时再详细地介绍

总结

从前，只知道在客户端软件上写下 SQL 语句，点击执行，拿到数据

到现在终于了解到一条查询语句传入 MySQL 服务端后需要经历这一系列的操作

  1. 解析器根据这条 SQL 语句的语法、词法进行检查，如果没有错误的话会按关键词拆分成一个个节点，最终形成一棵解析树
  2. 预处理器会检查 SQL 语句的语义，检查 SQL 语句是否有歧义、字段等是否存在，形成一棵新的解析树
  3. 查询优化器拿到这个解析树生成的各种执行计划，经过逻辑查询优化、物理查询优化后得到一个开销最小的执行计划
  4. 执行器拿到这份执行计划调用存储引擎的接口
  5. 存储引擎根据执行计划进行数据查询，查询会查询调用操作系统中文件系统的一些接口，完成数据查询，最后返回给客户端

以上就是 SQL 语句在 MySQL 的 Server 层流转的整体流程。

如果这篇文章对你有帮助，请给我一个免费的大拇哥👍

我的个人知识库 GitHub 地址：github.com/gelald/javr… 请给我 star 支持一下，谢谢～

转载自:https://juejin.cn/post/7185476329427927101
评论
请登录