数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，是一个按数据结构来存储和管理数据的计算机软件系统。

1 并发控制

1.1 事务

  • 事务（transaction）指一组 SQL 语句；
  • 回退（rollback）指撤销指定 SQL 语句的过程；
  • 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
  • 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退

事务的 ACID 特性：

  • 原子性（Atomicity）（并发控制）
  • 一致性（Consistency）——事务开始前后，数据库的完整性不会被破坏。（并发控制、日志恢复）
  • 隔离性（Isolation）——事务操作在最终提交前对其他事务不可见，不会因为交叉执行导致数据不一致。（日志恢复）
  • 持久性（Durability）——事务处理结束提交后，对数据的修改就是永久的。（日志恢复）

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

1.2 并发一致性问题

产生原因：破坏了事务的隔离性；解决方法：并发控制。

  • 丢失修改（lost update）
    • 第一类：A 事务撤销时，把已经提交的 B 事务的更新数据覆盖了。
    • 第二类：A 事务覆盖 B 事务已经提交的数据，造成 B 事务所操作丢失。
  • 读脏数据（dirty read）：A 事务读取了 B 事务尚未提交的更改数据，并且在这个数据基础上进行操作。如果此时恰巧 B 事务进行回滚，那么 A 事务读到的数据是不被承认的。
  • 不可重复读（unrepeatable read）：A 事务读取了 B 事务已经提交的更改数据，两次读取的数据不一致。
  • 幻读（phantom read）：A 事务读取了 B 事务已经提交的新增数据，两次读取的数据不一致。

隔离级别

  • 未提交读（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其它事务也是可见的。
  • 已提交读（READ COMMITTED）：一个事务只能读取已经提交的事务所做的修改。
  • 可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同一数据的结果是一样的。
  • 可串行化（SERIALIZABLE）：强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。
隔离级别读脏数据不可重复读幻读
未提交读可能可能可能
已提交读不可能可能可能
可重复读不可能不可能可能
可串行化不可能不可能不可能

幻读的解决办法

  • 快照读：简单的 select 操作 —— 通过 MVCC 来避免幻读
  • 当前读：特殊的读操作，插入/更新/删除操作 —— 通过 Next-Key 来避免幻

1.3 并发控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。

  • 版本号，MVCC 事务使用两个版本号：
    • 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
    • 事务版本号：事务开始时的系统版本号。
    • MVCC 在每行记录后面都保存着两个隐藏的列，用来存储创建版本号和删除版本号。
  • 在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照，存储在 Undo 日志中。
  • InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

Next-Key Locks（间隙锁和行锁合称 Next-Key Locks，表示锁定一个范围（前开后闭））

  • MySQL 的 InnoDB 存储引擎的一种锁实现。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。
  • 不仅锁定一个记录上的索引，也锁定索引之间的间隙。

1.4 封锁

封锁粒度：行级锁、表级锁、全局锁

封锁协议（行级锁）

  • 三级封锁协议
    1. 一级封锁协议：事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以解决丢失修改问题。
    2. 二级封锁协议：在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。可以解决读脏数据问题。
    3. 三级封锁协议：在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。可以解决不可重复读的问题。
  • 两段锁协议
    • 加锁和解锁分为两个阶段进行。
      1. 在对任何数据进行读、写操作之前，要申请并获得对该数据的封锁。
      2. 每个事务中，所有的封锁请求先于所有的解锁请求。
    • 可串行化调度：通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。
    • 事务遵循两段锁协议是保证可串行化调度的充分条件。

封锁类型：

  1. 读写锁
    • 互斥锁（Exclusive），简写为 X 锁，又称写锁。
    • 共享锁（Shared），简写为 S 锁，又称读锁。
  2. 意向锁
    • 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
    • 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

数据库语句会隐式采用必要的锁

  • 行独占锁：INSERT, UPDATE, DELETE
  • 行共享锁：SELECT FOR UPDATE

2 数据库设计

2.1 关系数据库理论

  • 函数依赖：在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X -> Y。
    • 完全函数依赖：假如 Y 函数依赖于 X，且对于 X 的任何一个真子集X’，X’ -> Y 不成立，那么我们称 Y 对于 X 完全函数依赖，记作 X F-> Y。
    • 部分函数依赖：假如 Y 函数依赖于 X，但同时 Y 并不完全函数依赖于 X，那么我们就称 Y 部分函数依赖于 X，记作 X P-> Y
  • 码：设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K，那么我们称 K 为候选码，简称为码。
  • 主属性：包含在任何一个码中的属性成为主属性。

范式

不符合范式的关系，会产生主要四种异常：

  • 冗余数据
  • 修改异常
  • 删除异常
  • 插入异常

范式是符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。

  1. 第一范式 1NF：每个属性都不可再分。
  2. 第二范式 2NF：在 1NF 的基础上，消除非主属性对码的部分函数依赖。
  3. 第三范式 3NF：在 2NF 的基础上，消除非主属性对码的传递函数依赖。
  4. BCNF：在 3NF 的基础上，消除主属性对码的部分和传递函数依赖。
  5. 第四范式 4NF：在 BCNF 的基础上，不存在非平凡多值依赖。

2.2 完整性约束

  1. 实体完整性：规定表的每一行在表中是惟一的实体。
  2. 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
  3. 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
  4. 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

2.3 数据库模型

  • 概念模型：实体关系图 Entity-Relationship，ER，有三个组成部分：实体（矩形）、属性（菱形）、联系。
  • 逻辑模型：二维表
  • 物理模型：表结构

3 SQL

SQL（Structured Query Language)，结构化查询语言，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。

3.1 视图

视图是虚拟的表，本身不包含数据，不能对其进行索引操作。对视图的操作和对普通表的操作一样。

视图具有如下好处：

  • 简化复杂的 SQL 操作，比如复杂的连接；
  • 只使用实际表的一部分数据；
  • 通过只给用户访问视图的权限，保证数据的安全性；
  • 更改数据格式和表示。

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。触发器中 BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

⤧  Next post 【DB】数据库 02 MySQL ⤧  Previous post 【Java】Java 04 Java虚拟机