MYSQL--日志和内存

参考链接：https://xiaolincoding.com/mysql/log/how_update.html

日志

执行一条update语句的流程：

客户端先通过连接器建立连接，连接器自会判断用户身份；
因为这是一条 update 语句，所以不需要经过查询缓存，但是表上有更新语句，是会把整个表的查询缓存情空的，所以说查询缓存很鸡肋，在 MySQL 8.0 就被移除这个功能了；
解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法；
预处理器会判断表和字段是否存在；
优化器确定执行计划，因为 where 条件中的 id 是主键索引，所以决定要使用 id 这个索引；
执行器负责具体执行，找到这一行，然后更新。

undo log（回滚日志）：Innodb存储引擎生成的日志，实现事务的原子性 用于事务回滚和MVCC
redo log（重做日志）：innodb存储引擎生成的日志，用于掉电等故障回复 实现了事务的持久性
binlog（归档日志）：是Server层生成的日志，用于数据备份和主从复制

undo log

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

通过 trx_id 可以知道该记录是被哪个事务修改的；
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；
版本链：

另外，undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）。
如果在在ReadView中不满足可见行，可以顺着undo log版本链找到满足其可见性的记录

总结，undo log两大作用：
1.实现事务回滚，保障事务原子性
2.实现MVCC关键因素之一

buffer pool

为了缓存已被命中的数据，方便下次查询，Innodb设计了缓冲池（Buffer Pool），用于提高数据库读写性能，buffer pool内部分页

当读数据时，如果在Buffer Pool 里有，直接去Buffer Pool读取
当修改数据时，如果在Buffer Pool里，直接修改Buffer Pool中数据所在页，然后将该页设置为脏页（该页数据和磁盘上的数据不一样的页），然后由后台线程选择一个合适时机把脏页写入磁盘（为减少磁盘IO次数，不会立即写入）

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。

buffer pool包括数据页、索引页、插入缓存页、undo页、自适应哈希索引、锁信息。
其中undo里会存放undo log
开启事务后，innodb更新记录前，要先记录undo log，如果是更新操作，要先把更新的列的旧值记下来，生成一条undo log，然后undo log写入buffer pool的undo

redo log

buffer pool是基于内存的，断电后没来得及落盘的脏页数据会丢失，为解决这个问题，innodb会先把记录写入redo log然后更新内存，同时，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术，指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。
过程：

redo log是一个物理日志，记录某个数据页做了什么修改。在事务提交时，先将redo log持久化到磁盘，可以不先把buffer pool的脏页持久化到磁盘

redo log和undo log区别：
undo log 是存在buffer pool中，在内存中，是记录事务开始前的数据状态 是更新前的值
redo log 是一个物理日志，在磁盘中，是记录事务完成后的数据状态 是更新后的值

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务
有了redo log和WAL，可以实现崩溃恢复（crash-safe），保证了事务的持久性。

redo log要写到磁盘，数据也写到磁盘，为何要多此一举

写入redo log是使用了追加操作，磁盘操作是顺序写
写入数据要先找到写入位置，然后才写道磁盘，磁盘操作是随机写
顺序写比随机写更加高效（WAL的优点：随机写变顺序写）

总结，redo log 优点：
1.实现事务的持久性，让MYSQL有crash-safe能力
2.将写操作从随机写变为顺序写 提高磁盘写入效率

redo log也不是直接写入到磁盘的，也有自己的缓存 redo log buffer，他会在以下几个时机写入磁盘：
1.MYSQL正常关闭
2.当redo log buffer中的记录写入量大于redo log buffer内存空间的一半
3.innodb后台线程每隔1秒，将redo log buffer持久化到磁盘
4.每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）
redo log写满怎么办

redo log是为了防止脏页丢失，所以当脏页写入磁盘redo log里的记录就没有意义了
redo log写满后会从开始的地方继续写，覆盖掉之前的 具体看：https://xiaolincoding.com/mysql/log/how_update.html#redo-log-%E6%96%87%E4%BB%B6%E5%86%99%E6%BB%A1%E4%BA%86%E6%80%8E%E4%B9%88%E5%8A%9E

binlog

undo log和redo log都是innodb引擎生成的，binlog是server层生成的

binlog记录所有数据库表结构变更和表数据修改的日志，不记录查询类操作

redo log和binlog区别

1.适用对象不同：
binlog是server层日志，所有引擎都可以用
redo log是innodb的日志，只能innodb引擎用

2.文件格式不同
3.写入方式不同
binlog是追加写，写满一个文件就创建一个新的文件继续写
redo log循环写，日志大小固定，写满就从头开始
4.用途不同：
binlog用于备份回复，主从复制
redo log用于掉电回复

binlog何时刷盘

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。
MySQL 给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。

重看update执行过程（重要）

1.执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
2.执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
如果一样的话就不进行后续更新流程；
如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
3.开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在修改该 Undo 页面前需要先记录对应的 redo log，所以先记录修改 Undo 页面的 redo log ，然后再真正的修改 Undo 页面。
4.InnoDB 层开始更新记录，根据 WAL 技术，先记录修改数据页面的 redo log ，然后再真正的修改数据页面。修改数据页面的过程是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。
5.至此，一条记录更新完了。
6.在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
7.事务提交，剩下的就是「两阶段提交」的事情了，接下来就讲这个。

两阶段提交

