【Sofice小司笔记】1 关系数据库基础，包含关系数据库设计，索引，事务，安全性，完整性，查询处理优化等

数据库管理系统 DataBase Management System / DBMS

DBMS 是位于用户和操作系统之间的一层数据管理软件。数据库管理系统和操作系统一样是计算机的基础软件，也是一个大型复杂的软件系统。

数据管理系统的主要任务就是科学的组织和存储数据，高效的维护和获取数据

主要功能如下：

• 数据定义功能

  DBMS提供数据定义语言（DDL），用户通过它可以方便的对数据库中的数据对象的组成和结构进行定义

• 数据组织，存储和管理

• 数据操作功能

  DBMS 还提供数据操作语言（DML），用户可以使用它操纵数据，实现CRUD

• 数据库的事务管理和运行管理

  保证事务的正确性，保证数据的安全性、完整性、多用户对数据的并发使用以及发生故障后的系统恢复

• 数据库的建立和维护功能

• 其他功能

  

数据库系统的特点

• 数据结构化

  数据库的主要特征之一，也是数据库系统与文件系统的本质区别

• 数据的共享性高、冗余度低且易扩充

• 数据独立性高

• 数据由数据管理系统统一管理和控制

  DBMS提供以下几方面的数据控制功能

  • 数据的安全性保护

  • 数据的完整性检查

  • 并发控制

  • 数据库恢复：DBMS必须具有将数据库从错误状态恢复到某一正确状态的功能

数据库系统的三级模式结构

1. 内模式

   内模式也称存储模式，一个数据库中只有一个内模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

2. 模式

   模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图

3. 外模式

   外模式也称子模式或用户模式，是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

   外模式通常是模式的子集，一个模式可以有多个外模式

   如果不同的用户在应用需求、看待数据的方式、对数据保密的要求等方面存在差异，则其外模式的描述就是不同的。

   外模式是保证数据库安全的一个有力措施。每个用户只能看见和访问对应的外模式中的数据，数据库的其余数据是不可见的。

关系数据库

域

域是一组相同数据类型的值的集合

比如人的年龄 1 - 120 岁

关系

学生（学号，姓名，年级）就是一个关系

• 若关系中的某一属性组的值能够唯一的标识一个元组（其子集是不能的），则称该属性组称为候选码 / 候选键 / 键 / 键码 / 超键。候选码的各个属性称为主属性，不包含在主属性中的其他属性称为非主属性/非码属性
• 如果一个关系有多个候选码，则选定其中一个作为主码 / 主键。常在主键的主属性下加下划线，以标出主键
• 如果关系中的属性或属性组不是本关系的键，而是引用其他关系或本关系的键，则称为外键。由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性

关系代数

1. 笛卡尔积

   笛卡尔积是域上的一种集合运算

   

2. 选择

   选择元组

   查询学生表Student中年龄小于20岁的学生的所有信息

   σ age<20 (Student)
   

3. 投影

   选择列

   查询学生表Student中都有哪些系

   Ⅱ Sdept (Student)
   

   

4. 连接

   从两个关系的笛卡尔积中选取属性间满足一定条件的元组

   • 等值连接：θ 为 = 的连接运算称为等值连接。他是从关系R与S的笛卡尔积中选取A、B 属性值相等的那些元组

   • 自然连接：自然连接是一种特殊的等值连接。它要求两个关系中进行比较的分量必须是同名的属性组，并且在结果中把重复的属性列去掉

   

   在做自然连接的时候，两个关系中的某些元组可能会被抛弃，这些被舍弃的元组就称为悬浮元组。

   • 如果要把悬浮元组也留在结果中，而在其他属性上填NULL，那么这种连接就叫做外连接 outer join

   • 左外连接 left join：只保留左表的悬浮元组

   • 右外连接 right join：只保留右表的悬浮元组

   

5. 除运算

   R ÷ S = T 表示 T 包含所有在 R 但不在 S 中的属性及其值，且 T 的元组与 S 的元组的 所有组合 都在 R中

   **示例：**可以理解为在R中查找B、C属性和S中的B、C属性相同的，A属性的值

   

数据字典

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

一对一关系的作用（拆分表）

一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

关系数据库设计理论

数据依赖是一个关系内部属性和属性之间的一种约束关系。这种约束关系是通过属性间值的相等与否体现出来的数据间的相关联系。其中最重要的是函数依赖和多值依赖。

一个模式的数据依赖会有哪些不好的性质，如何改造一个不好的模式，这就是规范化要讨论的内容

异常

不符合范式的关系，会产生很多异常

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

主要有以下四种异常：

• 数据冗余：例如 学生 -2 出现了两次。
• 更新异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
• 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
• 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

规范化

规范化的目的

• 关系数据库进行规范化的目的：使结构更合理，解决数据中可能出现的异常情况（比如数据冗余、更新异常、删除异常、插入异常），从而增强数据的稳定性和灵活性

• 关系模式进行规范化的原则：遵从概念单一化“一事一地”原则，即一个关系模式描述一个实体或实体间的一种联系。规范的实质就是概念的单一化。

• 关系模式进行规范化的方法：将关系模式投影分解成两个或两个以上的关系模式。

规范化的思想是逐步消除数据依赖中不合适的部分，使模式中的各关系模式达到某种程度的分离。

函数依赖（Functional Dependency）

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

在一个关系中，任意元组，若属性 A1,A2…An 一样，则属性 B1,B2…Bm 必一样，那么称 A1,A2…An 函数决定 B1,B2…Bm。记号为 A1,A2...An → B1,B2...Bm ，Ai与Bi有函数依赖）

