面试干货之数据库相关问题(一)

数据库是当下后端面试中最重要的一部分, mysql也是我们在校学生学习应用的最主要的事务型数据库, 笔者将自己在2021年春招以及秋招中的数据库相关总结稍加修改后, 回馈给各位大佬牛油们, 如有错误希望大佬不吝赐教~

数据库事务

数据库的ACID四大特性

A(Automisity)原子性: 一个数据库事务被视为最小分割单元, 一个事务要么全部提交成功, 要么全部提交不成功.

C(Consistency)一致性: 一个数据库事务在执行前后都要保证一致性状态, 所有数据库事务对一个数据读取结果相同.

I(Isolation)隔离性: 一个数据库事务的修改在最终提交之前, 其他事务不可见

D(Durability)持久性: 一个数据库事务一旦提交, 就永远保存在数据库中, 即使崩溃也可以根据日志回溯.

数据库的一致性问题

• 丢失修改

• 读脏数据

  在一个事务中读取了另一个未提交事务中的数据

• 不可重复读

  一个事务多次查询返回的结果不同, 读取到了另一个已提交事务的数据

• 幻影读

  一个事务的两次查询读取到的结果不同, 与不可重复读的差别在于不可重复读针对的是一条数据, 而幻读针对的是一批数据, 比如数据的个数的变化

数据库的隔离级别

• 读未提交
• 读已提交
• 可重复读
• 串行化

四种隔离级别,每种隔离级别实现的原理?

读已提交和可重复读 采用MVCC多版本并发控制解决读写的并发问题

串行化采用表锁方式

MVCC多版本并发控制解决读写线程安全问题

https://www.jianshu.com/p/8845ddca3b23

什么是MVCC

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

MVCC是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 MVCC可以做到在读操作时不需要阻塞写操作, 写操作也可以不阻塞读操作, 从而提高数据库并发读写的性能.

MVCC如何实现

MVCC的实现主要依赖于记录中的三个隐式字段, undo log 和read view:

三个隐式字段:

DB_TRX_ID: 事务ID

*DB_ROLL_PTR: *回滚指针, 指向这条记录的上一个版本

DB_ROW_ID: 隐藏的自增ID

undo log

undo log主要分为两种：

• insert undo log
  代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
• update undo log
  事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

对于MVCC存在作用的是undo log中记载着事务的版本链. 每次查询, 需要选择在版本链中满足read view要求的记录版本返回.

read view

Read View就是事务进行快照读操作的时候生产的读视图,在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID, 并根据可视性算法, 来在记录的版本链中选择当前可读取的记录版本.

RR级如何在RC基础上实现可重复读

RC级的的read view是在每次select时都会重新产生, 而RR级的read view 只在事务中的第一个select语句产生, 也就是以事务的第一个select前的快照产生read view, 也就是保证了每次读取的数据都是这个read view所控制的版本的.

如何实现事务的持久性

数据库通过binlog日志和 redo log日志来保证持久性

数据库事务的提交过程如下:

对于每个事务, mysql会将修改操作记录到redo log buffer中, 在所有操作执行结束后, 通过2pc将redo log和binlog同时写盘, 保证redo log 和 binlog的一致性. 这样想要回滚到某一时刻, 可以通过binlog实现, 而因为宕机而mysql缓冲池中的数据未持久化到磁盘中的问题, 可以通过重做redo log实现.

• 首先对新数据的写盘, 新数据是不会直接进行写入磁盘的, 这样的随机写的磁盘IO会大量占用数据库的时间, 降低数据库的存储效率. mysql采用了缓冲区, 每次写入将数据写入到缓冲区中, 而采用后台线程将数据异步的写回磁盘.
• 其次是数据回滚的流程, 首先找到一个全量备份的版本, 然后采用binlog从备份的时间点开始进行binlog的重放.
• 宕机恢复的流程, 将磁盘中的 redo log执行一次, 因为redo log是物理日志, 因此对于一个写入指令的操作是幂等的, 因此不会因重复执行而造成不一致.

