数据库-MySQL结构

1 MySQL基础架构分析

学习一条SQL是如何完成查询/更新的

1.1 MySQL基本架构

简单来说，MySQL可以分为：Server层+存储引擎层

Server层: 包括连接器，查询缓存，分析器，优化器，执行器等
所有跨存储引擎的功能都在这一层实现，如存储过程，触发器，视图，函数等
还有一个通用日志模块binglog日志模块

存储引擎层: 主要负责数据的存取，采用可以替换的插件结构，支持InnoDB，MyISAM等多个引擎
InnoDB是5.5之后的默认存储引擎，有自带的日志模块redo log模块

连接器: 身份认证和权限相关(登录MySQL时)

查询缓存: 执行查询语句时，会优化查询缓存(在MySQL8.0后移除，因为不太实用)

分析器: 没有命中缓存时，SQL语句会经过分析器
分析器判断SQL语句的功能，并检查语法是否错误

优化器: 按照MySQL认为最优的方案去执行

执行器: 执行语句，然后从存储引擎返回数据

1.2 Server层基本组件

1.2.1 连接器

连接器主要和身份认证和权限相关的功能有关，像一个门卫一样

连接器主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作
如果账户密码通过，连接器会到权限表中查询该用户的所有权限
之后再这个连接里的权限逻辑判断都会依赖此时读取到的权限数据

只要这个连接不断开，即使此时root修改了该用户的权限，此时该用户也不会受影响

1.2.2 查询缓存

查询缓存主要用来缓存SELECT语句结果以及该语句的结果集

连接建立后，执行查询语句时，会先查询缓存，MySQL会先校验这个SQL是否曾执行过
以K-V的形式存储到缓存中，如果命中了缓存，会直接将结果返回给客户端
如果没有命中，执行后续的操作，并把结果缓存起来，方便下一次调用

MySQL查询不建议使用缓存，因为查询缓存但缓存可能频繁失效
如果对一个表进行更新，那么这个表上的所有查询缓存都会被清空
所有在MySQL8.0后，查询缓存被移除掉了

1.2.3 分析器

主要用来分析SQL语句的目的，判断SQL语句是否合法

分析器的工作分为两步：
1，词法分析: 一条SQL语句有多个字符串组成
首先提取关键字，如SELECT，操作的表，字段名，操作条件等

2，语法分析: 判断SQL是否正确，是否符合语法

1.2.4 优化器

将按它认为最优的执行方案去执行

优化器的作用就是将按照它认为最优的执行方案去执行
如多个索引的时候该如何选择索引，多表查询时如何选择关联顺序等

经过了优化器后，这个语句该如何执行就已经定下来了

1.2.5 执行器

校验权限，开始执行

当确定了执行方案后，MySQL准备开始执行，首先校验用户有没有权限
如果没有，返回错误信息，如果有则去调用引擎的接口，返回接口执行的结果

2 语句分析

SQL语句可以分成两种，一种是查询，一种是更新(增加，删除，修改)

2.1 查询语句

SELECT * FROM user WHERE user.age = '18' AND user.name = 'coisini';
结合上述的SQL，来看看它的执行过程：

1，先检查该语句是否有权限，如果没有直接返回错误信息
有权限且在MySQL8.0之前，会查询缓存，以这条SQL为键，在缓存中查询是否有结果，如果有直接返回缓存结果
没有则执行下一步

2，通过分析器进行词法分析，提取SQL的关键元素，如SELECT，user,name等
然后判断这个SQL是否有语法问题，如关键字是否错误，如果没错则执行下一步

3，优化器确定执行方案，上述的SQL可以有两种执行方案
	A，先查询表中name为coisini的用户，再判断年龄是否为18
	B，先找出name为18的用户，再查询name为coisini的用户
优化器将根自己的优化算法选择一个效率最好的方案(优化器认为的最好，但有时不一定最好)
接着准备执行

4，执行器进行权限校验，如果没有权限返回错误信息
有权限则调用数据库引擎接口，返回引擎的执行结果

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

2.2 更新语句

UPDATE user SET user.age = '19' WHERE user.name = 'coisini';
更新语句首先要走一遍查询流程，且执行更新时，需要记录日志，这就会引入日志模块
MySQL自带的日志模块是binlog(归档日志)，所有存储引擎都可以使用
常用的InnoDB引擎还自带了一个redo log(重做日志)，在InnoDB模式下：

1，先查询到coisini这条数据，如果有缓存，也会用到缓存

2，然后将查询到的语句，把age改为19，然后调用引擎的接口写入这一行数据
InnoDB引擎将数据保存在内存中，同时记录redo log，此时redo log进入prepare状态
然后告诉执行器，执行完成了，可以随时提交

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

4，更新完成

更新语句的流程：
分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

2.2.1 两阶段提交日志

进行更新操作需要两个日志配合工作，使用反证法说明为什么这样：

1，先写redo log直接提交，再写binlog:
假设写完redo log后，机器异常重启了，binlog 没有被写入
那么机器重启后，会通过redo log恢复数据，但binlog并没有记录这条数据
后续机器进行备份时，会丢失这条数据，主从同步也会丢失这条数据(主从复制基于binlog)

2，先写binlog，再写redo log:
假设写入了binlog，机器异常重启了，由于没有redo log
本机无法恢复这条记录，但是binlog却记录了这条数据
同上一样，出现了数据不一致的情况

但使用两阶段提交日志就不一样了，写完binlog后，立刻写入redo log就会避免上述问题
如果遇到redo log处于prepare，刚写完binlog时机器异常重启，次数MySQL也有对应处理机制：

1，判断redo log是否完整，如果是完整的，提交redo log

2，如果redo log处于prepare而不是commit状态
判断binlog是否完整，完整则提交redo log，不完整则回滚事务