MySQL45讲笔记（一）逻辑架构、redolog、binlog

一 MySQL逻辑架构图

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

1.1 连接器

负责和客户端简历连接、获取权限、维持和管理连接

比如通过命令行连接到MySQL

权限问题
一个用户成功建立连接后，即使这个用户的权限修改了，也不会影响到已经连接的权限。

小结

• 当一个连接长时间没有动作时，连接器会主动断开连接.由参数wait_timeout控制
• 连接分为长连接和短连接。尽量复用长连接。
• 长连接也可能会产生一个问题：占用内存过多，导致OOM，MySQl异常重启

1.2 查询缓存

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

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

但不建议使用查询缓存！因为命中率低，所以8.0之后删除了该功能

1.3 分析器

1. 词法分析。将语句拆分成词。
2. 语法分析。根据语法规则，判断sql语句是否合法,不合法就报错了。

1.4 优化器

一条sql可能会有多种执行方案，优化器的作用是从中选择使用一个它认为最优的方案.
这个阶段之后，sql的执行方案就确定了

1.5 执行器

开始执行sql

会先检查当前用户的查询操作权限（查询也会在优化器之前调用precheck验证权限）。

执行过程

select * from T where ID=10;

ID字段没有索引，那么执行器的执行流程是这样的：

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

二 redo log

原文：redo log —— MySQL宕机时数据不丢失的原理

MySQL 在更新数据时，为了减少磁盘的随机 IO，因此并不会直接更新磁盘上的数据，而是先更新 Buffer Pool中缓存页的数据，等到合适的时间点，再将这个缓存页持久化到磁盘。而 Buffer Pool 中所有缓存页都是处于内存当中的，当 MySQL 宕机或者机器断电，内存中的数据就会丢失，因此 MySQL 为了防止缓存页中的数据在更新后出现数据丢失的现象，引入了 redo log 机制

2.1 定义

当进行增删改操作时，MySQL 会在更新 Buffer Pool 中的缓存页数据时，会记录一条对应操作的 redo log 日志，这样如果出现 MySQL 宕机或者断电时，如果有缓存页的数据还没来得及刷入磁盘，那么当 MySQL 重新启动时，可以根据 redo log 日志文件进行恢复

redo log 中记录的是物理日志，是 InnoDB 引擎才有的机制

2.2 查看命令

redo log 日志文件是持久化在磁盘上的，磁盘上可以有多个 redo log 文件，MySQL 默认有 2 个 redo log 文件，每个文件大小为 48MB，这两个文件默认存放在 MySQL 数据目录的文件夹下，这两个文件分别为 ib_logfile0 和 ib_logfile1。
可以通过如下命令来查看 redo log 文件相关的配置

show variables like 'innodb_log%'

2.3 redo log buffer

当一条 SQL 更新完 Buffer Pool 中的缓存页后，就会记录一条 redo log 日志，前面提到了redo log 日志是存储在磁盘上的，那么此时是不是立马就将 redo log 日志写入磁盘呢？显然不是的，而是先写入一个叫做 redo log buffer 的缓存中，redo log buffer 是一块不同于 buffer pool 的内存缓存区，在 MySQL 启动的时候，向内存中申请的一块内存区域，它是 redo log 日志缓冲区，默认大小是 16MB，由参数 innodb_log_buffer_size 控制

刷入磁盘时机

1. MySQL 正常关闭的时候；
2. MySQL 的后台线程每隔一段时间定时的将 redo log buffer 刷入到磁盘，默认是每隔 1s 刷一次；
3. 当 redo log buffer 中的日志写入量超过 redo log buffer 内存的一半时，即超过 8MB 时，会触发 redo log buffer 的刷盘；
4. 当事务提交时，根据配置的参数 innodb_flush_log_at_trx_commit 来决定是否刷盘。如果 innodb_flush_log_at_trx_commit 参数配置为 0，表示事务提交时，不进行 redo log buffer 的刷盘操作；如果配置为 1，表示事务提交时，会将此时事务所对应的 redo log 所在的 redo log block 从内存写入到磁盘，同时调用 fysnc，确保数据落入到磁盘；如果配置为 2，表示只是将日志写入到操作系统的缓存，而不进行 fysnc 操作。（进程在向磁盘写入数据时，是先将数据写入到操作系统的缓存中：os cache，再调用 fsync 方法，才会将数据从 os cache 中刷新到磁盘上）

2.4 两阶段提交

