凡人多烦事01

这个屌丝很懒，什么也没留下！

热门标签

Java面试之数据库篇(offer 拿来吧你)_java 面试数据库

作者：凡人多烦事01 | 2024-04-14 13:15:19

踩

java 面试数据库

现在关于Java面试的资料是层出不穷，对于选择困难症的同学来说，无疑是陷入了一次次的抉择与不安中，担心错过了关键内容，现在小曾哥秉持着"融百家之所长,汇精辟之文档"的思想，整理一下目前主流的一些八股文，以达到1+1 > 2 的效果！

文章目录

基础概念

1、什么是DBDBMSDBSDBA

上面名词傻傻分不清，一问搞定

数据库 : 数据库(DataBase 简称 DB)就是信息的集合(数据库是由数据库管理系统管理的数据的集合）
数据库管理系统 :数据库管理系统(Database Management System 简称DBMS)是一种操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库。
数据库系统 : 数据库系统(Data Base System，简称 DBS)通常由软件、数据库和数据管理员(DBA)组成。
数据库管理员 : 数据库管理员(Database Administrator, 简称 DBA)负责全面管理和控制数据库系统。

2、主键和外键有什么区别？

介绍主键和外键例子

学生表(学号，姓名，性别，班级)，其中每个学生的学号是唯一的，学号就是一个主键；
用户表(用户名、密码、用户级别)，其中用户名是唯一的, 用户名就是一个主键

在成绩表(学号,课程号,成绩) 中，成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以 学号和课程号的属性组是一个主键。
成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键。
在这里插入图片描述

3、数据库范式(1NF、2NF、3NF)介绍？

第一范式：数据库表中的每一列都不可再分，也就是原子性
在这里插入图片描述 >上述不满足原子性原则，所谓原子就是最小的，不能再把它进行划分了，上述可以将部门岗位进行划分为下面的表
第二范式：在满足第一范式基础上要求每个字段都和主键完整相关，而不是仅和主键部分相关（主要针对联合主键而言)
第二范式的另一种表述方式是：两张表要通过外键关联，不保存冗余字段。
注意：如果不是联合主键（两个字段共同充当表的主键），不存在不遵守第二范式的问题。
在这里插入图片描述
“订单详情表”使用**“订单编号”和“产品编号”**作为联合主键。
此时“产品价格”、 “产品数量”都和联合主键整体相关，但“订单金额”和“下单时间” 只和联合主键中的“订单编号”相关，和“产品编号”无关。所以只关联了主键中的部分字段，不满足第二范式。

修改后如下图：
在这里插入图片描述
第三范式：表中的非主键字段和主键字段直接相关，不允许间接相关

上面表中的“部门名称”和“员工编号”的关系是“员工编号”→“部门编号” →“部门名称”，不是直接相关。此时会带来下列问题：
1.数据冗余：“部门名称”多次重复出现。
2.插入异常：组建一个新部门时没有员工信息，也就无法单独插入部门信息。就算强行插入部门信息，员工表中没有员工信息的记录同样是非法记录。
3. 删除异常：删除员工信息会连带删除部门信息导致部门信息意外丢失。
4.更新异常：哪怕只修改一个部门的名称也要更新多条员工记录。

解决方案：把上表拆分成两张表，以外键形式关联
在这里插入图片描述

基础操作 + 内容

一个完整的SELECT语句包括6个子句，其中前面的2个子句是必不可少的，其他子句可以省略。

SELECT 语句的完整格式如下：
SELECT [DISTINCT] 目标列名序列-----要查看的列
FROM 表名或视图名------------------数据来源
[WHERE 条件表达式]----------------查询条件
[GROUP BY + 列名] --------------------分组依据
[HAVING + 组条件表达式] ---------分组条件
[ORDER BY + 列名 + [ASC|DESC]序列]----排序依据
1
2
3
4
5
6
7

主要包含四个部分(基础查询、操作符、高级查询、多表查询），详细的可以看到MySQL快速入门实例篇（入内不亏)

1、常见的聚合函数

