Mysql 都有那些最需要掌握的原理？

如何写好 SQL？如何发现日常开发中慢 SQL 的问题？

关于这些问题的解决，最好的方式当然是去了解 MySQL 的原理。本文选取 MySQL 体系中较为核心的部分内容，尽可能详细的介绍这些核心模块及其底层原理，尽可能多的加入实战案例来加深理解，以便于在以后的开发中更好的去避免问题，发现问题，解决问题。

一、MySQL 体系架构

MySQL Server 架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层，具体如下图所示：

网络连接层

客户端连接器（ Client Connectors） ：提供与 MySQL 服务器建立的支持。支持现在主流的编程技术 Java、C、Python、PHP 等，通过各自的 API 技术与 MySQL 建立连接。

服务层（MySQL Server）

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

• 连接池（Connection Pool） ：负责用户的登录鉴权，执行操作鉴权，存储和管理客户端与数据库的连接

• 系统管理和控制工具（Management Services & Utilities） ：包含例如备份恢复、安全管理、集群管理服务和工具

• SQL 接口（SQL Interface） ：用于接受客户端发送的各种 SQL 命令，并且返回用户需要查询的结果。比如 DML、DDL、存储过程、视图、触发器等

• 解析器（Parser） ：负责将请求的 SQL 解析生成一个"解析树"。然后根据一些 MySQL 规则进一步检查解析树是否合法

• 查询优化器（Optimizer） ：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互

• 缓存（Caches & buffers）： 包括全局和引擎特定的缓存，这个缓存机制是由一系列小缓存组成，如表缓存、记录缓存、key 缓存、权限缓存等

存储引擎层（Pluggable Storage Engines）

存储引擎负责 MySQL 中数据的存储与提取，与底层系统文件进行交互。MySQL 存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是 MyISAM 和 InnoDB，以及支持归档的 Archive 和内存的 Memory 等。

系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

二、一条查询 sql 的执行过程

一条查询语句的过程大致如下：

 

第一步：建立连接

输入用户名、密码进行登录，权限验证后正式建立连接。连接器会到权限表里面查出你拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

长连接与短连接

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

建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。在客户端体现就是使用线程池，springboot2 及公司 RDS SDK 默认的数据库连接池为 HikariCP。

Hikari 数据库连接池配置详解

• 另外，数据库连接池参数配置参考：HikariCP连接池参数配置多大合适HikariCP连接池参数配置多大合适(二)

第二步：查询缓存

建立连接后，就可以执行 select 语句了。执行逻辑就会来到查询缓存，MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

MySQL 8.0 版本后移除了该功能，因为查询缓存失效在实际业务场景中可能会非常频繁，假如对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。所以，一般在大多数情况下都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

第三步：分析器

MySQL 没有命中缓存，就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句由多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

第四步：优化器

优化器的作用就是以它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

第五步：执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

 

更新语句

sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。先分析下查询语句，语句如下：

update tb_employee set level = '5' where name='张三'复制代码

复制代码

其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志，这就会引入日志模块，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，InnoDB 引擎还自带了一个日志模块 redo log（重做日志），就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

先查询到张三这一条数据，如果有缓存，也是会用到缓存。

然后拿到记录，把 level 改为 5，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。

执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。

更新完成。

为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没有 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做：

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

• 判断 redo log 是否完整 (commit)，如果判断是完整的，就立即提交。

• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

总结

• 查询语句执行流程如下： 权限校验（如果命中缓存）–> 查询缓存 —> 分析器 --> 优化器 —> 权限校验 —> 执行器 —> 引擎

• 更新语句执行流程如下： 分析器 —> 优化器 —> 权限校验 —> 执行器 —> 引擎 —> redo log(prepare) —> binlog —> redo log(commit)

三、InnoDB VS MyISAM

功能对比 （InnoDB 能取代 MyISAM 的原因）

InnoDB 和 M