MySQL基础架构和日志系统

本文主要内容来源于《MySQL实战45讲》（作者：林晓斌），是自己做了一下归纳整理的学习笔记。

1，逻辑架构图

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

• Server层：涵盖MySQL的大多数核心服务功能，所有跨存储引擎的功能都在这一层实现，如内置函数、存储过程、触发器、视图等。
• 存储引擎层：负责数据的存储和提取，其架构是插件式的，支持InnoDB、MyISAM、Memory等

1.1 连接器

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

-- 建立连接
mysql -h$ip -P$port -u$user -p

-- 查询连接
show processlist
1
2
3
4
5

一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。只有再新建的连接，才会使用新的权限设置。

1.2.1 查询缓存

MySQL执行一个查询请求后，会把查询结果以key（查询语句）-value（查询结果）的形式存入查询缓存中，下次查询相同语句会直接从查询缓存中返回结果，效率很高。

但查询缓存弊大于利，建议关闭。甚至MySQL 8.0 版本已将查询缓存删掉了，没有这个功能了。

因为查询缓存失效非常频繁，只要对某个表进行更新操作，这个表上所有查询缓存都会被清空。

1.2.2 分析器

1. 词法分析：识别SQL字符串中表名、字段名等。
2. 语法分析：判断语句是否满足 MySQL 语法。

1.3 优化器

确定最终执行计划：

1. 多个索引时索引的选择；
2. 多表关联join时，各个表的连接顺序等;

1.4 执行器

返回结果：

1. 判断用户对执行的表是否有权限；
2. 执行器根据表的引擎定义，去使用这个引擎提供的接口;

2，日志系统

• Service层：binlog（归档日志）
• 引擎层：InnoDB特有的redo log（重做日志）

2.1 redo log（重做日志）

如果每次更新时，都需要在磁盘中找到对应的记录，然后更新并将新数据写进磁盘，整个过程的IO成本、查找成本都很高。

为了提升效率，InnoDB引擎使用了WAL 技术（Write-Ahead Logging），即先写日志，再写磁盘。
具体来讲，当有一条记录需要更新时，InnoDB引擎会先把记录写到redo log中，并更新内存，这时更新就算已经完成。在系统比较空闲时，再把这个更新操作记录到磁盘里（redo log文件名：ib_logfile+数字）。

redo log 是固定大小的，可以配置。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示：

write pos 是当前记录的位置，checkpoint 是当前要擦除的位置（在擦除记录前要把记录更新到磁盘的数据文件中），它们都是往后推移并且循环的。

write pos 和 checkpoint 之间空的部分可以用来记录新的操作，如果两者重合，则不能再执行新的更新，需要把一些数据写进磁盘，让checkpoint 往后移动才行。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

redo log 的写入机制：

1. 写入redo log buffer‌：‌事务在执行过程中，生成的 redo log 是要先写到 redo log buffer，‌这个buffer位于MySQL进程的内存中；
2. 写入文件缓存（‌write）‌‌：‌redo log被写入到磁盘文件系统的page cache里面。‌
3. 持久化到磁盘（‌fsync）‌‌：‌InnoDB 提供了 innodb_flush_log_at_trx_commit 参数控制写入磁盘的策略。‌

• innodb_flush_log_at_trx_commit=0，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
• innodb_flush_log_at_trx_commit=1，表示每次事务提交时都将 redo log 直接持久化到磁盘；
• innodb_flush_log_at_trx_commit=2，表示每次事务提交时都只是把 redo log写到 page cache。

建议：innodb_flush_log_at_trx_commit=1

此外，没有提交的事务也会被MYSQL写入到磁盘：

• MYSQL 会有一个后台线程，每隔1秒把redo log buffer 持久化到磁盘， 直接经过file cache到磁盘。
• 如果并发的事务提交落盘后也会连带着把另外一个事务的redo log buffer持久化到磁盘。 redo log buffer
• 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘来减少 redo log buffer的空间占用

2.2 binlog（归档日志）

redo log是InnoDB引擎特有的日志，Service层也有自己的日志：binlog（归档日志）。

为什么要有两份日志呢？
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。InnoDB是另外的公司以插件形式引入MySQL的，为了弥补其没有crash-safe能力而实现的redo log。

两种日志主要有三个不同点：

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

binlog 的写入机制：

1. 写到 binlog cache：事务执行过程中，先把日志写到 binlog cache；
2. 写入文件缓存（‌write）‌‌：‌事务提交的时候，再把 binlog cache 写到文件系统的缓存 page cache中；
3. 持久化到磁盘（‌fsync）‌‌：‌最后mysql会根据你的sync_binlog配置决定什么时候刷新到磁盘binlog文件中;

• sync_binlog=0，表示每次提交事务都只 write，不 fsync；
• sync_binlog=1，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) ，表示每次提交事务都 write，但累积 N
  个事务后才 fsync。

在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
建议：sync_binlog = 1 每次事务的binlog都持久化到磁盘

一个事务的 binlog 是不能被拆开的，不论这个事务多大，也要确保一次性写入。系统给每个线程都分配了一片 binlog cache内存，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

2.3 两阶段提交

这里的关键部分是，redo log 的写入拆成了两个步骤：prepare 和 commit。这就是所谓的"两阶段提交"。

为什么会有两段提交呢？是为了让两份日志之间的逻辑一致。如果不一致，则数据库的数据和用binlog恢复或者同步出来的数据会不一致。

2.3.1 崩溃恢复机制是什么？

情况1：写入redo log后，写入binlog之前崩溃，因为未commit，事务回滚，redo log也会回滚；
情况2：写入redo log，又写入binlog后，commit之前崩溃：

• 如果rodo log 里面事务是完整的（有commit标识），则直接提交；
• 如果redo log里面事务只有prepare，则判断binlog是否存在且完整：
  a，如果是，则提交事务
  b，如果否，回滚事务

2.3.2 如何知道binlog是否完整？

一个事务的binlog是由完整格式的：

• statement格式的binlog，最后会有COMMIT；
• row格式的binlog，最后会有一个XID event；
• MySQL5.6.2以后，引入binlog-checksum参数，用来验证binlog内容的正确性；

2.3.3 redo log 和binlog如何关联的？

他们有一个共同的数据字段XID。
情况2时，会拿redo log的XID去binlog中找对应的事务。

2.3.4 为什么不用binlog来做崩溃恢复？

因为binlog不支持崩溃恢复。

MySQL原生引擎MyISAM在设计之初就没有支持崩溃恢复。binlog的目的主要用于数据复制和某些类型的增量备份，‌它虽然保存了全量的日志，‌但没有提供崩溃恢复的功能。

最重要的是，binlog是追加写的，‌但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有。

2.3.5 redo log 一般设置多大？

redo log 太小的话，会导致很快被写满，然后不得不强制刷redo log到磁盘，WAL机制的能力有发挥不出来。
如果几个TB的磁盘，直接将redo log设为4个文件，每个文件1GB.
innodb_log_files_in_group=4
innodb_log_file_size=1000M