MySQL面试题:事务 ACID 知道么,原理是什么?

2023-03-0717:38:53数据库教程Comments924 views字数 4374阅读模式

2022 MySQL 面试题

  1. 事务的特性?ACID实现原理是什么
  2. MySQL索引的结构?为什么选用B+数?每种数据结构区别优缺点。
  3. 一个SQL插入在MySQL中执行过程是怎样的?
  4. 数据库性能瓶颈?如何排查慢SQL?如何优化?
  5. MVCC的实现原理是什么?
  6. 在索引上进行一次增删改查的流程,B+树的操作,节点调整 分裂合并等瞎扯
  7. 讲讲bin log和redo log,以及他们的区别?
  8. MySQL事务隔离级别 Read Uncommit Read Commit Repeatable Read Serialize,各自特点?原理?你们使用哪种?
  9. 幻读和可重复读有什么区别?举例子说明?怎么解决?
  10. InnoDb 的锁有哪些?
  11. Gap lock 原理
  12. mysql主从同步如何实现?瓶颈在哪里?你们的MySQL架构?如何解决同步时效问题?

ACID 原理(事务的特性及原理)

说到MySQL事务,首先要提他的四大特性(ACID):文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

原子性(Atomicity)、一致性(Consistent)、隔离性(Isolation)以及持久性(Durable)。正是这些特性,才保证了数据库事务的安全性。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

原子性 (Atomicity):

定义:原子性是指一个事务是一个不可分割的工作单位,其中的操作要么都做,要么都不做;如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

实现原理:从定义可以看出原子性具有要么成功要么失败两方面。所以实现原子性的关键,是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚,靠的是undo log:当事务对数据库进行修改时,InnoDB会生成对应的undo log。如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。原子性的成功靠的是redolog来实现,保证这批变更的数据一定提交成功。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

关于每一种log会在后文给出介绍。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

持久性(Durable)

定义:持久性是指事务一旦提交,它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

实现原理:持久性依赖于redolog实现,保证对于数据库的改变可以持久化到磁盘中。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

隔离性(Isolation)

定义:与原子性、持久性侧重于研究事务本身不同,隔离性研究的是不同事务之间的相互影响。隔离性是指,事务内部的操作与其他事务是隔离的,并发执行的各个事务之间不能互相干扰。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

严格的隔离性,对应了事务隔离级别中的Serializable (可串行化),但实际应用中出于性能方面的考虑很少会使用可串行化。隔离性追求的是并发情形下事务之间互不干扰。SQL标准定义了4类隔离级别:Read Uncommitted(读取未提交内容)、Read Committed(读取提交内容)、Repeatable Read(可重读)、Serializable(可串行化)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

实现原理:简单起见,我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作),那么隔离性的探讨,主要可以分为两个方面:(一个事务)写操作对(另一个事务)写操作的影响:更新/删除/插入会用LBCC(基于锁的并发控制)保证隔离性;(一个事务)写操作对(另一个事务)读操作的影响:MVCC保证隔离性(MVCC依赖于undo log)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

一致性(Consistent)

定义:一致性是指事务执行结束后,数据库的完整性约束没有被破坏,事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于:实体完整性(如行的主键存在且唯一)、列完整性(如字段的类型、大小、长度要符合要求)、外键约束、用户自定义完整性(如转账前后,两个账户余额的和应该不变)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

可以说,一致性是事务追求的最终目标。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

实现原理:前面提到的原子性、持久性和隔离性,都是为了保证数据库状态的一致性。此外,除了数据库层面的保障,一致性的实现也需要应用层面进行保障。实现一致性的措施包括:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

1.保证原子性、持久性和隔离性,这三种特性共同保证了一致性,如果这些特性无法保证,事务的一致性也无法保证文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

2.数据库本身提供保障,例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

3.应用层面进行保障,例如如果转账操作只扣除转账者的余额,而没有增加接收者的余额,无论数据库实现的多么完美,也无法保证状态的一致文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

三大log

这里对于MySQL三大 log 只做最精华的介绍,根据反馈如有需要可以后期写更详细的文章。redo log和undo log都属于InnoDB的事务日志,下面先聊一下redo log存在的背景。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

BufferPool的产生

InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,但如果每次读写数据都需要磁盘IO,效率会很低。为此,InnoDB提供了缓存(Buffer Pool),Buffer Pool中包含了磁盘中部分数据页的映射,作为访问数据库的缓冲:当从数据库读取数据时,会首先从Buffer Pool中读取,如果Buffer Pool中没有,则从磁盘读取后放入Buffer Pool;当向数据库写入数据时,会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中(这一过程称为刷脏)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