• 部分函数依赖（完全函数依赖）：对于 A->B，如果能找到 A 的真子集 A’，使得 A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

• 传递函数依赖：对于 A->B，B->C，则 A->C 是一个传递函数依赖。

范式理论

关系数据库中的关系是要满足一定要求的，满足不同程度要求的为不同范式。

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

一个低一级的范式的关系模式通过模式分解可以转换为若干个高一级的关系模式的集合，这个过程就叫 规范化（normalization）

① 第一范式 (1NF)

属性不再可分。

中国北京市 -> 中国|北京市

② 第二范式 (2NF)

在满足第一范式前提下，使每个非主属性完全函数依赖于键码。

一个关系模式不符合2NF定义，会导致如下问题：插入异常，删除异常，修改复杂。

可以通过分解来满足。

例：

分解前

Sno	Sname	Sdept	Mname	Cname	Grade
1	学生-1	学院-1	院长-1	课程-1	90
2	学生-2	学院-2	院长-2	课程-2	80
2	学生-2	学院-2	院长-2	课程-1	100
3	学生-3	学院-2	院长-2	课程-2	95

以上学生课程关系中，键码为{Sno, Cname} ，有如下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname
• Sno, Cname-> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

关系-1

Sno	Sname	Sdept	Mname
1	学生-1	学院-1	院长-1
2	学生-2	学院-2	院长-2
3	学生-3	学院-2	院长-2

键码为{ Sno }，有以下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname

关系-2

Sno	Cname	Grade
1	课程-1	90
2	课程-2	80
2	课程-1	100
3	课程-2	95

键码为{ Sno, Cname }，有以下函数依赖：

• Sno, Cname -> Grade

③ 第三范式（3NF）

在满足第二范式前提下，非主属性不传递函数依赖于键码。

一个关系模式不符合3NF定义，会导致如下问题(同2NF)：插入异常，删除异常，修改复杂

例：

上面的 关系-1 中存在以下传递函数依赖：

• Sno -> Sdept -> Mname

可以进行以下分解：

关系-11

Sno	Sname	Sdept
1	学生-1	学院-1
2	学生-2	学院-2
3	学生-3	学院-2

关系-12

Sdept	Mname
学院-1	院长-1
学院-2	院长-2

③ 修正的第三范式（BCNF）

在满足第二范式的条件下，所有属性不传递依赖于主属性。

可能会丢失一些函数依赖，所以一般不要求

例

STC(Sid,Tid,Cid) 学生选课m:n，老师授课m:1，键码（Sid,Tid）
函数依赖：

• (Sid,Cid)->Tid

• (Sid,Tid)->Cid

• Tid->Cid

这个表不符合BCNF但符合第三范式。
改为：表ST(Sid,Tid); 表TC(Tid,Cid)。现在就符合BCNF了。

多值依赖（Multi-Valued Dependency）

④ 第四范式（4NF）

当满足第三范式时，非主属性不应该有多值。

例

有这样一个用户联系方式表TELEPHONE(CUSTOMERID,PHONE,CELL)。 CUSTOMERID为用户ID,PHONE为用户的固定电话,CELL为用户的移动电话。

本来，这是一个非常简单的第3范式表。主键为CUSTOMERID，不存在传递依赖。但在某些情况下，这样的表还是不合理的。比如说，用户有两个固定电话，两个移动电话。这时，表的具体表示如下：

CUSTOMERID	PHONE	CELL
1000	8828-1234	149088888888
1000	8838-1234	149099999999

由于PHONE和CELL是互相独立的，而有些用户又有两个和多个值。这时此表就违反第四范式。

在这种情况下，此表的设计就会带来很多维护上的麻烦。例如，如果用户放弃第一行的固定电话和第二行的移动电话，那么这两行会合并吗？等等

解决问题的方法为，设计一个新表NEW_PHONE(CUSTOMERID,NUMBER,TYPE).这样就可以对每个用户处理不同类型的多个电话号码，而不会违反第四范式。

E-R 模型（Entity-Relationship）

E-R 模型使用 E-R 图来描述现实世界的概念模型。

• **实体 entity：**客观存在并可相互区别的事物，比如一个学生，一门课，学生的一次选课
• **属性：**实体所具有的特性，比如学生的身高
• **码：**唯一标识实体的属性集，比如学生的学号
• 实体型： 实体名+属性名，比如 学生（学号，姓名，性别）就是一个实体型
• 实体集：同一类型的实体的集合，比如全体学生
• 联系 relationship：实体之间的联系（有一对一，一对多，多对多等多种类型）

实体之间的联系

一对一

一对多

多对多

多实体

实体内部

弱实体：依赖于其他实体存在。双线矩形

复合实体：用于实现两个或多个实体间的 M:N 联系。矩形内有个菱形

复合属性：具有多个属性的组合。椭圆连椭圆

多值属性：多个取值。双线椭圆形

派生属性：非永久性存于数据库的属性，通常在线计算。虚线椭圆

可选属性：可以没有值。在文字后加上 (O)

联系属性：一般用于 M:N 的两个实体间，指定此联系具有的属性。菱形连椭圆

E-R 图

实体——矩形，属性——椭圆形，关系——菱形。

E-R 图向关系模式的转换

转换的一般原则：

• 一个实体型转换为一个关系模式

  • 关系的属性就是实体的属性

• 关系的码就是实体的码

• 一个 1：1 联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并

• 一个 1：n 联系可以转换为一个独立的关系模式，也可以与 n 端对应的关系模式合并

• 一个 m：n 联系可以转换为一个独立的关系模式

  • 关系的属性：与该联系相连的各实体的码以及联系本身的属性
  • 关系的码：各实体型码的组合

