数据库定义

数据库是保存有组织的数据的容器

表是某种特定类型数据的结构化清单

存储过程

存储过程是一些预编译的 SQL 语句，更加直白的说，存储过程是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能，然后再给这个代码块取个名字，在用到这个功能的地方调用他就可以了。

优点

• 存储过程是一个预编译的代码块，执行效率比较高
• 可以在一定程度上确保数据安全
• 一个存储过程替代大量 T-SQL 语句，可以降低网络通信量，提高通信速率

事务

事务是并发控制的基本单位，指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

事务特性 ACID

原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时方向执行这些修改操作即可

一致性（Consistency）

数据库在事务执行前后都保持一致性状态。

隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

持久性（Durability ）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

对于事务的理解

• 只有满足一致性，事务的执行结果才是正确的
• 在无并发的情况下，事务串行执行，隔离性一定能够满足要求，此时只要满足原子性，就一定可以满足一致性
• 在并发情况下，多个事务并发执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化就是为了能够应对数据库崩溃的情况

事务隔离级别

未提交读（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其它事务也是可见的。

提交读（READ COMMITTED）：一个事务所做的修改在提交之前对其他事务是不可见的

可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样数据的结果是一样的

可串行化（SERIALIXABLE）：强制事务串行执行

并发

一致性非锁定读

InnoDB 存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上的锁释放，相反地，InnoDB 存储引擎会去读取行的而一个快照数据。这种机制极大提高了数据库的并发性。

并发带来的问题

修改丢失

T1 更新记录 r 但未提交，T2 更新记录 r 但未提交，T1 提交，T2 提交。T1 的操作被 T2 覆盖。解决方案：T1 和 T2 分别加一个 X 锁

读脏数据

脏读是指一个事务可以读到另外一个事务中未提交的数据，这违反了数据库的隔离性

不可重复读

在第一个事务两次读取数据之间，由于第二个事务已提交的修改，那么第一个事务两次读到的数据可能是不一样的，这违反了数据库的一致性

幻影读

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

解决方案

产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，比较复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

封锁

封锁粒度

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

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

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

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

封锁类型

读写锁

排它锁（Exclusive），简写为 X 锁，又称写锁。

共享锁（Shared），简写为 S 锁，又称读锁。

对于读写锁，有如下两个规定：

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

读写锁的兼容关系

意向锁

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

在存在行级锁和表级锁的情况下，事务 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 锁失败。

例如：如果对页上的记录 r 进行上锁，那么分别需要堆数据库 A，表，页上意向锁 IX，最后对记录 r 上 X 锁。

以上锁的兼容关系：

• 任意 IS/IX 锁之间都是兼容的，因为它们只是表示想要对表加锁，而不是真正加锁
• S 锁只与 S 锁和 IS 锁兼容，也就是说事务 T 想要对数据行加 S 锁，其它事务可以已经获得对表或者表中的行的 S 锁

锁

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。

解决死锁问题最简单的一种方法是超时，即当两个事务相互等待时，当一个等待时间超过设置的某一个阈值时，其中一个事务进行回滚，另一个等待事务就能继续执行。

数据库中对于死锁的检测使用的是 wait-for graph 等待图，等待图要求数据库保存一下两种信息：锁的信息链表和事务等待链表。

事务 T1 指向 T2 边的定义为：事务 T1 最终等待 T2 所占用的资源，也就是事务之间在等待相同的资源，而事务 T1 发生在事务 T2 的后面，若存在回路则存在死锁

悲观锁

在关系数据库管理系统里，悲观并发控制是一种并发控制的方法，它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都堆某行数据应用了锁，那么只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作，悲观并发控制主要应用于数据竞争较为激烈的环境，以及发生并发冲突时能够使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁：对数据被外界修改持悲观态度，因此，在整个数据处理过程中，将数据处于锁定状态。

悲观锁的实现

1、在对任意记录进行修改前，先尝试为该记录加上排他锁

2、如果加锁失败说明该记录正在被修改，那么当前查询可能要等待或者抛出异常

3、如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了

4、期间如果有其他对该记录做修改或加排他锁的操作，都会等待或者抛出异常

