MySQL 的内部组件结构

大体来说，MySQL 可以分为 Server 层和存储引擎层。

Server 层

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

引擎层

负责数据等存储和提取。其架构模式是插件式的，支持 InnoDB，MyISAM，Memory 等多个存储引擎。

连接器

介绍

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

连接名中的 mysql 是客户端工具用来跟服务端建立连接。在完成 TCP 握手后，连接器就要开始认证你的身份，这时候就需要输入用户名和密码。一个用户建立连接后，即使你用管理员账号对这个用户对权限做了修改，也不会影响到已经存在连接到权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果你没有后续的动作，连接处于空闲状态，可以运行 show processlist 查看。客户端如果长时间不发送命令到服务端，连接器就会自动断开。这个时间由参数 wait_timeout 控制，默认是 8 小时。

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

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

MySQL 异常重启问题

开发过程中我们大多数时候用的是长连接放在 Pool 内进行管理，但是长连接有些时候会导致 Mysql 占用内存涨的特别快，这是因为 Mysql 执行过程中临时使用的内存是管理再连接对象里面的。这些资源会在连接断开时才释放，所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），这就是 MySQL 异常重启了。

怎么解决这类问题？

定期断开长连接，使用一段时间，或者程序里面判断执行一个占用内存的大查询后，断开连接，之后要查询再重连。
如果用的是 Mysql 或者更新版本，可以再每次执行一个毕竟大的操作后，执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

Mysql 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这个语句。之前执行过到语句及其结果可能会以 key-value 对的形式，直接缓存在内存中。key 是查询语句，value 是查询结果。

但是，大多数查询缓存就是鸡肋。

查询缓存往往弊大于利，因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

所以只建议在静态表（极少更新的表）里使用查询缓存，比如系统配置表，字典表，那这张表上的查询才适合使用查询缓存。

查询缓存配置

可以配置 my.cnf 参数 query_cache_type 来选择按需使用。

query_cache_type 值类型：

0，代表 OFF
1，代表 ON
2，DEMAID 代表当 SQL 语句中有 SQS_CACHE 关键词时才缓存

当 query_cache_type=2 时，默认的 SQL 语句都不使用查询缓存，对于你确定要使用查询缓存的语句，可以使用 SQL_CACHE 显示指定，例如：select SQL_CACHE * from test where ID=5;

# 查看当前是否开启缓存机制
show global variables like "%query_cache_type%";

监控查询缓存的命中率

mysql> show status like'%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 1       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

Qcache_free_blocks，标识查询缓存中目前还有多少剩余的 blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多，可能在一定的时间进行整理；
Qcache_free_memory，查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了还是不够用。
Qcache_hits 多少次命中缓存。数值越大，效果越理想。
Qcache_inserts，多少次未命中然后插入。意思时新来的 SQL 请求在缓存中未找到，不得不执行查询处理。
Qcache_lowmem_prunes，记录有多少条查询因为内存不足被移除出查询缓存。
Qcache_not_cached，标识因为 query_cache_type 的设置而没有被缓存的查询数量。
Qcache_queries_in_cache 缓存中缓存的查询数量
Qcache_total_blocks 当前缓存的 block 数量。

MySQL 8 已经移除了查询缓存功能。

分析器

Mysql 需要知道你要做什么，因此需要对 SQL 语句做解析。

分析器会做词法分析。你输入是由多个字符串和空格组成的一条 SQL 语句，Mysql 需要识别里面的字符串分别是什么，代表什么。

Mysql 从你输入的 select 这个关键词识别出来，这是一个查询语句，也要把字符串 T 识别成表名 T，把字符串 ID 识别成列 ID。

词法分析器原理

主要有 6 个步骤

词法分析
语法分析
语义分析
构造执行树
生成执行计划
计划的执行

SQL 语句的分析分为词法分析和语法分析，Mysql 的词法分析由 MySQLLex（Mysql 自己实现的）完成，语法分析由 Bison 生成。除了 Bison 外，Java 中也有开源的词法结构分析工具例如 Antlr4，Antlr 从语法生成一个解析器，可以构建和遍历解析树。

分析器可以参考 antlr4 的使用。

分析器的具体应用场景

例如，分库分表中对 SQL 语句的解析。

优化器

优化器是在表有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候，决定各个表的连接顺序。

执行器

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

查询语句

比如语句，假设 ID 字段没有索引：

select * from test where id=1;

执行器的流程是这样的：

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

更新语句

update tb_student A set A.age='19' where A.name=' 张三 ';

执行流程如下：

先查询到张三这一条数据，如果有缓存，也是会用到缓存；
拿到查询的语句，把 age 改为 19，调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 处于 prepare 状态，然后告诉执行器，执行完成了，随时可以提交；
执行器收到通知后记录 binlog，然后调用引擎接口，记录 redo log 为提交状态；
更新完成。

日志模块

上述中的更新操作就涉及到了日志模块，Mysql 日志包括错误日志、慢查询日志、事务日志、二进制日志等。我们需要关注的是二进制日志 binlog 和事务日志（包括 redo log 和 undo log）。

binlog

用来记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中。binlog 是 mysql 的逻辑日志，由 server 层进行记录，使用任何存储引擎都会记录 binlog 日志。

逻辑日志：可以简单理解为记录的就是 sql 语句。
物理日志： mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

binlog 使用场景

在实际应用中，binlog 的主要使用场景有两个，分别是主从复制和数据恢复。

主从复制。在 Master 端开启 binlog，然后将 binlog 发送到各个 Slave 端，Slave 端重放 binlog 从而达到主从数据一致。
数据恢复。通过使用 mysqlbinlog 工具来恢复数据。

binlog 日志格式

有 3 种格式，分别为 STATMENT、ROW 和 MIXED。

在 Mysql 5.7.7 之前，默认到格式是 STATMENT，5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

STATMENT。基于 SQL 语句的复制（statement-based replication, SBR），每一条修改数据的 sql 语句会记录到 binlog 中
优点：不需要记录每一行的变化。减少了 binlog 日志量，节约了 IO，从而提高了性能
缺点：在某些情况下会导致主从数据不一致，比如执行 sysdate()，sleep() 等。
ROW。基于行的复制（row-based replication，RBR），不记录每条 sql 的上下文信息，仅需记录哪条数据被修改了。
优点：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题；
缺点：会产生大量日志，尤其是 alter table 的时候会让日志暴涨
MIXED。基于 STATMENT 和 ROW 的两种模式的混合复制，一般的复制使用 STATMENT 模式保存 binlog，对于 STATMENT 模式无法复制的操作使用 ROW 模式保存 binlog。

推荐用 ROW 模式，比如 cacal 就是基于 ROW 解析的。

binlog 命令

# 查看bin-log是否开启
show variables like '%log_bin%';

# 会多一个最新的bin‐log日志
flush logs;

# 查看最后一个bin-log日志的相关信息
show master status;

# 清空所有bin-log日志
reset master;

MySQL的SQL执行过程