数据库相关.md · aaahxz/For the interview

### 1. 数据库三大范式？

第一范式（1NF）：

1、数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。

第二范式（2NF）：

满足1NF后要求表中的所有列，每一行的数据只能与其中一列相关，即一行数据只做一件事。

第三范式（3NF）：

满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。

### 2. 数据库五大约束？

1.主键约束（Primay Key Coustraint） 唯一性，非空性；

2.唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个；

3.默认约束 (Default Counstraint) 该数据的默认值；

4.外键约束 (Foreign Key Counstraint) 需要建立两表间的关系；

5.非空约束（Not Null Counstraint）:设置非空约束，该字段不能为空。

**五大约束的语法示例：**

1. 添加主键约束

Alter table 表名 add Constraint 主键名 primary key(字段)

2. 添加唯一约束

Alter table 表名 add Constraint 约束名 unique(字段)

3. 添加默认约束

Alter table 表名 add Constraint 约束名 default（默认内容） for 字段名

4. 添加检查约束

Alter table 表名 add Constraint 约束名 check (字段表达)

5. 添加外键约束

Alter table 表名 add Constraint 约束名 foreign key(字段) references 表名（字段名）

### 3. 范式优化、反范式优化？

对于不满足数据库第二和第三范式的，会存在如下的问题：

- 数据冗余(想修改一个属性,就要更新多行数据)
- 插入异常(想要插入数据,结构因为表设计的问题,导致不能成功插入)
- 删除异常(只想删除其中的某些数据 ,结果把不该删的也删了)
- 更新异常(想更新一条数据,结果工作量大,还容易出错)

**范式优化：**

1、减少数据的冗余

2、尽量避免在数据插入、删除和更新异常

范式的利弊：

优点：范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。

缺点：按照范式的规范设计出来的表，等级越高的范式设计出来的表越多。如第一范式可能设计出来的表可能只有一张表而已，再按照第二范式去设计这张表时就可能出来两张或更多张表，如果再按第三范式或更高的范式去设计这张表会出现更多比第二范式多的表。表的数量越多，当我们去查询一些数据，必然要去多表中去查询数据，这样查询的时间要比在一张表中查询中所用的时间要高很多。也就是说我们所用的范式越高，对数据操作的性能越低。所以我们在利用范式设计表的时候，要根据具体的需求再去权衡是否使用更高范式去设计表。

**反范式优化：**

1、 增加冗余、减少连表查询（空间换时间的思路）

优点：查询时可以减少表的关联；可以更好的进行索引优化；

缺点：存在数据冗余以及数据维护异常；对数据的修改需要更多的成本

### 4. 冷热数据分离？

热数据：是需要被计算节点频繁访问的在线类数据。
		冷数据：是对于离线类不经常访问的数据，比如企业备份数据、业务与操作日志数据、话单与统计数据。

**将经常使用查询的数据放在生产库中，而查询不多的历史数据就放在历史库中，这样既可以保证数据的完整，也可以减轻生产库的压力**。

### 5. 一条SQL是如何执行的？

![img](https://upload-images.jianshu.io/upload_images/16367972-e8d13fec9d851e5d.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)

Server层有连接器、查询缓存、分析器、优化器、最后是执行器，覆盖大多数MySQL的核心功能：以及所有的内置函数，所有的跨存储引擎的功能都在这一层实现，比如存储过程、触发器、师徒等等。

1. **连接器**
   连接器负责跟客户端建立连接、获取权限、维持和管理连接。

2. **查询缓存**

![img](https://upload-images.jianshu.io/upload_images/16367972-df6dda8a7902d593.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)

大多数情况下建议不要使用缓存、因为频繁的更新对表的资源占用十分大你，MySQL8.0直接将缓存删除了。

3. **分析器**

词法分析（识别关键字，操作，表名，列名）
   语法分析 (判断是否符合语法）

4. **优化器**

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
   优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

5. **执行器**

开始执行的时候，要先判断一下你对这个表 table 有没有执行查询的权限。如果没有，就会返回没有权限的错误

总结：

1. 执行语句前要先连接数据库，这是连接器的工作。
2. 在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。
3. 分析器会通过词法和语法解析知道这是一条更新语句。
4. 优化器决定要使用 ID 这个索引。
5. 执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：**redo log 和binlog。**

**redo log？**

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时再写账本。简单来讲，就是当有一条记录需要更新的时候，InnoDB存储引擎会先把记录写道redo log中，也就是所谓的粉笔板，InnoDB会在适当的时候将记录更新到磁盘里，这个时间往往在系统比较空闲的时候去做

**binlog？**

MySQL 整体来看，其实就有两块：一块是 Server 层,它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关事宜。

作用？

- 恢复数据库（全量备份 + 备份时间点后的binglog)
- 扩容搭建备库（全量备份 + 备份时间点后的binglog)
- 同步数据

粉板 redo log 是 InnoDB 引擎特有的日志，而server层也有自己的日志，称为 binlog（归档日志）。
		redo log和binlog区别？

![img](https://upload-images.jianshu.io/upload_images/16367972-68833e8c113911cc.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)

### 6. 索引条件下推（index condition pushdown)

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
 例如有联合索引(a,b), 查询条件是a=x and b=y and c=z, 在Mysql5.6前，先在联合索引中找到a=x的所有记录，然后回表在主键索引中匹配b,c条件。利用索引下推则可以直接在索引(a,b）中匹配a,b 条件，减少回表的记录数。

### 7. MVCC？

网上看到大量的文章讲到MVCC都是说给没一行增加两个隐藏的字段分别表示行的创建时间以及过期时间，它们存储的并不是时间，而是事务版本号。

事实上，这种说法并不准确，严格的来讲，InnoDB会给数据库中的每一行增加三个字段，它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

**MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE会对所有读取到的行都加锁**。

#### 7.1 增删查改

在InnoDB中，给每行增加两个隐藏字段来实现MVCC，一个用来记录数据行的创建时间，另一个用来记录行的过期时间（删除时间）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。

于是乎，默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：

- SELECT
  - 读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
- INSERT
  - 将当前事务的版本号保存至行的创建版本号
- UPDATE
  - 新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
- DELETE
  - 将当前事务的版本号保存至行的删除版本号

#### 7.2 快照读和当前读

快照读：读取的是快照版本，也就是历史版本

当前读：读取的是最新版本

普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE是当前读

#### 7.3 锁定读

在一个事务中，标准的SELECT语句是不会加锁，但是有两种情况例外。SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE。

SELECT ... LOCK IN SHARE MODE

给记录假设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交

SELECT ... FOR UPDATE

给索引记录加锁，这种情况下跟UPDATE的加锁情况是一样的

#### 7.4 一致性非锁定读

[consistent read](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_consistent_read) （一致性读），InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照；如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

### 8. RR隔离级别的运行原理？

1、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题

综上所述，默认隔离级别的实现依赖于MVCC和锁，再具体一点是一致性读和锁。

aaahxz/For the interview

简介

发行版

贡献者

近期动态

aaahxz/For the interview .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

aaahxz/For the interview