【数据库】面试知识点总结_数据库面试知识点

1. 事务

事务（Transaction） 是 访问和更新数据库的程序执行单元，事务是数据库管理系统（DBMS）中最基础的单位，且不可分割。事务和程序是两个概念，一般来说，一个程序中包含多个事务。

数据库事务的四个基本特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称 ACID。保证事务 ACID 特性是事务管理的重要任务（但不是必须满足的条件）。因为在实际的数据库实现中，真正严格满足 ACID 的事务很少，比如 InnoDB 默认的事务隔离级别是可重复读，不满足隔离性。

1.1 事务的四个基本特性（ACID）

1）原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

如银行转账，A 向 B 转账 1000 元，这里可分为三个操作，1）A 向 B 转账、2）银行处理、3）B 账户收到转账。原子性就是保证这三个操作要么都成功，要么都失败，如果 1、2 成功，3 失败，那么1、2 都要进行回滚。

【MySQL 保证原子性的实现原理】

解决方案：MySQL 的 InnoDB 存储引擎提供事务日志 undo log

实现原子性的关键，就是当事务回滚时能够撤销所有已经成功执行的 sql 语句。当事务对数据库进行修改时，InnoDB 会生成对应的 undo log，当事务执行失败或调用 rollback 对事务进行回滚时，可以利用 undo log 中的信息将数据回滚到修改前的样子。

undo log 属于逻辑日志，它记录的是 sql 执行相关的信息。当发生回滚时，InnoDB 会根据 undo log 的内容做与之前相反的工作：对于每个 insert，回滚时会执行 delete；对于每个 delete，回滚时会执行 insert；对于每个 update，回滚时会执行一个相反的 update，把数据改回去。

2）一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

比如转账时，用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

从数据库层面讲，原子性、隔离性和持久性都是手段，只有维护这三大特性，才有可能实现一致性。

3）隔离性（Isolation）：多个事务并发访问时，事务不应该影响其它事务运行效果。比如当多个用户并发访问数据库，操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。实现隔离有两种方式：1）加锁，2）MVCC（Mutil-Version Concurrency Control，多版本并发控制）。

【MySQL 保证隔离性的实现原理】

解决方案：InnoDB 通过锁机制保证同一时刻只有一个事务对数据进行写操作。通过MVCC降低事务的写操作对其他事务读操作的影响。

1. 锁机制（事务写操作 & 其他事务写操作）

锁机制的基本原理：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源（获得锁、检查锁、释放锁等都需要消耗资源），因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL 中不同的存储引擎支持的锁是不一样的，例如 MyISAM 只支持表锁，而 InnoDB 同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

MySQL 查看 InnoDB 中锁的情况：

select * from table_name.innodb_locks;
1

2. MVCC（事务写操作 & 其他事务读操作）

在并发状态下，事务的写操作可能导致其他事务的读操作出现：1）脏读、2）不可重复读、3）幻读。

MVCC（多版本并发控制）：在同一时刻，不同的事务读取到的数据可能是不同的（即多版本）。InnoDB 实现 MVCC 主要基于以下技术：

1. 隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。
2. 基于 undo log 的版本链：前面说到每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。
3. ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本；但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务 id 与 trx_sys 快照比较，从而判断数据对该 ReadView 是否可见，即对事务 A 是否可见。
   

4）持久性（Durability）：指一个事务一旦被提交了，不会因为宕机等原因导致数据丢失。实现主要基于 redo log。

【MySQL 保证持久性的实现原理】

为什么会有持久性的要求？ 因为 InnoDB 作为 MySQL 的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘 IO，效率会很低。InnoDB 提供了缓存（Buffer Pool），Buffer Pool 中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从 Buffer Pool 中读取，如果 Buffer Pool 中没有，则从磁盘读取后放入 Buffer Pool；当向数据库写入数据时，也会首先写入 Buffer Pool，Buffer Pool 中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但如果 MySQL 宕机，而此时 Buffer Pool 中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

解决方案：MySQL 的 InnoDB 存储引擎提供事务日志 redo log

当数据修改时，除了修改 Buffer Pool 中的数据，还会在 redo log 记录这次操作；当事务提交时，会调用 fsync 接口对 redo log 进行刷盘。如果 MySQL 宕机，重启时可以读取 redo log 中的数据，对数据库进行恢复。redo log 采用的是 WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到 Buffer Pool，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求。

