MySQL的约束、锁、范式

1.表与表的对应关系

        一对一：学生与手机号，一个学生对一个手机号

        一对多：班级与学生，一个班级对应多个学生

        多对多：学生与科目，一个学生对应多个科目，一个科目也对应多个学生

2.约束

2.1作用

        约束是一种限制，用于限制表中的数据，为了保证数据的准确性以及可靠性

2.2约束分类

（1）.not null，非空，用于保证某个字段不为空，支持列级约束

（2）.default，默认，用于保证某个字段具有默认值，支持列级约束

（3）.primary，主键，用于保证某个字段具有唯一性且非空，支持列级约束以及表级约束。

（4）.unique，唯一，用于保证某个字段具有唯一性。支持列级约束以及表级约束。

（5）.forgien key，外键，用于限制两个表间的关系。支持表级约束。

列级约束：指的是定义列的同时指定的约束。

表级约束：指的是列定义之后指定的约束。

外键常用于一对多的关系。即表的某条数据，对应另外一张表的多条数据。
将 “一” 的一方称为 ：主表。

将 “多” 的一方称为 ：从表。
通常将 外键 置于从表上，即从表上增加一列作为外键，并依赖于主表的某列。

3.锁

3.1根据锁的粒度分：行锁与表锁

表级锁：只有当前用户可以操作整张表，其他请求排队等候，等待当前sql操作执行完毕。

特点：开销小，加锁快。不会出现死锁，锁定粒度大，发生锁冲突的概率高，并发性极差，一致性极好。

行级锁：只有当前用户可以操作该行记录，其他请求排队等候，等待当前sql操作执行完毕。

特点：开销大，加锁慢。会出现死锁，锁定粒度小，发生锁冲突的概率低，并发度高。

页面锁

开销时间、加锁时间、锁定粒度在表级锁与行级锁之间，会出现死锁，并发度中等。

3.2根据数据库系统分：读锁与写锁

读锁

其他请求可读不可写（共享），用于不更改或不更新数据的操作（只读操作），如select语句。

select加共享锁

select * from [table] lock in share mode;

写锁

其它请求不可读不可写（排他），用于数据修改操作，例如insert、update、或delete确保不会同时同一资源进行多重更新。

SELECT加排他锁：

SELECT * FROM [TABLE] FOR UPDATE;

注意：select...for update语句在执行中所有扫描过的行都会被锁上，因此在MySQL中用悲观锁，务必确定走了索引，而不是全表扫描，否则将会把整个数据表锁住。

3.3引擎对比MyISAM与InnoDB

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

InnoDB会自动给UPDATE、DELETE和INSERT语句涉及的数据集加排他锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。     

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

3.4根据数据库使用方式分：乐观锁与悲观锁

悲观锁

定义：悲观锁，正如其名，它指的是对数据对外界（包括当前系统的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性），否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据。

悲观锁并不是适用于任何场景，它也存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

乐观锁

定义

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

接下来我们看一下乐观锁在数据表和缓存中的实现。

实现方式：

1 版本号

2 时间戳

每次查询，记录当前行（row）的版本号(version)或时间戳(timestamp)，在提交修改前判断版本号是否改变，没有改变则提交数据并更新版本号（+1）。

4.数据库范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

规范化目的是使数据表结构更合理，消除存储异常，使数据冗余尽量小。便于插入，删除和更新。

相关概念

1) 函数依赖：若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y；

2) 传递函数依赖：假如 Z 函数依赖于 Y，且 Y 函数依赖于 X （Y 不包含于 X，且 X 不函数依赖于 Y），那么我们就称 Z 传递函数依赖于 X ，记作 X Z；

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

4) 非主属性：不包含在任何一个码中的属性为非主属性，反之则为主属性；

1 第一范式（1NF）

所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。

错误示例：

姓名	联系方式
	手机号	固定电话号

正确示例：

姓名

手机号

固定电话号

2 第二范式（2NF）

第二范式建立在1NF的基础上，即满足第二范式一定满足第一范式，第二范式要求数据表每一个实例或者行必须被唯一标识。除满足第一范式外还有两个条件，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

3 第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息（在一个表中出现的非主字段，不允许出现在其他表中）。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。

错误示例：

学生id	姓名	班级id	班级名称	科目id	科目名称	成绩

正确示例：

学生表

学生id

姓名

班级id

班级表

班级id

班级名称

班主任

科目表

科目id

科目名称

学生成绩表

学生id

科目id

成绩

4 巴斯-科德范式（BCNF）

Boyce-Codd Normal Form（巴斯-科德范式）

在3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖），巴斯-科德范式（BCNF）是第三范式（3NF）的一个子集，即满足巴斯-科德范式（BCNF）必须满足第三范式（3NF）。

通常情况下，巴斯-科德范式被认为没有新的设计规范加入，只是对第二范式与第三范式中设计规范要求更强，因而被认为是修正第三范式，也就是说，它事实上是对第三范式的修正，使数据库冗余度更小。这也是BCNF不被称为第四范式的原因。某些书上，根据范式要求的递增性将其称之为第四范式是不规范，也是更让人不容易理解的地方。而真正的第四范式，则是在设计规范中添加了对多值及依赖的要求。

5 第四范式（4NF）

4NF就是限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖；

第四范式是BCNF的子集，即如果一个关系模式是4NF，则必为BCNF。