视图

视图是一个虚拟表，其内容由查询定义。视图一经定义便存入数据库中。视图中的数据只是存放在基本表中的数据。

视图作用

  • 简单性：视图可以简化用户的操作
  • 安全性：通过视图用户只能查询和修改他们所看到的数据
  • 逻辑数据独立性：视图可以帮助用户屏蔽真实表结构变化带来的影响

完整性约束

关系完整性约束是为保证数据库中数据的正确性和相容性，对关系模型提出的某种约束条件或规则。完整性通常包括域完整性，实体完整性、参照完整性和用户定义完整性，其中域完整性，实体完整性和参照完整性，是关系模型必须满足的完整性约束条件

  • 域完整性：保证指定列的数据具有正确的数据类型、格式和有效的数据范围
  • 实体完整性：指关系的主关键字不能重复也不能取“空值”，保证数据库中数据表的每一个特定实体的记录都是唯一的
  • 参照完整性：定义建立关系之间联系的主关键字与外部关键字引用的约束条件。当增加、修改或删除数据库表中记录时，可以借助参照完整性来保证相关联表之间数据的一致性
  • 用户定义完整性：由用户定义的完整性。用户定义完整性可以定义不属于其他任何完整性分类的特定业务规则

索引

可参考：MySQL索引背后的数据结构及算法原理

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引的优点

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

索引的缺点

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

设置索引的原则

应设置索引的情况

  • 较频繁查询的列上创建索引
  • 在经常需要根据范围进行搜索的列上创建索引
  • 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
  • 在经常用在连接的列上
  • 在经常需要排序的列上创建索引，因为索引已经排序
  • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

不应设置索引的情况

  • 在查询中很少使用或者参考的列不应该创建索引
  • 选择性很差的列，即这些列取值很少
  • 当修改性能远远大于检索性能时，不应该创建索引

索引分类：

  • 唯一索引：唯一索引不允许两行具有相同的索引值
  • 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
  • 聚集索引(Clustered)：表中各行的物理顺序与索引顺序相同，每个表只能有一个
  • 非聚集索引(Non-clustered)：非聚集索引指定表中记录的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置指针

索引类型

顺序索引

使用B树或者B+树作为其索引结构

散列索引

哈希索引（hash index）基于哈希表实现。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。对于hash相同的，采用链表的方式解决冲突。

优点：

  • 访问哈希索引的数据非常快

限制：

  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
  • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
  • 哈希索引只支持等值比较查询，包括=、IN()、<>，也不支持任何范围查询
  • 当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行

散列索引与顺序索引的区别

Hash索引 顺序索引
实现方式 Hash表 B+树
是否有序 无序 有序
查询方式 只支持点查询 支持点查询和范围查询

存储过程

存储过程是一个预编译的SQL语句。

存储过程优点

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

存储过程缺点

  • 每个数据库的存储过程语法几乎都不一样，十分难以维护
  • 业务逻辑放在数据库上，难以迭代

存储过程和函数的区别

存储过程 函数
用途 用于在数据库中完成特定的操作或者任务 用于特定的数据
声明 程序头部声明用procedure 程序头部声明用function
返回类型 程序头部声明时不需描述返回类型 程序头部声明时要描述返回类型
参数模式 可以使用in/out/in out 三种模式的参数 可以使用in/out/in out 三种模式的参数
能否独立执行 可作为一个独立的语句来执行 不能独立执行，必须作为表达式的一部分调用
调用 SQL语句(DML 或SELECT)中不可调用存储过程 SQL语句(DML 或SELECT)中可以调用函数

触发器

触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。

join

内连接

在每个表中找出符合条件的共有记录

外连接

左连接

根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null表示

右连接

根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配，如果找不到匹配的，用null填充

全外连接

返回左表和右表中的所有记录。若某一行再另一个表中没有与之匹配的，用null表示（结果是左连接和右连接的并集）

笛卡尔积（交叉连接）

将两个表中的每条记录都进行组合。不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积，如果带where，返回或显示的是匹配的行数（先生成笛卡尔积，再根据查询条件进行筛选）。

事务

事务是并发控制的基本单元。事务是一个操作序列，由一条或多条SQL语句组成，这些操作要么都执行成功，要么都不执行，它是一个不可分割的工作单位。

事务的ACID特性：

原子性（Atomicity）

事务是不可分割的工作单位。只有事务中的全部操作执行成功，事务才算执行成功。若任何操作失败，则该事务执行失败，数据库的状态必须回滚到事务开始前的状态。

一致性（Consistency）