参考：深入学习MySQL事务：ACID特性的实现原理

1.2 数据库的并发与隔离

1.2.1 并发带来的问题

并发操作破坏了事务的隔离性（ACID 中的 I），导致数据不一致：

• 丢失修改（lost update）：事务 T1 和 T2 先后读入同一数据并修改，T1 先提交，T2 后提交，T2 提交的结果将覆盖 T1 的结果，导致 T1 的修改被丢失。
• 不可重复读（non-repeatable read）：事务 T1 读入某一数据后（未进行操作），T2 对同一数据进行了操作（修改、插入、删除），当 T1 再次读入该数据时，得到与前一次不同的值。
• 读“脏”数据（dirty read）：事务 T1 修改某一数据后将其写入磁盘，事务 T2 读入修改后的数据，此时 T1 由于某种原因被撤销（ROLLBACK），被 T1 修改的数据恢复原值，导致 T2 读入的数据与数据库中的数据不一致，是脏数据。
• 幻读：T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和第一次读取的结果不同。

脏读和不可重复读的区别：脏读读到的是其他事务未提交的数据，不可重复读读到的是其他事物已经提交的数据。
不可重复读和幻读的区别：不可重复读是读的数据变了，幻读是读的数据的行数变了。

1.2.2 数据库的隔离级别

一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。

• Read Uncommitted（未提交读）：最低的隔离级别，事务中的修改即使没有提交，对其他事务也是可见的。可以提高并发性能，但会导致脏读问题。
• Read Committed（提交读）：一个事务只能读取其他事务已经提交的修改，也就是一个事务所做的修改在提交之前对其他事务是不可见的。该级别可以解决脏读问题，但不能避免不可重复读。
• Repeated Read（可重复读）：在一个事务中，对同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作。可以解决脏读、不可重复读，但不能避免幻读。
• Serialization（可串行化）：最高的隔离级别，强制事务串行化执行，可以解决并发事务的所有问题，但牺牲了系统的并发性。

InnoDB 默认的隔离级别是可重复读（RR），按理说 RR 无法避免幻读问题，但是 InnoDB 实现的 RR 避免了幻读问题。

隔离级别与读问题的关系如下：

在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是已提交读（如 Oracle）或可重复读（RR）。

查看隔离级别：

# 查看全局隔离级别
select @@global.tx_isolation;
# 查看本次会话的隔离级别
select @@tx_isolation;
1
2
3
4

2. 数据库三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。通常用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）

总的来说，第一范式的关键词是列（属性）的原子性，第二范式的关键词是不能包含部分依赖，第三范式的关键词是不能包含传递依赖。

2.1 第一范式（1NF）

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

举例，数据库表中的属性 “学历信息” 存储 “硕士，研二” 就不满足原子性，需要继续拆分为属性 “学历” 和 “所在年级”，对应值为 “硕士” 和 “研二”，这样就满足了原子性。

例子来源：关系型数据库设计：三大范式的通俗理解

2.2 第二范式（2NF）

第二范式（2NF）：确保表中的每列都和主键相关。若关系模式 R 满足第一范式，并且 R 的所有非主属性都完全依赖于 R 的每一个候选关键属性，称 R 满足第二范式 。

第二范式在第一范式的基础上更进一步。第二范式需要确保数据库表中的每列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是要在第一范式的基础上 消除非主属性对主属性的部分函数依赖。

举例，联合主键为（订单号，产品号），而非主属性 订单金额 和 订单时间 仅与联合主键的 订单号 有关，也就是部分依赖，故不满足第二范式要求。做出相应的调整，将属性 订单金额 和 订单时间 与主键 订单号 独立出来，单独作为一张表。这样两张表都满足第二范式要求。

例子来源：关系型数据库设计：三大范式的通俗理解

2.3 第三范式（3NF）

第三范式（3NF）：确保每列都和主键直接相关，而不是间接相关。在第二范式基础上，任何非主属性不依赖于其他非主属性，即需要在第二范式的基础上消除传递依赖。

举例，主键为 学号，所有属性都完全依赖于主键，故满足第二范式。但是属性 班主任姓名 和 班主任年龄 是直接依赖于非主属性 班主任姓名，间接依赖于主属性 学号，所以不满足第三范式。做出相应的调整，将被直接依赖的非主属性和依赖于它的非主属性独立出来，单独作为一张表。这样两张表都满足第三范式要求。