常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()，下面以MySQL为例，说明这些函数的作用。

COUNT()函数：统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数

COUNT()函数，它有两种用法：
COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。
COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
COUNT()函数可以与GROUP BY一起使用来计算每个分组的总和。

AVG()函数()：通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

AVG()函数可以与GROUP BY一起使用，来计算每个分组的平均值。

SUM()函数：SUM()是一个求总和的函数，返回指定列值的总和。

SUM()可以与GROUP BY一起使用，来计算每个分组的总和。

MAX()函数：返回指定列中的最大值。

MAX()也可以和GROUP BY关键字一起使用，求每个分组中的最大值。 MAX()函数不仅适用于查找数值类型，也可应用于字符类型。

MIN()函数：返回查询列中的最小值。

MIN()也可以和GROUP BY关键字一起使用，求出每个分组中的最小值。
MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

2、关系数据库

概念：关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。
在这里插入图片描述
一对多关联：这种关联形式最为常见，一般是两张表具有主从关系，并且以主表的主键关联从表的外键来实现这种关联关系。另外，以从表的角度来看，它们是具有多对一关系的，所以不再赘述多对一关联了。

多对多关联：这种关联关系比较复杂，如果两张表具有多对多的关系，那么它们之间需要有一张中间表来作为衔接，以实现这种关联关系。这个中间表要设计两列，分别存储那两张表的主键。因此，这两张表中的任何一方，都与中间表形成了一对多关系，从而在这个中间表上建立起了多对多关系。

自关联：自关联就是一张表自己与自己相关联，为了避免表名的冲突，需要在关联时通过别名将它们当做两张表来看待。一般在表中数据具有层级（树状）时，可以采用自关联一次性查询出多层级的数据。

MySQL

1、MySQL 基础架构的了解？

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的

1.应用程序把查询SQL语句发送给服务器端执行;
2.查询缓存，如果查询缓存是打开的，服务器在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据，如果存在，则直接返回给客户端。只有缓存不存在时，才会进行下面的操作;
3.查询优化处理，生成执行计划。这个阶段主要包括解析SQL、预处理、优化SQL执行计划;
4. MySQL根据相应的执行计划完成整个查询;
5.将查询结果返回给客户端。

在这里插入图片描述
从上图可以看出， MySQL 主要由下面几部分构成：

连接器：身份认证和权限相关(登录 MySQL 的时候)。
查询缓存：执行查询语句的时候，会先查询缓存
分析器：没有命中缓存的话，SQL语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器：按照 MySQL认为最优的方案去执行。
执行器：执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB MyISAM、Memory 等多种存储引擎。

2、索引

2.1 说一说你对MySQL索引的理解

概念：索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。
功能：可以快速找出在某个或多个列中有一特定值的行，提高数据的查询效率。
通俗理解：正如新华字典，我想查询"索引"这个词，那我得首先根据字典目录找到对应偏旁或者拼音，下一步再对应到具体的字，而索引本身的含义就类似于新华字典中目录，而新华字典对应的也就是数据库中的表。

优点：高效

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的主要原因。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点：耗费时间和空间成本

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

建立索引的原则:
1.在最频繁使用的、用以缩小查询范围的字段上建立索引;
2.在频繁使用的、需要排序的字段上建立索引。

不适合建立索引的情况:
1.对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引;
2.对于一些特殊的数据类型，不宜建立索引，比如:文本字段(text) 等。

2.2 索引有哪几种？

MySQL的索引可以分为以下几类：

普通索引和唯一索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

单列索引和组合索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

2.3 索引的实现原理？

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现：
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM索引的原理图如下。这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。在这里插入图片描述
InnoDB索引实现：
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件：从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
在这里插入图片描述
是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

2.4、索引的底层使用的是什么数据结构

索引的数据结构和具体存储引擎的实现有关，在MySQL 中使用较多的索引有Hash索引、B+树索引等。而我们经常使用的InnoDB存储引擎的默认索引实现为B+树索引。

2.5、B树和B+树的区别

