2-4 mysql 约束 & 锁 & 范式

约束

作用

• 限制表中的数据，确保数据的准确性和可靠性。

约束分类

列级约束与表级约束

• 列级约束：列级约束是字段定义的一部分，只能应用于一个字段上。除了外键没效果，其他都支持列级约束。

create table t_student（
	id int not null,
	name varchar(32) not null,
	email varchar(64) unique
)

• 表级约束：表级约束独立于字段定义，可应用于一个表的多个字段上。除了默认、非空，其他都支持表级约束。

create table t_student（
	id int not null,
	name varchar(32) not null,
	email varchar(64),
	unique(email)  # 表级约束
)

create table t_student（
	id int not null,
	name varchar(32) not null,
	email varchar(64),
	# 表级约束可以命名，方便以后删除
	constraint t_student_email_unique unique（email)
)

基本约束

1. primary key（主键）

• 用于保证字段唯一且不为空
• 支持列级约束以及表级约束。

2. foreign key（外键）

• 用于限制两个表之间的关系
• 支持表级约束。

3. unique（唯一）.

• 用于保证字段唯一
• 支持列级约束以及表级约束

4. not null（非空）

• 用于保证某一字段非空
• 支持列级约束

5. default（默认值）

• 用于保证某一字段具有默认值
• 支持列级约束

6. MySQL 不支持check约束，语法不报错但无效果

表与表的对应关系

1. 一对一：学生与手机号，一个学生对一个手机号。
2. 一对多：班级与学生，一个班级对应多个学生。
3. 多对一：班级与学生，多个学生对应一个班级。
4. 多对多：学生与科目，一个学生对应多个科目，一个科目也对应多个学生。

• 外键常用于一对多的关系。即表的某条数据，对应另外一张表的多条数据。
  将 “一” 的一方称为 ：主表。
  将 “多” 的一方称为 ：从表。
  通常将 外键 置于从表上，即从表上增加一列作为外键，并依赖于主表的某列。

锁

锁的分类

按锁的粒度分类

• 锁的粒度，即被封锁的对象的粒度。例如数据项、记录、文件或整个数据库。
• 锁粒度越小事务的并行度越高。
• InnoDB 和 Oracle 支持行锁和表锁。而 MyISAM 只支持表锁，MySQL 中的 BDB 存储引擎支持页锁和表锁。SQL Server 可以同时支持行锁、页锁和表锁。
  

1. 表级锁
   优点：开销小，加锁快，不会出现死锁。
   缺点：锁的粒度大，发生锁冲突的概率高，并发度低。
2. 行级锁
   优点：锁的粒度小，发生锁冲突的概率低，并发度高。
   缺点：开销大，加锁慢，会出现死锁。
3. 页面锁
   表级锁与行级锁的折中，会出现死锁，并发度中等。

从数据库管理角度分类

共享锁

又称读锁、S锁，其他请求可读不可写，一般用于select语句。
select语句加共享锁：

select * from [table] lock in share mode;

排他锁

又称写锁、X锁，其他请求不可读不可写， 用于insert、update、delete语句，确保不会同时对同一资源进行多次重复更新。
select语句加排他锁：

select * from [table] for update;

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

意向锁

• 意向共享锁（IS）、意向排他锁（IX）。

从程序员的角度分类

乐观锁

• 乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。
• 实现方式：1. 版本号 2. 时间戳
  每次查询，记录当前行的版本号或时间戳(timestamp)，在提交修改前判断版本号是否改变，没有改变则提交数据并更新版本号（+1）。

悲观锁

定义

• 悲观锁(Pessimistic Locking)，正如其名，它指的是对数据被外界（包括当前系统的其它事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排它性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。
• 悲观锁并不是适用于任何场景，它也存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

数据库引擎对比：MyISAM与InnoDB

MyISAM

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

InnoDB

• InnoDB会自动给UPDATE、DELETE和INSERT语句涉及的数据集加排他锁。更新时一般是加行锁（通过索引实现），但是在默认的情况下，select是不加任何行锁的，如果需要，可以使用语句手动添加共享锁或排他锁。
• InnoDB行锁是通过给索引上的索引项加锁来实现的，而Oracle是通过在数据块中对相应数据行加锁来实现的。
• InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

数据库范式

范式简介

• 范式，关系型数据库遵从的规范要求。
• 关系型数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。
• 数据库规范化的目的：使结构更加合理，消除存储异常，减少数据冗余。便于插入、删除和更新。

数据库相关概念

函数依赖

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

部分函数依赖

AB→C，存在AB的真子集→C，那么说C部分依赖于AB。

完全函数依赖

AB→C，AB的任意真子集 ！→ C，那么C完全依赖于AB。

传递函数依赖

X→Y， Y→Z， Z不是Y的子集,，Y！→X, 则称Z对X传递函数依赖。

码

• 码是数据系统中的基本概念，是一个或多个属性的集合。它包括超码，候选码，主码。

超码

• 超码是一个或多个属性的集合，这些属性可以让我们在一个实体集中唯一地标识一个实体。如果k是一个超码，那么k的任意超集也是超码，也就是说如果k是超码，那么所有包含k的集合也是超码。

候选码

• 候选码是从超码中选出的，自然地候选码也是一个或多个属性的集合。因为超码的范围太广，很多是我们并不感兴趣即无用处的。所以候选码是最小超码，其任意真子集都不能成为超码。例如，如果k是超码，那么所有包含k的集合都不能是候选码；如果k，j都不是超码，那么k和j组成的集合（k，j）有可能是候选码。
• 候选码的诸属性称为主属性。

主码

• 从多个候选码中任意选出一个做为主码，如果候选码只有一个，那么候选码就是主码。虽然说主码的选择是比较随意的，但在实际开发中还是要靠一定的经验，不然开发出来的系统会出现很多问题。一般来说主码都应该选择那此从不或者极少变化的的属性。
  

范式类型

第一范式（1NF）

• 每一列都是不可分割的基本数据项。
  

第二范式（2NF）

• 在1NF的基础上，消除部分函数依赖。
• 表必须有一个主键。
• 非主属性必须完全函数依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）

• 在2NF的基础上，消除传递函数依赖。
• 任何非主属性不依赖于其它非主属性，即在一个表中出现的非主属性，不允许出现在其他表中。
  

巴斯-科德范式（BCNF）

• 建立在3NF的基础上。
• 任何非主属性不能对主键子集依赖。

第四范式（4NF）

第五范式（5NF）