数据库基础

现在主流的数据库系统主要是关系数据库。关系数据库使用集合代数等概念和方法来处理数据库中的数据，同时数据组织成描述性的表格。关系数据库通常由三级模式两层映像组成。在具体设计数据库的时候，通常使用范式来对数据库进行约束设计。

近些年 NoSQL（非关系型数据库）应用也比较多，主要以 Key-Value 类数据库为主，比如 Redis。

理论基础

References

SQL 语言

SQL 语言共分为四大类：

  • 数据查询语言 DQL
  • 数据操纵语言 DML
  • 数据定义语言 DDL
  • 数据控制语言 DCL

数据查询语言DQL

数据查询语言 DQL 基本结构是由 SELECT 子句，FROM 子句，WHERE 子句组成的查询块：

SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

数据操纵语言DML

主要有三种形式：

  • 插入：INSERT
  • 更新：UPDATE
  • 删除：DELETE

数据定义语言 DDL

数据定义语言 DDL 用来创建数据库中的各种对象—–表、视图、索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
ALTER
DROP

DDL 操作是隐性提交的！不能 rollback。

数据控制语言 DCL

数据控制语言 DCL 用来授予或回收访问数据库的某种特权，并控制 数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

  • GRANT：授权
  • ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
  • COMMIT [WORK]：提交

回滚—ROLLBACK 回滚命令使数据库状态回到上次最后提交的状态。其格式为:

SQL> ROLLBACK;

References

数据库范式

数据库范式是指数据库中数据满足的某种约束，用来指导数据库设计。随意设计的数据库可能存在数据冗余、插入异常、更新异常、删除异常等。数据库范式用于减少或消除上述异常。

数据库范式分为：1NF,2NF,3NF,BCNF,4NF,5NF。通常设计时只需要考虑到 BCNF。符合高一级的范式，必定符合低一级范式。

1NF

1NF的定义为：符合1NF的关系中的每个属性都不可再分。实际上，1NF 是所有关系型数据库的最基本要求，你在关系型数据库管理系统（RDBMS），例如 SQL Server，Oracle，MySQL 中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。也就是说，只要在 RDBMS 中已经存在的数据表，一定是符合 1NF 的。

函数依赖

函数依赖指出了一张表中属性之间的函数关系：将属性分为 X,Y 两组，对于任意的 X 满足 X 相同时 Y 一定相同，即 X 值确定的情况下，Y 的值也能确定，此时称为 X → Y。从“函数依赖”这个概念展开，还有两个概念：

  • 完全函数依赖：在一张表中，若 X → Y，且对于 X 的任何一个真子集（假如属性组 X 包含超过一个属性的话），X’ → Y 不成立，那么我们称 Y 对于 X 完全函数依赖，记作 X F→ Y。

  • 部分函数依赖假：如 Y 函数依赖于 X，但同时 Y 并不完全函数依赖于 X，那么我们就称 Y 部分函数依赖于 X，记作 X P→ Y，

码

设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（这个“完全”不要漏了），那么我们称 K 为候选码，简称为码。在实际中我们通常可以理解为：假如当 K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定，那么 K 就是码。一张表中可以有超过一个码。（实际应用中为了方便，通常选择其中的一个码作为主码）。

在任意一个码中的属性称为主属性，与之对应的就是非主属性。

2NF

2NF的定义为：非主属性不部分地依赖于码。第二范式要求数据具有唯一性。以学生、学校、学校地址(st,sc,addr)为例，其中学生、学校为主属性，学校地址只依赖于学校，不满足第二范式。如果新办了座学校，校内还没有学生上课，那么就无法插入数据（插入异常）；如果学校地址改变了，需要对每一条数据中的学校地址都进行更新，非常麻烦（更新异常）。

2NF

3NF的定义为：非主属性不传递地依赖于码。第三范式要求任何非主属性不能由其他属性派生出来。

BCNF

BCNF的定义为：主属性既不部分地依赖于码，也不传递地依赖于码。BCNF 要求主属性之间满足唯一且不能由其他属性派生出来。

关于范式的详解可以参考：数据库第一二三范式到底在说什么？。

数据库完整性约束

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

域完整性约束：域完整性是保证数据库字段取值的合理性。属性值应是域中的值，这是关系模式规定了的。除此之外，一个属性能否为 NULL，这是由语义决定的，也是域完整性约束的主要内容。域完整性约束是最简单、最基本的约束。在当今的关系 DBMS 中，一般都有域完整性约束检查功能。包括检查（CHECK）、默认值（DEFAULT）、不为空（NOT NULL）等。

实体完整性

实体完整性是指关系的主关键字不能重复也不能取“空值”。一个关系对应现实世界中一个实体集。现实世界中的实体是可以相互区分、识别的，也即它们应具有某种惟一性标识。在关系模式中，以主关键字作为惟一性标识，而主关键字中的属性(称为主属性)不能取空值，否则，表明关系模式中存在着不可标识的实体(因空值是“不确定”的)，这与现实世界的实际情况相矛盾，这样的实体就不是一个完整实体。按实体完整性规则要求，主属性不得取空值，如主关键字是多个属性的组合，则所有主属性均不得取空值。

