数据库相关知识与面试题

数据库基础

数据库范式

• 1NF（第一范式）：属性不可再分
• 2NF（第二范式）：在1NF的基础上，消除了非主属性对码的部分函数依赖
• 3NF（第三范式）：3NF在2NF的基础上，消除了非主属性对码的传递函数依赖

部分重要概念

• 函数依赖（functional dependency） ：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。
• 部分函数依赖（partial functional dependency） ：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。比如学生基本信息表 R 中（学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；
• 完全函数依赖(Full functional dependency) ：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；
• 传递函数依赖 ： 在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事务，因此可将其合并放到一个表中。比如在关系 R(学号 ,姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

drop、delete 与 truncate 区别

• drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
• truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。（truncate是删除表再创建，truncate不知道删除了几条数据。当被用于带分区的表时，truncate会保留分区。）
• delete（删除数据） : delete from 表名 where 列名=值，删除某一列的数据，如果不加 where 子句和truncate table 表名 作用类似。（delete是逐条删除，delete知道删除了几条。）

truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构(定义)，执行 drop 语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而 delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segement 中，事务提交之后才生效。

执行速度：一般来说:drop > truncate > delete。

---

MySQL

MyISAM和InnoDB的区别

MySQL 5.5之前，MyISAM是MySQL的默认存储引擎；MySQL 5.5之后，MySQL引入了InnoDB，默认存储引擎改为InnoDB。

1. 否支持行级锁

MyISAM只有表级锁（table-level blocking），而InnoDB支持行级锁（row-level blocking）和表级锁，默认为行级锁。

2. 是否支持事务

MyISAM不提供事务支持；而InnoDB提供事务支持，具有提交（commit）和回滚（rollback）事务的能力。

3. 是否支持外键

MyISAM不支持外键（Foreign Key）；而InnoDB支持外键。

拓展：一般我们不建议在数据库层面使用外键的，因为可以在应用层面解决。不过这样可能会对数据的一致性造成威胁，所以具体要不要使用外键还是要根据你的项目来决定。

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

MyISAM不支持，而使用InnoDB的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于redo log。

5. 是否支持MVCC

MyISAM不支持，InnoDB支持；MVCC可以看做是行级锁的升级，可以有效的减少加锁操作，提高性能。

MySQL索引

索引类型

MySQL目前主要支持以下索引类型：FULL TEXT（全文索引）、HASH（哈希索引）、BTREE（B+树）、RTREE（R树）

1. FULLTEXT
   • 即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
   • 全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
2. HASH
   • 由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
   • HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
3. BTREE
   • BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
4. RTREE
   • RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

索引的种类

1. 普通索引：仅加速查询
2. 唯一索引：加速查询 + 列值唯一（可以有null）
3. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
4. 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
5. 全文索引：对文本的内容进行分词，进行搜索

​ 索引合并：使用多个单列索引组合搜索
​ 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

索引的优缺点

优点： 提高系统性能

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点： 占用额外的存储空间，创建和维护索引耗时

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

在两个列上单独建立索引和在两个列上建立联合索引他们的查询效率有没有区别？

1. 联合索引使用时，如果检索条件使用了联合索引的第一列或全部列，则索引生效，如果只使用后面的一列或多列，则索引失效。

2. 多个单列索引使用时，优化器会选择最优搜索策略，即：

   • 如果有两个单列索引A和B，并且单独使用索引A的查询效率比单独使用索引B高的话，则优化器可能只会使用索引A进行查询。

   • 如果两个索引同时使用效率高于单独使用其中任意一个索引时，则优化器会同时使用两个索引。

   也就是说，当使用多个单列索引执行多条件查询时，可能只使用一个索引，也可能使用多个索引甚至全部。

事务

定义：事务是逻辑上的一组操作，要么都执行，要么都不执行。

举例：小明给小红转账1000元，这就是一个事务。在这个事务中包含两步操作（可以看成是两个SQL操作）：1）小明账户减去1000元； 2）小红账户加上1000元；这两个操作有任何一个执行失败，都会导致整个事务的失败。

关系型数据库（MySQL、Oracle、SQL Server等）都支持ACID特性。

ACID特性

• 原子性（Atomicity）：事务是最小的单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。
• 一致性（Consistency）：执行事务前后，数据保持一致。例如转账业务中，无论事务是否成功，两人的总额应该是不变的。
• 隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
• 持久性（Durability）：一个事务被提交之后，它对数据库的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

数据事务的实现原理

• InnoDB使用redo log（重做日志）来保证事务的持久性，而使用undo log(回滚日志)来保证事务的原子性；
• InnoDB使用锁机制、MVCC来保证事务的隔离性，默认支持的隔离级别是REPEATABLE-READ;
• 保证了持久性、原子性、隔离性之后，一致性才能得到保障；

并发事务带来的问题

• 脏读（Dirty read）：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
• 不可重复读（UNrepeatable read）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别： 不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

事务的隔离级别

SQL标准定义了四个隔离级别：

• READ-UNCOMMITED（读取未提交）： 最低的隔离级别，允许读取尚未提交的数据变更。可能导致脏读、不可重复读或幻读；
• READ-COMMITED（读取已提交）： 允许读取并发事务已经提交的数据。可以阻止脏读，但是幻读或不可重复读仍可能发生；
• REPEATABLE-READ（可重复读）： 对同一字段的多次读取结果是一致的，除非是被当前事务自己所修改。可以阻止脏读和不可重复读，但幻读仍可能发生；
• SERIALIZABLE（可串行化）： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说该级别可以阻止脏读、幻读以及不可重复读；

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

数据库中的锁

锁的分类

• 按粒度划分：行级锁、表级锁、页级锁
• 按级别划分：共享锁（S锁）、排他锁（X锁）、意向锁
• 按加锁方式划分：自动锁、显式锁
• 按使用方式划分：乐观锁、悲观锁

行级锁、表级锁、页级锁

• 行级锁：分为共享锁和排他锁。行级锁是MySQL中锁定粒度最细的锁。InnoDB引擎支持行级锁和表级锁，只有在通过索引条件检索数据的时候采用行级锁，否则使用表级锁。行级锁开销大，加锁慢，锁定粒度最小，发生锁冲突的概率最低，并发度最高。
• 表级锁：分为共享锁和表独占锁。表级锁开销大，加锁快，锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多，行级锁冲突少但速度慢，因此取了折中的页级锁，一次锁定相邻的一组记录。BDB支持页级锁，页级锁会出现死锁，并发度一般。

共享锁、排他锁

• 共享锁（share lock）：又叫读锁，如果事务T对数据A加上共享锁，则其他事务只能对A再加共享锁，不能加其他锁。共享锁的事务只能读取数据，不能写数据。
• 排他锁（exclusive lock）：又叫写锁，如果事务T对数据A加上排他锁，则其他事务都不能对A加任何类型的锁，获得排他锁的事务既能读数据也能写数据。

意向锁（intent lock）：分为意向共享锁（IS）和意向排他锁（IX）

有了共享锁和排他锁，为什么还要意向锁？

举个栗子：

在MySQL中有表锁，读锁锁表，会阻塞其他事务修改表数据。写锁锁表，会阻塞其他事务读和写。

1. InnoDB引擎支持行级锁，行锁分为共享锁和排他锁。
2. 这两种类型的锁共存的问题，考虑这个例子：事务A锁住了表中的一行数据，让这行数据只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上他就能修改表中的任意一行，这与A持有的行锁是冲突的。因此，数据库需要避免这种冲突，也就是说要让B的申请被阻塞，知道A释放了行锁。

数据库怎么判断这个冲突呢？

• step1：判断表是否已被其他事务用表锁锁表
• step2：判断表的每行数据是否已被行锁锁住

在step2中，需要遍历整个表的所有数据行。因此效率太低。

于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上述判断步骤可以改为：

• step1：判断表是否已被其他事务用表锁锁表
• step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁时会被阻塞。

注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行数据的行锁的时候， 数据库会自动先申请表的意向锁，不需要我们程序员使用代码来申请。

InnoDB四种锁的共存关系

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；

间隙锁（Next-Key lock）

当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项 加锁；对于键值在条件范围内但并不存在的记录，叫做 ”间隙（GAP）“，InnoDB也会对这个 间隙 加锁，这种锁机制就是间隙锁。

举个栗子：

假如emp表中只有101条记录，其emp_id的值分别为1,2，...，101，下面的查询SQL：

select * from emp where emp_id>100 for update;

是一个范围检索，InnoDB不仅会对符合条件的emp_id值为101的记录加锁，也会对emp_id大于101（这些记录不存在）的间隙加锁。究其原因，加锁是为了防止在当前事务查询过程中，其他事务在该记录后偷偷的插入数据，从而导致查询前后结果不一致，即防止出现幻读。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待**。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。**

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

MVCC（Multi-Version Concurrency Control，多版本并发控制）

MVCC是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，另一个保存了行的过期时间（或者删除时间）。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

下面以Repeatable Read隔离级别为例，看一下MVCC具体是如何操作的：

select

InnoDB会根据以下两个条件检查每行记录：

1. InnoDB之查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于当前事务的系统版本号），这样不可以确保事务读取的行记录，要么是在事务开始之前就已经存在的，要么是当前事务自身插入或者修改过的
2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保当前事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的记录才能返回作为查询结果。

insert

InnoDB为新插入的每行数据保存当前事务的系统版本号作为行的版本号。

delete

InnoDB为删除的每行数据保存当前事务的系统版本号作为行删除标识。

update

InnoDB为插入一行新记录，保存当前事务的系统版本号作为行版本号，同时保存当前当前的系统版本号到原来的行作为行删除标识。

保存这两个额外的系统版本号，是大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

细节小问题总结

阿里巴巴 Java 开发手册里要求禁止使用存储过程

【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

MySQL触发器监听的表必须是永久性的表，不能将触发器与临时表（TEMPRORAY）或视图（VIEW）关联起来。

尽量避免使用触发器，如果要使用触发器的话一定要谨慎使用，确定它是非常高效的。另外触发器是针对每一行的，对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

一千行 MySQL 学习笔记

同财务相关的金额类数据必须使用decimal类型

• 非精准浮点：float,double
• 精准浮点：decimal

Decimal 类型为精准浮点数，在计算时不会丢失精度

占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节

可用于存储比 bigint 更大的整型数据

为什么不建议在MySQL中使用utf8

MySQL 字符编码集中有两套 UTF-8 编码实现：

• utf8 ： utf8编码只支持1-3个字节 。 在 utf8 编码中，中文是占 3 个字节，其他数字、英文、符号占一个字节。但 emoji 符号占 4 个字节，一些较复杂的文字、繁体字也是 4 个字节。
• utf8mb4 ： UTF-8 的完整实现，正版！最多支持使用 4 个字节表示字符，因此，可以用来存储 emoji 符号。

因此，如果你需要存储emoji类型的数据或者一些比较复杂的文字、繁体字到 MySQL 数据库的话，数据库的编码一定要指定为utf8mb4 而不是utf8 ，要不然存储的时候就会报错了。

存储时间建议使用timestamp

• DateTime类型没有时区信息（时区无关），Timestamp和时区有关；
• Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间；

Reference

• JavaGuide