例子来源：关系型数据库设计：三大范式的通俗理解

3. 数据库索引

数据库索引是数据库管理系统中对数据库表的一列或多列的值进行排序的 数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+ 树。

创建索引可以大大提高系统的性能：

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

增加过多索引不利的方面：一是 增加了数据库的存储空间，二是 在插入和修改数据时要花费较多的时间（因为索引也要随之变动）。

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

应考虑在这些列上创建索引：

• 在经常需要搜索的列上创建，可以加快搜索速度
• 在作为主键的列上创建
• 在经常用在连接的列上创建，这些列主要是外键，可以加快连接速度
• 在经常需要根据范围进行搜索的列上创建
• 在经常需要排序的列上创建
• 在经常使用在 WHERE 子句中的列上创建，加快条件判断速度

不应该创建索引的列：

• 在查询中很少使用到的列
• 只有很少数据值的列
• 当修改操作远远大于检索操作时，不应该创建索引，创建索引会降低修改性能

3.1 MySQL 索引类型

MySQL 索引分为单列索引和组合索引：

• 单列索引：普通索引（最基本的索引）、唯一索引（与普通索引类似，不同的是索引列的值必须唯一，允许有null）、主键索引（在唯一索引的基础上，不允许有空值）。一个单列索引只包含一个列，一个表可以有多个单列索引。
• 组合索引（联合/复合索引）：一个组合索引包含两个或两个以上的列。

# 创建普通索引
CREATE INDEX account_Index ON `award`(`account`);
ALTER TABLE award ADD INDEX account_Index(`account`);

# 创建唯一索引
CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

# 创建联合索引
CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);
1
2
3
4
5
6
7
8
9

在 Student 表上按 Sno 降序建唯一索引 Stusno：

CREATE UNIQUE INDEX Stusno ON Student(Sno desc);
1

删除 Student 表的 Stusno 索引：

ALTER TABLE Student DROP INDEX Stusno;
1

索引的最左匹配原则：像 (A, B, C) 这样的联合索引，mysql 会从左向右依次匹配，所以把最常用的、筛选数据最多的字段放在左侧。

参考：
数据库的五种索引类型
聚集索引与非聚集索引的总结

3.2 聚集索引和非聚集索引

聚集索引：在数据库里面，所有行数都会按照主键索引进行排序
非聚集索引：就是给普通字段加上索引

聚集和非聚集的区别：表记录的排列顺序和与索引的排列顺序是否一致。

• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
• 聚集索引查询数据速度快，插入数据速度慢；非聚集索引反之（这个道理和数组、链表的查询、增删一样）

聚集索引和非聚集索引的实现：都采用 B+ 树实现。

参考：聚集索引和非聚集索引的区别

3.3 B 树

m 阶 B 树特点：

• 根结点至少有两个子女
• 每个中间节点都包含 k-1 个元素和 k 个孩子，其中 m/2 <= k <= m
• 每一个叶子节点都包含 k-1 个元素，其中 m/2 <= k <= m
• 每个节点中的元素从小到大排列，节点当中 k-1 个元素正好是 k 个孩子包含的元素的值域分划
• 所有的叶子结点都位于同一层

3.4 B+ 树

B+ 树是基于 B 树的一种变体，有着比 B 树更高的查询性能。B+ 树通常用于数据库和操作系统的文件系统中。B+ 树的特点是 能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。

m 阶 B+ 树特点：

• 有 k 个子树的中间节点包含有 k 个元素（B 树中是 k-1 个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点
• 所有的叶子结点中包含了全部元素的信息及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接
• 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素
  

1. 每一个父节点的元素都出现在子节点中，且是子节点的最大（或最小）元素。
2. 根节点的最大元素就是整个 B+ 树的最大元素，以后无论怎样插入删除，始终要保持最大元素在根节点中。
3. 由于父节点元素都出现在子节点，因此所有叶子节点包含了全部元素信息。
4. 每一个叶子节点都带有指向下一个节点的指针，形成了一个有序链表。

B+ 树的优点： 1）查询时的 IO 次数更少；2）查找性能稳定；3）便于范围查询。

3.5 B+ 树与 B 树比较

B+ 树与 B 树的区别：

