MySQL面试题：你知道事务ACID吗？它的原理是什么？

2022 MySQL面试题

1. 事务有哪些特点？ ACID的实现原理是什么？
2. MySQL索引结构？为什么选择 B+ 号码？每种数据结构都有优点和缺点。
3. MySQL中的SQL插入执行流程是怎样的？
4. 数据库性能瓶颈？如何解决 SQL 速度慢的问题？如何优化？
5. MVCC的实现原理是什么？
6. 进行索引的增、删、改、查过程，B+树操作，节点调整，拆分合并等。
7. 请介绍一下垃圾日志和重做日志以及它们之间的区别？
8. MySQL事务隔离级别读取消合并读提交重复读顺序它们各自的属性是什么？原则？您使用哪一款？
9. 幻读和重复读有什么区别？举个例子？怎么处理呢？
10. 什么是InnoDb锁？
11. 间隙锁原理
12. 如何实现mysql主从同步？瓶颈在哪里？你的 MySQL 架构？如何解决同步老化问题？

ACID原则（事务的特点和原则）

说起MySQL事务，首先要提到它的四大特性（ACID）：

原子性（Atomicity）、一致性（Continuity）、隔离性（Isolation）持久性（耐用）。正是这些功能保证了数据库事务的安全。

原子性：

定义：原子性是指事务是一个不可分割的工作单元，其中要么执行操作，要么不执行操作；如果事务的SQL语句执行失败，那么执行的语句也必须回滚，数据库返回到事务前的状态。

实现原理：从定义中可以看出，原子性有两个方面，要么成功，要么失败。因此，实现原子性的关键是在事务回滚时能够撤消所有成功执行的 SQL 语句。 InnoDB基于回滚日志实现回滚：当事务修改数据库时，InnoDB会生成相应的回滚日志。如果事务提交失败或回滚，导致事务回滚，可以使用回滚日志中的信息将数据回滚到更改前的状态。 Atomic的成功依赖于redolog来保证这批变更的数据成功提交。

后面会对每类日志进行介绍。

持久性（Persistent）

定义：持久性是指一旦事务被提交，其更改应该在数据库中永久存在。它不应受到后续操作或故障的影响。

实现原理：持久化依赖于重做日志来确保数据库更改保存到磁盘。

隔离性（Isolation）

定义：与原子性和持久性重点研究事务本身不同，隔离性研究的是不同事务之间的交互。隔离性是指一个事务内的操作与其他事务是隔离的，并发的事务之间不能相互干扰。

严格隔离对应于事务隔离级别的可串行化（serializable），但出于性能考虑，在实际应用中很少使用可串行化。隔离的目标是并发情况下事务之间互不干扰。 SQL 标准定义了四种类型的隔离级别：未提交读、已提交读、可重复读和可串行化。

实现原理：为了简单起见，我们只考虑最简单的读写操作（暂时忽略锁定读等特殊操作），那么隔离性讨论主要可以分为两个方面：（事务）的作用（另一个事务）写操作上的写操作：更新/删除/插入使用LBCC（基于锁的并发控制）进行隔离； （一个事务的）写操作对（另一个事务的）读操作的影响： MVCC 确保隔离（MVCC 依赖于撤消日志）。

一致性（Constantency）

定义：一致性是指事务执行后数据库的完整性约束不被破坏，事务执行前后数据的状态都是合法的。数据库完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段类型、大小、长度必须满足要求）、外键约束、用户-定义的完整性（例如，在转账之前和之后两个账户余额的总和保持不变）。

连续性可以说是交易追求的最终目标。

实现原理：前面提到的原子性、持久性、隔离性保证了数据库状态的一致性。此外，实现一致性除了数据库级的保证之外还需要应用程序级的保证。为实现一致性而采取的措施如下：

1。保证原子性、持久性和隔离性。这三个特征共同确保了一致性。如果不能保证这些属性，则也无法保证交易的连续性

2。数据库本身提供了保证，例如不允许在整数列中输入字符串值、字符串的长度不能超过列限制等​​3。在应用层面保证，比如转账操作只减少了发送方的余额，而没有增加接收方的余额，那么无论数据库实现的多么完美，都无法保证状态一致性

三大日志

这里只是对MySQL主要的三个日志进行最重要的介绍。如果有需要，以后可以根据反馈写得更精确。文章。 redo log和undo log都属于InnoDB事务日志。首先我们来说一下重做日志的背景。

BufferPool 生成

InnoDB 是 MySQL 存储引擎。数据存储在磁盘上，但如果每次读写数据都需要磁盘IO，效率很低。 InnoDB 为此提供了缓存（Buffer Pool）。缓冲池包含一些数据页在磁盘上的映射，充当访问数据库的缓冲区：从数据库读取数据时，首先从缓冲池中读取。如果缓冲池不在池中，则从磁盘读取并放入缓冲池中；当数据写入数据库时​​，首先写入缓冲池，缓冲池中发生变化的数据会定期刷新到磁盘（这个过程称为脏刷新）。

重做日志

