Day 17

Channing Hsu
  2024-08-01 20:23:53 2024-08-01 20:23:53 创建   2024-12-03 01:50:49 2024-12-03 01:50:49 更新      5.5k 字  19 分钟  

1. undo log、redo log、binlog 有什么用？

a) undo log: 用于事务回滚和MVCC(多版本并发控制)。记录数据被修改前的值，用于撤销操作。
b) redo log: 确保事务的持久性。记录数据页的物理修改，用于故障恢复。
c) binlog: 用于主从复制和数据恢复。记录数据库的所有变更操作。

举例:
当执行UPDATE语句时，MySQL会同时记录undo log(原始数据)和redo log(修改后的数据)。如果事务提交，则应用redo log；如果回滚，则使用undo log恢复原始数据。

执行一条 update 语句，期间发生了什么？，比如这一条 update 语句：

1
UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

查询语句的那一套流程，更新语句也是同样会走一遍：

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

不过，更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志） 、binlog（归档日志）这三种日志：

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

%E6%8F%90%E7%BA%B2.png

为什么需要 undo log？

在执行执行一条“增删改”语句的时候，虽然没有输入 begin 开启事务和 commit 提交事务，但是 MySQL 会隐式开启事务来执行“增删改”语句的，执行完就自动提交事务的，这样就保证了执行完“增删改”语句后，可以及时在数据库表看到“增删改”的结果了。

执行一条语句是否自动提交事务，是由 autocommit 参数决定的，默认是开启。所以，执行一条 update 语句也是会使用事务的。

那么，考虑一个问题。一个事务在执行过程中，在还没有提交事务之前，如果 MySQL 发生了崩溃，要怎么回滚到事务之前的数据呢？

如果每次在事务执行过程中，都记录下回滚时需要的信息到一个日志里，那么在事务执行中途发生了 MySQL 崩溃后，就不用担心无法回滚到事务之前的数据，我们可以通过这个日志回滚到事务之前的数据。

实现这一机制就是 undo log（回滚日志），它保证了事务的ACID特性中的原子性（Atomicity）。

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

回滚事务

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

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

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，然后进行 insert 操作。

不同的操作，需要记录的内容也是不同的，所以不同类型的操作（修改、删除、新增）产生的 undo log 的格式也是不同的。

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

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

版本链如下图：

版本链

另外，undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）。

对于读提交和可重复读隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

  • 读提交隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
  • 可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过事务的 Read View 里的字段和**记录中的两个隐藏列（trx_id 和 roll_pointer）**的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

因此，undo log 两大作用：

  • 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执
    行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
  • 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

为什么需要 Buffer Pool？

MySQL 的数据都是存在磁盘中的，那么要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？

当然是缓存起来好，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。

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

Buffer Poo

有了 Buffer Pool 后：

  • 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。

  • 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

Buffer Pool 缓存什么？

InnoDB 会把存储的数据划分为若干个页，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按页来划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页，Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

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

Buffer Pool 除了缓存索引页和数据页，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

Undo 页是记录什么？

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

查询一条记录，就只需要缓冲一条记录吗？

不是的。

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

为什么需要 redo log？

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL（Write-Ahead Logging）技术。

WAL 技术指的是，MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

过程如下图：

什么是 redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

被修改 Undo 页面，需要记录对应 redo log 吗？

需要的。

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

不过，在内存修改该 Undo 页面后，需要记录对应的 redo log。

redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

  • redo log 记录了此次事务完成后的数据状态，记录的是更新之后的值；
  • undo log 记录了此次事务开始前的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务，如下图：

事务恢复

所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。

为什么需要 binlog？

前面介绍的 undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事务执行过程中产生的所有 binlog 统一写 入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

为什么有了 binlog，还要有 redo log？

这个问题跟 MySQL 的时间线有关系。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

redo log 和 binlog 有什么区别？

这两个日志有四个区别。

1、适用对象不同：

  • binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
  • redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

  • binlog 有 3 种格式类型，分别是 STATEMENT、ROW、MIXED，区别如下：

在 MySQL 5.7.7 之前，binlog_format的默认值是STATEMENT，而在 5.7.7 及之后的版本，默认值为ROW。

  • STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式，binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；

  • ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；

  • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

  • redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新；

3、写入方式不同：

  • binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
  • redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4、用途不同：

  • binlog 用于备份恢复、主从复制；
  • redo log 用于掉电等故障恢复。

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

总结

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下：

  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。
  4. InnoDB 层开始更新记录，先生成对应redo log，并存入redo log buffer里面，当事务提交时，将redo log写入redo log file，并更新buffer pool中的数据页，将其放入flush 链表并标记脏页和记录redo log对应的lsn到该页的oldest_modification，这个时候更新就算完成了。为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
  5. 至此，一条记录更新完了。
  6. 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
  7. 事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：
    • prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
    • commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；
  8. 至此，一条更新语句执行完成。

2. 什么是慢查询？原因是什么？可以怎么优化？

慢查询是指执行时间超过某个阈值（通常是几秒）的SQL查询。

原因可能包括:
a) 未使用索引或索引使用不当
b) 表数据量过大
c) 复杂的联接查询
d) 服务器配置不足
  • 分析查询语句:使用EXPLAIN命令分析SQL执行计划，找出慢查询的原因，比如是否使用了全表扫描,是否存在索引未被利用的情况等，并根据相应情况对索引进行适当修改。
  • 创建或优化索引:根据查询条件创建合适的索引，特别是经常用于WHERE子句的字段、Order by 排序的字段、Join 连表查询的字典、 Group By的字段，并且如果查询中经常涉及多个字段，考虑创建联合索引，使用联合索引要符合最左匹配原则，不然会索引失效
  • 避免索引失效: 比如不要用左模糊匹配、函数计算、表达式计算等等。
  • 查询优化:避免使用SELECT*，只查询真正需要的列;使用覆盖索引，即索引包含所有查询的字段;联表查询最好要以小表驱动大表，并且被驱动表的字段要有索引，当然最好通过冗余字段的设计，避免联表查询。
  • 分页优化: 针对 Limit N,Y 深分页的查询优化，可以把Limit查询转换成某个位置的查询:Select * From Tb sku where Id>20888 Limit 18，该方案适用于主键自增的表,优化数据库表:如果单表的数据超过了千万级别，考虑是否需要将大表拆分为小表，减轻单个表的查询压力。也可以将字段多的表分解成多个表，有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。
  • 使用缓存技术:引入缓存层，如Redis，存储热点数据和频繁查询的结果，但是要考虑缓存一致性的问题，对于读请求会选择旁路缓存策略，对于写请求会选择先更新 Db，再删除缓存的策略,

优化方法:
a) 添加适当的索引
b) 优化SQL语句，避免全表扫描
c) 分析执行计划，使用EXPLAIN命令
d) 适当拆分大表
e) 使用缓存技术
f) 升级硬件配置

举例:
假设有一个慢查询:

1
SELECT * FROM orders WHERE create_time > '2023-01-01' AND status = 'completed';

优化方法可以是在create_time和status列上建立复合索引:

1
CREATE INDEX idx_time_status ON orders (create_time, status);
评论
目录
Day 17
  1. 1. undo log、redo log、binlog 有什么用？
    1. 为什么需要 undo log？
    2. 为什么需要 Buffer Pool？
    3. 为什么需要 redo log？
    4. 为什么需要 binlog？
    5. 总结
  2. 2. 什么是慢查询？原因是什么？可以怎么优化？