第一章 从零看MySQL基础架构

第一章 从零看MySQL基础架构

MySQL 逻辑架构图

MySQL 可以分为 Server 层 和 存储引擎层 两部分

• Server 层包括 连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory（内存引擎） 等多个存储引擎。
  • 现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
  • 不同存储引擎的表数据存取方式不同，支持的功能也不同

通过一条 SQL 语句，解释这条语句在 MySQL 内部的执行过程

mysql> select * from T where ID=10；

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

连接命令一般是这么写的：

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

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
  • 这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

查询 MySQL 连接状态

show processlist;

• 其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
• 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

查询 MySQL 连接超时时间

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

• 时间单位为：秒，转换为小时是：8小时

长连接 与 短连接

• 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
• 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

短连接和长连接是怎么设置的？是连接数据库的时候有什么参数吗？

• 这是“行为”，比如连接完，执行一个查询，就断开，这是短连接
• 执行一个查询，不断开，下次查询还用这个连接，持续使用，就是长连接
• 建立连接的过程通常是比较复杂的，所以建议尽量使用 长连接

MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

如何解决？

1. 定期断开长连接
2. 初始化连接资源，恢复到刚刚创建完时的状态

查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句

• 之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中
  • key 是查询的语句，value 是查询的结果
  • 如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？

• 因为查询缓存往往弊大于利
• 因为数据库表如果添加、删除、修改后缓存就会失效，除非这个表不会动，正常我们平时的表的改动会很频繁，这样就会造成不停的更新缓存
• 注意：8.0 之后的版本就已经将查询缓存的整块功能删掉了

分析器

如果没有命中查询缓存，就要开始真正执行语句了

1. 先进行“词法分析”，识别传进来的字符串
2. 根据传进来的字符串用“语法分析”进行判断
3. 如果有错误，将会收到 You have an error in your SQL syntax 的报错

词法分析 和 语法分析 的区别?

• 词法分析主要做的是根据 MySQL 的关键字进行验证和解析
• 语法分析会在词法解析的基础上进一步做表名和字段名称的验证和解析

优化器

通过分析器，明白你要干啥后，数据库就要针对你的需求想一个最优的解决方案，也就是执行计划，这个最优方案选择的操作，就是优化器要做的事情了。

原则是：尽可能扫描少的数据库行记录

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

• join 是 inner join 的缩写
• using(ID) 是 on t1.ID == t2.ID 的简写，因为联表 on 条件是两张表中同一个字段，可以简写为 using(字段名)
• 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20
• 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10

一条 SQL 语句可能有不同的执行逻辑（或者执行顺序），而优化器就是选择最优的执行顺序。

执行器

MySQL 执行的流程包括：

• 分析器阶段的词法分析和语法分析（分析器解释出你要做什么）
• 优化器阶段的索引和连接顺序优化（优化器解释出你需要该怎么做）
• 最后由执行器开始执行（执行器负责执行）

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

• 权限验证不仅仅在执行器这部分会做
• 在分析器之后，也就是知道了该语句要“干什么”之后，也会先做一次权限验证。叫做precheck。
• 而 precheck 是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。
• 连接器是 账号权限校验
• 分析器是 库权限验证
• 执行器是 语句在表中权限验证

到了执行的时候才会进入到数据库引擎，然后执行器也是通过调用 数据库引擎的API 来进行数据操作的。也因此数据库引擎才会是插件形式的。

select * from T where ID=10；

比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
4. 没有索引：执行器调用sql引擎的api进行全表扫描
5. 存在索引：执行器调用sql引擎的api，引擎通过索引的缓存快速定位到数据并返回（索引的缓存原理需要继续深入了解）

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

• 引擎扫描行数：因为存在查询没有索引的字段的情况，引擎扫描行数可能会从头扫到尾
• 假设一个表有10行，那引擎扫描行数就是10。
• rows_examined：并不是执行器调用了引擎多少次，也不是引擎总共扫描了多少次（如果扫描的是有索引的字段则是）
• 而是引擎扫描到我们需要的数据总共有多少行，假设一个表有10行，而有5行是我们需要的，那么 rows_examined 就是5。

存储引擎 和 执行器 的区别?

1. 存储引擎层的各种数据获取方法都是已经定义好了的，是静态的方法；
2. 优化器 生成的执行计划，决定了 执行器会选择 存储引擎的哪个方法去获取数据，而这也决定了一个操作是否能快速执行完成；
3. InnoDB 存储引擎层的优化措施还有好多，对执行器来说只是一个黑箱，可以再深入了解。

问题?

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢 ?

• 是在 分析器 阶段报出来的