在这里插入图片描述

2.6、 MySQL的索引为什么用B+树？

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。
在这里插入图片描述
用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页(对应索引树的节点)。

2.7、聚簇索引和非聚簇索引有什么区别？

在InnoDB存储引擎中，可以将B+树索引分为聚簇索引和辅助索引(非聚簇索引)
聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。
辅助索引是根据索引键创建的一棵B+树，与聚簇索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。

2.8、索引分类？

从数据结构角度：树索引、Hash索引。
从物理存储角度：1.聚集索引(clustered index)；2.非聚集索引(non-clustered index)
从逻辑角度：1.普通索引 2.唯一索引 3.主键索引 4.联合索引 5.全文索引

3、事务

3.1、什么是事务？

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

经典例子：转账，假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作，这两个操作必须都成功或者都失败。

将小明的余额减少 1000 元
将小红的余额增加 1000 元。

事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。这样就不会出现小明余额减少而小红的余额却并没有增加的情况。

3.2、事务的ACID特性

A（atomicity），原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。
C（consistency），一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。[例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的]
I（isolation），隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。
D（durability），持久性。事务一旦提交，其结果就是永久性的，即使发生宕机等故障，数据库也能将数据恢复。持久性保证的是事务系统的高可靠性，而不是高可用性。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！
在这里插入图片描述

3.2、并发事务有哪些问题？

并发情况下，读操作可能存在的三类问题：

脏读（Dirty read）：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
不可重复读（Unrepeatable read）：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
幻读（Phantom read）：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

细节请参考下图：
在这里插入图片描述

3.3、MySQL的事务隔离级别

SQL 标准定义了四种隔离级别，这四种隔离级别分别是：

读未提交（READ UNCOMMITTED): 最低的隔离级别，允许读取尚未提交的数据变更
读提交（READ COMMITTED）: 允许读取并发事务已经提交的数据
可重复读（REPEATABLE READ）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改
串行化（SERIALIZABLE）:最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰.
事务隔离是为了解决脏读、不可重复读、幻读问题，下表展示了 4 种隔离级别对这三个问题的解决程度：

上述4种隔离级别MySQL都支持，并且InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，因此避免了幻读的产生。

MySQL默认采用的REPEATABLE_ READ隔离级别

3.4、MVCC的理解

数据库并发场景:
1.读-读:不存在任何问题，也不需要并发控制;
2.读-写:有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读;
3.写-写:有线程安全问题，可能会存在更新丢失问题。
多版本并发控制(MVCC) 是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
MVCC可以为数据库解决以下问题:
1.在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能;
2.同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。

3.5、MySQL 的隔离级别是基于锁实现的吗？

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

SERIALIZABLE 隔离级别，是通过锁来实现的。除了 SERIALIZABLE 隔离级别，其他的隔离级别都是基于 MVCC 实现。

4、锁

4.1、表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。

InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

表级锁： MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

4.2、共享锁和排他锁、意向锁

InnoDB存储引擎实现了如下两种标准的行级锁：

共享锁（S Lock），允许事务读一行数据。
排他锁（X Lock），允许事务删除或更新一行数据。

共享锁

SELECT … LOCK IN SHARE MODE;

排他锁

SELECT … FOR UPDATE;

下图显示了共享锁和排他锁的兼容性，可以发现X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。
在这里插入图片描述

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁。
意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁。
由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下图所示。

4.3、InnoDB 有哪几类行锁

Record Lock：单个行记录上的锁。
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

4.4、死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

解决死锁的方案：
1、超时机制：当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。
2、wait-for graph（等待图）：这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息：锁的信息链表；事务等待链表；
通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。这是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

5、存储引擎

5.1、对存储引擎的了解？

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

最常用的引擎是InnoDB和MyISAM：

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键
MyISAM拥有较高的插入、查询速度，但不支持事务

1.是否支持行级锁

MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！

2.是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

3.是否支持外键

MyISAM 不支持，而 InnoDB 支持。

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

4.是否支持数据库异常崩溃后的安全恢复