• 三个或三个以上实体间的一个多元联系可以转换为一个关系模式

  • 关系的属性：与该多元联系相连的各实体的码以及联系本身的属性
  • 关系的码：各实体码的组合

• 具有相同码的关系模式可合并

  目的：减少系统中的关系个数

例：

物资管理的 E-R 图如下：

• 一个实体型转换为一个关系模式：

  供应商（供应商号，姓名，地址，电话号，账号）

  项目（项目号，预算，开工日期）

  零件（零件号，名称，规格，单价，描述）

  仓库（仓库号，面积，电话号）

• 三个或三个以上实体间的一个多元联系可以转换为一个关系模式：

  供应（供应商号，项目号，零件号，供应量）

• 一个 1：n 联系可以转换为一个独立的关系模式，也可以与 n 端对应的关系模式合并

  职工（职工号，姓名，年龄，职称，仓库号，领导职工号）

• 一个 m：n 联系可以转换为一个独立的关系模式

  库存（仓库号，零件号，库存量）

索引

一个没加聚集索引的表，它的数据是一行一行无序地存放在磁盘存储器上的。而如果给表添加了索引，那么表在磁盘上的存储结构就由一行一行排列的结构转变成了高效的树结构等。换句话说，就是整个表就变成了一个索引，也就 “索引即数据，数据即索引”。

通过对数据库表创建索引，可以提高查询速度，不同索引决定了不同的的物理存储结构。

通过创建唯一索引，可以保证某一列的值具有唯一性。

数据库索引对于用户和应用程序来说都是透明的。

索引物理结构

① B+ 树索引

B+ 树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，各叶子节点之间通过双向链表进行连接。

B+ 树的叶子节点存储真正的记录，而非叶子节点的存在是为了更快速的找到对应记录所在的叶子节点

B+ 树索引并不能找到一个给定键值的具体“行”。B+ 树索引能找到的只是被查找数据行所在的**“页”**。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

❓ B+ 树能够存放多少行数据

其实问的就是这棵 B+ 树的非叶子节点中存放的数据量：根节点指针数 * 每个叶子节点存放的行记录数。每个叶子节点存放的行记录数就是每页存放的记录数，简单按照一行记录的数据大小为 1k 来算的话，一页或者说一个叶子节点可以存放 16 行这样的数据。非叶子节点里面存的是主键值 + 指针，我们假设主键的类型是 BigInt，长度为 8 字节，而指针大小在 InnoDB 中设置为 6 字节，这样一共 14 字节，一页或者说一个非叶子节点能够存放 16384 / 14=1170 个这样的单元。所以对于这样一棵高度为 2 的 B+ 树，能存放 1170（一个非叶子节点中的指针数） * 16（一个叶子节点中的行数）= 18720 行数据。

❓ 为什么不使用红黑树

• 树节点出度更大，高度更低，查找次数更少，查找更快
• 利用磁盘预读取特性，预先载入分支节点

② 哈希索引

哈希索引也就是得益于哈希算法的快速查找特性，不过哈希索引的致命缺点就是无法顺序查询和范围查询。

索引类型

聚集索引（主键索引）/ 辅助索引（二级索引）

**聚集索引：**以主键作为 B+ 树索引的键值而构建的 B+ 树索引，叶子节点中存放的为完整的一行信息，数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同。每张表只能拥有一个聚集索引。

**辅助索引：**叶子节点只存储了 每行数据的辅助索引键 和 该行数据对应的聚集索引键。当通过辅助索引来寻找数据时，InnoDB 存储引擎会先遍历辅助索引的 B+ 树，通过叶子节点获得某个辅助索引键对应的聚集索引键，然后再通过聚集索引来找到一个完整的行记录（回表）。每张表上可以有多个辅助索引。

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

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

聚集索引的叶子结点存放的是数据，辅助索引叶子结点存放的是主键

假设有张 user 表，包含 id（主键），name，age（普通索引）三列，有如下数据：

  id	name	age
  1	Jack	    18
  7	Alice	    28
  10	Bob	    	38
  20	Carry	    48

聚集索引（id）：

辅助索引（age）：

单列索引 / 覆盖索引（联合索引）

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

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

例：我们把 age,name 设置为联合索引：

create index idx_age_name on user(`age`,`name`);

此时 age 和 name 作为辅助索引键都在同一棵辅助索引的 B+ 树上，所以只需扫描一次这个组合索引的 B+ 树即可获取到 id、age 和 name，这就是实现了索引覆盖

⭐️ 最左前缀原则

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

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

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

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

创建索引原则

• 选择唯一性索引，一般唯一性索引基于Hash，可快速定位；
• 为经常需要做条件、排序、分组、联合操作、查询的字段建立索引；
• 限制索引数量，索引越多更新越慢；
• 尽量使用数据量少的索引，索引字段值过长降低效率，可使用前缀；
• 尽量选择区分度高的列作为索引，例如生日就比性别区分度高；
• 删除不再使用或使用很少的索引；
• 索引列不能参与计算，带函数的查询不建议参与索引；
• 尽量扩展现有索引，建立联合索引；
• 对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

数据库的存储介质一般由多个磁盘阵列组成。数据在磁盘阵列上的分布也是数据库物理设计的内容之一，这就是所谓的分区设计 partition design。分区设计的一般原则如下：

• 减少访盘冲突，提高 I/O 并行性
• 分散热点数据，均衡 I/O 负荷
• 保证关键数据的快速访问，缓解系统的瓶颈

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

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

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

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

事务

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

事务是恢复和并发控制的基本单位

ACID

数据库事务具有ACID这4个特性：

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

