数据库知识点

八股文系列文章目录

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

数据库知识点

---

1、数据库是什么？种类有什么？

数据库(DataBase 简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。

优点：数据可永久保存且数据安全性高；使用 SQL 语句，查询方便效率高；便于数据管理、智能化数据分析。
缺点：数据库移植不方便；不支持集群；不擅长业务逻辑的处理。

类型	定义	优点	缺点	例子
关系型数据库	建立在关系模型基础上，由多张能互相连接的表组成的数据库	使用表结构，格式一致，易于维护；使用 SQL 语句，可用于复杂查询；数据存储在磁盘中，安全性高	读写性能比较差；建立在关系模型上，不可避免空间浪费；固定的表结构，灵活度较低	MySQL，Microsoft SQL Server，Oracle，PostgreSQL 等
非关系型数据库	数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定	存储数据的格式可以是 key-value 、文档、图片等形式，应用场景更广泛；可轻松进行海量数据的维护和处理；具有可扩展、高并发、高稳定性、成本低的优势；可以实现数据的分布式处理	不提供 SQL 支持；无事务处理，无法保证数据的完整性和安全性；功能没有关系型数据库完善	Neo4j，Redis，MongoDB 等

---

2、数据库三大范式

数据库范式是设计数据库时，需要遵循的一些规范。各种范式是条件递增的联系，越高的范式数据库冗余越小。常用的数据库三大范式为：
第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性。第一范式要求数据库中的表都是二维表。
第二范式（2NF）：在第一范式的基础上，一个表必须有一个主键，非主键列 完全依赖 于主键，而不能是依赖于主键的一部分。
第三范式（3NF）：在第二范式的基础上，非主键列只依赖（直接依赖）于主键，不依赖于其他非主键。

---

3、索引

索引的数据结构主要有 B 树索引、Hash 索引 和 位图索引 三种。

在最频繁使用的、用以缩小查询范围的字段,需要排序的字段上建立索引。 不宜： 1）对于查询中很少涉及的列或者重复值比较多的列 2）对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等。

3.1 B 树索引

B 树索引，又称 平衡树索引，是 MySQL 数据库中使用最频繁的索引类型，MySQL、Oracle 和 SQL Server 数据库默认的都是 B 树索引（实际是用 B+ 树实现的，因为在查看表索引时，MySQL 一律打印 BTREE，所以简称为 B 树索引）。

一棵 m 阶 B-Tree 的特性
每个结点最多 m 个子结点；
除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点；
所有的叶子结点都位于同一层；
每个结点都包含 k 个元素（关键字），这里 m/2≤k<m，这里 m/2 向下取整；
每个节点中的元素（关键字）从小到大排列；
每个元素子左结点的值，都小于或等于该元素，右结点的值都大于或等于该元素。

B+ Tree的特性
所有的非叶子结点只存储 关键字信息；
所有具体数据都存在叶子结点中；
所有的叶子结点中包含了全部元素的信息；
所有叶子节点之间都有一个链指针。

使用B+ 树的好处：
1.由于 B+ 树的内部结点只存放键，不存放值，因此，一次读取，可以在同一内存页中获取更多的键，有利于更快地缩小查找范围。
2.B+ 树的叶结点由一条链相连，因此当需要进行一次 全数据遍历 的时候，B+ 树只需要使用 O(logN) 时间找到最小结点，然后通过链进行 O(N) 的顺序遍历即可；或者，在找 大于某个关键字或者小于某个关键字的数据 的时候，B+ 树只需要找到该关键字然后沿着链表遍历即可。

3.2 Hash索引

哈希索引采用一定的 哈希算法（常见哈希算法有 直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置，如果发生 Hash 碰撞（两个不同关键字的 Hash 值相同），则在对应 Hash 键下以 链表形式 存储。

检索时不需要类似 B+ 树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，平均检索时间为 O(1)。

---

Hash 索引和 B+ 树索引有以下几点显见的区别：

• Hash 索引进行等值查询更快（一般情况下），但是却无法进行范围查询；
• Hash 索引不支持使用索引进行排序；
• Hash 索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为 Hash 函数的不可预测；
• Hash 索引任何时候都避免不了回表查询数据，而 B+ 树在符合某些条件（聚簇索引，覆盖索引等）的时候可以只通过索引完成查询；
• Hash 索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生 Hash 碰撞，此时效率可能极差；而 B+ 树的查询效率比较稳定，对于所有的查询都是从根结点到叶子结点，且树的高度较低。

既然Hash比B+树更快，为什么MySQL用B+树来存储索引呢？
MySQL中存储索引用到的数据结构是B+树，B+树的查询时间跟树的高度有关，是log(n)，如果用hash存储，那么查询时间是O(1)。

采用Hash来存储确实要更快，但是采用B+树来存储索引的原因主要有以下两点：

一、从内存角度上说，数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。

二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。

3.3 位图索引

B 树索引擅长于处理包含许多不同值的列，但是在处理基数较小的列时会变得很难使用。如果用户查询的列的基数非常的小， 即只有几个固定值，如性别、婚姻状况、行政区等等，要么不使用索引，查询时一行行扫描所有记录，要么考虑建立位图索引。

位图索引为存储在某列中的每个值生成一个位图。例如针对表中婚姻状况这一列，生成的位图索引大致如下所示：

对于婚姻状况这一列，索引包含 3 个位图，即生成有 3 个向量，分别属于每一个取值，每个位图为每一个人（行）都分配了 0/1 值（每一行有且仅有一个 1 ），未婚为 110000……，已婚为 000111……，离婚为 001000……
当进行数据查找时，只要查找相关位图中的所有 1 值即可（可根据查询需求进行与、或运算）。
除了上述提及的，位图索引适合只有几个固定值的列，还需注意 ，位图索引适合静态数据，而不适合索引频繁更新的列。

3.4 最左前缀匹配原则

在 MySQL 建立 联合索引（多列索引） 时会遵守最左前缀匹配原则，即 最左优先，在检索数据时从联合索引的最左边开始匹配。例如有一个 3 列索引（a,b,c），则已经对（a）、（a,b）、（a,b,c）上建立了索引。所以在创建 多列索引时，要根据业务需求，where 子句中 使用最频繁 的一列放在最左边。

根据最左前缀匹配原则，MySQL 会一直向右匹配直到遇到 范围查询（>、<、between、like）就停止匹配，比如采用查询条件 where a = 1 and b = 2 and c > 3 and d = 4 时，如果建立（a,b,c,d）顺序的索引，d 是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，并且 where 子句中 a、b、d 的顺序可以任意调整。

如果建立的索引顺序是 （a,b） ，那么根据最左前缀匹配原则，直接采用查询条件 where b = 1 是无法利用到索引的。

3.5 MyISAM和InnoDB实现B树索引方式的区别 || 什么是聚簇索引、非聚簇索引

• MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”

• InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方
  在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

什么是聚合索引 ？
聚簇索引就是按照拼音查询，非聚簇索引就是按照偏旁等来进行查询。

其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查"安"字，就会很自然地翻开字典的前几页，因为"安"的拼音是"an"，而按照拼音排序 汉字的字典是以英文字母"a"开头并以"z"结尾的，那么"安"字就自然地排在字典的前部。如果您翻完了所有以"a"开头的部分仍然找不到这个字，那么就 说明您的字典中没有这个字；同样的，如果查"张"字，那您也会将您的字典翻到最后部分，因为"张"的拼音是"zhang"。也就是说，字典的正文部分本身 就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为"聚集索引"

什么是非聚合索引?
如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而 需要去根据"偏旁部首"查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合"部首目录"和"检字表"而查到的字的排序并不是 真正的正文的排序方法，比如您查"张"字，我们可以看到在查部首之后的检字表中"张"的页码是672页，检字表中"张"的上面是"驰"字，但页码却是63 页，"张"的下面是"弩"字，页面是390页。很显然，这些字并不是真正的分别位于"张"字的上下方，现在您看到的连续的"驰、张、弩"三字实际上就是他 们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后 再翻到您所需要的页码。

我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为"非聚集索引"

---

4、事务管理

数据库的 事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。如果任意一个操作失败，那么整组操作即为失败，会回到操作前状态或者是上一个节点。

4.1 事务状态

• 活跃状态：事务的第一个状态，任何正在执行的事务都处于此状态，所做的 更改 存储在 主内存的缓冲区 中。
• 部分提交状态：执行上次操作后，事务进入部分提交状态。之所以是部分提交，是因为所做的更改仍然在主内存的缓冲区中。
• 失败状态：如果某个检查在活动状态下失败，在活动状态或部分提交状态发生一些错误，并且事务无法进一步执行，则事务进入失败状态。
• 中止状态：如果任何事务已达到失败状态，则恢复管理器将数据库回滚到开始执行的原始状态。
• 提交状态：如果所有操作成功执行，则来自 部分提交状态 的事务进入提交状态。无法从此状态回滚，它是一个新的 一致状态。

4.2 事务的四大特征（ACID）

• 原子性：事务是最小的执行单位，不可分割的（原子的）。事务的原子性确保动作要么全部执行，要么全部不执行。
• 一致性：当事务完成时，数据必须处于一致状态，多个事务对同一个数据读取的结果是相同的。
• 隔离性：并发访问数据库 时，一个用户的事务不被其他事务所干扰，各个事务不干涉内部的数据。
• 持久性：一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

如何保证ACID：
DBMS 采用 日志 来保证事务的 原子性、一致性 和 持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。

DBMS 采用 锁机制 来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许 持有锁的事务 能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

4.3 事务之间的相互影响

• 脏读（Dirty Read） 一个事务读取了另一个事务未提交的数据。

• 不可重复读（Non-repeatable Read） 就是在一个事务范围内，两次相同的查询会返回两个不同的数据，这是因为在此间隔内有其他事务对数据进行了修改。

• 幻读（Phantom Read） 幻读是指当事务 不是独立执行时 发生的一种现象，例如有一个事务对表中的数据进行了修改，这种修改涉及到表中的全部数据行，同时，第一个事务也修改这个表中的数据，这种修改是向表中 插入一行新数据。那么，第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。

• 丢失更新（Lost Update） 两个事务同时读取同一条记录，事务 A 先修改记录，事务 B 也修改记录（B 是不知道 A 修改过），当 B 提交数据后， 其修改结果覆盖了 A 的修改结果，导致事务 A 更新丢失。

4.4 事务的隔离级别

隔离级别（能否发生）	脏读	不可重复读	幻读	丢失更新
读取未提交	是	是	是	是
读取已提交	否	是	是	是
可重复读	否	否	是	否
可串行化	否	否	否	否

• 读取未提交
  最低的隔离级别，一个事务可以读到另一个事务未提交的结果，所有的并发事务问题都会发生。

• 读取已提交
  只有在事务提交后，其更新结果才会被其他事务看见，可以解决 脏读问题，但是不可重复读或幻读仍有可能发生。Oracle 默认采用的是该隔离级别。

• 可重复读
  在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交，除非数据是被本身事务自己所修改。可以解决 脏读、不可重复读。MySQL 默认采用可重复读隔离级别。

• 可串行化
  事务 串行化执行，隔离级别最高，完全服从 ACID，牺牲了系统的并发性，也就是说，所有事务依次逐个执行，所以可以解决并发事务的所有问题。
  不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记 录就变为了5条，这样就导致了幻读。

与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别 下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以 说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内 容)，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失
InnoDB 存储引擎在分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

