本文已汇总与个人的Wiki库，欢迎前来交流学习 👉 CS-Wiki

---

一、数据库基础知识

1. 为什么要使用数据库

数据保存在内存

优点： 存取速度快

缺点： 数据不能永久保存

数据保存在文件

优点： 数据永久保存

缺点：

• 速度比内存操作慢，频繁的IO操作。
• 查询数据不方便

数据保存在数据库

• 数据永久保存
• 使用SQL语句，查询方便效率高。
• 管理数据方便

2. 数据库的三级模式结构

• 内模式：也称存储模式。数据物理结构和存储方式的描述，是数据在数据库中的组织方式

• 模式：也称逻辑模式。是数据库中全体数据成员的逻辑结构和特征的描述

• 外模式：也称用户模式。是数据库用户能看见和使用的局部数据的逻辑结构和特征的描述

  

3. 什么是 SQL

结构化查询语言(Structured Query Language) 简称 SQL，是一种数据库查询语言。

作用：用于存取数据、查询、更新和管理关系数据库系统。

4. 什么是MySQL?

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

5. MySql 有哪些数据类型

varchar 和 char 的区别

• CHAR是定长的，根据定义的字符串长度分配足够的空间。CHAR存储的内容超出设置的长度时，内容会被截断。
• VARCHAR用于存储可变长字符串，它比定长类型更节省空间。VARCHAR存储的内容超出设置的长度时，内容同样会被截断。
• 对于经常变更的数据来说，CHAR 比 VARCHAR 更好，因为 CHAR 不容易产生碎片。
• 对于非常短的列，CHAR 比 VARCHAR 在存储空间上更有效率。

6. 超键、候选键、主键、外键

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

7. 什么是数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典中的相应信息

8. 关系型数据库和非关系型数据库比较

关系型数据库：

• 采用了关系模型来组织数据的数据库，以行和列形式存储数据，以便于用户理解。
• 通用的 SQL 语言使得操作关系型数据库非常方便。
• 关系型数据库遵循 ACID 原则。
• 常见的关系型数据库比如 MySQL，Oracle

关系型数据库存在的问题：

• 网站的用户并发性非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘 I/O 是一个很大的瓶颈
• 网站每天产生的数据量是巨大的，对于关系型数据库来说，在一张包含海量数据的表中查询，效率是非常低的。因此，关系型数据不适合持久存储海量数据
• 很难进行横向扩展（增加服务器），也就是说想要提高数据处理能力，要使用性能更好的计算机（纵向扩展）
• 性能欠佳：导致关系型数据库性能欠佳的最主要原因就是多表的关联查询，为了保证数据库的ACID特性，必须尽量按照范式要求设计数据库，关系数据库中的表存储的往往是一个固定的、格式化的数据结构

而非关系型数据库就可以很好的解决关系型数据库很难解决的大数据问题

非关系型数据库 NoSQL：

• 非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。
• 支持分布式存储，容易进行横向扩展
• 不遵循 ACID 特性（不提供对事务的处理）
• 常见的非关系型数据库比如 Redis、MongoDB、Elasticsearch

9. 数据库连接池

① 概述

数据库连接池是负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是每次访问数据库的时候都需要重新建立一次连接。

② 为什么要使用连接池

数据库连接是一种关键的有限的昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出。 一个数据库连接对象均对应一个物理数据库连接，每次操作都打开一个物理连接，使用完都关闭连接，这样造成系统的性能低下。

数据库连接池的解决方案是在应用程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由应用程序动态地对池中的连接进行申请、使用和释放。对于多于连接池中连接数的并发请求，应该在请求队列中排队等待。并且应用程序可以根据池中连接的使用率，动态增加或减少池中的连接数。

连接池技术尽可能多地重用了消耗内存地资源，大大节省了内存，提高了服务器地服务效率，能够支持更多的客户服务。通过使用连接池，将大大提高程序运行效率，同时，我们可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。

③ 传统的连接机制与连接池运行机制区别

执行一个SQL命令

不使用数据库连接池的步骤：

• TCP建立连接三次握手

• MySQL认证三次握手

• 真正的SQL执行

• MySQL关闭

