数据库面试题

一：事务

1：什么是事务

数据库事务指的是一组数据操作，事务内的操作要么就是全部成功，要么就是全部失败

事务可以看做是一次大的活动,它由不同的小活动组成,这些活动要么全部成功,要么全部失败。(同

2：事务的四大特性(ACID)

原子性(Atomicity)
一个事务就是一个最小的无法分割的独立单元，不允许部分成功部分失败
实现原理:利用Innodb的undo log，undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息

一致性(Consistency)

所谓一致性是数据库处理前后结果应与其所抽象的客观世界中真实状况保持一致。这种一致性是一种需要管理员去定义的规则。管理员如何指定规则，数据库就严格按照这种规则去处理数据。
简单点说：一致性要求任何写到数据库的数据都必须满足于预先定义的规则，

通俗得讲就是：结果数据与期望数据一致

举个例子：

如果说AB账户总金额5000就是数据库的一致性规则，那么我能不能把A账户转走10000给B，让B账户有10000，而A剩下-5000？从数学上来看完全正确，但这显然是不符合常理的。而这种常理，就是所谓的一致性。

隔离性(Isolation)
隔离性要求如果两个事务修改同一个数据，则必须按顺序执行，并且前一个事务如果未完成，那么未完成的中间状态对另一个事务不可见。

持久性(Durability)
持久性的关键在于一旦“完成提交”（committed），那么数据就不会丢失。提交之前会先持久到redolog，然后提交后刷新到db。
undolog实现事务原子性，redolog实现事务的持久性。

3：MySQL事务隔离级别和实现原理

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，两次读取同一行的数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。（针对其他提交前后，读取数据本身的对比）

不可重复读和脏读的区别：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。　

幻读

幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。（针对其他提交前后，读取数据条数的对比）

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

1. 读未提交（READ UNCOMMITTED）
2. 读提交 （READ COMMITTED）
3. 可重复读 （REPEATABLE READ）
4. 串行化 （SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

以上四种隔离级别最高的是串行化，最低的是读未提交，当然级别越高，执行效率就越低，像串行化就是以锁表的方式(类似于Java多线程中的锁)使得其他线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况来，在MYSQL数据库中默认的隔离级别是Repeatable read（可重复读）。

在MYSQL数据库中，支持上面四种隔离级别；而在Oracle数据库中，只支持Serializeble(串行化)级别和Read committed(读已提交)这两种级别，其中默认的为Read committed(读已提交)

在MYSQL数据库中查看当前事务的隔离级别

select @@tx_isolation;

8.0+就已经抛弃了这样的查询方法,采用：

select @@transaction_isolation

 在MYSQL数据库中设置事务的隔离级别：

 记住：设置数据库的隔离级别一定要是在开启事务之前：

数据库的常用范式：

第一范式（1NF）：指表的列不可再分，数据库中表的每一列都是不可分割的基本数据项，同一列中不能有多个值；
第二范式（2NF）：在 1NF 的基础上，还包含两部分的内容：一是表必须有一个主键；二是表中非主键列必须完全依赖于主键，不能只依赖于主键的一部分；
第三范式（3NF）：在 2NF 的基础上，消除非主键列对主键的传递依赖，非主键列必须直接依赖于主键。

MySQL索引的实现原理：

索引本质上就是一种通过减少查询需要遍历行数，加快查询性能的数据结构，避免数据库进行全表扫描，好比书的目录，让你更快的找到内容。（一个表最多16个索引）

索引的优缺点：

（1）索引的优点：

减少查询需要检索的行数，加快查询速度，避免进行全表扫描，这也是创建索引的最主要的原因。
如果索引的数据结构是B+树，在使用分组和排序时，可以显著减少查询中分组和排序的时间。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（2）索引的缺点：

当对表中的数据进行增加、删除和修改时，索引也要进行更新，维护的耗时随着数据量的增加而增加。
索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引的分类：

（1）普通索引、唯一索引、主键索引、全文索引、组合索引。

普通索引：最基本的索引，没有任何限制
唯一索引：但索引列的值必须唯一，允许有空值，可以有多个NULL值。如果是组合索引，则列值的组合必须唯一。
主键索引：一种特殊的唯一索引，不允许有空值。
全文索引：全文索引仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
组合索引：主要是为了提高mysql效率，创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。
（2）聚簇索引与非聚簇索引：

如果按数据存储的物理顺序与索引值的顺序分类，可以将索引分为聚簇索引与非聚簇索引两类：

聚簇索引：表中数据存储的物理顺序与索引值的顺序一致，一个基本表最多只能有一个聚簇索引，更新聚簇索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引
非聚簇索引：表中数据的物理顺序与索引值的顺序不一致的索引组织，一个基本表可以有多个聚簇索引。

索引的数据结构：

常见的索引的数据结构有：B+Tree、Hash索引。

哪些情况下索引会失效？

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

2.like查询是以%开头

3.where中索引列有运算;
4.where中索引列使用了函数;

有哪些常见的数据库优化

（1）：性能优化

• 表的设计合理化，符合三大范式（3NF)

  • 1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
  • 2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
  • 3NF是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到。
• 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
  • 较频繁的作为查询条件字段应该创建索引；
  • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
  • 更新非常频繁的字段不适合创建索引
  • 不会出现在WHERE子句中的字段不该创建索引
• 分表技术(水平分割、垂直分割)；
• 读写[写: update/delete/add]分离；
• 存储过程 [模块化编程，可以提高速度]；
• 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]；
• mysql服务器硬件升级；
• 定时的去清除不需要的数据

  （2）：SQL语句优化

• 通过show status命令了解各种SQL的执行频率；
• 定位执行效率较低的SQL语句-（重点select；
• 通过explain分析低效率的SQL；

     （1）：连接的表越多，性能越差，如果不可避免多表连接，很可能是设计缺陷

     （2）：Null列使用索引没有意义，任何包含null值的列都不会被包含在索引中。因此where语句中的is null或is not null的语句优化器是不允许使用索引的。

      （3）：like通配符出现在首位，无法使用索引，反之可以。

-- 无法使用索引
select .. from .. where name like '%t%' ;
-- 可以使用索引
select .. from .. where name like 't%' ;

        （4）：order by子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低。

        （5）：not运算无法使用索引，可以改成其他能够使用索引的操作。如下：

-- 索引无效
select .. from .. where sal != 3000 ;
-- 索引生效
select .. from .. where sal < 3000  or sal > 3000;

        （6）：exists替代in

not in是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或not exists。如下：

-- 正确
SELECT  *
FROM EMP 
WHERE  
	EMPNO > 0 
	AND  EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

-- 错误
SELECT  * 
FROM  EMP 
WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

explain你记得哪些字段,分别有什么含义

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：

id：SQL查询中的序列号，id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

select_type：查询的类型

 table：查询的表名。不一定是实际存在的表名。

type（重要）：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：

system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

partitions：版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

possible_keys：查询可能使用到的索引都会在这里列出来

key：查询真正使用到的索引。

ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows（重要）：这是mysql估算的需要扫描的行数（不是精确值）。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

filtered：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。这个字段不重要

extra（重要）：EXplain 中的很多额外的信息会在 Extra 字段显示