• B 树中无论中间节点还是叶子节点都带有卫星数据，而 B+ 树中只有叶子节点带有卫星数据，其余中间节点仅有索引，没有任何数据关联，所以同样大小的磁盘页可以容纳更多的节点元素，在数据量相同的情况下，B+ 树比 B 树更加 “矮胖”，查询时的 IO 次数也更少
• B+ 树查询必须查找到叶子节点，而 B 树只要找到匹配元素即可，不管匹配元素处于中间节点还是叶子节点，因此 B 树查找性能不稳定（最好情况是只查到了根节点，最坏情况是查到了叶子节点），而 B+ 树的每一次查找都是稳定的
• 范围查询时，B 树需要依靠繁琐的中序遍历，而 B+ 树的所有叶子节点形成有序链表，只需要在链表上做遍历即可

参考：
B+ 树总结
B+ 树图文详解

4. 数据库锁

4.1 乐观锁和悲观锁

一般可以分为两种锁，一个是悲观锁，一个是乐观锁，悲观锁就是数据库的锁机制，乐观锁一般是指用户自己实现的一种锁机制。

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁，读锁，写锁等，都是在做操作之前先上锁。Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现。

乐观锁： 顾名思义，就是很乐观，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改。

悲观锁按使用性质划分：

• 1）共享锁（Share locks，S 锁）：也称读锁，事务 A 对对象 T 加 S 锁，其他事务也只能对 T 加 S 锁，多个事务可以同时读，但不能有写操作，直到所有事务都释放对 T 的 S 锁。
• 2）排它锁（Exclusivelocks，X 锁）：也称写锁，事务 A 对对象 T 加 X 锁后，其他事务不能对 T 加任何锁，只有事务 A 可以读写对象 T 直到 A 释放对 T 的 X 锁。
• 3）更新锁（U 锁）：用来预定要对此对象施加 X 锁，它允许其他事务读，但不允许再施加 U 锁或 X 锁；当被读取的对象将要被更新时，则升级为 X 锁，U 锁主要是用来防止死锁的。

悲观锁按照作用划分：

• 1）行锁：锁的作用范围是行级别，数据库能够确定哪些行需要锁的情况下就使用行锁，如果不知道会影响哪些行的时候就会使用表锁。
• 2）表锁：锁的作用范围是整张表。

【两种锁使用场景】 两种锁各有优缺点，不可认为一种好于另一种。乐观锁适用于写比较少的情况下（多读场景），这样可以省去锁的开销，加大系统吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行 retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁比较合适。

4.2 两段锁协议

两段锁协议（TwoPhase Locking，2PL）指所有事务的执行分为两个阶段：生长阶段（加锁阶段）和衰退阶段（解锁阶段）。

1. 加锁阶段：在该阶段可以进行加锁操作，不能进行解锁操作。

在对任何数据进行读操作之前要申请并获得 S 锁，在进行写操作之前要申请并获得 X 锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。

2. 解锁阶段：在该阶段可以进行解锁操作，不能进行加锁操作。

当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

例如：事务 T 遵循两段锁协议，其封锁协议为：

BEGIN TRANSACTION;
LOCK(A);
READ A; 
A := A + 100; 
WRITE A; 
LOCK(B); 
UNLOCK(A); 
READ(B), UNLOCK(B);
COMMIT;     
1
2
3
4
5
6
7
8
9

若并发执行的所有事务均遵守两段锁协议，则对这些并行事务的任何并行调度策略都可以串行化。注意该条件为 充分条件而非必要条件（即可串行化的多个事务不一定均遵守两段锁协议，但均遵守两段锁协议的事务一定可串行化）。

两段锁协议与一次封锁法的区别：

一次封锁法要求事务必须一次对所有要使用到的数据项进行加锁，否则不能继续运行。显然，一次封锁法符合两段锁协议，但是两段锁协议并不要求一次就要对所有需要用到的数据项进行加锁，因此遵守两段锁协议的事务有可能死锁。

4.3 封锁

事务 T 在对某个数据对象，如表、记录等操作之前，需要先向系统发出加锁请求，在事务 T 释放它的锁之前，其他事务不能对此数据进行修改。

1）排他锁（exclusive locks，X 锁）

