【数据库】面试题汇总（持续更新）

1.数据库中left join与right join，inner join，full join的区别###

Paste_Image.png

2.关于索引：###

索引的数据结构：#####

• B+树结构

Paste_Image.png

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点是不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

** b+树的查找过程： **

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

应该建索引的字段：#####

• 1.经常作为查询条件的字段
• 2.外键
• 3.经常需要排序的字段
• 4.分组排序的字段

应该少建或者不建索引的字段：#####

• 1.表记录太少
• 2.经常需要插入，删除，修改的表，
• 3.表中数据重复且分布平均的字段

因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%宜采用;
• 基于非唯一性索引的检索

mysql 慢查询的优化 参考链接：慢查询的优化

3.ACID###

原子性（Atomicity）#####

原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）#####

一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

隔离性（Isolation）#####

多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。

持久性（Durability）#####

在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

**参考链接 **
点击谈谈数据库的ACID

4.数据库事务隔离级别###

数据库的事务隔离级别（TRANSACTION ISOLATION LEVEL）是一个数据库上很基本的一个概念。事务隔离级别的前提是一个多用户、多进程、多线程的并发系统，在这个系统中为了保证数据的一致性和完整性，我们引入了事务隔离级别这个概念，对一个单用户、单线程的应用来说则不存在这个问题。

为了避免上述几种事务之间的影响，SQL Server通过设置不同的隔离级别来进行不同程度的避免。因为高的隔离等级意味着更多的锁，从而牺牲性能。所以这个选项开放给了用户根据具体的需求进行设置。不过**默认的隔离级别Read Commited **符合了多数的实际需求.

SQL Server隔离事务之间的影响是通过** 锁 **来实现的，通过阻塞来阻止上述影响。不同的隔离级别是通过加不同的锁，造成阻塞来实现的，所以会以付出性能作为代价；安全级别越高，处理效率越低；安全级别越低，效率高。

**使用方法: **set transaction isolation level repeatable read

未提交读(Read Uncommitted)： 写事务阻止其他写事务，避免了更新遗失。但是没有阻止其他读事务，读数据时不会检查或使用任何锁。因此，在这种隔离级别中可能读取到没有提交的数据（即脏读）。解决办法就是下面的“可读取确认”。

已提交读(Read Committed)： ** 写事务会阻止其他读写事务，读事务不会阻止其他任何事务。 已提交读是SQL Server的默认隔离级别。 ** 存在的问题：不可重复读。即在一次事务之间，进行了两次读取，但是结果不一样，可能第一次id为1的人叫“李三”，第二次读id为1的人就叫了“李四”。因为读取操作不会阻止其他事务。解决办法就是下面的“可重复读”。

**可重复读(Repeatable Read )： 读事务会阻止其他写事务，但是不会阻止其他读事务。存在的问题：幻读。可重复读阻止的写事务包括update和delete（只给存在的表加上了锁），但是不包括insert（新行不存在，所以没有办法加锁），所以一个事务第一次读取可能读取到了10条记录，但是第二次可能读取到11条，这就是幻读。解决办法就是下面的“串行化”。

可串行读(Serializable)：读加共享锁，写加排他锁。这样读取事务可以并发，但是读写，写写事务之间都是互斥的，基本上就是一个个执行事务，所以叫串行化。工作方式类似于可重复读。但它不仅会锁定受影响的数据，还会锁定这个范围。这就阻止了新数据插入查询所涉及的范围。

5.数据库并发事务的控制是如何实现的？###

6.数据库的多版本控制###

7. 实践中如何优化MySQL

四条从效果上第一条影响最大，后面越来越小。

① SQL语句及索引的优化
② 数据库表结构的优化
③ 系统配置的优化
④ 硬件的优化

6. 什么情况下设置了索引但无法使用

① 以“%”开头的LIKE语句，模糊匹配
② OR语句前后没有同时使用索引
③ 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

7. SQL语句的优化

• order by要怎么处理
• alter尽量将多次合并为一次
• insert和delete也需要合并

