MySQL的架构组成

导读

为了更深刻的理解 MySQL 的运行和架构组成，写下这篇博客便于以后进行复习。可以在这篇博客中学习到：
1.MySQL的逻辑架构
2.一条查询语句是如何执行的
3.一条更新语句是如何执行的

---

一：MySQL的逻辑架构

MySQL逻辑架构简单示意图：

MySQL在逻辑架构上包含了Server层和存储引擎层两大部分。

• Server层：覆盖了MySQL的大多数核心服务，包含了连接池、查询缓存、分析器、优化器。执行器等，简单来说就是负责功能层面的事。
• 存储引擎层：负责数据的存储和提取，MySQL采用插件式的存储引擎，支持InnoDB、MyISAM、Memory等多个存储引擎，简单来说就是负责存储相关的事。

二：一条查询语句是如何执行的

2.1 连接器

连接器用于用户权限验证，建立连接。我们通过 mysql 这个客户端工具进行数据库的连接。
连接命令如下：

mysql -h localhost -u root -p

因为 MySQL 是基于TCP协议进行传输的，连接过程需要先经过三次握手，如果 MySQL 服务没有启动则会报错。如果服务正常运行，完成TCP连接之后，连接器进行用户名和密码验证，如果错误则收到 "Access denied for user"的错误，然后客户端程序结束运行。验证通过连接器将获取该用户的权限，然后保存起来，后续该用户在此连接池的任何操作都会基于连接开始时读到的权限进行权限逻辑的判断。

• 如果客户端太长时间没有动静，连接器就会自动将它断开，断开时间由 wait_timeout 参数控制，默认是8小时，在连接断开之后，客户端再发生请求则会收到一个错误提醒。
• 长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接。
• 短链接：每次执行完很少的几次查询就断开连接，下次查询再建立连接。

由于连接过程始是复杂的，尽量减少连接动作，但是使用长连接，临时使用的内存是管理在连接对象里面的，需要等到连接断开时才释放，从而导致内存占用太大，被系统强行杀掉。解决办法有两种：
 方法一：定期断开长连接，执行一个占用内存大的操作后，断开连接。
 方法二：每次执行一个比较大的操作后，执行 mysql_reset_connection 来初始化连接资源，不需要重连和重新做权限验证。
查看 MySQL 连接数

show processlist;

手动断开空闲连接

kiill connection +id;

查看 MySQL 最大连接限制
如果连接数超过了这个数值，系统会拒绝接下来的请求

show variables like 'max_connections';

2.2 查询缓存

连接器完成工作后，客户端可以向 MySQL 服务发送 SQL语句，MySQL 服务收到 SQL 语句后首先解析 SQL 的第一个字段看看是什么类型的语句，如果是查询语句，先去缓存中查询是否执行过这条语句，在缓存中，执行过的语句及结果，可能会以 key-value 的形式存储，如果执行过则返回 value，不需要再执行下面的步骤，极大的提高了效率。如果没有执行过，则继续执行后面的阶段，执行完成后，将结果存入缓存当中。但是大多数情况下不建议使用查询缓存，使用查询缓存往往弊大于利。因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。
关闭缓存的两种方法：

• 临时关闭

set global query_cache_size = 0
set global query_cache_type = 0

• 永久关闭：将以下两个参数添加至配置文件 my.cnf，并重启MySQL

query_cache_type=0
query_cache_size=0

在 MySQL 8.0 版本直接删除了查询缓存，也就是执行一条 SQL 语句之后不再走查询缓存。

2.3 分析器

在正式执行 SQL 查询语句之前，MySQL 需要对 SQL 语句进行解析。

首先进行词法分析：主要是根据 MySQL 的关键字进行验证和解析，他会从左到右一个字符、一个字符的输入，然后根据构词规则识别单词。

再执行语法分析：进行表名和字段名的验证和解析，判断输入的SQL语句是否符合MySQL的语法规则，如果语句不对，则收到错误提醒。

注意：这个阶段才会打开表，知道有哪些表，进行语法分析，词法分析，检查sql语法顺序得到解析树。

2.4 优化器

经过分析器后，就要执行 SQL 查询语句了，但是真正执行之前需要检查用户是否有访问该数据库表的权限，如果没有则直接报错，有权限则进入优化阶段。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。不同的执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是根据不同的方案挑选出效率最高的执行方案。

2.5 执行器

经历优化器后，确定了执行方案，接下来就真正开始执行 SQL 语句，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。这个阶段是真正执行 SQL 语句的阶段，调用 InnoDB 接口从存储引擎中获取结果集。
执行器的执行流程：

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

小结

执行一条 SQL 查询语句，期间发生了什么？

1.连接器： 建立连接，管理连接，检验用户身份。
2.查询缓存： 当连接通过之后，Mysql 拿到查询请求，先去缓存中查询是否执行过这条语句。如果命中则返回结果，未命中则继续下面的流程。
3.语/词法分析： 缓存未命中后，进入分析器，进行语法和语义的分析，对关键字和表名字段进行验证和解析，如果不正确则收到错误提醒。
4.执行优化： 分析器通过后进入优化阶段，优化器选择执行效率最高的方案进行执行。
5.调用接口： 执行器就会根据表的引擎定义，去使用这个引擎提供的接口，获取结果集。

三：MySQL的物理组成

我们都直到 MySQL 可以恢复到半个月内任意一秒的状态，这是怎样做到的呢？
看一下 MySQL 的物理组成简单示意图：

可以分为日志文件和数据文件两大部分组成。
日志文件：包含了不同类型的日志文件。
数据文件：数据文件主要指不同存储引擎的物理文件。
1.二进制日志-binlog
位于Server 层的日志，是MySQL的重要日志模块，以二进制额形式，将所有修改数据的 query 记录到日志文件中，包括 query 语句、执行时间、相关事务信息等。
2.redo log
是存储引擎 InnoDB 生成的日志，是InnoDB引擎特有的，主要为了保证数据的可靠性。redo log 记录了 InnoDB 所做的所有物理变更和事务信息。
两种日志的区别：
1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2.redo log 是物理日志，记录的是“在某个数据页上做了什么修改”，；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”，即SQL语句。
3.redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
4.redo log 是在事务执行过程不断的写入；binlog 是在事务最终提交前写入。
5.redo log 作为异常宕机或者介质故障后的数据恢复使用，binlog 可以应用于数据归档，主从搭建等场景。
那么为什么会有两份日志呢？
原因：在刚开始的时候，MySQL的执行引擎是MyISAM，不存在 crash-safe 的能力，只能用于数据归档，后来另一家公司Innobase Oy公司(2006年被甲骨文收购)创造了InnoDB,并且支持 crash-safe 能力，而且支持事务功能，所以再5.5之后的版本就默认使用 InnoDB 作为存储引擎。
crash-safe：指数据库发生故障重启，之前提交的数据不会丢失。

四：一条更新语句是如何执行的

更新语句内部执行流程：

update table_name set N=N+1 where id = 1;

1.执行器找引擎取 ID=2这一行，ID 是主键，引擎直接用树搜索找到这一行。如果这一行所在的数据页在内存当中，就直接返回给执行器，否则需要先从磁盘中读入内存，再返回。
2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4.执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5.执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。