• TCP四次握手关闭

可以看到，为了执行一条SQL，却多了非常多网络交互，应用需要频繁的创建连接和关闭连接。

使用数据库连接池的步骤：

第一次访问的时候，需要建立连接。 但是之后的访问，均会复用之前创建的连接，直接执行SQL语句。

二、关系数据库，SQL语句

更多详见：

• 【二】关系数据库
• 【三】关系数据库标准语言SQL

1. SQL 语句主要分为哪几类

• 数据定义语言 DDL（Data Definition Language）：CREATE、DROP、ALTER，主要是对表结构、视图、索引等的操作
• 数据查询语言：DQL（Data Query Language）：SELECT
• 数据操纵语言：DML（Data Manipulation Language）：INSERT、DELETE、UPDATE
• 数据控制语言：DCL（Data Control Language）：GRANT、REVOKE、COMMIT、ROLLBACK

2. in 和 exists 的区别

select id from Student
where id exists (select id from SC);

select id from Stduent
where id in (select id from SC);

• in 先进行子查询 select id from SC，再进行外查询 select id from Student

  exists 先执行外查询，再执行子查询

• in 语句是把外表和内表作连接

  而 exists 语句是对外表作循环，每次循环再对内表进行查询

• exists 适合子查询的表比外查询大的查询语句

  用过内表和外表差不多大，那么 in 和 exists 的效率差别不大

3. 什么是内连接、外连接、交叉连接

• 内连接 inner join: 只连接匹配的行（默认）

• 左外连接 left outer join: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

  右外连接 right outer join: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

  全外连接 outer join: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

• 交叉连接 cross join: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

4. MySql 分页 limit

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

• 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

  SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 
  

• 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

  SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. 
  

• 如果只给定一个参数，它表示返回最大的记录行数目：

  SELECT * FROM table LIMIT 5; //检索前 5 个记录行 
  

• limit 和 offset 连用

  select * from table limit 2 offset 1; //跳过前面1条数据，检索2条数据
  

5. 什么是视图，视图的优缺点

# 建立计算机系学生视图，并要求插入/修改/删除操作时，保证该视图只有计算机系学生
create view CS_Student
as
select *
from Student
where Sdept = 'CS'
with check option;

视图是从一个或几个基本表（或视图）导出的表。

它与基本表不同，是一个虚表。

数据库中只存放视图的定义，不存放视图对应的数据，这些数据任然存放在原来的基本表中。所以一旦基本表中的数据变化，那么视图中的数据也会相应变化。

其实视图就好像一个窗口，透过它可以看到自己想要看到的数据及其变化

视图的优点：

• 视图能够简化用户的操作

• 视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性

• 适当利用视图可以更清晰的表达查询

• 视图使用户能以多种角度看待同一数据

• 视图对重构数据库提供了一定数据的逻辑独立性

  数据的逻辑独立性是指当数据库数据库构造时，如增加新的关系或对原来关系增加新的字段等，用户的应用程序不会受到影响

视图的缺点：

• 查询视图时，必须把对视图的查询转化为对基本表的查询。如果这个视图是由一个复杂的多表查询所定义，那么即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
• 当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改，如果视图设计多个表的话，由于完整性约束，可能是无法修改的

三、数据库安全性和完整性

1. 安全性和完整性的区别

**数据库的安全性：**保护数据库防止不合法使用造成的数据泄露、更改或破坏

**数据库的完整性：**防止数据库中存在不符合语义 / 不正确的数据信息

2. 数据库安全性控制机制

• 用户身份鉴别

• 存取控制

  • GRANT 授予权限

    # 把查询Student表的权限授给用户User1，并允许他将此权限授予其他用户
    grant select
    on table Student
    to User1
    with grant option;
    

  • REVOKE 收回权限

    # 收回用户User1对Student表的查询权限，并级联收回User1授予的其他用户的该权限
    revoke select
    on table Student
    from User1 CASCADE;
    

• 视图机制 VIEW

• 审计 / 跟踪审查

  • AUDIT 设置审计功能

    # 对修改Student表结构和修改Student表数据的操作进行审计
    AUDIT alter,update
    on Student;
    

  • NOAUDIT 取消审计功能

    # 取消取Student 表的一切审计
    noaudit alter,update
    on Student;
    

