MySQL(六) - A Sort Of A Blog

本篇，我们说说 MySQL 中的外键、视图以及事务安全等问题。

外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。
以另一个关系的外键作为主关键字的表称为主表，具有此外键的表称为主表的从表，外键又称为外关键字。

外键操作

增加外键

MySQL 中提供两种方式增加外键:

在创建表时，直接新增外键
基本语法: [constraint {外键名}] foregin key({外键字段}) references {主表}(主键);
在创建表后，再新增外键
基本语法: alter table {从表名} add [constraint {外键名}] foregin key({外键字段}) references {主表}(主键);

外键基本要求

外键字段需要保证与关联的主表的主键字段类型一致
基本属性也要相同
如果在表后增加外键，对数据有一定要求
外键只能使用 innodb 引擎

外键约束

外键约束: 通过建立外键关系之后，对主表和从表都会有一定数据约束。

约束的基本概念

当一个外键产生时，从表会受制于主表数据的存在从而导致数据不能进行某些不符合规范的操作(不能插入主表不存在的数据)
如果一个表被其他表外键引入，那么该表的数据操作就不能随意，必须保证从表数据的有效性(不能删除从表存在的数据)

外键约束的概念

可以在创建外键时，对外键约束进行选择性的操作。

基本语法: add foregin key({外键字段}) references {主表名}(主键) on {约束模式};

约束模式有三种:

district，严格模式，默认，不允许操作
cascade，级联模式，一起操作变化
set null，置空模式，主表变化，从表字段置空，前提是从表该外键字段允许为空

常用约束: on update cascade on delete set null;

约束的作用

保证数据的完整性: 主表数据与从表数据一致。
但是外键很强大，但是很少使用，因为其可能会导致业务无法把握。

视图

视图基本操作

创建视图

视图的本质是 SQL 指令(select 语句)。
基本语法: create view {视图名} as select 指令

-- 两表具有同名字段会有问题
create view student_v as select * from my_student as s left join my_class as c on s.class_id = c.class_id;

create view student_v as select s.*, c.name from my_student as s left join my_class as c on s.class_id = c.class_id;

使用视图

视图是一张虚拟表，可以把视图当作”表”来操作，但是视图本身没有数据，是临时执行 select 语句得到对应的结果。
基本语法: select 字段列表 from {视图名};

select * from student_v;

修改视图

本质是修改视图对应的查询语句。
基本语法: alter view {视图名} as 新 select 指令

alter view student_v as select * from my_student as s left join my_class as c using(class_id);

删除视图

基本语法: drop view {视图名};

drop view student_v;

事务

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言书写的用户程序的执行所引起的。
事务由事务开始(Begin Transaction)和事务结束(End Transaction)之间执行的全体操作组成。

事务的基本原理

MySQL(Innodb) 允许将事务统一进行管理，将用户操作暂存，不直接操作数据表，等用户确认结果之后再进行操作。
事务在 MySQL 中通常是自动提交，也可以手动事务。

自动事务

自动事务(autocommit): 当客户端发送一条 SQL 指令(写操作)，给服务器时，服务器在执行之后，不用等待用户反馈结果，自动将结果同步到数据表。
关闭自动事务: set autocommit = off;
一旦关闭自动事务，就需要用户来提供是否同步的指令:

Commit;: 提交(同步到数据表并清空该事务)
Rollback;: 回滚(清空该事务)

执行事务端的客户端，在提供是否同步指令之前，该客户端仍可以根据日志对数据加工，返回同步之后的结果。

通常我们不关闭自动事务。

手动事务

开启事务

从该语句开始，之后的语句都不会直接写入数据库，而是保存在事务日志中。

-- 开启事务
start transaction; 
-- 有的数据库使用 begin transaction;

执行事务

执行需要在事务中执行的 SQL 指令。

提交事务或回滚

-- 提交
commit;
-- 回滚
rollback;

回滚点(savepoint)

当有一系列事务操作，可以设置回滚点，使得当有事务操作失败时，可以回到该位置重新开始执行。
基本语法: savepoint {回滚点名};

-- 设置回滚点
savepoint sp1;

-- 回到回滚点
rollback sp1;

事务特点

事务具有四个特性(ACID):

原子性(Atomicity): 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚
一致性(Consistency): 数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的
隔离性(Isolation): 一个事务所做的修改在最终提交以前，对其它事务是不可见的
持久性(Durability): 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系:

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

并发一致性问题

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

封锁

封锁粒度

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 锁失败。

封锁协议

一级封锁协议: 事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖
二级封锁协议: 在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据
三级封锁协议: 在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变