1. MySQL Server 层的执行器调用 InnoDB 存储引擎的数据更新接口；
2. 存储引擎更新 Buffer Pool 中的缓存页，
3. 同时存储引擎记录一条 redo log 到 redo log buffer中，并将该条 redo log 的状态标记为prepare 状态；
4. 接着存储引擎告诉执行器，可以提交事务了。执行器接到通知后，会写 binlog 日志，然后提交事务；
5. 存储引擎接到提交事务的通知后，将redo log 的日志状态标记为 commit状态；
6. 接着根据innodb_flush_log_at_commit 参数的配置，决定是否将 redo log buffer 中的日志刷入到磁盘。

redo log 在进行数据重做时，只有读到了 commit 标识，才会认为这条 redo log 日志是完整的，才会进行数据重做，否则会认为这个 redo log 日志不完整，不会进行数据重做

例如，如果在 redo log 处于 prepare 状态后，buffer pool 中的缓存页（脏页）也还没来得及刷入到磁盘，写完 biglog 后就出现了宕机或者断电，此时提交的事务是失败的，那么在 MySQL 重启后，进行数据重做时，在 redo log 日志中由于该事务的 redo log 日志没有 commit 标识，那么就不会进行数据重做，磁盘上数据还是原来的数据，也就是事务没有提交，这符合我们的逻辑

如何保证数据不丢失

实际上要严格保证数据不丢失，必须得保证 innodb_flush_log_at_trx_commit 配置为 1。

如果配置成 0，则 redo log 即使标记为 commit 状态了，由于此时 redo log 处于 redo log buffer 中，如果断电，redo log buffer 内存中的数据会丢失，此时如果恰好 buffer pool 中的脏页也还没有刷新到磁盘，而 redo log 也丢失了，所以在 MySQL 重启后，由于丢失了一条 redo log，因此就会丢失一条 redo log 对应的重做日志，这样断电前提交的那一次事务的数据也就丢失了。

如果配置成 2，则事务提交时，会将 redo log buffer（实际上是此次事务所对应的那条 redo log 所在的 redo log block ）写入磁盘，但是操作系统通常都会存在 os cache，所以这时候的写只是将数据写入到了 os cache，如果机器断电，数据依然会丢失。

而如果配置成 1，则表示事务提交时，就将对应的 redo log block 写入到磁盘，同时调用 fsync，fsync 会将数据强制从 os cache 中刷入到磁盘中，因此数据不会丢失。

2.5 疑惑

既然生产环境一般建议将 innodb_flush_log_at_trx_commit 设置为 1，也就是说每次更新数据时，最终还是要将 redo log 写入到磁盘，也就是还是会发生一次磁盘 IO，而我为什么不直接停止使用 redo log，而在每次更新数据时，也不要直接更新内存了，直接将数据更新到磁盘，这样也是发生了一次磁盘 IO， 何必引入 redo log 这一机制呢？

1. 因为写 redo log 时，我们将 redo log 日志追加到文件末尾，虽然也是一次磁盘 IO，但是这是顺序写操作（不需要移动磁头）；而对于直接将数据更新到磁盘，涉及到的操作是将 buffer pool 中缓存页写入到磁盘上的数据页上，由于涉及到寻找数据页在磁盘的哪个地方，这个操作发生的是随机写操作（需要移动磁头），相比于顺序写操作，磁盘的随机写操作性能消耗更大，花费的时间更长，因此 redo log 机制更优，能提升 MySQL 的性能。
2. 从另一方面来讲，通常一次更新操作，我们往往只会涉及到修改几个字节的数据，而如果因为仅仅修改几个字节的数据，就将整个数据页写入到磁盘（无论是磁盘还是 buffer pool，他们管理数据的单位都是以页为单位），这个代价未免也太了（每个数据页默认是 16KB），而一条 redo log 日志的大小可能就只有几个字节，因此每次磁盘 IO 写入的数据量更小，那么耗时也会更短

2.6 redo log大小是有限的

循环利用

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

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

三 binlog

redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）

3.1 为什么两份日志

因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redo log来实现crash-safe能力。

3.2 对比

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

update语句执行过程

3.3 两阶段提交

binlog会记录所有的逻辑操作，并且是采用追加写的 形式。如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时!!!系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

简单说，redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。(注意redolog是否生效，是以binlog里面是否有对应事务为准的。而不是以commit为准)

四 事务隔离原理-MVVC

多版本并发控制（MVCC）

如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本。

回滚日志总不能一直保留吧，什么时候删除呢？ 答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时（就是当系统里没有比这个回滚日志更早的read-view的时候），回滚日志会被删除。

在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

查看长事务的sql

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

参考

1. MySQL实战45讲