• 数据加密

3. 完整性约束

• 实体完整性约束

  主键必须存在且不为空

  primary key (Sno,Cno)
  

• 参照完整性约束

  外键要么不存在，要么存在且不为空

  foreign key(Sno) references Student(Sno)
  

• 用户自定义完整性约束

  • NOT NULL：字段的内容不能为空

  • UNIQUE：字段内容不能重复，一个表允许有多个 Unique 约束

  • PRIMARY KEY：主键

  • FOREIGN KEY：外键

  • CHECK：控制字段的值范围

    Ssex char(2) check(Ssex in('男','女')), //性别属性只能取男或女
    

4. 触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程，可用于保证数据库的完整性。

触发器是指一段代码，当触发某个事件时，自动执行这些代码。

触发器仅限于数据库中增 删 改三种操作

在MySQL数据库中有如下六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

触发器示例：cource表中删除一个元组，若该元组的主键是sc表中的外键，则卷回删除该元组的操作。

CREATE TRIGGER mytrigger 
BEFORE DELETE ON cource  
referencing old as o 
for each row
when (exists (select * from sc
             where cno = o.cno))
ROLLBACK;

四、关系数据库设计理论

1. 关系模式设计中的四种数据语义问题 / 异常

• 数据冗余：比如选课表（教师，学生）一个教师需对选他课的所有学生都重复一次

  教师A 学生A

  教师A 学生B

  教师A 学生C

• 更新异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。比如修改了第一个元组的教师A的工号，但是下面元组教师A的信息并没有得到修改

• 删除异常：删除一个信息，那么也会丢失其它信息。比如删除第一个元组，那么学生A的信息也会丢失

• 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

2. 函数依赖 - 三大范式 + BCNF

函数依赖：

比如关系模式 S(Sno, Cno, Name, Grade)

假设 主键是（Sno，Cno)，有如下关系模式

• (Sno, Cno) ——> Grade：Grade 完全依赖于主键

• Sno ——> Name：Name 部分依赖于主键

范式理论：（解决四种数据语义问题 / 四种异常）

• **第一范式 1 NF：**属性不可分。可以认为任何表都属于第一范式，因为每个表的最小单位为表中的各个属性。

• **第二范式 2 NF：**在满足 1 NF 的条件下，消除非主属性对主键的部分函数依赖

• **第三范式 3 NF：**在满足 2 NF 的条件下，消除非主属性对主键的传递函数依赖

• **修正的第三范式 BCNF：**在满足第二范式的条件下，消除所有属性对主属性的传递依赖。即如果一个属性/属性组 A 决定其他属性/属性组B，则 A 必须包含主键

  满足 BCNF 则一定满足 3NF，反之不一定

3. E-R 图

实体之间的联系：

• 1：1 关系：比如一个年级对应一个班长
• 1：n 关系：比如一个老师教授多门课程
• n：n 关系：比如一门课程由若干个学生选修，一个学生可选修若干门课程

E-R 图基本表示方法：

• 实体型用矩形表示
• 属性用椭圆表示
• 联系用菱形表示

五、查询处理和优化

1. 查询处理的具体流程

• 查询分析

  对 sql 语句进行扫描，词法分析+语法分析，判断查询语句是否符合 SQL 语法规则

• 查询检查

  语义检查，即检查数据库对象，如关系名、属性名是否存在且有效

• 查询优化

  优化器选择一个高效执行的查询处理策略，以达到最好的查询优化效果

• 查询执行

  代码生成器生成执行这个查询计划的代码，然后加以执行，并返回查询结果

2. 查询优化的方法

• 代数优化：对关系代数式的运算顺序进行优化
  • 选择操作尽量先做；
  • 若投影操作和选择操作是对同一个关系的，则投影和选择同时进行；
  • 把投影操作和他前后的双目运算符结合起来
  • 把选择操作和他前面的笛卡尔积结合起来形成连接操作（因为连接操作往往快于笛卡尔积）
  • 找出公共子表达式（比如定义视图的表达式就是公共子表达式）