-- 开启事务
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交事务
COMMIT;
-- 回滚事务（整个事务会失败）
ROLLBACK;

事务ACID特性可能遭到破坏的因素有：

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

数据库恢复技术

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

数据恢复技术是衡量系统性能优劣的重要指标。

故障的种类

1. 事务内部的故障

   事务内部的故障更多是非预期的、不能由应用程序处理的故障。一般我们所说的事务故障都是指这类非预期故障。

   begin transaction;
   	读甲账户的余额balance1;
   	balance1=balance1-amount;
   	if(balance1<0) then{
   		打印'金额不足不能转账'; # 内部故障
   		rollback;
   	}
   	else{
   		读乙账户的余额balance2;
   		balance2=balance2+amount;
   		写回balance2;
   		commit;
   	}
   		
   

   事务故障意味着事务没有到达预期的终点（commit 或者 rollback）因此，数据库可能处于不正确的状态。

   恢复程序要在不影响其他事务运行的情况下，强行回滚该事务，即撤销该事务已经做出的任何对数据库的修改。这类恢复操作称为 事务撤销 UNDO

2. 系统故障

   系统故障是指造成系统停止运转的任何事件，使得系统要重新启动。

   例如，特定类型的硬件错误(CPU故障) 、操作系统故障、DBMS代码错误、系统断电等。这类故障影响正在运行的所有事务，但不破坏数据库。此时主存内容，尤其是数据库缓冲区 中的内容都被丢失，所有运行事务都非正常终止。发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，从而造成数据库可能处于不正确的状态。

   为保证数据库的一致性，系统重新启动后， 恢复子系统需要撤销所有未完成的事务，并**重做(REDO)**所有已提交的事务，以将数据库真正恢复到一致状态 。

3. 介质故障

   系统故障称为软故障，介质故障称为硬故障。

   硬故障指外存损坏，比如磁盘损坏等

4. 计算机病毒

恢复的实现技术

恢复的基本原理：冗余。数据库中任何一部分被破坏或者不正确的数据都可以根据存储在系统别处的冗余数据来重建。

恢复机制的两个关键问题：

• 如何建立冗余数据（数据转储 + 登记日志文件 logging）
• 如何利用冗余数据实现数据库恢复

实现方法：

1. 数据转储

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

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

   转储十分耗时，不能频繁进行。

2. 登记日志文件

   **日志文件（logging）**中需要登记的内容包括：

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

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

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

并发控制技术

并发控制的主要技术有封锁 locking、时间戳 timestamp、乐观控制法 optimistic scheduler 和多版本控制 MVCC 等

死锁 / 活锁

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

  解决死锁的方法：

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

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

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

  • 死锁检测：

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

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

  • 死锁处理：

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

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

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

并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

1. 丢失修改（Lost update）

   丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。

   

2. 脏读（Dirty read）

   读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

   

3. 不可重复读（no-repeatable read）

   不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

   

   脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。

4. 幻读（Phantom read）

   幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

   

   ⚠ 不可重复度和幻读区别：

   不可重复读的重点是修改，幻读的重点在于新增或者删除。

   例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：

   事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

   例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：

   假如某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

隔离级别标准

https://blog.csdn.net/gklifg/article/details/38752691

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

• 未提交读（READ UNCOMMITTED）——一级封锁协议

  事务中的修改，即使没有提交，对其它事务也是可见的。

• 提交读（READ COMMITTED）——二级封锁协议

  一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

• 可重复读（REPEATABLE READ）——三级封锁协议

  保证在同一个事务中多次读取同一数据的结果是一样的。 InnoDB 默认的隔离级别。

• 可串行化（SERIALIZABLE）——两段提交协议

  强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

封锁

封锁是众多数据库产品采用的基本方法。

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

封锁粒度

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

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

封锁类型

1. 读写锁

   • 排他锁（Exclusive Lock，X Lock，写锁）
   • 共享锁（Shared Lock，S Lock，读锁）

   有以下两个规定：

   • 一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
   • 一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。
   

2. 意向锁（Intention Locks）

   使用意向锁可以更容易地支持多粒度（granular）封锁。

   在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

   意向锁在原来的 X/S 行锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

   • 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
   • 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

   通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

   意向锁之间是相互兼容的，但是与表级读写锁之间大部分都是不兼容的：

   

   解释如下：

   • 任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
   • 以上兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

   1）为什么没有意向锁的话，表锁和行锁不能共存？

   2）意向锁是如何让表锁和行锁共存的？

   首先来看第一个问题，假设行锁和表锁能共存，举个例子：事务 T1 锁住表中的某一行（行级写锁），事务 T2 锁住整个表（表级写锁）。

   问题很明显，既然事务 T1 锁住了某一行，那么其他事务就不可能修改这一行。这与 ”事务 T2 锁住整个表就能修改表中的任意一行“ 形成了冲突。所以，没有意向锁的时候，行锁与表锁是无法共存的。

   再来看第二个问题，有了意向锁之后，事务 T1 在申请行级写锁之前，会先自动给事务 T1 申请这张表的意向排他锁，当表上有意向排他锁时其他事务申请表级写锁会被阻塞，也即事务 T2 申请这张表的写锁就会失败。

封锁协议

• 三级封锁协议

  1. 一级封锁协议

     事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。只对修改数据的并发操作做限制，不能修改其他事务正在修改的数据，但可以读取到其他事务中尚未提交的修改

     可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

     

  2. 二级封锁协议

     在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。

     可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

     

  3. 三级封锁协议

     在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

     可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

     

     三级封锁协议总结

  