• 既然binlog可以恢复数据 redo log存在的意义

  1. redo log是物理日志 相比于逻辑日志binlog 恢复起来更快
  2. 宕机恢复时, binlog日志对于一条语句的恢复不是幂等的, 而redolog是幂等的, 所以恢复只需要将磁盘中的redolog全量执行一遍就行了.

• redolog binlog 2pc的具体流程

  将事务的commit分为prepare和commit两个阶段：
  1、prepare阶段：redo持久化到磁盘（redo group commit），并将回滚段置为prepared状态，此时binlog不做操作。

  

  2、commit阶段：innodb释放锁，释放回滚段，设置提交状态，binlog持久化到磁盘，然后存储引擎层提交

2PC保证了事务在引擎层（redo）和server层（binlog）之间的原子性。其中binlog作为XA协调器，即以binlog是否成功写入磁盘作为事务提交的标志（innodb commit标志并不是事务成功与否的标志）。所以在崩溃恢复中也是以redolog中的xid与binlog中的xid进行比较，如果xid在binlog中则提交，否则回滚。

数据库的锁

数据库中锁可以分为lock和latch，lock的对象是事务， 一般常用的模式为行锁，表锁，意向锁， 也就是我们常说的锁， 而latch就是用于真正锁数据结构的锁，为了防止内存数据结构临界资源被同时访问。

意向锁

Innodb的锁是支持多粒度的锁同时存在的， 为了多粒度锁的同时存在， 出现了意向锁，意向锁的含义是：若希望在更低粒度上加锁，则需要在该粒度上加意向锁（比如希望在某页的行上加X锁， 那么首先需要在表，页上加IX锁，最后在行上加X锁），在加锁任何一个步骤导致阻塞都会导致整个过程阻塞。

外键与锁

外键一般用于数据库的完整性约束的检查， 每次插入数据含有外键时，需要到父表中去进行一致性的锁定读即当前读，这样才能保证一致性。

行锁

行锁包括三种：

1. record lock： 单行记录的记录锁
2. gap lock： 一个间隙内的间隙锁
3. next-key lock：record lock + gap lock， 是结合两种锁的一种锁算法， 锁定一个范围并锁定本身。

在RR级别下为了解决幻读，是采用了MVCC+next-key lock方式的。

可以分为几种情况来分类分析；

• 主键索引
  • 等值查询且数据存在， 数据行加 record lock
  • 等值查询且数据不存在，查询所在间隙加gap lock
  • 范围查询， 范围内的所有间隙加gap lock
• 唯一索引
  • 与主键基本相同， 不同在于在in shared mode且覆盖索引查询的时候不需要锁主键其他时候均需要锁主键
• 普通索引
  • 等值查询， 在前后间隙加gap lock， 并在已存在记录加record lock

在Mysql中的锁可以分为分享锁/读锁（Shared Locks）、排他锁/写锁（Exclusive Locks） 、间隙锁、行锁（Record Locks）、表锁。

共享锁是针对同一份数据，多个读操作可以同时进行，简单来说即读加锁，不能写并且可并行读；排他锁针对写操作，假如当前写操作没有完成，那么它会阻断其它的写锁和读锁，即写加锁，其它读写都阻塞 。

行锁和表锁，是从锁的粒度上进行划分的，行锁锁定当前数据行，锁的粒度小，加锁慢，发生锁冲突的概率小，并发度高，行锁也是MyISAM和InnoDB的区别之一，InnoDB支持行锁并且支持事务 。

间隙锁则分为两种：Gap Locks和Next-Key Locks。Gap Locks会锁住两个索引之间的区间，比如select * from User where id>3 and id<5 for update，就会在区间（3，5）之间加上Gap Locks。

Next-Key Locks是Gap Locks+Record Locks形成闭区间锁select * from User where id>=3 and id=<5 for update，就会在区间[3,5]之间加上Next-Key Locks。

乐观锁与悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性

乐观锁的实现

• CAS

  CAS的全称是Compare-and-Swap，也就是比较并交换，它包含了三个参数：V，A，B，V表示要读写的内存位置，A表示旧的预期值，B表示新值

  具体的机制是，当执行CAS指令的时候，只有当V的值等于预期值A时，才会把V的值改为B，如果V和A不同，有可能是其他的线程修改了，这个时候，执行CAS的线程就会不断的循环重试，直到能成功更新为止。