• 物理优化：选择高效的操作算法或者存取路径

六、事务处理 - 数据库恢复技术

1. 什么是数据库事务？

事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。

一个程序包含多个事务。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。

万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

2. 事务的 ACID 特性

• 原子性（Atomicity） 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
• 一致性（Consistency） 数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。 例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的.
• 隔离性（Isolation） 一个事务的执行不能被其他事务干扰，即一个事务的内部操作即使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰
• 持久性（Durability） 一旦事务提交，则其所做的修改将会永远保存到数据库中。接下来的操作和故障不应该对其执行结果有任何影响

3. 事务的ACID特性遭到破坏的因素

• 多个事务并发执行，互相干扰
• 事务在运行过程中被强行终止

4. 数据库恢复技术

数据库恢复技术就是把数据库从错误状态恢复到某一已知的正确状态。

恢复的基本原理十分简单。可以用一个词来概括，冗余。就是说数据库中任何一部分被破坏或者不正确的数据都可以根据存储在系统别处的冗余数据来重建。

• 数据转储：数据转储就是管理员定期的将整个数据库复制到磁带、磁盘或其他存储介质上。这些备用的数据称为后备副本 backup

  重装后备副本只能将数据库恢复到转储时的状态，其之后的事务操作都必须重新执行一遍才能恢复到故障发生时的状态。

• 登记日志文件：日志文件中需要登记的内容包括：

  • 各个事务的开始标记
  • 各个事务的结束标记
  • 各个事务的更新操作

  登记日志文件时必须遵循两条原则

  • 登记的次序必须严格按照并发事务执行的时间次序
  • 必须先写日志文件，后进行数据库操作

5. 数据库如何保证事务的 ACID 特性

• 原子性实现原理 - Undo Log

  为了实现原子性，需要通过日志：将所有对数据更新操作都写入日志，如果一个事务中的一部分已经操作成功，但以后的操作由于断电/系统崩溃/其他软硬件错误或者用户提交了rollback 导致无法进行，则通过回溯日志，将已经执行成功的操作撤销 undo，从而达到全部操作失败的目的，使得数据库恢复到一致性的状态，可以继续被使用。

• 持久性实现原理 - Redo Log

  和Undo Log 相反，Redo（重做） Log 记录的是新数据的备份。在事务提交前，只是将Redo Log 持久化即可，不需要数据持久化。当系统崩溃时，虽然数据没有持久化，但Redo Log 已经持久化了。系统可以根据Redo Log 将数据更新到最新的状态。

• 隔离性实现原理 - 锁

  当然，保证事务的隔离性，即并发控制不止可用封锁协议，还有时间戳、多版本控制等等。

七、事务处理 - 并发控制

1. 并发事务带来的问题

• 脏读：事务T1修改了一份数据，还没有提交操作，这时事务T2读取了这份数据，由于某些原因事务T1回滚了他的修改操作使得数据恢复到原来，这样事务T2读取的数据就是脏数据
• 丢失修改：事务T1修改了一份数据，还没有提交操作，这时事务T2也修改了这份数据，这样就导致事务T1 的修改结果丢失了
• 不可重复读：事务 T1 读取一份数据，然后 事务 T2 修改了该数据，事务 T1 再一次读取了该数据，这样，事务 T1 两次读取的这份数据的结果是不一样的，称为不可重复读
• 幻读：幻读与不可重读类似，不过幻读侧重于数据的增加和删除，不可重读侧重于修改。事务 T1 读取了一份数据，然后事务 T2 给这个数据添加了几条记录，事务 T1 再一次读取该数据，发现多了一些原本不存在的数据，就好像发生了幻觉一样。

2. 事务的隔离级别

SQL 标准为事务定义了四个不同的隔离级别以满足事务的 ACID 特性，由低到高依次为

• READ-UNCOMMITTED(读取未提交)：

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

• READ-COMMITTED(读取已提交)：

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

• REPEATABLE-READ(可重复读)：

  对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)：

  最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。 （该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。）

这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

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

• Mysql InnoDB 存储引擎 默认采用的 REPEATABLE_READ 可重读 隔离级别
• Oracle 默认采用的 READ_COMMITTED 读取已提交 隔离级别

