MySQL的框架以及重要日志

一、基础架构图

 

 

1.MySQL的框架的几个组件

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

查询缓存：查询请求先访问缓存(key 是查询的语句，value 是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)--mysql8.0已经没有缓存了。

分析器：对 SQL 语句做解析，判断sql是否正确

优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。

执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。

2.erver层和存储引擎层各是什么作用?

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

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

3.对于表的操作权限验证在哪里进行?
执行器

4.select * from T where ID=10；

执行器的执行查询语句的流程是什么样的?

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

二、redoLog和binlog日志

mysql WAL 技术 Write-Ahead Logging，关键点就是先写日志，再写磁盘

mysql里涉及两个重要日志分别为redoLog 和binlog，redolog 负责事务； binlog负责归档恢复

重要日志：redoLog

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redolog里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当(空闲)的时候，将这个操作记录更新到磁盘里面

redolog也是在磁盘上，这也是一个写磁盘的过程，但是与更新过程不一样的是，更新过程是在磁盘上随机IO，费时。 而写redolog 是在磁盘上顺序IO。效率要高。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

 write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

重要日志：binlog

update 语句时的内部流程。

比如mysql> update T set c=c+1 where ID=2;

1.执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4.执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5.执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

三、两阶段提交

图上所示 将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。  

1 prepare阶段 2 写binlog 3 commit
当在2之前崩溃时
重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。
一致
当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

redoLog 和binlog两个日志也是用来保证crash-safe能力

需要认识：crash-safe（即使mysql服务宕机，也不会丢失数据的能力）

只有两份日记才有crash-safe功能 为什么binlog不能做到crash-safe？ 假如只有binlog，有可能先提交事务再写binlog，有可能事务提交数据更新之后数据库崩了，还没来得及写binlog。我们都知道binlog一般用来做数据库的主从复制或恢复数据库，这样就导致主从数据库不一致或者无法恢复数据库了。同样即使先写binlog再提交事务更新数据库，还是有可能写binlog成功之后数据库崩掉而导致数据库更新失败，这样也会导致主从数据库不一致或者无法恢复数据库。所以只有binlog做不到crash-safe。为了支持crash-safe，需要redolog，而且为了保证逻辑一致，事务提交需要两个阶段：prepare阶段和commit阶段。写redolog并落入磁盘(prepare状态)-->写binlog-->commit。commit的时候是不会落盘的。

为什么还需要binlog

一个原因是，redolog只有InnoDB有，别的引擎没有。
另一个原因是，redolog是循环写的，不持久保存，binlog的“归档”这个功能，redolog是不具备的。

四、为什么需要两阶段提交

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1.先写 redo log 后写 binlog。（数据库可以恢复到半个月内任意一秒的状态--依靠的就是binLog的归档日志）

假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

2.先写 binlog 后写 redo log。

如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

redoLog 和binlog两者区别在于

 1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

 2.redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1

 3.redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

五、binlog 的写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

（每个线程有自己 binlog cache，但是共用同一份 binlog 文件）

 

图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。（将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。）

六、redo log 的写入机制

redo log 是要先写到 redo log buffer

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;

设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；

设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。

另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘

redo log  prepare 阶段，一定会持久化到磁盘，但commit是提交到page cache上，然后等待时机持久化到磁盘(可能是有事务提交带上去，也可能是buffer占用空间达到了，也可能是后台线程刷新的)。但是会有个风险点就是，

如果一个事务写完redo log  prepare 阶段，并且也写完binlog，但是commit还没来得及持久化到硬盘的时候 mysql挂了，重启通过prepare阶段和binlog是可以恢复事务的，就是会有延时性。

如果买来得及写binlog,那么mysql恢复后会回滚事务，数据就会丢失。

MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。（事务提交后，commit阶段也需要等待刷盘）

WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，这磁盘读写次数也没变少呀？

WAL 机制主要得益于两个方面：

1.redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；

2.组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

七、组提交机制

LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

如图 3 所示，是三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160

 

 trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。为了让一次 fsync 带的组员更多，MySQL 有一个很有趣的优化：拖时间。

 如图，binlog和redolog都是组提交

想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

八、问题

如果 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

1.设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

2.将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000，表示每次提交事务都 write，但累积 N 个事务后才 fsync，风险点：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。）。

3.将 innodb_flush_log_at_trx_commit 设置为 2（表示每次事务提交时都只是把 redo log 写到 page cache。）。这样做的风险是，主机掉电的时候会丢数据。

学习链接：https://zhuanlan.zhihu.com/p/343449447