参照完整性

参照完整性是定义建立关系之间联系的主关键字与外部关键字引用的约束条件。关系数据库中通常都包含多个存在相互联系的关系，关系与关系之间的联系是通过公共属性来实现的。所谓公共属性，它是一个关系 R (称为被参照关系或目标关系)的主关键字，同时又是另一关系 K (称为参照关系)的外部关键字。如果参照关系 K 中外部关键字的取值，要么与被参照关系 R 中某元组主关键字的值相同，要么取空值，那么，在这两个关系间建立关联的主关键字和外部关键字引用，符合参照完整性规则要求。如果参照关系 K 的外部关键字也是其主关键字，根据实体完整性要求，主关键字不得取空值，因此，参照关系 K 外部关键字的取值实际上只能取相应被参照关系 R 中已经存在的主关键字值。

用户定义完整性

实体完整性和参照完整性适用于任何关系型数据库系统，它主要是针对关系的主关键字和外部关键字取值必须有效而做出的约束。用户定义完整性则是根据应用环境的要求和实际的需要，对某一具体应用所涉及的数据提出约束性条件。这一约束机制一般不应由应用程序提供，而应有由关系模型提供定义并检验，用户定义完整性主要包括字段有效性约束和记录有效性。

数据库事务

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

事务的 ACID

Atomicity

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

Consistency

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。换句话说就是：事务开始和结束之间的中间状态不会被其他事务看到。

Isolation

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

隔离性主要涉及到事务的并发控制，根据不同的并发控制策略，可能出现脏读、不可重复读、幻读。

脏读

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户 A 向用户 B 转账 $100$ 元，对应 SQL 命令如下:

update account set money=money+100 where name=’B’;  (此时A通知B)
update account set money=money-100 where name=’A’;

当只执行第一条 SQL 时，A 通知 B 查看账户，B 发现确实钱已到账（此时即发生了脏读），而之后无论第二条 SQL 是否执行，只要该事务不提交，则所有操作都将回滚，那么当 B 以后再次查看账户时就会发现钱其实并没有转。

不可重复读

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。不可重复读就是一个事务范围内多次查询结果不同。例如事务 T1 读取了某一数据，而事务 T2 立马修改了这个数据并且提交事务给数据库，事务 T1 再次读取该数据就得到了不同的结果，发送了不可重复读。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

虚读(幻读)

幻读是事务非独立执行时发生的一种现象。例如事务 T1 对一个表中所有的行的某个数据项做了从 $1$ 修改为 $2$ 的操作，这时事务 T2 又对这个表中插入了一行数据项，而这个数据项的数值还是为 $1$ 并且提交给数据库。而操作事务 T1 的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务 T2 中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

Durability

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的 4 种隔离级别

隔离级别 脏读 不可重复读 幻读
Read uncommitted × × ×
Read committed ✓ × ×
Repetable read ✓ ✓ ×
Serializable ✓ ✓ ✓

四种隔离级别，重高到低有：

  • Serializable 串行化
  • Repeatable read 可重复读
  • Read committed 读已提交
  • Read uncommitted 读未提交

Serializable 采用的是范围锁 RangeS、RangeS_S 模式，锁定检索范围为只读，这样就避免了幻影读问题。Repeatable read 读取数据时加共享锁，写数据时加排他锁，都是事务提交才释放锁。读取时候不允许其他事物修改该数据，不管数据在事务过程中读取多少次，数据都是一致的，避免了不可重复读问题。Read committed 修改时加排他锁，直到事务提交后才释放，读取时加共享锁，读取完释放。事务1读取数据时加上共享锁后（这样在事务1读取数据的过程中，其他事务就不会修改该数据），不允许任何事物操作该数据，只能读取，之后1如果有更新操作，那么会转换为排他锁，其他事务更无权参与进来读写，这样就防止了脏读问题。Read uncommitted 相当于不加锁。

在 MySQL 数据库中默认的隔离级别为 Repeatable read (可重复读)。

事务的提交和回滚

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

显式提交

用 COMMIT 命令直接完成的提交为显式提交。其格式为：

SQL> COMMIT;

隐式提交

用 SQL 命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME

自动提交

若把 AUTOCOMMIT 设置为 ON ，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

SQL> SET AUTOCOMMIT ON;

有时执行 SQL 语句会出现异常，又或提交事务失败，总之事务没有成功完成。这种情况下，需要把事务前面执行了的语句回滚，才能保证事务的 ACID。

如果出现了异常导致数据库没有执行到 COMMIT，用户需要手动执行 ROLLBACK 回滚刚才的操作。

undo、redo 与检查点