使用缓冲池大大提高了读写数据的效率，但也带来了新的问题：如果MySQL宕机了，缓冲池中变化的数据还没有刷新到磁盘上，这时间长了就会导致数据丢失并且无法保证交易的持续时间。

所以，为了解决这个问题，引入了重做日志：当数据发生改变时，除了改变缓冲池数据外，还将操作记录在重做日志中；当事务提交时，调用fsync接口从磁盘执行重做日志刷写。如果MySQL出现故障，您可以读取重做日志中的数据并在重新启动时恢复数据库。重做日志使用WAL（Write-aheadlogging，预写日志）。所有更改都会先写入日志，然后更新到缓冲池，确保数据不会因MySQL宕机而丢失，从而满足弹性要求。

既然重做日志在执行事务时也需要将日志写入磁盘，为什么它比将缓冲卷中更改的数据直接写入磁盘（即脏数据）要快呢？造成这种情况的原因主要有三个：

1. 脏刷是随机IO，因为每次改变的数据位置是随机的，但写入重做日志是添加操作，属于顺序IO。对于磁盘来说，顺序读写的速度是非常快的。关于。
2. 刷牙是根据数据表（页）。 MySQL 的默认页面大小为 16 KB。一个页面上的一个小改动就需要写一整页；而重做日志只包含真正需要写入的无效部分。 IO显着减少。
3. Redologi还有群组提交机制。多个事务的数据一起呈现并写入磁盘，因此提高了刷新到磁盘的效率。我们这里就不展开展开了。

Binlog

Binlog存储了MySQL数据库中表结构变化和表数据变化的所有二进制日志。它不存储查询操作（例如 select 和 show）的日志。 Binlog日志以事件的形式存储，并且还包含完成语句所花费的时间。开启Binlog有两个最重要的使用场景。

• 主从复制：在主库上启用Binlog功能，以便主库可以将Binlog传输到子库。子库收到Binlog后，主子即可实现数据恢复，实现数据一致性。
• 数据恢复：使用mysqlbinlog等工具恢复数据

Binlog文件存储模式有STATMENT、ROW、MIXED，常用行模式。

redo log 和 binlog 的区别

我们知道 MySQL 也有 binlog（二进制日志），同样可以记录写操作，可以用来恢复数据，但是两者有着本质的区别：

1. 功能不同：使用重做日志进行崩溃恢复，使MySQL宕机不影响持久性； binlog用于时间点恢复，以便服务器可以根据时间点恢复数据。另外，binlog还用于主从复制。 。
2. 级别不同：redo log由InnoDB存储引擎实现，而binlog由MySQL服务器层实现，同时支持InnoDB和其他存储引擎。
3. 内容不同：重做日志是物理日志，内容是基于磁盘页的； binlog的内容是二进制的。根据 binlog_format 参数，这可以基于 SQL 语句、数据本身或两者的混合。
4. 写入时机不同：binlog一般在事务提交时写入，或者一次提交N个事务时写入。写入重做日志的时间是多种多样的。可以每次提交事务时提交，也可以由其他线程事务提交，也可以每次都提交。闪存驱动器只需几秒钟。 （注：重做日志中未提交的事务也可能会刷新到磁盘）

前面提到：当事务提交时，会调用 fsync 来刷新重做日志；这是默认策略，可以通过更改 innodb_flush_log_at_trx_commit 参数来更改。策略，但不保证交易的持续时间。

innodb_flush_log_at_trx_commit 参数

如果设置为 0，此模式速度最快，但安全性较差。 mysqld进程崩溃会导致前一秒的所有事务数据丢失。

设置为 1 时，此模式最安全，但也是最慢的。如果 mysqld 服务或服务器主机崩溃，二进制日志最多可能丢失一条语句或事务。

设置为2时，该模式比0更快、更安全。只有操作系统崩溃或系统关闭时，前一秒的所有交易数据才会丢失。

一般情况下，该参数设置为1

Undo log

Undo Log字面意思是undo操作的日志，指的是把MySQL数据恢复到某种状态。在MySQL数据库中，在事务开始之前，MySQL会将正在更改的记录记录在撤消日志中。如果数据库崩溃或者某个事务需要回滚，MySQL可以使用Undo Log将数据库中的数据恢复到之前的状态。地位。

Undo log是记录变更过程的逻辑日志。例如，当MySQL执行删除操作时，Undo Log会记录添加操作；当MySQL执行插入操作时，Undo Log会记录删除操作；当MySQL执行更新操作时，Undo Log会记录反向更新操作。

以更新操作为例：当事务执行更新时，生成的回滚日志包含更改行的主键（以了解哪些行被更改）、哪些列被更改、使用过程中更改前后的那些列等可恢复数据更新前的状态。

总结

原子性：事务一起成功或一起失败。取决于撤消日志和重做日志。

隔离性：交易是透明的（彼此不可见）。正常选项依赖于 MVCC，更新/删除/插入是使用 LBCC（基于锁的并发控制）实现的。

持久性：一旦进行交易，其更改将永久存储在数据库中。取决于 redolog 的实现。

一致性：事务提交前后的数据完整性保持一致，依靠原子性、隔离性和持久性来确保这一点。

来自后台开发技术，作者阿栗从事健身