排他锁又称为写锁。事务 T 对数据对象 A 加上 X 锁后，允许事务 T 对 A 进行读取和修改，其他事务不能对 A 加任何类型的锁，直到 T 释放它的 X 锁为止。从而保证了其他事务在 T 释放 A 上的 X 锁之前不能对 A 进行读取和修改。

2）共享锁（share locks，S 锁）

共享锁又称为读锁。事务 T 对数据对象 A 加上 S 锁后，事务 T 能对 A 进行读取，但不能修改。其他事务可以继续对 A 加上 S 锁，但是不能加 X 锁，直到 T 释放它的 S 锁为止。从而保证了其他事务可以读取 A，但是在 T 释放 A 上的 S 锁之前不能对 A 进行修改。

另外，同一事务可以不断对某个数据对象加锁，不需要等锁的释放。

封锁协议

1）一级封锁协议

定义：事务 T 在修改数据 A 前，必须先对 A 加 X 锁，直到事务结束才释放。因此 X 锁又被称为写锁，意为修改。

目的：解决 “丢失修改” 的不一致问题，即在下一个事务操作前，先把上一个事务的修改操作结束。

实现：事务 T1 的 X 锁 → 事务 T2 的 X 锁。事务 T1 对数据对象 A 加上 X 锁后，只有等事务 T1（修改）结束，释放 X 锁，事务 T2 才能对 A 加锁并进行操作（读取、修改）。

2）二级封锁协议

定义：在一级封锁协议的基础上，增加事务 T 在读取数据 A 前，必须先对 A 加 S 锁，读完后即可释放。因此 S 锁又被称为读锁，意为读取。

目的：在一级封锁协议的基础上，进一步解决 “读‘脏’数据” 的不一致问题，即在下一个事务读取前，先等上一个事务的撤销操作结束。

实现：事务 T1 的 X 锁→事务 T2 的 S 锁（读取数据后释放 S 锁）。事务 T1 对数据对象 A 加上 X 锁后，只有等事务 T1（修改、撤销）结束，释放 X 锁，事务T2才能对 A 加锁并进行操作（读取）。

3）三级封锁协议

定义：在一级封锁协议的基础上，增加事务 T 在读取数据 A 前，必须先对 A 加 S 锁，直到事务结束才释放（与二级的区别）。

目的：在二级封锁协议的基础上，进一步解决 “不可重复读” 的不一致问题，即在下一个事务修改前，先等上一个事务的重复读操作结束。

实现：事务 T1 的 S 锁（事务结束后释放 S 锁）→事务 T2 的X锁。事务 T1 对数据对象 A 加上 S 锁后，只有等事务 T1（重复读取）结束，释放 S 锁，事务 T2 才能对 A 加 X 锁并进行操作（修改）。

封锁导致的问题

和操作系统一样，封锁的方法可能引起活锁和死锁等问题。

1）活锁

根据事务的优先级顺序，可能会出现某个事务永远在等待封锁的情况，即事务 T1 封锁了数据对象 A 后，T2、T3 陆续请求封锁，但是 T1 释放锁后，系统优先批准了 T3 的请求，T2 仍然在等待。

最简单的解决方法就是先来先服务（FCFS），不考虑事务的优先级。

2）死锁

事务 T1 封锁了数据 A，事务 T2 封锁了数据 B，然后 T1 请求封锁 B，同时 T2 也请求封锁 A，但因为两个事务的请求都需要等待对方释放锁，这样就出现了永远在等待对方的死锁。

在数据库中，解决死锁问题主要有两类方法：预防和诊断解除。

1）预防：

1. 一次封锁法：每个事务一次将所有要使用的数据加锁，否则事务不能继续执行。缺点：阻碍了其他事务对数据的利用，从而降低了系统的并发度。

2. 顺序封锁法：预先对数据规定一个封锁顺序，所有事务都按照这个顺序加锁，保证 “先到先得”。缺点：需要处理的信息太多，开销大，成本高。

2）诊断与解除：

1. 超时法：某个事务的等待时间超过规定时间，则系统判定为死锁。缺点：规定时间过短，可能误判死锁；规定时间过长，可能不能及时发现死锁。

2. 等待图法：并发控制子系统周期性地生成事务等待图，动态地反映所有事务的等待情况。如果发现图中存在回路，则表示系统中出现了死锁。解除方法：通常撤销一个处理代价最小的事务，释放此事务持有的所有锁，使其他事务得以继续运行下去。