• 两段提交协议（TwoPhase Commit，2PC）

  目前数据库管理系统普遍采用两段锁协议实现并发调度的可串行性，从而保证调度的正确性

  可串行化调度：通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。数据库管理系统对并发事务不同的调度可能会产生不同的结果。只有串行执行的事务互不干扰，不会出现并发一致性问题。

  两段锁协议就是指所有事务必须分两个阶段对数据项进行加锁和解锁。在事务执行过程中，随时都可以执行加锁操作，但是只有在事务执行 COMMIT 或者 ROLLBACK 的时候才会释放锁，并且所有的锁是在同一时刻被释放。

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

  事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

  

  两段提交缺点：

  • 同步阻塞：执行过程中，所有参与者任务阻塞执行
  • 单点故障：协调者故障时，所有参与者阻塞
  • 数据不一致：commit请求丢失
  • 协调者宕机后事务状态丢失

• 三段提交协议

  引入超时机制。分为CanCommit（准备），PreCommit（预执行事务），DoCommit（真正提交）

​

分布式事务，柔性事务，BASE理论

安全性

数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露、更改或破坏。

主要包括用户身份鉴别、多层存取控制、审计、视图和数据加密等技术

① 用户身份鉴别

用户身份鉴别是数据库管理系统提供的最外层安全保护措施，每个用户在系统中都有一个用户标识，每个用户标识由用户名和用户标识号UID两部分组成。UID在系统的整个生命周期中是唯一的。系统内部记录着所有合法用户的标识。

每个用户要求进入系统时，由系统进行核对，通过鉴定后才提供使用数据库管理系统的权限。

用户身份鉴别的方法主要有以下几种：

• 静态口令鉴别：静态口令一般由用户自己设定，鉴别使输入正确口令即可获得权限

• 动态口令鉴别：每次鉴别时均需使用动态产生的新口令登录数据库管理系统。比如短信验证码登录

• 生物特征鉴别：比如指纹、虹膜鉴别

• 智能卡鉴别：智能卡由用户随身携带，插入专用的读卡器进行身份验证

② 存取控制

数据库安全最重要的一点就是确保只能有资格的用户授予访问权限，这主要通过存取控制机制实现。存取控制机制主要包括定义用户权限和合法权限检查两部分

权限授予和收回：

• GRANT 授予权限

  不允许循环授权，即被授权者不能把权限再授回授权者或者其祖先

  # 把查询Student表的权限授给用户User1，并允许他将此权限授予其他用户
  grant select
  on table Student
  to User1
  with grant option; # with grant option允许他将此权限授予其他用户
  
  # 把对Student表和Cource表的全部操作权限授予用户User2和User3
  grant all privileges
  on table Student,Cource
  to User2,User3;
  
  # 把对表SC的查询权限授予所有用户
  grant select
  on table SC
  to public;
  
  # 把查询Student表和修改学生学号的权限授予用户User4
  grant update(Sno),select
  on table Student
  to User4;
  

• REVOKE 收回权限

  # 收回Uer4修改学生学号的权限
  revoke update(Sno)
  on table Student
  from Uer4;
  
  # 收回用户User1对Student表的查询权限，并级联收回User1授予的其他用户的该权限
  revoke select
  on table Student
  from User1 CASCADE;
  

③ 视图机制

还可以为不同的用户定义不同的视图，把要保密的数据对无权存取的用户隐藏起来，从而自动对数据提供一定程度的安全保护。

# 建立计算机学生的视图，并把对该视图的select权限授予User1,对该视图的所有操作权限授予User2
create view CS_Student
as
select *
from Student
where Sdept = 'CS';

grant select
on CS_Student
to User1;

grant all privileges
on CS_Student
to User2;

④ 审计 / 跟踪审查

审计功能把用户对数据库的所有操作自动记录下来放入审计日志（audit log）中。审计员可以利用审计日志监控数据库中的各种行为，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等

• AUDIT 设置审计功能

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

• NOAUDIT 取消审计功能

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

⑤ 数据加密

加密的基本思想是根据一定的算法原始数据——明文（plain text) 变换为不可直接识别的格式——密文（cipher text），从而使得不知道解密算法的人无法获知数据的内容。

SQL注入

如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"：

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''

这个SQL语句执行的时候，根本不用判断口令是否正确，这样一来，登录就形同虚设。

使用 PreparedStatement 可以完全避免SQL注入的问题，因为PreparedStatement始终使用?作为占位符，并且把数据连同SQL本身传给数据库，这样可以保证每次传给数据库的SQL语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存。上述登录SQL如果用PreparedStatement可以改写如下：

User login(String name, String pass) {
    ...
    String sql = "SELECT * FROM user WHERE login=? AND pass=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setObject(1, name);
    ps.setObject(2, pass);
    ...
}

所以，PreparedStatement比Statement更安全，而且更快。

使用Java对数据库进行操作时，必须使用PreparedStatement，严禁任何通过参数拼字符串的代码！

完整性

数据的完整性是指数据的正确性和相容性。

• 数据的正确性：数据是符合现实世界语义，反映当前实际状况的
• 数据的相容性：数据库同一对象在不同关系表中的数据是符合逻辑的

例如：学生的学号必须唯一，性别只能是男或女等等

3类完整性

① 实体完整性（主键）

• 检查主键是否唯一，如果不唯一则拒绝插入或修改
• 检查主键的各个属性是否为空，只要有一个为空则拒绝插入或修改

② 参照完整性（外键）

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

当上述的不一致发生时，系统可以采用以下策略

• 拒绝执行 NO ATION：默认策略

• 级联操作 CASCADE

  当删除或修改被参照表的一个元组导致与参照表的不一致时，删除或修改参照表中的所有导致不一致的元组。

  例如：删除Student表中001学生，则SC表中关于001的记录也全部删除