redo log和binlog都需要持久化到磁盘，且是两个独立逻辑，可能出现半成功状态，造成日志间逻辑不同。
为避免两份日志逻辑不一致，使用两阶段提交来解决。 两阶段提交就是把单个事务的提交拆分成两个阶段，分别是 准备（Prepare）和 提交（Commit）

当客户端执行commit语句或者自动提交的情况下，MYSQL内部开启一个XA事务，分两阶段来完成XA事务提交，如图：

由图，将redo log的写入拆分为两个步骤prepare和commit 中间穿插写入binlog
prepare：将XID（内部XA事务的ID）写入到redo log，同时将redo log对应事务状态设置为prepare，然后将redo log刷新到硬盘。
commit：将XID写入到binlog，然后将binlog刷新到磁盘，接着调用引擎的提交事务接口，将redo log状态设置为commit（将事务设置为commit状态后，刷入到磁盘redo log 文件，所以commit状态也是会刷盘的）

在这个过程成 如果MYSQL异常重启，MYSQL重启后按顺序扫描redo log文件 碰到处于prepare状态的redo log 就拿着redo log中的XID去binlog查看是否存在XID
如果binlog中没有XID，说明redo log完成刷盘，binlog还没，则回滚事务。
如果binlog中有XID，说明redo log和binlog都完成刷盘，则提交事务

对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID
两阶段提交是以 binlog 写成功为事务提交成功的标识

两阶段提交虽然能保证两日志一致性，但性能很差：
1.磁盘IO次数高，每个事务提交都会进行两次刷盘（redo log和binlog刷盘）
2.锁竞争激烈；在早期的 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交的顺序，在一个事务获取到锁时才能进入 prepare 阶段，一直到 commit 阶段结束才能释放锁，下个事务才可以继续进行 prepare 操作。

通过加锁虽然完美地解决了顺序一致性的问题，但在并发量较大的时候，就会导致对锁的争用，性能不佳。

组提交

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数
详情：https://xiaolincoding.com/mysql/log/how_update.html#%E7%BB%84%E6%8F%90%E4%BA%A4

内存

Buffer Pool

 Buffer Pool是在MYSQL启动时，InnoDB向操作系统申请的一片连续的内存空间，默认配置下Buffer Pool只有128MB，按照默认16KB的大小划分成一个个的页，叫做缓存页。

此时缓存页是空闲的，之后随着程序运行，才会有磁盘上的页被缓存到Buffer Pool中

所以MYSQL刚启动的时候，会观察到虚拟内存空间很大，但使用的物理内存空间很小，是因为只有这些虚拟内存被访问后，OS才会触发缺页中断，接着将虚拟内存和物理地址建立映射关系

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息（看前文）

为了更好的管理Buffer Pool里的缓存页，InnoDB为每个缓存页都创建一个控制块，控制块信息包括缓存页的表空间、页号、缓存页地址、链表节点等，控制块被放在Buffer Pool的最前面，接着才是缓存页

当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，因为，通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。

如何管理Buffer Pool

如何管理空闲页

Buffer Pool是一片连续内存空间，当MYSQL运行一段时间，这片连续空间的缓存页有空闲的，也有被使用的。
为了快速找到空闲缓存页，可以使用链表结构，将空闲缓存页的控制块作为链表的节点，这个链表称为Free链表（空闲链表）

Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

如何管理脏页

为了快速知道哪些缓存页是脏页，设计出Flush链表，它和Free链表相似，链表的节点也是控制块，区别在于Flush链表的元素是脏页

如何提高缓存命中率

采用LRU算法：

当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。

根据空间局部性原理，靠近当前被访问数据的数据，在未来很大概率会访问到，所以MYSQL在加载数据页时会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘IO，这叫做预读

但是可能这些提前加载进来的数据页并没有被访问，相当于预读白做了，这叫做预读失效

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。

如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。

如何解决预读失效而导致的缓存命中率低的问题？

让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。
MYSQL改进了LRU算法，将LRU划分为两个区域：old区和young区

young区在LRU链表前半部分，old后半部分
old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pc 参数来设置，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37。
划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。

虽然通过划分 old 区域 和 young 区域避免了预读失效带来的影响，但是还有个问题无法解决，那就是 Buffer Pool 污染的问题。
Buffer Pool污染：
当某个SQL语句扫描大量数据时，在Buffer Pool空间有限的情况下，可能会将Buffer Pool里的所有页都替换出去，导致大量热数据被淘汰

有的全表扫描的查询，很大缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入young区，从而导致热点数据被替换
LRU链表中young区域就是热点数据，只要我没提高进入到young区域的门槛，就能有效的保证young区域的热点数据不会被替换掉

MYSQL是这样做的 进入到young区域条件增加一个停留在old区域的时间判断：

如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部

总结：
Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

Buffer Pool 以页为单位缓冲数据，可以通过 innodb_buffer_pool_size 参数调整缓冲池的大小，默认是 128 M。

Innodb 通过三种链表来管理缓页：

Free List （空闲页链表），管理空闲页；
Flush List （脏页链表），管理脏页；
LRU List，管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。；
InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：

将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。
当**「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」**时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决批量数据访问，大量热数据淘汰的问题。
可以通过调整 innodb_old_blocks_pc 参数，设置 young 区域和 old 区域比例。

在开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可因为脏页在刷新到磁盘时导致数据库性能抖动。如果在很短的时间出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。