数据库 E-R 图

实体-联系图（Entity Relationship Diagram，E-R）用于表示实体、属性和联系，是表示概念关系模型的一种方式。

酒店管理系统E-R图：

（实体 - 矩形框，实体属性 - 椭圆框，实体间的关系 - 菱形框）

视图

视图是从一个或几个基本表中导出的表。与基本表不同的是，视图是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存在基本表中。

建立学生表 Student 的视图：

CREATE VIEW CS_Student
AS
SELECT sno, sname
FROM Student
WHERE Sdept = 'CS';
1
2
3
4
5

删除视图：

DROP VIEW CS_Student;
1

SQL 语句

SQL 语句包括 数据查询（select）、数据定义（create、drop、alter）、数据操纵（insert、update、delete）、数据控制（grant、revoke）。

1）定义基本表：

CREATE TABLE <表名> (<列名> <数据类型> [列级完整性约束条件])
1

2）修改基本表：

ALTER TABLE <表名>
[ADD <新列名> <数据类型> [完整性约束]]
[DROP <完整性约束名>]
[MODIFY COLUMN <列名> <数据类型>]
1
2
3
4

ADD 用于添加新列，DROP 用于删除指定的完整性约束条件，MODIFY COLUMN 用于修改原有的列定义。

3）删除基本表：

DROP TABLE <表名> [RESTRICT | CASCADE]
1

选择 RESTRICT，则删除时有限制条件：待删除的基本表不能被其他表的约束所引用，不能有视图、触发器、存储过程或函数等。如果存在这些依赖该表的对象，则不能被删除。
选择 CASCADE，则该表的删除没有限制，且删除时相关的依赖对象都会被一起删除。

4）数据查询：

SELECT [ALL | DISTINCT] <目标列表达式>
FROM <表名 | 视图名>
[WHERE <条件表达式>]
[GROUP BY <列名 1> [HABING <条件表达式>]]
[ORDER BY <列名 2> [ASC | DESC]]
1
2
3
4
5

根据 WHERE 条件表达式，从 FROM 指定的基本表或视图中找出满足条件的元组，再按 SELECT 中的目标列表达式，选出元组中的对应属性值形成一个结果表。
GROUP BY，结果会按照 <列名1> 的值进行分组。
ORDER BY，结果会按照 <列名2> 的值进行升序或降序排序。

5）聚合函数： 包括 count、sum、avg、max 和 min。

# 计算表中的记录数（行数）
SELECT count(*) FROM table_name

# 计算表中某列的数据的合计值，若有空值则忽略改行
SELECT sum(column_name) FROM table_name

# 计算表中某列的数据的平均值，若有空值则忽略改行
SELECT avg(column_name) FROM table_name

# 计算表中某列的数据的最大值，若有空值则忽略改行
SELECT max(column_name) FROM table_name

# 计算表中某列的数据的最小值，若有空值则忽略改行
SELECT min(column_name) FROM table_name

# 计算表中某列数据的种类数
SELECT count(distinct column_name) FROM table_name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

6）插入元组： 将新元组插入到指定表中。

INSERT INTO table(field1, field2...)
VALUES(value1, value2);
1
2

7）修改数据： 修改指定表中满足 WHERE 条件的元组。

UPDATE table1
SET field1 = value1, field2 = value2
WHERE 范围;
1
2
3

8）删除数据： 删除满足 WHERE 条件的所有元组，如果省略 WHERE，表示删除表中所有元组，但表仍存在。

DELETE
FROM table1
WHERE 范围;
1
2
3

9）group by 子句：一般与聚合函数一起使用，目的是对数据进行分类汇总。

假设有数据库表 Stu：

SELECT class, count(name) AS 人数 FROM Stu GROUP BY class
1

10）order by 子句：可以对结果进行排序，默认为升序（asc），降序为（desc）

select column1,count(column2)
from table_name
group by column1
having count(*)>10
order by columns,count(column2) desc
1
2
3
4
5

11）having 子句：筛选满足条件的组，即在分组之后进行过滤。

select column1,count(column2)
from table_name
group by column1
having count(*)>10
1
2
3
4

参考资料：
数据库索引的实现原理

---

并发调度的可串行性