• 设置为空值

  当删除或修改被参照表的一个元组导致与参照表的不一致时，将不一致的属性设置为空值

③ 用户自定义完整性（属性 & 值）

1. 属性上的约束条件

   当不满足属性约束条件的时候，操作将被拒绝执行

   • 不允许空值

     Sno char(9) not null,
     

   • 列值唯一

     Dname char(9) unique not null, # 列值唯一且不能为空
     

   • 用check 短语指定列值应该满足的条件

     Ssex char(2) check(Ssex in('男','女')), # 性别属性只能取男或女
     Grade smallint check(Grade >= 0 and Grade <= 100), # 分数属性只能取值0-100
     

2. 元组上的约束条件

   元组级的约束可以设置不同属性之间的相互约束条件。

   当不满足这些约束条件的时候，操作将被拒绝执行

   create table Student(
       Sno char(9),
       Sname char(9) not null,
       primary key(Sno),
       check(Ssex = '女' or Sname not like 'Ms.%') # 性别是女或者名字不以MS.%开头则可以通过check检查
   );
   

命名完整性约束（Constraint）

完整性约束命名

constraint 命名完整性约束，方便增加和删除一个完整性约束条件

格式：constrain 完整性约束条件名 完整性约束条件

完整性约束条件包括：not null，unique，primary key，foreign key，check

create table Student(
	Sno numberic(9)
    	constraint c1 check (Sno between 100-1000),
    Sname char(9) 
    	constraint c2 not null,
    Sage numeric(3)
    	constraint c3 check(Sage < 30),
    constrain StudentKey primary key(Sno)   
);

修改表中的完整性约束

# 去除对年龄小于30的约束
alter table Student
	drop constraint c3;

# 添加年龄小于40的约束
alter table Student
	add constraint c4 check(Sage < 40);

断言（Assertion）

关键词：ASSERTION

任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行

# 限制数据库课程最多60名学生选修
create assertion asse_sc_db_num
	check (60 >= (select count(*)
                 from Cource,SC
                 where SC.Cno = Cource.Cno and
                 Couce.Cname = '数据库')
          );

触发器（Trigger）

触发器是用户定义在关系表上的一类由事件驱动的特殊过程

① 创建触发器

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

create trigger 触发器名 
	before/after 触发事件 /*指明触发器的激活时间*/
	on 表名 /*触发器只能定义在基本表上，不能定义在视图上*/
	[referencing 引用名] 可选的
	for each row/statement /*定义触发器的类型，指明动作体执行的频率*/
	when SQL语句
	动作

before/after：触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

触发事件：

• insert：触发器包含一个名为 NEW 的虚拟表。

• delete ：触发器包含一个名为 OLD 的虚拟表，并且是只读的。

• update：触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

  也可以是 update of < 触发列名1，触发列名2 ... >

引用名：

触发器事件既然是数据库更新操作，这些操作的执行势必会引起数据库中某些值的改变，即由旧值变成新值，这些新旧值称为过渡值。在触发器的条件和动作中可以引用这些过渡值

• OLD【ROW】AS 旧元组别名 （row旧元组名是可选的）
• NEW【ROW】AS 新元组别名
• OLD TABLE AS 旧表别名
• NEW TABLE AS 旧表别名

触发器类型：

• for each row：行级触发器，针对行

• for each statement : 语句级触发器（默认），针对一条sql语句

示例：

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;

② 删除触发器

DROP TRIGGER 触发器名

③ 触发器实现参照完整性

比如有三个表：student（学生表），cource（课程表），sc（选修表），其中sc定义了两个外键sno和cno以及其完整性约束，试写出触发器实现该参照完整性约束的规则

首先分析：有哪些操作会影响到本例的完整性约束

• sc 表的 insert 操作
• cource 表的 delete 操作
• student 表的 delete 操作
• sc 表的 update（sno, cno) 操作
• cource 表的 update（cno) 操作
• student 表的 update（sno) 操作

对上述6中操作分别定义6条规则，实现参照完整性约束

规则1：

create trigger referential_integrity_check
before insert on sc
referencing new as n
when (not(exists(select * from student
                where sno = n.sno)
         and
         exists(select * from cource
               where cno = n.cno)
         )
     )
rollback;

如果 sc 表中插入元组，其外键在 student 和 cource 表中均不存在，则卷回插入该元组操作

规则2：

create trigger cource_delete
before delete on cource
referencing old as o
for each row
when (exists(select * from sc
            where o.cno = sc.cno)
     )
rollback;

如果 cource 表中删除一个元组，若该元组是 sc 表中的外键，则卷回删除该元组的操作（此处我们假定在sc表的定义中，外键 cno 使用了 restrict 选项）

规则3：

create trigger student_delete
before delete on student
referencing old as o
for each row
when(exists(select * from sc
           where sc.sno = o.sno)
    )
delete from sc
where sc.sno = o.sno;

假设在 sc 表的定义中，外键 sno 的定义中采用了 cascade 选项，即当在 student 表中删除一个元组的时候，则在 sc 表中删除引用该元组主键作为外键的所有元组

规则4：

create trigger sc_fk_update
before update of sno,cno on sc
referencing new as n
for each row
when(not(exists(select * from student
               where sno = n.sno)
        and
        exists(select * from cource
              where cno = n.cno)
        )
    )
rollback;

对于 sc 表的更新操作，若更新的外键 sno 或者 cno 在 student 和 cource 表中无相应的主键供其引用，则卷回更新该元组的操作

规则5：

create trigger cource_cno_update
before update of cno on cource
referencing old as o
for each row
when(exists(select * from sc
               where sc.cno = o.cno)
     )
    