• 版本号

  版本号机制是数据库更新操作里非常实用的技巧，其实原理很简单，就是获取数据的时候会拿一个能对应版本的字段，然后更新的时候判断这个字段是否跟之前拿的值是否一致，一致的话证明数据没有被别人更新过，这时就可以正常实现更新操作。

Mysql 日志

binlog

binlog是 mysql的逻辑日志，并且由 Server层进行记录，使用任何存储引擎的 mysql数据库都会记录 binlog日志。 可以简单理解为记录的就是sql语句 。

binlog使用场景

在实际应用中， binlog的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

1. 主从复制 ：在 Master端开启 binlog，然后将 binlog发送到各个 Slave端， Slave端重放 binlog从而达到主从数据一致。
2. 数据恢复 ：通过使用 mysqlbinlog工具来恢复数据。

Binlog 日志格式

针对不同的使用场景，Binlog 也提供了可定制化的服务，提供了三种模式来提供不同详细程度的日志内容。

• Statement 模式：基于 SQL 语句的复制(statement-based replication-SBR)
• Row 模式：基于行的复制(row-based replication-RBR)
• Mixed 模式：混合模式复制(mixed-based replication-MBR)

Statement 模式

保存每一条修改数据的SQL。

该模式只保存一条普通的SQL语句，不涉及到执行的上下文信息。

因为每台 MySQL 数据库的本地环境可能不一样，那么对于依赖到本地环境的函数或者上下文处理的逻辑 SQL 去处理的时候可能同样的语句在不同的机器上执行出来的效果不一致。

比如像 sleep()函数，last_insert_id()函数，等等，这些都跟特定时间的本地环境有关。

Row 模式

MySQL V5.1.5 版本开始支持Row模式的 Binlog，它与 Statement 模式的区别在于它不保存具体的 SQL 语句，而是记录具体被修改的信息。

比如一条 update 语句更新10条数据，如果是 Statement 模式那就保存一条 SQL 就够，但是 Row 模式会保存每一行分别更新了什么，有10条数据。

Row 模式的优缺点就很明显了。保存每一个更改的详细信息必然会带来存储空间的快速膨胀，换来的是事件操作的详细记录。所以要求越高代价越高。

Mixed 模式

Mixed 模式即以上两种模式的综合体。既然上面两种模式分别走了极简和一丝不苟的极端，那是否可以区分使用场景的情况下将这两种模式综合起来呢？

在 Mixed 模式中，一般的更新语句使用 Statement 模式来保存 Binlog，但是遇到一些函数操作，可能会影响数据准确性的操作则使用 Row 模式来保存。这种方式需要根据每一条具体的 SQL 语句来区分选择哪种模式。

MySQL 从 V5.1.8 开始提供 Mixed 模式，V5.7.7 之前的版本默认是Statement 模式，之后默认使用Row模式， 但是在 8.0 以上版本已经默认使用 Mixed 模式了。

redo log

作用

redo log用来实现事务的持久性, 由两部分组成, 一是内存中的重做日志缓冲, 二是磁盘中的重做日志文件.

因为日志缓冲是保存在内存中, 是易失的, 若想保证每个操作的持久性, 需要在每次commit后将缓冲区的redo log写入到os buffer 并通过fsync系统调用刷盘到磁盘中, 而fsync的操作是限制数据库性能的重要因素. mysql提供了 innodb_flush_log_at_trx_commit 参数来配置刷盘时机.

与binlog的区别

• 产生层不同. binlog是在server层产生的, 而redolog是在存储引擎层产生的.
• 记录形式不同. binlog是一种逻辑日志, 记录的是sql语句. 而redo log是一种物理日志. 记录的是数据库每个页的修改.
• 两种日志写入磁盘的时间点不同. binlog只在事务提交后进行一次性写入. 而redolog 在事务进行中不断的写入, 而不是随日志提交写入的.

undo log

undo log 有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。