事务应保证数据库只能从一个一致状态转移到另一个一致状态。一致状态是指数据库中的数据应满足完整性约束。即事务开始前和结束后数据库数据的完整性没有被破坏。

隔离性（Isolation）

事务的隔离性要求每个事务的对象和其它事务的操作对象能相互分离。即该事务提交前对其它事务都不可见，这通常使用锁来实现。多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

持久性（Durability）

事务一旦提交其结果就是永久性的。即数据写入到了数据库中，即使宕机数据也不会丢失。

可串行化

可串行化指事务并行执行的结果与某个顺序的串行执行结果相同。可串行化

先后顺序图/串行化图

对于调度S，先后顺序图是一个有向图 \(G = (N, E)\)，由节点集合 \(N\) 和有向边集合 \(E\) 构成，构建方式如下：

  • 为每个事务创建一个节点
  • 如果事务 \(T_i\) 读取了 \(T_j\) 已经写操作的数据项，创建一条有向边 \(T_j \to T_i\)
  • 如果事务 \(T_i\) 对 \(T_j\) 已经读取的数据项进行写操作，创建一条有向边 \(T_j \to T_i\)
  • 如果事务 \(T_i\) 对 \(T_j\) 写操作的数据项进行写操作，创建一条有向边 \(T_j \to T_i\)