---

5、关联查询

• 交叉连接（CROSS JOIN）
  除了在 FROM 子句中使用 逗号间隔连接的表 外，SQL 还支持另一种被称为交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数 乘以 第二个表中符合查询条件的数据行数。惟一的不同在于，交叉连接分开列名时，使用 CROSS JOIN 关键字而不是逗号，即以下两个表达式等价：
  SELECT * FROM A, B
  SELECT * FROM A CROSS JOIN B

• 内连接（INNER JOIN）
  内连接分为三类，分别是 等值连接：ON A.id = B.id、不等值连接：ON A.id > B.id 和 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid。

• 外连接（LEFT JOIN/RIGHT JOIN）
  左外连接：以左表为主，先查询出左表，按照 ON 后的关联条件匹配右表，没有匹配到的用 NULL 填充，可以简写成 LEFT JOIN；
  右外连接：以右表为主，先查询出右表，按照 ON 后的关联条件匹配左表，没有匹配到的用 NULL 填充，可以简写成 RIGHT JOIN；

• 联合查询（UNION 与 UNION ALL）
  SELECT * FROM A UNION SELECT * FROM B UNION …
  联合查询就是把多个结果集集中在一起，UNION 前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并；
  如果使用 UNION ALL，不会合并重复的记录行，所以效率更高。

