MySQL知识点归纳总结（二）

10、MVCC实现原理？

事务ID（Transaction ID）：每个事务在执行时都会被分配一个唯一的事务ID，用于标识该事务的开始时间顺序。事务ID是一个递增的整数，随着每个新事务的开始而递增。

Undo日志（Undo Log）：在MySQL中，每次修改数据的操作都会被记录在Undo日志中，包括修改前的数据内容和修改后的数据内容。这样即使事务回滚，也可以通过Undo日志来还原数据。

Read View：读视图用于实现MVCC中读取数据的一致性。在InnoDB中，每次启动一个新事务时，都会创建一个Read View，其中包含了该事务启动时正在运行的所有活跃事务的事务ID和已提交事务的ID范围。

版本链和版本库：为了支持MVCC，InnoDB使用了一种称为undochain的数据结构来管理数据的不同版本。版本链中的每个版本都有一个指向前一版本的指针，形成了一个链表结构。当一行数据被更新时，会在版本链中创建一个新版本，并将旧版本标记为已删除。

通过上述组件的协作，MySQL的MVCC机制实现了高效的并发控制。具体地，通过事务ID、Undo日志、读视图和版本链，MySQL能够实现数据的多版本管理，实现事务的隔离性和一致性，同时支持高并发读写操作，提高数据库性能和并发能力。

11、一条select语句发生了什么？

首先通过连接器简建立连接，这个连接通过show processlist查看，TCP协议建立连接，有空闲时间过期等方式断开连接。

其次通过语法树解析SQL，语法树不会去查表和检查字段是否存在，只会检查语法是否正确。

然后SQL执行器工作，这时候分三个阶段

• 预处理阶段：这时候就会看看字段表存在不存在

• 优化阶段：explain可以查看策略，例如using index就是使用了覆盖索引，all就是全表扫描，Using index condition用了索引下推

• 执行阶段

总结：
执行一条 SQL 查询语句，期间发生了什么？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

12、MySQL锁有哪些？

• 全局锁
• 表级锁
  1. 表锁
  2. 元数据锁
  3. 意向锁
• 行级锁
  1. Record lock
  2. 间隙锁
  3. 临键锁
  4. 插入意向锁

13、讲一下什么时候加什么锁？全表扫描会发生什么呢？

14、什么是意向锁，意向锁什么时候生效？意向锁会和行级锁冲突吗?

15、出现过数据库死锁嘛？举个例子？为什么导致的死锁？

两个事务都对同一个范围加间隙锁，然后尝试插入导致死锁。

16、知道mysql的undo log日志嘛？有什么作用？

首先描述结构然后说一下保证数据回滚等作用，最后结合MVCC机制再讲一下作用。

17、redo log干什么用的？熟悉Buffer Pool嘛？

如何回答？首先回答Buffer pool然后讲一下WAL技术，最后引出redo log存在的必要性。因为buffer pool内存的内容丢失问题。

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

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

18、两个日志的区别？

redo log 和 undo log 区别在哪？

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

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

19、redo log刷盘时机？

InnoDB 还提供了另外两种策略，由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1

当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。

20、redo log写满了怎么办？需要一直存着嘛？

重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

21、介绍binlog 日志？

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

• binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。

• redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

• binlog 用于备份恢复、主从复制；

• redo log 用于掉电等故障恢复。

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

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

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

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

23、redo log文件满了会发生什么？

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置。

24、Mysql主从复制怎么实现的？

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。（说白了就是执行更新过来的新信息，让主从同步。因为只是同步了日志，这个日志还没有执行）

25、Compact记录行为什么要这么设计？

主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

26、buffer pool的刷盘时机？

后台会有专门的线程每隔一段时间就把 flush 链表中的脏页刷入磁盘中，刷新的速率取决于当前系统是否繁忙。在这样的机制下，万一系统崩溃，是会产生数据不一致的问题的，没有刷入磁盘的数据就会丢失，而MySQL通过日志系统解决了这个问题。

InnoDB Buffer Pool的刷盘时机取决于以下几个因素：

1. 脏页比例：InnoDB引擎会将修改了而未被刷新到磁盘的数据页称为“脏页”。当脏页比例超过一定阈值时，InnoDB会启动刷盘操作，将一部分脏页写回到磁盘。
2. Checkpoint：Checkpoint是InnoDB引擎用于控制脏页比例的一种技术。通过定期向磁盘写入Checkpoint，InnoDB可以使脏页的数量保持在一个合理的范围内。
3. LRU算法：LRU（Least Recently Used）算法是InnoDB Buffer Pool用于管理内存中数据页的一种置换算法。当内存空间不足时，InnoDB会根据LRU算法选择一些不常用的数据页从内存中移除，以便为新的数据页腾出空间。同时，如果这些数据页是脏页，InnoDB也会将它们刷入磁盘。

在我们开启了慢 SQL 监控后，如果你发现**「偶尔」会出现一些用时稍长的 SQL**，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小

27、为什么bin log要等一个事务提交了刷盘？

因为中间就提交会导致事务原子性分离。

28、为什么需要两阶段提交？

将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

• prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
• commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘

锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数

29、MySQL 磁盘 I/O 很高，有什么优化的方法？

设置组提交。将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。

将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的。

30、buffer pool 缓存了什么？

数据页：包括表数据、索引数据等。当查询需要访问某个表或索引的数据时，如果数据页已经在Buffer Pool中，则可以直接从内存中读取，避免了频繁的磁盘IO操作，加快数据访问速度。

修改页（Dirty Page）：当对表数据进行修改时，会将数据页加载到Buffer Pool中，并在内存中进行修改，此时该数据页会被标记为"脏页"。之后会通过MySQL的后台线程将脏页刷新回磁盘，确保数据的一致性。

适应性哈希索引（Adaptive Hash Index）：Buffer Pool中还会缓存一些适应性哈希索引，在一定程度上提高一些常访问数据的查询速度。

通过缓存表数据和索引数据，Buffer Pool减少了对磁盘的IO操作，提高了数据库的性能和响应速度。合理调整Buffer Pool的大小能够有效地优化数据库的性能，但需要根据实际情况评估系统的内存和磁盘资源，避免出现内存不足或过度消耗内存等问题。

31、如何管理buffer pool的脏页和空闲页？

为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。

32、如何提高mysql的缓存命中率？

InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：

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

33、为什么mysql没有使用LRU算法？做了什么改进呢？

简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避免下面这两个问题：

• 预读失效；

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

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

怎么解决预读失效而导致缓存命中率降低的问题？

我们不能因为害怕预读失效，而将预读机制去掉，大部分情况下，局部性原理还是成立的。

要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。、

那到底怎么才能避免呢？

MySQL 是这样做的，它改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域 和 young 区域。

划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。

• Buffer Pool 污染；

当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。

那到底怎么才能避免呢？

LRU 链表中 young 区域就是热点数据，只要我们提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题 。（理解：短期内的访问是临时的）