rollback;

对于 cource 表的 更新操作，在修改主键cno的同时，如果sc表中有元组正引用修改前的cno值作为外键，则卷回该操作

规则6：

create trigger student_sno_update
before update of sno of student
referencing old as o
for each row
when (exists(select * from sc
            where sc.sno = o.sno)
     )
rollback;

对于 student 表的更新操作，在修改主键sno的同时，如果sc表中有元组正引用修改前的sno值作为外键，则卷回该操作

数据库连接池

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

数据库连接池的解决方案是在应用程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由应用程序动态地对池中的连接进行申请、使用和释放。

不使用数据库连接池的步骤：TCP建立连接三次握手，MySQL认证三次握，真正的SQL执行，MySQL关闭，TCP四次握手关闭

数据库编程

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

储存过程

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

优点

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

缺点

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

游标

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

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

查询处理和优化

查询处理

查询处理是数据库管理系统把用户提交上来的查询语句转换成高效的查询执行计划。

关系数据库管理系统查询处理可以分为4个阶段：

• 查询分析
• 查询检查
• 查询优化
• 查询执行

1. 查询分析

首先对查询语句进行扫描、语法分析和词法分析。从查询语句中识别出语言符号，如SQL关键字、属性名和关系名等，进行语法检查和语法分析，判断查询语句是否符合SQL语法规则

2. 查询检查

对合法的查询语句进行语义检查，即检查数据库对象，如关系名、属性名是否存在和有效。

还要根据用户权限和完整性约束定义对用户的存取权限进行检查。如果用户没有相应权限或者违反了完整性约束，就拒绝执行该查询。

检查过后将SQL查询语句转成内部表示即等价的关系代数表达式，一般用 查询树 / 语法分析树 来表示扩展的关系代数表达式

3. 查询优化

查询优化就是优化器选择一个高效执行的查询处理策略，以获得最好的查询优化效果

按照优化的层次分为代数优化和物理优化

4. 查询执行

根据优化器得到的执行策略生成查询执行计划，由代码生成器生成执行这个查询计划的代码，然后加以执行，并返回查询结果

实现查询操作的算法

1. 选择操作的实现

① 全表扫描算法 table scan

适用于规模较小的表。

对于大规模的表，当选择率较低时，这个算法的效率很低

② 索引扫描算法 index scan

如果选择条件中的属性上有索引，可以用索引扫描算法，通过索引先找到满足条件的元组指针，再通过元组指针在查询的基本表中找到元组

2. 连接操作的实现 / 多表连接

以下面这条 SQL 语句为例：

select * from Student,SC where Student.Sno = SC.Sno;

① 嵌套循环算法 nested loop

最简单可行的算法。

• 取 Student 表的一个元组，与 SC 表的所有元组进行比较，凡满足连接条件的元组就进行连接并且作为结果输出。
• 然后再取 Student 表的下一个元组，与 S 的所有元组比较，直至 Student 表的所有元组与 SC 表的所有元组比较完毕为止。

② 排序-归并算法 sort-merge

等值连接常用的算法，如果 Student 表和 SC 表已经按连接属性排好序了，则可按序比较两个表的连接属性，找出匹配的所有元组。

核心思想：分别从两个表中取出一行元组进行比较，如果匹配就连接起来放入结果集；如果不匹配，将较小的那个元组丢弃，继续匹配这个表的下一行，依次处理直到将两表的数据取完。

如果 Student 表 和 SC 表在做连接操作之前没有按连接属性进行排序，则我们需要事先为之排序，由于排序是开销很大的操作，在此情况下是否值得使用排序归并法，那就需要权衡了。

③ 索引连接算法 index join

• 在 SC 表上已经建立了 Sno 的索引
• 对 Student 中的每一个元组，在 SC 表中通过 Sno 的索引查找对应的 SC 元组，把相匹配的两个表中的元组连接起来。循环执行，直到 Student 表扫描结束

④ 散列连接算法 hash join

🚨 Oracle 支持 hash join，MySQL 不支持

用来处理等值连接。把连接属性作为 hash 的 value，用同一个 hash 函数把 Student 表和 SC 表中的元组散列到 hash 表中。

• 创建阶段：创建 hash 表。对包含较少元组的表进行处理，把他的元组按 hash 函数分散到 hahs 桶中（采用拉链法）
• 连接阶段：对另一个表进行 hash。并把这个表中元组和上一个表中相匹配的元组（同义词）连接起来。如果一个桶中只有 Student 或者 SC 的元组，则不进行连接。

查询优化

每个查询都会有许多可供选择的执行策略和操作算法，查询优化就是选择一个高效执行的查询处理策略。

查询优化的优点不仅在于用户不必考虑如何最好的表达查询以获得较高的效率，而且在于系统可以比用户程序的优化做的更好。

代数优化

代数优化就是通过对关系代数式的等价变换来提高查询效率

代数优化改变的是查询语句中操作的次序和组合，但不涉及底层的存取路径

最常用的优化原则是尽量缩减查询过程中的中间结果。由于选择、投影等一元操作分别从水平或垂直方向减少关系的大小，而连接、并等二元操作不但操作本身的开销较大，而且很可能产生大的中间结果。因此，再做查询优化时，总是让选择和投影先做，再做连接等二元操作。在连接时，也是先做小关系之间的连接，再做大关系之间的连接。

常见的对关系表达式进行查询优化的方法有：