• 全连接（FULL JOIN）
  MySQL 本身不支持全连接，但可以通过联合使用 LEFT JOIN、UNION 和 RIGHT JOIN 来实现。
  SELECT * FROM A LEFT JOIN B ON A.id = B.id
  UNION SELECT * FROM A RIGHT JOIN B ON A.id = B.id

---

6、DROP、DELETE和TRUNCATE的区别

	DROP	DELETE	TRUNCATE
SQL语句类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	从数据库中 删除表，所有的数据行，索引和权限也会被删除	表结构还在，删除表的 全部或者一部分数据行	表结构还在，删除表中的 所有数据
删除速度	最快	速度慢，需要逐行删除	删除速度快
使用场景	因此，在不再需要一张表的时候，采用 DROP	在想删除部分数据行时候，用 DELETE	在保留表而删除所有数据的时候用 TRUNCATE

---

7、数据库中的主键、超键、候选键、外键是什么

超键：在关系中能唯一标识元组的属性集称为关系模式的超键

候选键：不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！

主键：用户选作元组标识的一个候选键程序主键

外键：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

---

8、 MySQL中CHAR和VARCHAR的区别有哪些？

char的长度是不可变的，用空格填充到指定长度大小，而varchar的长度是可变的。
char的存取数度还是要比varchar要快得多
char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

9、 ER图

E-R 图 也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。 它是描述现实世界关系概念模型的有效方法。 是表示概念关系模型的一种方式。

下图是一个学生选课的 ER 图，每个学生可以选若干门课程，同一门课程也可以被若干人选择，所以它们之间的关系是多对多（M: N）。另外，还有其他两种关系是：1 对 1（1:1）、1 对多（1: N）。

我们试着将上面的 ER 图转换成数据库实际的关系模型(实际设计中，我们通常会将任课教师也作为一个实体来处理)：