数据库刚提交的事务通常存放在缓冲区中，等到缓冲区满或者其他需要写入硬盘的时候，才写入硬盘。数据库通常用日志文件记录操作，undo 日志记录某数据被修改前的值，可以用来在事务失败时进行 rollback；redo 日志记录某数据块被修改后的值，可以用来恢复未写入。

数据库先将操作写入日志文件，然后把数据写入缓冲区。等到时机合适时才将数据持久化到硬盘上。这种顺序保证了数据库故障时恢复最后修改的操作，也可以用于 rollback。当进行恢复操作时，对未提交的操作执行 undo，对提交了的执行 redo。

在持久化时，会记录 checkpoint 发生的”时刻“。在故障回复时候，只需要 redo/undo最近的一次 checkpoint 之后的操作。

References

数据库索引

数据库索引是对数据库中一个或多个值进行排序的结构，用于加速数据查询等。数据库索引类似与书籍附录的名词索引，在不用扫描整个数据库表的情况下，允许程序快速找到表中的数据。

索引加快了数据查找速度，提高系统性能。而维护索引需要使用额外的物理空间，同时插入、更新、删除时也需要对索引进行维护。

从底层的视角来看，索引通常以 B+ 树为数据结构。B+ 树是多路搜索树，所以能保证索引的查找速度。对于高纬度如 GIS 数据索引，可以考虑使用 R 树作为数据结构。

从物理空间的角度来看，索引通常分为聚簇索引和非聚簇索引。聚簇索引是指表中的数据存储顺序和索引的顺序一致；非聚簇索引正好相反。因此，聚簇索引一张表只能有一个。非聚簇索引又通常被成为辅助索引。

在实际的程序逻辑中，有主键索引、唯一索引和普通索引。主键索引要求内容不能为空值，唯一索引要求数据不能重复。自然的，主键索引是唯一索引的特殊情况，其要求能唯一确定具体表项。一般情况下，DBMS 会在主键上建立聚簇索引，比如 Mysql 的 Innodb。如果索引不止建立在一列上，就称之为联合索引（与之对应的成为单列索引），一般情况下，联合索引使用时通常按照最左前缀原则匹配。

References

锁与并发控制

锁

从功能角度来看：

  • 共享锁：允许多个读，不允许写；
  • 排他锁：只允许一个读写；
  • 意向锁

共享锁、排他锁通常有具体作用范围：表、页、行。有时需要给整个表加锁，加锁前需要检查有无事务对某一行加了锁，如果一行一行检测，效率是不可接受的。这种情况下，DBMS 要求事务在某子节点加上读、写锁时，要持有父节点的意向锁；后面加锁的事务只需要检查父节点的意向锁就能得知。

从范围角度来看：

  • 行级锁：表示只针对当前操作的行进行加锁
  • 表级锁：表示对当前操作的整张表加锁
  • 页级锁：介于行级锁和表级锁之间

下面看看 Mysql 中的行级锁、表级锁及页级锁。

类别 开销 速度 冲突率 粒度 并发度
行级锁 最大 最慢 低 最小 最高
表级锁 小 快 最高 最大 最低
页级锁 一般 一般 一般 一般 一般

References

两阶段锁协议

整个事务分为两个阶段，前一个阶段为加锁，后一个阶段为解锁。在加锁阶段，事务只能加锁，也可以操作数据，但不能解锁，直到事务释放第一个锁，就进入解锁阶段，此过程中事务只能解锁，也可以操作数据，不能再加锁。两阶段锁协议使得事务具有较高的并发度，因为解锁不必发生在事务结尾。它的不足是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。如两个事务分别申请了A, B锁，接着又申请对方的锁，此时进入死锁状态。

并发控制

数据库管理系统（DBMS）中的并发控制的任务是：

  1. 确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性；
  2. 统一性以及数据库的统一性；

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段：

  1. 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作；
  2. 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性；

无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。所以，不要把乐观并发控制和悲观并发控制狭义的理解为 DBMS 中的概念，更不要把他们和数据中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在 DBMS 中，悲观锁正是利用数据库本身提供的锁机制来实现的。

在 Mysql 中，悲观锁通过 select for update 实现，乐观锁则在表中加上 version 字段实现。

具体关于加锁时机可以参考深入详解乐观锁和悲观锁、乐观锁、悲观锁。

事务隔离级别和乐观锁、悲观锁的关系：事务隔离级别是并发控制的整体解决方案，其实际上是综合利用各种类型的锁和行版本控制，来解决并发问题。锁是数据库并发控制的内部机制，是基础。对用户来说，只有当事务隔离级别无法解决一些并发问题和需求时，才有必要在语句中手动设置锁。

数据库扩展

随着业务规模的不断扩大，需要选择合适的方案去应对数据规模的增长，以应对逐渐增长的访问压力和数据量。

关于数据库的扩展主要包括：主从复制、读写分离、数据库分库与分表。

References

Mysql

References

Redis & Memcached