如果 \(S\) 的先后顺序图中出现了有向边 \(T_i \to T_j\)，则在任何与之等价的串行调度 \(S'\) 中，\(T_i\) 必须出现在 \(T_j\) 的前面。如果先后顺序图中存在环，则该调度不可串行化。

数据冲突引起的问题

  • 脏读（read uncommitted data）：在 \(T_2\) 提交前，\(T_1\) 读取了 \(T_2\) 已经修改了的数据
  • 不可重复读（unreaptable read）：在 \(T_2\) 提交前，\(T_1\) 修改了 \(T_2\) 已经读取的数据，如果 \(T_2\) 再次读取该数据，则发现不同的值
  • 更新丢失（overwrite uncommitted data）：在 \(T_2\) 提交前，\(T_1\) 修改了 \(T_2\) 已经写的数据。

事务的隔离级别

read uncommit 读脏数据 unreaptable read 不可重复读 overwrite uncommitted 更新丢失
Read uncommited 读未提交 可能 可能 可能
Read committed 读已提交 不可能 可能 可能
Reaptable read 重复读取 不可能 不可能 可能
Serializable 不可能 不可能 不可能

解决冲突的乐观与悲观方法

悲观技术

加锁方法

加锁是并发事务串行化的常用方法。事务必须在开始数据库读/写操作前获声明一个共享的（读）锁或者互斥的（写）锁。如果声明互斥锁，锁禁止其他事务修改甚至读取该数据项。

  • 共享锁：如果事务在一个数据项上加上共享锁，他只能读而不能更新该数据项。
  • 互斥锁：如果事务在一个数据项上加上互斥锁，他既能读也能更新该数据项。

读操作是不冲突的，因此允许多个并发事务获取数据项的共享锁。另一方面，互斥锁赋予事务对该数据项的独占的访问权限，因此其他事务都无法读取或者更新该数据项。

两段锁（2PL）协议
  • 事务对一个数据项操作之前，必须先获得对该项的锁
  • 一旦事务释放了第一个锁，就不能再获得任何新锁
  • 如果允许对锁进行升级，只能在增长阶段进行，且事务必须等待，直到另一个事务释放了该数据项的共享锁。锁的降级只能在缩减阶段进行

算法：

  • 在Transaction开始时，对每个需要访问的数据加锁；如果不能加锁，就等待，直到加锁成功
  • 执行Transaction的内容
  • 在Transaction commit前，集中进行解锁
  • Commit
锁的粒度
  • 意向锁
    • IS(a)：将对a下面更细粒度的数据元素进行读
    • IX(a)：将对a下面更细粒度的数据元素进行写
  • 为了得到S，IS：所有祖先必须为IS或IX
  • 为了得到X，IX：所有祖先必须为IX
  • 锁兼容性矩阵：
IS IX S X
IS \(\checkmark\) \(\checkmark\) \(\checkmark\) \(\times\)
IX \(\checkmark\) \(\checkmark\) \(\times\) \(\times\)
S \(\checkmark\) \(\times\) \(\checkmark\) \(\times\)
X \(\times\) \(\times\) \(\times\) \(\times\)

时间戳方法

在时间戳方法中，较旧的事务（即有较小时间戳的事务），在冲突事件中有较高的优先级。采用时间戳协议，如果一个事务试图读或写一个数据项。只有当该数据项是由一个较旧事务更新时，这次读写才被允许。否则，请求读/写的事务将重新启动，并获得一个新的时间戳。

每个数据项也有一个读时间戳（其值为最后一个读该数据项事务的时间戳）和一个写时间戳（其值为最后一个写该数据项事务的时间戳）。

时间戳排序协议
  • 事务\(T\)发出一个读请求 \(read(x)\)
    • 若 \(writeTimestamp(x) > T_{timestamp}\)，则事务 \(T\) 将被回滚，并以一个新的时间戳启动
    • 反之，若 \(writeTimestamp(x) \le T_{timestamp}\)，则读操作继续，并令 \(readTimestamp(x) = T_{timestamp}\)
  • 事务T发出一个写请求 \(write(x)\)
    • 若 \(writeTimestamp(x) > T_{timestamp}\)，则事务 \(T\) 将被回滚，并以一个新的时间戳启动
    • 若 \(readTimestamp(x) > T_{timestamp}\)，则事务 \(T\) 将被回滚，并以一个新的时间戳启动
    • 否则，\(readTimestamp(x) \le T_{timestamp}\) 且 \(writeTimestamp(x) \le T_{timestamp}\)，则写操作继续，并令 \(writeTimestamp(x) = T_{timestamp}\)
托马斯写规则
  • 事务T发出一个写请求 \(write(x)\)
    • 若 \(writeTimestamp(x) > T_{timestamp}\)，则写操作被忽略
    • 若 \(readTimestamp(x) > T_{timestamp}\)，则事务 \(T\) 将被回滚，并以一个新的时间戳启动
    • 否则，\(readTimestamp(x) \le T_{timestamp}\) 且 \(writeTimestamp(x) \le T_{timestamp}\)，则写操作继续，并令 \(writeTimestamp(x) = T_{timestamp}\)

乐观技术

乐观技术基于这样一个假设：冲突是罕见的，去除为保证串行化而对事务的延迟，将会更高效。在事务提交时进行检查，若发生冲突，事务必须被回滚。

事务执行分为三个阶段：

  • 读：事务开始执行，读数据到私有工作区，并在私有工作区上完成事务的处理请求，完成修改操作
  • 验证：如果事务决定提交，检查事务是否与其它事务冲突
    • 如果存在冲突，那么终止事务，清空私有工作区
    • 重试事务
  • 写：验证通过，没有发现冲突，那么把私有工作区的修改复制到数据库公共数据中

确认阶段检查事务的读写操作是否发生了冲突。每个事务 \(T\) 开始时分配一个时间戳 \(start(T)\)，事务的验证阶段分配时间戳 \(validate(T)\)，事务的结束阶段 \(finish(T)\)。通过确认检查，需满足以下条件之一：

  • 事务 \(T\) 开始前，所有较旧时间戳的事务 \(S\) 均已完成，即 \(start(T) > finish(S)\)
  • 若事务 \(T\) 在一个较旧的事务 \(S\) 结束前开始，则：
    • \(S\) 所写数据不是当前事务 \(T\) 读取的数据，（保证较旧事务的写不被当前事务读）且
    • 当前事务 \(T\) 进入验证阶段前，所有较旧事务 \(S\) 已完成其写阶段，即 \(validate(T) > finish(S) > start(T)\)。（保证写是串行的）

数据库的恢复

采用如下机制进行恢复：

  • 备份机制：周期地对数据库进行备份
  • 日志机制：跟踪当前事务的状态与数据库的改变
  • 检查点机制：数据库与事务日志文件的同步点

WAL 写前日志

日志文件

日志文件包括下列数据：

  • 事务记录
    • 事务标识符
    • 日志记录类型（事务开始，插入，删除，更新，撤销，提交）
    • 所操作数据项的标识符（更新，插入，删除）
    • 数据项的前像
    • 数据项的后像
  • 检查点记录

如果系统发生故障，恢复过程使用日志对事物进行撤销或重做：

  • 如果“事务开始”和“事务提交”都出现在日志中，使用日志记录来重做，按日志写入更新后字段的后像
  • 如果“事务开始”出现在日志文件中，但是“事务提交”未出现，则进行撤销，根据日志文件中数据项的前像，使数据库恢复到事务开始前的状态

三个范式

第一范式

所有属性都是不可分的基本数据项

第二范式

每个表有且仅有一个数据元素为主键，其他数据元素与主键一一对应

第三范式

指表中的所有数据元素不但要能唯一地被主键所标识，而且他们之间还必须相互独立，不存在其他的函数关系。

No-SQL