MySQL面试题:事务 ACID 知道么,原理是什么?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

redo log

Buffer Pool的使用大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

于是,redo log被引入来解决这个问题:当数据修改时,除了修改Buffer Pool中的数据,还会在redo log记录这次操作;当事务提交时,会调用fsync接口对redo log进行刷盘。如果MySQL宕机,重启时可以读取redo log中的数据,对数据库进行恢复。redo log采用的是WAL(Write-ahead logging,预写式日志),所有修改先写入日志,再更新到Buffer Pool,保证了数据不会因MySQL宕机而丢失,从而满足了持久性要求。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

既然redo log也需要在事务提交时将日志写入磁盘,为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢?主要有以下三方面的原因:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

  1. 刷脏是随机IO,因为每次修改的数据位置随机,但写redo log是追加操作,属于顺序IO,对于磁盘来说顺序读写的速度是非常快的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html
  2. 刷脏是以数据页(Page)为单位的,MySQL默认页大小是16KB,一个Page上一个小修改都要整页写入;而redo log中只包含真正需要写入的部分,无效IO大大减少。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html
  3. redolog还有组提交的机制,多个事务的数据会一起提交写入磁盘,因此会提高刷入磁盘的效率,这里我们就不展开了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

Binlog

Binlog记录所有MySQL数据库表结构变更以及表数据修改的二进制日志,不会记录select和show这类查询操作的日志。Binlog日志是以事件形式记录,还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

  • 主从复制:在主库中开启Binlog功能,这样主库就可以把Binlog传递给从库,从库拿到Binlog后实现数据恢复达到主从数据一致性。
  • 数据恢复:通过mysqlbinlog等工具来恢复数据

Binlog文件记录模式有STATEMENT、ROW和MIXED三种,通常使用 row 模式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

redo log 和 binlog 区别

我们知道,在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复,但二者是有着根本的不同的:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

  1. 作用不同:redo log是用于crash recovery的,保证MySQL宕机也不会影响持久性;binlog是用于point-in-time recovery的,保证服务器可以基于时间点恢复数据,此外binlog还用于主从复制。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html
  2. 层次不同:redo log是InnoDB存储引擎实现的,而binlog是MySQL的服务器层实现的,同时支持InnoDB和其他存储引擎。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html
  3. 内容不同:redo log是物理日志,内容基于磁盘的Page;binlog的内容是二进制的,根据binlog_format参数的不同,可能基于sql语句、基于数据本身或者二者的混合。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html
  4. 写入时机不同:binlog在事务通常提交时写入或者N个事务提交一次,redo log的写入时机相对多元,可能每次事务提交时,也可能被其他线程事务提交,还可能每秒钟刷盘。(注意:redolog未提交的事务也可能刷入磁盘)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

前面曾提到:当事务提交时会调用fsync对redo log进行刷盘;这是默认情况下的策略,修改innodb_flush_log_at_trx_commit参数可以改变该策略,但事务的持久性将无法保证。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

innodb_flush_log_at_trx_commit参数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

当设置为0,该模式速度最快,但不太安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

当设置为1,该模式是最安全的,但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

当设置为2,该模式速度较快,也比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

通常这个参数设置为1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

undo log

Undo Log的字面意思就是撤销操作的日志,指的是使MySQL中的数据回到某个状态。在MySQL数据库中,事务开始之前,MySQL会将待修改的记录保存到Undo Log中,如果数据库崩溃或者事务需要回滚时,MySQL可以通过利用Undo Log日志,将数据库中的数据回滚到之前的状态。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

Undo Log是一种 逻辑日志, 记录的是一个变化过程。比如,MySQL执行一个delete操作,Undo Log就会记录一个insert操作;MySQL执行一个insert操作,Undo Log就会记录一个delete操作;MySQL执行一个update操作,Undo Log就会记录一个相反的update操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

以update操作为例:当事务执行update时,其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息,回滚时便可以使用这些信息将数据还原到update之前的状态。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

总结

原子性:事务一起成功或者失败。依赖 undo log 和 redo log。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

隔离性:事务之间是透明的(互相不可见)。普通 select 依赖 MVCC 实现,更新/删除/插入会用LBCC(基于锁的并发控制)实现。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

持久性:一旦事务提交,则其所做的修改就会永久保存到数据库中。依赖redolog实现。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

一致性:事务的执行的前后数据的完整性保持一致,依赖原子性、隔离性、持久性一起保证。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

来源于后端开发技术 ,作者阿笠在健身文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/31291.html
  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/31291.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定