3. 乐观锁和悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时，不破坏事务的 ACID 特性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

🚨 无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。所以，不应该拿乐观锁、悲观锁和其他的数据库锁等进行对比。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在修改数据之前先锁定，再修改。实现方式：悲观锁主要是 共享锁 或 排他锁（见下文）

• 乐观锁：乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。

  相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

4. 什么是锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

所谓封锁就是事务 T 在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁，在事务 T释放它的锁之前，其他事务不更新此对象。

5. 锁的类别

• 排他锁 / 写锁 X 锁 一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和修改。 加X锁期间其它事务不能对 A 加任何锁。这就保证了其他事务在该事务释放X锁之前不能读取和修改A

• 共享锁 / 读锁 S 锁

  一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。

  **加S锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。**这就保证了其他事务可以读A，但在该事务释放S锁之前不能对A进行修改

• 意向锁（意向锁的引入是为了解决多粒度封锁时，检查封锁冲突效率低下问题）

  • IS 锁

    如果对一个数据对象加 IS 锁，则表示它的后裔结点想要加 S 锁

  • IX 锁

    如果对一个数据对象加 IX 锁，则表示它的后裔结点想要加 X 锁

  • SIX 锁

    如果对一个数据对象加 SIX 锁，则表示对他加 S 锁，再加 IX 锁

6. 多粒度封锁

封锁对象的大小称为 封锁粒度 granularity

MySQL 中提供了两种封锁粒度：行级锁 以及 表级锁 。

• 行级锁 row-level locking 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

  特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 表级锁 table-level locking 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

因此如果在一个系统中同时支持多种封锁粒度供不同的事务选择是比较理想的，这种封锁方法称为 多粒度封锁 multiple granularity locking

首先我们需要知道多粒度树：多粒度树的根节点是整个数据库，表示最大的数据粒度，叶结点表示最小的数据粒度

下图给出了一个三级粒度树

多粒度封锁协议允许多粒度树中的每个结点被独立的加锁，对一个结点加锁意味着这个结点的所有后裔结点都被加以同样的锁

• 显示封锁：应事务的要求直接加到数据对象上的锁
• 隐式封锁：该数据对象没有被独立加锁，继承上级结点的锁

系统检查封锁冲突时不仅要检查显示封锁，还要检查隐式封锁。

显然，这样的检查方法效率很低，为此人们引进了意向锁（见上个问题）

7. 什么是死锁 / 活锁，怎么解决

• **死锁：**两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环等待的现象。

  解决死锁的方法：

  • 死锁预防：破坏死锁产生的条件

    一次封锁法：每个事务必须一次性将所有需要的数据全部加锁，否则不能执行

    顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按照整个顺序进行封锁

  • 死锁检测：

    超时法：如果一个事务的等待时间超过了规定的时限，就认为发生了死锁

    等待图法：如果图中存在回路，则表示系统中出现了死锁

  • 死锁处理：

    一般采取的死锁解除策略是：选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，使其他事务得以继续运行下去。

• **活锁：**事务T1 封锁了数据 1，事务 T2请求封锁数据1，于是 T2等待，T3 也请求封锁数据1，当事务 T1 释放了对数据 1 的封锁后，批准了 T3 的请求，于是 T2 继续等待，T4 请求封锁数据1，当事务 T3 释放了对数据 1 的封锁后，批准了 T4 的请求，于是 T2 继续等待。。。。T2 可能永远等待，即一个事务等待的时间过长而影响事务的执行，这就是活锁。

  解决活锁的方法：先来先服务

8. 封锁协议

• 三级封锁协议：运用 X 锁和 S 锁进行加锁的时候应遵守的协议规则

  • 一级封锁协议

    事务修改数据之前，必须加 X 锁，事务结束释放 X 锁

  • 二级封锁协议

    在一级封锁协议的基础上，事务读取数据前，必须加 S 锁，读取完马上释放 S 锁

  • 三级封锁协议

    在一级封锁协议的基础上，事务读取时间前，必须加 S 锁，直到事务结束才释放 S 锁

