【数据库】数据库设计规则、约束与索引概念

约束与索引

1、关系型数据库设计规则

关系型数据库设计规则

遵循ER模型和三范式

• E entity 代表实体的意思 对应到数据库当中的一张表
• R relationship 代表关系的意思

ER模型 实体关系模型 Java OR Object relationship Hibernate、Mybatis ORM 对象关系映射

数据表中的那些键

数据库中的键（Key）由一个或者多个属性组成。

常用的几种键和属性的定义：

• 超键：能唯一标识元组的属性集叫做超键。

  唯一标识元组

• 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。

  不包括多余属性的超键 候选键

• 主键：用户可以从候选键中选择一个作为主键。

  候选键的一个 主键

• 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。

​ 外键 主键

• 主属性：包含在任一候选键中的属性称为主属性。

  包含在任一候选键的属性称为主属性

• 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

通常，也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，还可以用主属性和非主属性来进行区分。

候选键 码 主键 主码

• 三范式：1、列不能拆分 2、唯一标识 3、关系引用主键

1.冗余、重复 列不能拆分、设计表列细到不能拆分

2.唯一标识 要区分

3.关系引用主键 一般引用尽量引用主键、唯一键

从 1NF 到 3NF

范式的定义会使用到主键和候选键（因为主键和候选键可以唯一标识元组）

1NF 指的是数据库表中的任何属性都是原子性的，不可再分。这很好理解，列不能拆分了，在设计某个字段的时候，对于字段 X 来说，就不能把字段 X 拆分成字段 X-1 和字段 X-2。事实上，任何的 DBMS 都会满足第一范式的要求，不会将字段进行拆分。列不能拆分

2NF 指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。所谓完全依赖不同于部分依赖，也就是不能仅依赖候选键的一部分属性，而必须依赖全部属性。 非主属性 候选键完全依赖

3NF 在满足 2NF 的同时，对任何非主属性都不传递依赖于候选键。也就是说不能存在非主属性 A 依赖于非主属性 B，非主属性 B 依赖于候选键的情况。

具体体现

• 将数据放到表中，表再放到库中。
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。 数据库有表，表有一个名字，唯一、标识自己。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中 “类”的设计。 数据在表中如何存储？ 类 属性、字段 字段名 数据类型

• 表由列组成，我们也称为字段。每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和字段名。每个字段类似java 或者python中的“实例属性”。字段 数据类型 字段名

  数据表的设计实际上就是对字段的设计

  设计字段 一个数据类型 数据长度 字段名

• 表中的数据是按行存储的，一行即为一条记录。每一行类似于java或python中的“对象”。

  数据按按行存储，一行即为一条记录，一个对象样的

表 类

字段 属性 列

行、记录 对象、实体

属性值一个个

设计数据表的原则

“三少一多”原则：

1.数据表的个数越少越好

RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。

关系型数据库管理系统 实体和联系的定义 E-R图

数据表的个数越少越好，证明实体和联系设计得越简洁，既方便理解又方便操作。

2.数据表中的字段个数越少越好

字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。

各个字段相互独立 设计数据表 数据库表中的字段个数越少越好

3.数据表中联合主键的字段个数越少越好

设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

联合主键个数越少越好

4.使用主键和外键越多越好

数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

使用主键和外键越多越好？？？

“三少一多”原则的核心就是简单可复用。简单指的是用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。

简单可复用 用更少的表 更少的字段 更少的联合主键字段来完成数据表的设计

可复用 主键 外键的使用来增强数据表之间的复用率

2、约束与索引的概念

1、数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性 数据的精确性 可靠性

数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

2、根据约束的特点，分为几种：

• 键约束：主键约束、外键约束、唯一键约束
• Not NULL约束：非空约束
• Check约束：检查约束
• Default约束：默认值约束
• 自增约束

键约束 主键约束 外键约束 唯一键约束

非空约束Not NULL 检查约束Check 默认值约束Default 自增约束

数据表的常见约束

创建数据表的时候，还会对字段进行约束，约束的目的在于保证 RDBMS 里面数据的准确性和一致性。来看下常见的约束有哪些。

首先是主键约束。

主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。

其次还有外键约束。

外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。

除了对键进行约束外，还有字段约束。

唯一性约束。

唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。需要注意的是，唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。

NOT NULL 约束。对字段定义了 NOT NULL，即表明该字段不应为空，必须有取值。

DEFAULT，表明了字段的默认值。如果在插入数据的时候，这个字段没有取值，就设置为默认值。

CHECK 约束，用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE。

3、约束（CONSTRAINTS）与索引（INDEX）

constraints、index

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限在当前数据库，约束可以被当做数据库对象来处理，它们具有名称和关联模式。是逻辑约束，不会因为设置约束而额外占用空间。

索引是一个单独、物理的存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单（类似于新华字典的目录索引页）。索引的存在会增加数据库的存储空间，也会使插入、修改数据的时间开销变多(因为插入和修改数据时，索引也要随之变动），但是可以大大提高查询速度。因此应该在键列、或其他经常要查询、排序、按范围查找的列上建立索引，而对于在查询中很少使用和参考的列、修改非常频繁的列，值很少的列（例如性别只有男和女）等列上不应该创建索引。

数据库中的索引，就好比一本书的目录，它可以帮我们快速进行特定值的定位与查找，从而加快数据查询的效率。

索引就是帮助数据库管理系统高效获取数据的数据结构。

虽然使用索引的本质目的是帮我们快速定位想要查找的数据，但实际上，索引有很多种类。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。

普通索引是基础的索引，没有任何约束，主要用于提高查询效率。唯一索引就是在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。主键索引在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。全文索引用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

按照物理实现方式，索引可以分为 2 种：聚集索引和非聚集索引。我们也把非聚集索引称为二级索引或者辅助索引。

Mysql会在主键、唯一键、外键列上自动创建索引，其他列需要建立索引的话，需要手动创建。

其中主键删除，对应的索引也会删除

删除唯一键的方式是通过删除对应的索引来实现的

删除外键，外键列上的索引还在，如果需要删除，需要单独删除索引