可串行性是并发事务正确调度的准则。当且仅当多个事务的并发执行结果，与按某一次序的串行执行结果相同，这种并发调度策略才是可串行化调度，即具有可串行性。

数据库知识整理 - 并发控制（封锁、两段锁协议、意向锁）

数据库引擎

数据库存储引擎是 数据库底层软件组织。在对数据进行访问时，都不是直接读写数据库文件，而是通过数据库引擎去访问数据库文件。数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

MySQL 的常见数据库引擎：

1. InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持，提供行级锁；
2. MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一
3. MEMORY：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问；
4. Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境；
5. Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性；

数据库引擎 InnoDB 与 MyISAM 区别：

• 是否支持事务：InnoDB支持事务，MyISAM不支持
• 支持锁的级别不同：InnoDB 支持行锁，MyISAM 只支持表锁
• 是否支持外键：InnoDB 支持外键，MyISAM 不支持
• 存放索引的方式：InnoDB 是聚集索引，MyISAM是非聚集索引
• 查询具体行数的差异：InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描，MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
• 是否支持全文索引：InnoDB不支持全文索引，MyISAM支持全文索引，查询效率上 MyISAM 要高于 InnoDB

如何选择存储引擎：

• 是否要支持事务，如果要请选择 innodb，如果不需要可以考虑 MyISAM
• 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB
• 系统崩溃后，MyISAM恢复起来更困难，能否接受
• MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，如果你不知道用什么，那就用InnoDB，至少不会差

mysql的数据库引擎

超键，候选键、主键、外键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对存储数据对象予以唯一和完整标示的数据列和属性组合。一个数据列只能有一个主键，且主键的取值不能为空（Null）。
外键：在一个表中存在另一个表的主键称为此表的外键。

什么是视图，以及视图的使用场景

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增、改、查操作，视图通常是由一个表或者多个表的行或列的子集，对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
1）只暴露部分字段给访问者，所以就建一个虚表，就是视图。
2）查询的数据来源于不同的表，而查询者希望一统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接在视图中获取数据，不必考虑数据来源于不同的表所带来的差异。

列举几种表的连接方式及区别

内连接、子连接、外连接、（左、右、全）、交叉连接
内连接：只有两个元素表相匹配的才能在结果集中显示。
外连接：左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 全外连接：连接的表中不匹配的数据全部会显示出来。
交叉连接：笛卡尔效应，显示的结果是连接表数的乘积。

什么是索引？索引有那几种?
索引是用来快速的寻找那些具有特定值的记录，所有的MySQL索引都以B+树的形式保存。如果没有索引，执行查询的时候MySQl必须从第一个记录开始扫面整个表中的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。虽然索引大大提高查询速度，同时会降低更新表的速度，如对表进行insert、update、delete。因为更新表时，不仅要保存数据，还要保存索引文件。建立索引会占用磁盘空间的索引文件，一般情况这个问题不太严重，但如果在一个大表上建立多种组合，索引文件会增长很快。索引只是提高效率的一种因素，如果有大数据量的表，就要花时间建立最优秀的索引，或优化查询语句。
索引的类型：
1）普通索引：这是最基本的索引类型，而且它没有唯一性之类的限制，即允许该列有重复的值
2）唯一性索引： 这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一
3）主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时自动创建主键索引。
4）组合索引：指多个自字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀集合。
5）全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

在数据库中查询语句速度很慢，如何优化？

1.建索引 2.减少表之间的关联 3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面 4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据 5.尽量用PreparedStatement来查询，不要用Statement。

drop、delete与truncate的区别

1）delete和truncate只删除标的数据不删除的结构
2）速度，一般来说：drop>truncate> delete
3)delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
4)不在需要一张表的时候，用drop（删除标的结构）
5）想删除部分数据行的时候。用delete，并且带上where字句（删除部分数据）
6）保留表而删除所有数据的时候用turncate（删除全部数据）

char和varchar的区别

Char的长度是固定的，而varchar的长度是可以变化的，比如，存储字符串“abc”对于char(20)，表示你存储的字符将占20个字节，包含17个空，而同样的varchar（20）只占了3个字节，20只是最大值，当你存储的字符小于20时，按实际长度存储。 char的效率要被varchar2的效率高。

order by与group by的区别

order by排序查询、asc升序、desc降序。group by分组查询，having 只能用于group by子句、作用于组内，having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。

---

参考：
备战秋招——数据库