• 两段锁协议：所有事务必须分两个阶段对数据项进行加锁和解锁，两端锁协议用来实现并发调度的可串行性，从而保证调度的正确性

  • 扩展阶段（加锁）：在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁
  • 收缩阶段（解锁）：在释放一个封锁的时候，事务不再申请和获得任何其他锁
  

9. 并发调度的可串行性

数据库管理系统对并发事务不同的调度可能会产生不同的结果，只有串行调度才能得到正确的结果

• 可串行化调度：多个事务并发调度的结果和按次序串行执行的结果相同，则这个并发调度就是可串行化调度

  一个给定的并发调度，当且仅当它是可串行化的，才认为是正确调度

• 冲突可串行化调度：冲突操作是指不同的事务对同一个数据的读写操作和写写操作

  不同事务或者同一事务的冲突操作时不能交换的。

  一个调度在保证冲突操作次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度B，则称调度B是冲突可串行化的调度。

  若一个调度是冲突可串行化调度，那么一定是可串行化调度

八、数据库编程

数据库编程就是用编程方法对数据库进行操纵的技术，SQL编程技术可以有效克服SQL语言实现复杂应用方面的不足（没有逻辑控制能力），提高应用系统和数据库管理系统间的互操作性。

1. 什么是存储过程？有哪些优缺点？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

• 存储过程是预编译过的，执行效率高。
• 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
• 安全性高，执行存储过程需要有一定权限的用户。
• 存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

• 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
• 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
• 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
• 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

2. 什么是游标

游标主要用于嵌入式 SQL，一条 SQL 语句可以产生或处理多条记录；而主语言是面向记录的，一组主变量一次只能存放一条记录。为此引入游标来协调这两种不同的处理方式

游标是面向集合的，游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

九、索引

1. 什么是索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B树， B+树和Hash。

索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

2. 索引的创建

create [unique][cluster] index 索引名
on 表名 (列名[次序]，列名[次序]...)

unique 表示此索引的每一个索引值只对应唯一的数据记录

cluster 表示该索引是聚集索引，详见后文

示例：

create unique index sno_index on student(sno);
create unique index cno_index on cource(cno);
# sc表按学号升序和课程号降序建立唯一索引
create unique index sc_index on sc(sno asc, cno desc);

3. 为什么要使用索引，索引的优缺点分析

① 索引的优点

• 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的 。

• 另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

② 索引的缺点

• 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低SQL执行效率。
• 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

③ 索引的使用条件

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

3. B+树原理 ⭐

① 数据结构

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，所有叶子节点位于同一层并且不带任何信息，所有索引信息和相应的数据都存储在分支结点中（既存放 键(key) 也存放 数据(data)）

B+树是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。B+树中叶节点包含关键字的全部信息（键(key) 和数据(data)），其他非叶结点/分支结点仅起索引作用（仅存储key）。并将关键字按照大小顺序排列，相邻叶子结点按照大小顺序相互链接起来（支持顺序查找）。

B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

② 操作

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。（插入删除操作详细见 数据结构【五】查找）

③ 与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：

• （一）更少的查找次数

  平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(logdN)，其中 d 为每个节点的出度。

  红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。

• （二）利用磁盘预读特性

  为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。

  操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

④ 与Hash索引的比较

• Hash索引定位快

  Hash索引指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置，这是B+树所不能比的。

• Hash冲突问题

  不过对于数据库来说这还不算最大的缺点。

• **Hash索引不支持顺序和范围查询是它最大的缺点。**⭐

  试想一种情况:

  SELECT * FROM tb1 WHERE id < 500;
  

  B+树是有序的，在这种范围查询中，优势非常大，直接遍历比500小的叶子节点就够了。

  而Hash索引是根据hash算法来定位的，需要对所有数据进行一遍hash才能得到查询结果，显然是不现实的。这就是Hash最大的缺点了。

4. 索引类型

① 主键索引

数据表的主键列使用的就是主键索引。

一张数据表有且只能有一个主键，并且主键不能为null，不能重复。

在mysql的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

② 辅助索引/二级索引

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

5. 聚集索引与非聚集索引