8. SQL语言

包括数据定义、数据操纵(Data Manipulation),数据控制(Data Control)

数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等
数据操纵：Select ,insert,update,delete,
数据控制：grant,revoke

SQL常用命令：

CREATE TABLE Student(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL);//建表
CREATE VIEW view_name AS
Select * FROM Table_name;//建视图
Create UNIQUE INDEX index_name ON TableName(col_name);//建索引
INSERT INTO tablename {column1,column2,…} values(exp1,exp2,…);//插入
INSERT INTO Viewname {column1,column2,…} values(exp1,exp2,…);//插入视图实际影响表
UPDATE tablename SET name=’zang 3’ condition;//更新数据
DELETE FROM Tablename WHERE condition;//删除
GRANT (Select,delete,…) ON (对象) TO USER_NAME [WITH GRANT OPTION];//授权
REVOKE (权限表) ON(对象) FROM USER_NAME [WITH REVOKE OPTION] //撤权
列出工作人员及其领导的名字：
Select E.NAME, S.NAME FROM EMPLOYEE E S
WHERE E.SUPERName=S.Name

9．触发器的作用？

答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

10.理解并解释下列术语的含义：

• **封锁 **
  它是实现并发控制的一个非常重要的技术。（例如：一个事务A对某个数据对象如表或者记录等操作之前，先向系统发出请求，对其加锁。加锁之后事务A就对该数据对象有了一定的控制，在事务A没有释放所之前，其他事务死不嗯呢该更新次数据对象的）它分为写锁和读锁。

• 活锁
  事务A加锁了数据X，然后事务B又请求加锁数据X，接着事务C又请求加锁数据X，当A释放掉数据X的锁之后，系统将数据X先交给了事务C，然后事务D又请求加锁数据X，当事务B使用完之后，系统又将数据X交给了D,这样事务B就一直处于等待状态，这种情况就是所谓的活锁状态。解决此种方法可以使用：先来先服务的策略。

• 死锁
  当事务A在加锁了数据X之后，需要数据Y，同时，事务B在加锁了数据Y之后，需要数据X。两者都不释放自己的那一部分资源，就都处于一直等待转态，此种情况就是死锁。

• 排他锁
  在一个事务A中为某个数据对象加入了一个写锁，则只允许A对数据进行读取和修改操作，其他事务都不能对该数据对象进行加锁，知道A将锁释放掉，其他事务才可以对它进行操作。（通俗点：就是一个事务如果加的是写锁，其他事务都不能对她加锁）。

• 共享锁
  如果事务A对一个数据加了读锁，那么只允许A对数据进行读取，不能进行修改操作，其他事务也可以对该数据进行加读锁操作，但是不能加写锁操作。

• 并发事务的可串行化调度
  多个事务的并发执行时正确的，当且仅当其结果与按照某一次序串行的执行这些事务时的结果相同，称这种调度策略为可串行化。

• 可串行化的调度
  通常是指不管数据库初态处于什么状态，一个调度对数据库状态的影响都和某个串行调度相同，我们就说这个调度是可串行化，称之为可串行调度。

• 两段锁协议
  事务分为两个阶段，第一阶段是获得封锁，在这阶段，事务可以申请获得任何数据项上的任何类型的锁，但是不能释放任何锁。第二阶段是释放封锁，在这阶段，事务可以释放任何数据项上的任何类型的锁，但是不能再申请任何锁

11.解决死锁的方案：

1.死锁的预防

1） 一次封锁法：将事务A所需要的数据全部加锁，否则不能执行。这种方法暴漏出来的几个特点：当数据量过大时，会降低熊的并发度；
2） 顺序加锁法：预先对数据规定一个封锁顺序，所有的事务都必须按照这个顺序实行封锁。

2.死锁的诊断与解除：

1） 超时法：规定一个时间，如果某个事务超过了规定的时间，就认为是死锁。
2） 等待图法

……暂时整理这些，不断更新