悲观锁的使用

//0.开始事务
begin;
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;

利用 select…for update 的方式开启排他锁实现了悲观锁

优缺点

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

乐观锁

在关系型数据库管理系统中，乐观并发控制是一种并发控制方法，它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据，在提交时数据更新之前，每个事务会先检测该事务读取数据后，有没有其他事务又修改了该数据，如果有其他事务更新的话，正在提交的事务会进行回滚。

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

乐观锁的实现

乐观锁并不会使用数据库提供的锁机制，一般的实现方式就是记录数据版本，实现数据版本有两种方式：使用版本号和使用时间戳

版本号实现

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作,并判断当前版本号是不是该数据的最新的版本号。

优缺点

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

范式

数据冗余

一个字段在多个表里重复出现，举个例子，如果每条客户购买商品的信息里都连带记录了客户自身的信息，这样的数据冗余可能造成不一致，因为客户自身的信息可能不一样

函数依赖

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

如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码（primary key）。

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

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

异常

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

不符合范式的关系，会产生很多异常，主要有以下四种异常：

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

范式理论

范式理论就是为了解决以上提到的四种异常，高级别范式的依赖于低级别的范式，一个数据库设计如果符合第二范式，一定也符合第一范式；如果符合第三范式，也一定符合第二范式，越高的范式数据冗余越小

第一范式（1NF）

属性不可分，1NF 是对属性的原子性的约束，要求属性具有原子性，不可再分解

第二范式（2NF）

每个非主属性完全函数依赖于键码，2NF 是对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性，更通俗说有主键 ID，可以通过分解来满足

分解前

在上面的学生课程关系中，{Sno，Cname} 为键码（主键），有如下函数依赖：

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

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

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

分解后

关系 -1

有以下函数依赖：

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

关系 -2

有以下函数依赖：

• Sno, Cname -> Grade

第三范式（3NF）

非主属性不传递依赖于键码，3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余

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

• Sno -> Sdept -> Mname

现在可以进行以下分解：

关系 -11

关系 -12

索引和 B+ 树

B+ 树

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

索引

索引是对数据库表中一或多个列的值进行排序的结构，是帮助 MySQL 高效获取数据的数据结构

聚集索引

聚集索引就是按照每张表的主键构造一课 B+ 树，同时叶子节点中存放的既为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。

辅助索引

辅助索引（非聚集索引）叶子节点并不包含行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点中的索引行还包含一个书签（相应行数据的聚集索引键）。

当通过辅助索引来寻找数据的时候，InnoDB 存储引擎会遍历辅助索引闭并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

优缺点

• 索引加快数据库的检索速度
• 索引降低了插入、删除、修改等维护任务的速度（大部分数据更新需要同时更新索引）
• 索引可以确保数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
• 索引需要占用物理和数据空间

视图

视图是一种基于数据表的一种虚表

• 视图是一种虚表
• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表
• 向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句
• 视图向用户提供基表数据的另一种表现形式
• 视图没有存储真正的数据
• 一个基表可以有 0 个或者多个视图

数据库连接语法

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

创建连接

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

内连接

内连接又称为等值连接，基于两个表之间的相等测试，两个表之间的关系以特定的关键字 INNER JOIN 指定，连接条件用特定的 ON 子句给出

SELECT A.value, B.value FROM table AS A INNER JOIN table AS B ON A.key = B.key;

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已

例如：一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名

SELECT e1.name FROM employee AS e1 INNER JOIN employee AS e2 ON e1.department = e2.department AND e2.name = "Jim";

外部连接

许多连接将一个表中的行与另一个表中的行相关联，但有时候会需要包含没有关联行的那些行，连接包含了那些在相关表中没有关联行的行就称之为外部连接。

外部连接使用关键字 OUTER JOIN 来指定联结的类型，必须使用 RIGHT 或 LEFT 关键字指定包含其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。

内部链接

检索所有客户的订单

SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id;

外部连接

检索所有客户，包括那些没有订单的客户

SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

参考

• 数据库系统概念
• SQL 必知必会
• MySQL 技术内幕：InnoDB 存储引擎