【820复试】综合面试之数据库篇

1.数据库中的关系代数有哪些？

选择：选择满足特定条件的元组

投影：对所有行输出指定的属性，并去掉重复值

自然连接：从两个输入关系上输出元组对，元组对需要满足——属性名字相同的时，内容也要相同

笛卡尔积：和自然连接的区别时不管属性值是否一样。

2.什么是完整性约束？

SQL禁止破坏完整性约束的任何数据库更新

3.SQL中对空值的处理？

将涉及空值的任何比较运算的结果视为unknown

4.视图是什么？

为了安全起见，只给用户提供视图，不是数据的螺髻层面，也可以隐藏不必要的信息。

5.为什么要创建索引？一般索引的数据结构是什么？

索引是在关系的属性上创建的一种数据结构，它允许系统高效地找到关系中在索引属性上取出给定值地元组，而不用扫描关系中地所有元组。即索引是帮助引擎高效获取数据库数据的数据结构。

简言之，索引就类似于书本，字典的目录。

其一般的数据结构是B+树。

B+树：

引入原因：随着文件的增大，索引顺序文件的查招性能会降低，虽然这种降低可以用文件重组来弥补，但是一般不希望进行频繁的文件重组。B+树索引结构会增加文件的插入和删除的性能开销，同时会增加空间的开销。

非叶子节点只存储索引值，叶子节点再存储索引+具体数据，从小到大用链表连接在一起，范围查询可直接遍历不需要回溯7

索引的作用与缺点

①作用

通过创建索引，可以在查询的过程中，提高系统的性能

通过创建唯一性索引，可以保持数据库表中每一行数据的唯一性

在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间

②缺点

创建索引和维护索引要耗费时间，而且时间随着数据量的增加而增大

索引需要占用物理空间，如果要建立聚簇索引，所需要的空间会更大

在对表中的数据进行增删改时需要耗费较多的时间，因为索引也要动态地维护

6.范式的类型有哪些?

第一范式（1NF）：当关系模式R的所有属性都不能再分解为更基本的数据单位时，称R是满足第一范式，即属性不可分。

属性的域是原子的，不可分的

解决第一范式的方法：将属性再细分。

第二范式（2NF）：如果关系模式R满足第一范式，并且R的所有非主键属性完全依赖于R的每一个候选关键属性，称R满足第二范式。

消除非主属性对主码的部分函数依赖

主码有多个，其中某个非主属性对主码是部分函数依赖

解决方式：将部分函数依赖的关系划分为两个关系

第三范式（3NF）：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，

为消除非主属性对主码的传递依赖

即关系中每一个属性都是和主要直接相关，而不能是间接相关的。

BC范式（BCNF）：为消除主属性对主码的传递依赖关系。

总之：第一范式保证原子性，第二范式消除部分函数依赖，第三范式消除非主属性对主码的传递函数依赖，BCNF是第三范式的加强版，在第三范式基础上增加了消除主属性对主码的传递函数依赖。

7.事务是什么？

引入事务的作用：

①提供数据库保持一致性的方法（数据库执行失败时可以回滚）；

②多个app并发访问数据库时，可以提供对这些app进程隔离的方法。

事务的四个特性：

​ 原⼦性： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么全不执行

​ 一致性： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；

​ 隔离性： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；

​ 持久性： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

数据库事务（Database Transaction），是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功，要么全部失败的单元，可以简化错误恢复并是应用程序更加可靠。

8.数据库事务隔离是什么?

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡取钱，在A取钱的过程结束前，B不能向这张卡转账。

9.内连接、左联接、右联接、等值联接

内连接取量表交集部分；
左连接取左表全部右表部分；
右连接取右表全部左表部分；
等值联接只返回两个表中联接字段相等的行。

10.MySql的四种隔离状态

**读未提交：**最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

**读已提交：**允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。

**可重复读：**同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，会有幻读。

**串行化：**最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰。

11.SQL的优化方法

①建立索引对查询进行优化

索引是加速数据库查询的关键。在设计表结构时，应该根据查询的需求添加合适的索引。常用的索引包括主键、唯一索引、普通索引、联合索引、前缀索引（vachar、text这种长的数据并且只需要前几个区分度就很高）等。同时，要避免过多的索引，因为每个索引都需要占用存储空间，会影响写入性能。

②对查询进行优化，尽量避免全表扫描

要避免使用子查询，尽可能使用连接查询；避免在查询中使用“%”通配符；避免多余的字段等等；避免使用in和not in；select的时候使用具体的字段代替*号。

③数据库表结构优化

合理的表结构可以提高查询效率和减少存储空间。应该避免使用大字段，如TEXT、BLOB等，因为这些字段会占用大量的存储空间。同时，应该避免冗余字段，避免更新和维护时的复杂性。

④缓存优化

使用缓存可以大大减轻MySQL数据库的压力，提高查询效率。常用的缓存技术包括Memcached和Redis等。

12.并发控制问题

12.1并发控制带来的数据不一致性

①丢失修改(lost update)
事务T1和T2读取同一数据，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失。

②不可重复读(non-repeatable read)
简单来说就是事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果。
会出现以下三种情况：
·事务T1读取某一数据后，事务T2对其进行了修改，当事务T1再次读数据，得到与之前一次不同的值；
·事务T1按一定的条件读取某些数据，事务T2删除了其中部分记录，T1再次读数据时，发现某些记录消失了；
·事务T1按一定的条件读取某些数据，事务T2插入了其中部分记录，T1再次读数据时，发现多了一些记录。
后两种称为不可重复读中的幻影(plantom row)现象。

