数据库面经

数据库范式

数据库范式有 3 种：

• 1NF(第一范式)：属性不可再分。
• 2NF(第二范式)：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。
• 3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。

1NF(第一范式)

属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求，也就是说关系型数据库中创建的表一定满足第一范式。

2NF(第二范式)

2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。如下图所示，展示了第一范式到第二范式的过渡。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。

3NF(第三范式)

3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求。

也就是说一个参数不能由另一个参数唯一确定，如果有就要分一个表出来存。

不推荐用外键、级联、存储过程

对于外键和级联，阿里巴巴开发手册这样说到：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明: 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度

为什么不要用外键呢？大部分人可能会这样回答：

1. 增加了复杂性： a. 每次做 DELETE 或者 UPDATE 都必须考虑外键约束，会导致开发的时候很痛苦, 测试数据极为不方便; b. 外键的主从关系是定的，假如那天需求有变化，数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。
2. 增加了额外工作：数据库需要增加维护外键的工作，比如当我们做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，保证数据的的一致性和正确性，这样会不得不消耗数据库资源。如果在应用层面去维护的话，可以减小数据库压力；
3. 对分库分表不友好：因为分库分表下外键是无法生效的。

阿里巴巴开发手册也明确提到禁止使用存储过程，这些操作都应该放到应用层去。与外键相似，主要是因为难以调试和扩展，而且没有移植性，还会消耗数据库资源。

MySQL基础

相关基础内容，不涉及设计之类的。

类型

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

CHAR(M) ，无论是字母、数字还是中文，每个都只占用一个字符，避免ENUM类型。

VARCHAR(100)和 VARCHAR(10) 能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，但VARCHAR(100) 会消耗更多的内存，这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。

不推荐用TEXT和BLOB，不能直接创建索引，需要指定前缀长度。

MySQL存储时间是个系统化的问题，这里只写类型上的：DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

• DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Bool类型如何表示：unsigned tinyint(1)

SQL执行过程

先看MySQL结构：

简单来说 MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
• 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。

连接器

连接器负责建立连接，以及这个过程中的认证一类的，建立连接后就不再进行身份认证。后续只要这个连接不断开，即使管理员修改了该用户的权限（指的是“是否允许连接”一类的权限），该用户也是不受影响的。

查询缓存

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 SQL 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询语句，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 8.0 版本后删除了缓存的功能，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。

分析器

词法分析、语法分析，如果错误就返回。

优化器

优化执行方案，比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器

首先检查是否有这个表的对应权限，如果有，交给存储引擎执行。

在分析器也会检查权限，执行器的地方再检查可以理解为：

sql执行过程中可能会有触发器这种在运行时才能确定的过程，分析器工作结束后的precheck是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查。

LOG相关

MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志）

例如：update tb_student A set A.age='19' where A.name=' 张三 ';

• 先查询到张三这一条数据，不会走查询缓存，因为更新语句会导致与该表相关的查询缓存失效。
• 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
• 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
• 更新完成。

注意细节：

1. 存储引擎把修改存在内存
2. 存储引擎记录redo为prepare状态
3. 执行器记录binlog
4. 存储引擎实际写入文件，提交redo为提交状态

事务

数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障，即所谓ACID。

并发事务

多个事务并发运行，经常会操作相同的数据来完成各自的任务，但可能会导致以下的问题。

• 脏读：一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。
• 丢失修改：在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
• 不可重复读：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读：它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

Spring如何管理事务

首先，Spring对事务的管理前提是数据库支持事务机制。

使用上，优先使用声明式的事务管理，即使用 @Transactional注解进行事务管理，例如这样就可以把这个方法作为一个事务管理：

注解内容是管理事务的“传播性”，即简单理解就是如果在事务内开了新事务，内部的事务失败了，要不要回滚外部的事务。

• 第一种是Required，内外视为一个事务，发生任何错误，全部回滚。
• 第二种是REQUIRES_NEW，内部事务作为独立事务，这个事务外发生回滚，不回滚内部事务，但如果内部事务回滚，按照外部的机制会认为异常，外部会回滚。
• 第三种是NESTED，作为“嵌套事务”，个人觉得这种似乎更接近直觉，内部错误不影响外面，外部错误内部跟着回滚。主要是因为事务这个东西，本来就是有可能成功有可能失败的，外部事务引用内部事务的时候，内部事务失败不应该被视为一个“发生错误的操作”，内部只是外部事务的一个步骤而已。但这样是不符合业务逻辑的。
• 第四种是MANDATORY，要求当前事务必须作为子事务，行为与Required一致。

@Transactional(propagation = Propagation.REQUIRED)
public void aMethod {
  //do something
  B b = new B();
  C c = new C();
  b.bMethod();
  c.cMethod();
}

实现上，

Spring 并不直接管理事务，而是提供了多种事务管理器 。

Spring 事务管理器的接口是：PlatformTransactionManager 。，事务管理相关最重要的 3 个接口如下：

• ​PlatformTransactionManager：（平台）事务管理器，Spring 事务策略的核心。
• ​TransactionDefinition：事务定义信息(事务隔离级别、传播行为、超时、只读、回滚规则)。
• ​TransactionStatus：事务运行状态。

通过这个接口，Spring 为各个平台如：JDBC(DataSourceTransactionManager​)、Hibernate(HibernateTransactionManager​)、JPA(JpaTransactionManager)等都提供了对应的事务管理器，但是具体的实现就是各个平台自己的事情了。

另外，如果不加事务注解，默认行为是每一句SQL作为单独事务，如果业务需要多SQL但你没有设置为一个事务，那么就可能出现事实上的重复读问题。对于只有读取数据查询的事务，可以指定事务类型为 readonly，即只读事务。只读事务不涉及数据的修改，数据库会提供一些优化手段，适合用在有多条数据库查询操作的方法中。

MVCC 等可靠性/隔离相关

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED 读未提交	√	√	√
READ-COMMITTED 读已提交	×	√	√
REPEATABLE-READ 不可重复读	×	×	√
SERIALIZABLE 序列化	×	×	×

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MVCC

中文名：多版本并发控制。

锁

首先分为表锁和行锁。

锁控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过读写锁来实现并发控制。

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

什么是意向锁？

事务有意向对表中的某些记录加锁，加共享锁前必须先取得该表的意向锁。如果需要用到表锁的话，如何判断表中的记录没有行锁呢？

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

确保了只要有行锁，表意向锁就被拿着的。意向锁之间都不互斥，但意向锁和表锁可能互斥。

还有个自增锁。

---

著作权归JavaGuide(javaguide.cn)所有 基于MIT协议 原文链接：https://javaguide.cn/system-design/framework/spring/spring-knowledge-and-questions-summary.html

‍