• 选择运算尽可能先做
• 若投影运算和选择运算都是对同一个关系进行操作，则将投影运算和选择运算同时进行
• 把投影同其前或后的双目运算符结合起来
• 把某些选择同在它前面要执行的笛卡尔积结合起来成为一个来连接运算（连接，特别是等值连接，要比同样关系上的笛卡尔积省很多时间）
• 找出公共子表达式（比如查询视图的时候，定义视图的表达式就是公共子表达式）

物理优化

物理优化就是选择高效合理的操作算法或者存取路径来达到查询优化的目标

选择的方法如下：

• 基于规则的启发式优化
• 基于代价估算的优化：选择代价最小的执行计划
• 两者结合的优化方法

① 基于规则的启发式优化

🚩 启发式优化：指的是在大部分情况下使用，但不是在所有情况下都是最好的规则

1）对于选择操作的启发式规则：

• 对于小关系，使用全表顺序扫描，即使选择列上有索引
• 对于大关系，启发式规则有：
  • 选择条件是 主键 = 值，采用主键索引
  • 选择条件是 非主属性 = 值，并且选择列上有索引，估算查询结果的元组数目，如果比例较小，可以使用索引，否则仍然使用全表顺序扫描
  • 选择条件是 非等值查询或范围查询，并且选择列上有索引，估算查询结果的元组数目，如果比例较小，可以使用索引，否则仍然使用全表顺序扫描
  • 使用 AND 连接的合取选择条件，如果有涉及这些属性的组合索引，则优先使用索引，否则使用全表顺序扫描
  • 对于 OR 连接的析取选择条件，一般使用全表顺序扫描

2）对于连接操作的启发式规则：

• 如果两个表都已经按照连接属性排序，则使用排序-合并算法
• 如果一个表在连接属性上有索引，则使用索引连接算法
• 如果上面两个规则不适用，且其中一个表较小，则使用 hash join 算法
• 最后可以使用嵌套循环算法

② 基于代价估算的优化

基于代价的优化方法要计算各种操作算法的执行代价，它与数据库的状态密切相关。为此在数据字典中存储了优化器需要的统计信息，主要包括以下几个方面：

散列连接算法 hash join**

🚨 Oracle 支持 hash join，MySQL 不支持

用来处理等值连接。把连接属性作为 hash 的 value，用同一个 hash 函数把 Student 表和 SC 表中的元组散列到 hash 表中。

• 创建阶段：创建 hash 表。对包含较少元组的表进行处理，把他的元组按 hash 函数分散到 hahs 桶中（采用拉链法）
• 连接阶段：对另一个表进行 hash。并把这个表中元组和上一个表中相匹配的元组（同义词）连接起来。如果一个桶中只有 Student 或者 SC 的元组，则不进行连接。

查询优化

每个查询都会有许多可供选择的执行策略和操作算法，查询优化就是选择一个高效执行的查询处理策略。

查询优化的优点不仅在于用户不必考虑如何最好的表达查询以获得较高的效率，而且在于系统可以比用户程序的优化做的更好。

代数优化

代数优化就是通过对关系代数式的等价变换来提高查询效率

代数优化改变的是查询语句中操作的次序和组合，但不涉及底层的存取路径

最常用的优化原则是尽量缩减查询过程中的中间结果。由于选择、投影等一元操作分别从水平或垂直方向减少关系的大小，而连接、并等二元操作不但操作本身的开销较大，而且很可能产生大的中间结果。因此，再做查询优化时，总是让选择和投影先做，再做连接等二元操作。在连接时，也是先做小关系之间的连接，再做大关系之间的连接。

常见的对关系表达式进行查询优化的方法有：

• 选择运算尽可能先做
• 若投影运算和选择运算都是对同一个关系进行操作，则将投影运算和选择运算同时进行
• 把投影同其前或后的双目运算符结合起来
• 把某些选择同在它前面要执行的笛卡尔积结合起来成为一个来连接运算（连接，特别是等值连接，要比同样关系上的笛卡尔积省很多时间）
• 找出公共子表达式（比如查询视图的时候，定义视图的表达式就是公共子表达式）

物理优化

物理优化就是选择高效合理的操作算法或者存取路径来达到查询优化的目标

选择的方法如下：

• 基于规则的启发式优化
• 基于代价估算的优化：选择代价最小的执行计划
• 两者结合的优化方法

① 基于规则的启发式优化

🚩 启发式优化：指的是在大部分情况下使用，但不是在所有情况下都是最好的规则

1）对于选择操作的启发式规则：

• 对于小关系，使用全表顺序扫描，即使选择列上有索引
• 对于大关系，启发式规则有：
  • 选择条件是 主键 = 值，采用主键索引
  • 选择条件是 非主属性 = 值，并且选择列上有索引，估算查询结果的元组数目，如果比例较小，可以使用索引，否则仍然使用全表顺序扫描
  • 选择条件是 非等值查询或范围查询，并且选择列上有索引，估算查询结果的元组数目，如果比例较小，可以使用索引，否则仍然使用全表顺序扫描
  • 使用 AND 连接的合取选择条件，如果有涉及这些属性的组合索引，则优先使用索引，否则使用全表顺序扫描
  • 对于 OR 连接的析取选择条件，一般使用全表顺序扫描

2）对于连接操作的启发式规则：

• 如果两个表都已经按照连接属性排序，则使用排序-合并算法
• 如果一个表在连接属性上有索引，则使用索引连接算法
• 如果上面两个规则不适用，且其中一个表较小，则使用 hash join 算法
• 最后可以使用嵌套循环算法

② 基于代价估算的优化

基于代价的优化方法要计算各种操作算法的执行代价，它与数据库的状态密切相关。为此在数据字典中存储了优化器需要的统计信息，主要包括以下几个方面：

[外链图片转存中…(img-UfwYt0wX-1642660440913)]