③读“脏”数据(dirty read)
事务T1修改某一数据并写回磁盘，事务T2读取同一数据后，T1因为某原因被撤回(回滚了rollback)，这时T1恢复到未修改之前的值，但是T2读取的数据不变，此时就称T2读到了“脏"数据，即不正确的数据。

12.2基于封锁的并发控制技术

封锁就是事务T在对某个数据对象操作之前，先向系统发出请求，对其加锁。
加锁后事务T就对该数据对象有了一定的控制，在事务T释放它之前，其他事务不能更新此数据对象。

基本的封锁类型：
①排他锁——写锁：在事务T对数据对象A加上排他锁，则只允许事务T读取和修改数据对象A，其他任何事物不能再对数据对象A加任何类型的锁，直到事务T释放锁为止。
②共享锁——读锁：在事务T对数据对象A加上共享锁，事务T可以读数据对象A但不能修改，其他事务只能对数据对象A加共享锁，而不能加排他锁，直到事务T释放数据对象A上的共享锁为止。

衍生的三种封锁协议：

1.一级封锁协议
事务T在修改数据R之前必须先对其加排他锁，直到事务结束才释放。
一级封锁协议可防止丢失修改，且保证事务T是可恢复的。
不能保证可重复读和不读“脏”数据。

2.二级封锁协议
在一级封锁协议基础上，增加事务T在读取R之前必须先对其加共享锁，读完后即可释放共享锁。
防止了丢失修改，且进一步防止读"脏"数据。
但是不能保证可重复读。

3.三级封锁协议
在一级协议基础上增加事务T在读取数据R之前必须先对其加共享锁，直到事务结束才释放。
注意二级和三级在释放共享锁的时机。
进一步防止了不可重复读。

主要区别在于什么操作需要申请封锁，以及什么适合释放锁。
不同的封锁协议使事务达到的一致性级别使不同的，封锁协议级别越高，一致性成都越高。

12.3活锁和死锁

活锁即系统一直批准其他事物加锁请求的情况，避免活锁的简单方法是采用先来先服务的策略。
当多个事务请求封锁同一数据对象时，封锁子系统按请求封锁的先后次序对事物排队，数据对象上的锁一旦释放就批准申请队列中的第一个事物获得锁。

死锁即形成了循环等待的情况，两个事务互相等待，永远不能结束，从而形成死锁。
预防死锁的两种方法：
①一次封锁法：
要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行。
会导致封锁的范围扩大，从而降低了系统并发度。
②顺序封锁法：
预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实施封锁。
会导致封锁的数据对象极多，随着数据的插入、删除不断变化，维护这样的封锁顺序困难，成本代价高。
死锁诊断和解除死锁：
①超时法：规定等待时间是否超过规定时限。
②等待图法：动态反映所有事务的等待情况。

12.4并发调度的可串行性

多个事务的并发执行是正确的(执行结果唯一)，当且仅当其结果与按某一次序串行地执行这些事务时地结果相同。

冲突操作：指不同地事务对同一个数据地读写操作和写写操作。

一个调度在保证冲突操作的次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度，若该调度时穿行的，那么称原调度为冲突可串行化的调度。

一个调度若是冲突可串行化，则一定是可串行化调度。反之则不然。

12.5两段锁协议

定义：事务分为两个阶段，第一阶段是获得封锁，也称为扩展阶段，在这个阶段，事务可以申请获得任何数据项上的任何类型的锁，但不能释放任何锁；
第二阶段是释放封锁，也称为收缩阶段，此阶段事务可以释放任何数据项上的任何类型的锁，但是不能再申请任何锁。

事务遵循两段锁协议是可串行化调度的充分条件，但不是必要条件。
即：若并发事务都遵循两段锁协议，那么对这些事务的任何并发调度策略都是可串行化的；
但是，若并发事务的一个调度是可串行化的，不一定所有事务都符合两段锁协议。

12.6封锁粒度

封锁对象的大小称为封锁粒度，与系统的并发度和并发控制的开销密切相关。
封锁粒度越大，能够封锁的数据单元越少，并发度越小，系统开销越小。反之亦反。

多粒度封锁即一个系统同时支持多种封锁粒度供不同的事务选择。

①多粒度封锁
有一棵多粒度树，根节点为整个数据库，即最大的数据粒度，叶子结点为最小的数据力度。
多粒度封锁协议允许多粒度树中每个结点被独立地加锁。
对一个结点加锁：这个结点地所有后裔结点也被加以同样的锁。
两种封锁方式
显示封锁：对于事务的要求直接加到数据对象上的锁；
隐式封锁：该数据对象没有被独立加锁，但是其上级结点家所而使得该数据对象也加上了锁。

②意向锁
如果对一个结点加意向锁，说明该结点的下层结点正在被加锁；对任意结点加锁时，必须先对它的上层结点加意向锁。

13.锁的分类

MySQL中的锁，按照锁的粒度分，分为以下三类:

1. 全局锁:锁定数据库中的所有表。
2. 表级锁:每次操作锁住整张表。
3. 行级锁:每次操作锁住对应的行数据。