① 聚集索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引，主键索引属于聚集索引。

举例：

地址	id	username	score
0x01	1	小明	90
0x02	2	小红	80
0x03	3	小华	92
..	..	..	..
0xff	256	小英	70

数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。

聚集索引的优点：

• 聚集索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点：

• 依赖于有序的数据 ：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

② 非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

二级索引属于非聚集索引。

MYISAM引擎的表的.MYI文件包含了表的索引， 该表的索引(B+树)的每个叶子非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚集索引的优点

• 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点

• 跟聚集索引一样，非聚集索引也依赖于有序的数据

• 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

总结来说，对于B+树，如果是聚集索引，那么叶子节点的data将存放数据，如果是非聚集索引，那么data将存放指向数据的指针

6. MySQL主要使用的两种索引

① B+树索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，即聚集索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

② 哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

7. 索引创建原则

• 单列索引

  单列索引即由一列属性组成的索引。

• 联合索引(多列索引)

  联合索引即由多列属性组成索引。

• **最左前缀原则 **⭐

  假设创建的联合索引由三个字段组成:

  ALTER TABLE table ADD INDEX index_name (num,name,age)
  

  那么当查询的条件有为 : num / (num AND name) / (num AND name AND age)时，索引才生效。

  所以在创建联合索引时，尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。

8. 索引创建注意点

① 最左前缀原则

虽然目前较高的Mysql版本好像不遵守最左前缀原则，索引也会生效。 但是我们仍应遵守最左前缀原则，以免版本迭代带来的麻烦。

② 选择合适的字段

• 不为NULL的字段 索引字段的数据应该尽量不为NULL，因为对于数据为NULL的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为NULL，建议使用0,1,true,false这样语义较为清晰的短值或短字符作为替代。

• 被频繁查询的字段 我们创建索引的字段应该是查询操作非常频繁的字段。

• 被作为条件查询的字段 被作为WHERE条件查询的字段，应该被考虑建立索引。

• 被经常频繁用于连接的字段 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

③ 不适合创建索引的字段

• 被频繁更新的字段应该慎重建立索引 虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

• 不被经常查询的字段没有必要建立索引

• 尽可能的考虑建立联合索引而不是单列索引 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

• 注意避免冗余索引 冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引。

  如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

• 考虑在字符串类型的字段上使用前缀索引代替普通索引 前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

9. 索引优化

• 独立的列

  在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

  例如下面的查询不能使用 actor_id 列的索引：

  SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
  

• 多列索引

  在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

  SELECT film_id, actor_ id FROM sakila.film_actor
  WHERE actor_id = 1 AND film_id = 1;
  

• 索引列的顺序

  让选择性最强的索引列放在前面。

  索引的选择性是指：不重复的索引值的个数 / 记录总数 (最大值为 1)。 此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

  例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

  SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
  COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
  COUNT(*)
  FROM payment;
  

  staff_id_selectivity: 0.0001
  customer_id_selectivity: 0.0373
                 COUNT(*): 16049
  

• 前缀索引

  对于BLOB、TEXT 和 VARCHAR类型的列，必须使用前缀索引，只索引开始的部分字符。

  前缀长度的选取需要根据索引选择性来确定。

• 覆盖索引

  如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

  覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

  如主键索引，如果一条SQL需要查询主键，那么正好根据主键索引就可以查到主键。

  再如普通索引，如果一条SQL需要查询name，name字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。那么name的索引就称为覆盖索引

10. 百万级别或以上的数据如何删除

索引是单独存在的文件，所以当我们对数据的增加、修改、删除时，都会产生额外的对索引文件的操作，这些操作需要消耗额外的 I/O，降低增删改的效率。

对于百万级别的数据来说，删除数据的速度和已建立的索引数量成正比。

所以，删除数据之前，我们需要先删除索引，再删除数据。

十、存储引擎 Storage engine

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

1. InnoDB

是 MySQL 默认的事务型存储引擎，提供了对数据库 ACID 特性的支持，并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚集索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

2. MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

3. 比较

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
• 并发：MyISAM 只支持表级锁，而 InnoDB 同时支持行级锁和表级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM 支持压缩表和空间数据索引。