MySQL的三大范式以及设计规范

数据库的设计规范

三大范式及巴斯(BCNF)范式

第一范式

第一范式主要是确保数据表中的每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。

属性的原子性是主观性的。

第二范式

在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。

对于非主属性来说，并非完全依赖候选键。这样会产生怎么样的问题？

1. 数据冗余：如果一个球员可以参加m场比赛，那么球员的姓名和年龄就重复了m-1次。一个比赛也可能会有n个球员，比赛的时间和地点就重复了n-1次。
2. 插入异常：如果我们想要添加异常新的比赛，但是这时还没有确定参加的球员都有谁，那么就没办法插入。
3. 删除异常：如果我们要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
4. 更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现异常比赛时间不同的情况。

小结：第二范式要求实体的属性完全依赖主关键字。如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与元实体之间是一对多的关系。

第三范式

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段

不能依赖于其他非主键字段。通俗的讲，该规则的意思是所有非主属性之间不能有依赖关系，必须相互独立。

符合3范式后的数据模型通俗地讲，2范式和3范式通常以这句话概括：每个非键属性依赖于主键，依赖于整个键，并且除了主键别无他

物。

BCNF(巴斯范式)

BCNF被人为没有新的设计规范加入，知识对第三范式中设计规范要求更强，使得数据库冗余度更小，所以称为是修正的第三范式，或扩

充的第三范式，BCNF不被称为第四范式。

若一个关系打到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BC范式。

1.案例

在这个表中，一个仓库只有一个管理员，同时一个管理员也只管理一个仓库。我们先来梳理下这些属性之间的依赖关系。

仓库名决定了管理员，管理员也决定了仓库名，同时（仓库名，物品名）的属性集合可以决定数量这个
属性。这样，我们就可以找到数据表的候选键。

• 候选键：是（管理员，物品名）和（仓库名，物品名），然后我们从候选键中选择一个作为 主键 ，比如（仓库名，物品名）。
• 主属性 ：包含在任一候选键中的属性，也就是仓库名，管理员和物品名。
• 非主属性 ：数量 这个属性。

2.是否符合三范式

如何判断一张表的范式呢？我们需要根据范式的等级，从低到高来进行判断。

首先，数据表每个属性都是原子性的，符合1NF 的要求；
其次，数据表中非主属性”数量“都与候选键全部依赖，（仓库名，物品名）决定数量，（管理员，物品名）决定数量。因此，数据表符合`` 2NF` 的要求；
最后，数据表中的非主属性，不传递依赖于候选键。因此符合3NF的要求。
3、存在的问题
既然数据表已经符合了 3NF 的要求，是不是就不存在问题了呢？我们来看下面的情况：

增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因此会出现 插入异常 ；

如果仓库更换了管理员，我们就可能会 修改数据表中的多条记录 ；

如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。

你能看到，即便数据表符合 3NF 的要求，同样可能存在插入，更新和删除数据的异常情况。
4、问题解决
首先我们需要确认造成异常的原因：
主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系，这样就有可能导致上面的异常情况。

因此引入BCNF，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于BCNF。

根据 BCNF 的要求，我们需要把仓库管理关系 warehouse_keeper 表拆分成下面这样：

仓库表 ：（仓库名，管理员）

库存表 ：（仓库名，物品名，数量）

这样就不存在主属性对于候选键的部分依赖或传递依赖，上面数据表的设计就符合 BCNF。

小结

关于数据表的设计，有三个范式要遵循。

• 第一范式，确保每列保持原子性
  • 数据库的每一列都是不可分割的原子数据项，不可再分的最小数据单元，而不能是集合、数组、记录等非原子数据项。
• 第二范式，确保每列都和主键完全依赖
  • 尤其在符合主键的情况下，非主键部分不应该依赖于部分主键。
• 第三范式，确保每列都和主键列直接相关，而不是间接相关。

范式优点：数据的标准化有助于消除数据库中的数据冗余，第三范式通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

范式的缺点：范式的使用，可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数

据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引失效。

范式知识提出了设计的标准，实际上设计数据局表时，未必一定要符合这些标准。开发中 ，我们会出现为了性能和读取效率违反范式化

的原则，通过增加少量的冗余或重复的数据来提高数据库的读性能，减少关联查询，join表的次数，实现空间换时间的目的。因此在实际

的设计过程中要理论结合时间，灵活运用。

范式本身没有优劣之分，只有应用场景不同。没有完美的设计，只有合适的设计，我们在数据表的设计中，还需要根据需求将范式和反范式混合使用。

反范式化

概述

有的时候不能简单按照规范要求设计数据表 ，因为有的数据看似冗余，其实对业务来说十分重要。这个时候，我们就要遵循业务优先的

原则，首先满足业务需求，再尽量减少冗余。

如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MySql的三大范式设计数据表，读数据时会产生大量的关联

查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式优化也是一种优化思路。此时，可以通过在数据表

中增加冗余字段来提高数据库的读性能

规范化 vs 性能

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要
2. 在数据规范化的同时 , 要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4. 通过在给定的表中插入计算列，以方便查询

反范式的新问题

存储空间变大了

一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致

若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源

在数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

反范式的适用场景

当冗余信息有价值或者能 大幅度提高查询效率 的时候，我们才会采取反范式的优化。

1. 增加冗余字段的建议

   增加冗余字段一定要符合如下两个条件：

   • 这个冗余字段不需要经常进行修改
   • 这个冗余字段查询的时候不可或缺。

2. 历史快照、历史数据的需要

   在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每 次发生的 订单收货信息 都属

   于 历史快照 ，需要进行保存，但用户可以随时修改自己的信息，这时保存这 些冗余信息是非常有必要的。

   反范式优化也常用在 数据仓库 的设计中，因为数据仓库通常 存储历史数据 ，对增删改的实时性要求不 强，对历史数据的分析需求

   强。这时适当允许数据的冗余度，更方便进行数据分析。

数据表的设计原则

“三少一多”

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

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

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

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

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

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

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

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

   这里的外键不是外键约束，而是逻辑外键，是描述外键关系的一种关系，如一对一，一对多。

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

数据库对象编写建议

1、 关于库
【强制】库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字母开头。
【强制】库名中英文 一律小写 ，不同单词采用 下划线分割。须见名知意。
【强制】库的名称格式：业务系统名称_子系统名。
【强制】库名禁止使用关键字（如type,order等）。
【强制】创建数据库时必须 显式指定字符集 ，并且字符集只能是utf8或者utf8mb4。创建数据库SQL举例：CREATE DATABASE crm_fund DEFAULT CHARACTER SET ‘utf8’ ;
【建议】对于程序连接数据库账号，遵循 权限最小原则使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号原则上不准有drop权限 。
【建议】临时库以tmp_ 为前缀，并以日期为后缀；备份库以 bak_ 为前缀，并以日期为后缀。
2、关于表、列
【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议以 英文字母开头 。
【强制】 表名、列名一律小写 ，不同单词采用下划线分割。须见名知意。
【强制】表名要求有模块名强相关，同一模块的表名尽量使用 统一前缀。比如：crm_fund_item
【强制】创建表时必须 显式指定字符集 为utf8或utf8mb4。
【强制】表名、列名禁止使用关键字（如type,order等）。
【强制】创建表时必须 显式指定表存储引擎 类型。如无特殊需求，一律为InnoDB。
【强制】建表必须有comment。
【强制】字段命名应尽可能使用表达实际含义的英文单词或 缩写 。如：公司 ID，不要使用corporation_id, 而用corp_id 即可。
【强制】布尔值类型的字段命名为is_描述 。如member表上表示是否为enabled的会员的字段命
名为 is_enabled。
【强制】禁止在数据库中存储图片、文件等大的二进制数据通常文件很大，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。
【建议】建表时关于主键： 表必须有主键
(1)强制要求主键为id，类型为int或bigint，且为auto_increment 建议使用unsigned无符号型。
(2)标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。
【建议】核心表（如用户表）必须有行数据的 创建时间字段 （create_time）和 最后更新时间字段 （update_time），便于查问题。
【建议】表中所有字段尽量都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT值 。 因为使用
NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问
题。
【建议】所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型
不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。
【建议】中间表（或临时表）用于保留中间结果集，名称以tmp_开头。
备份表用于备份或抓取源表快照，名称以 bak_开头。中间表和备份表定期清理。
【示范】一个较为规范的建表语句：

CREATE TABLE user_info (
	 `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
	 `user_id` bigint(11) NOT NULL COMMENT '用户id',
	 `username` varchar(45) NOT NULL COMMENT '真实姓名',
	 `email` varchar(30) NOT NULL COMMENT '用户邮箱',
	 `nickname` varchar(45) NOT NULL COMMENT '昵称',
	 `birthday` date NOT NULL COMMENT '生日',
	 `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
	 `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
	 `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
	 `user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
	 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
	CURRENT_TIMESTAMP COMMENT '修改时间',
	 `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
	PRIMARY KEY (`id`),
	 UNIQUE KEY `uniq_user_id` (`user_id`),
	 KEY `idx_username`(`username`),
	 KEY `idx_create_time_status`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息'

3、关于索引
【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新 。
【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。
【建议】主键的名称以pk_ 开头，唯一键以 uni_ 或uk_开头，普通索引以idx_开头，一律
使用小写格式，以字段的名称或缩写作为后缀。
【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如:
sample 表 member_id 上的索引：idx_sample_mid。
【建议】单个表上的索引个数 不能超过6个 。
【建议】在建立索引时，多考虑建立 联合索引 ，并把区分度最高的字段放在最前面。
【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。
【建议】建表或加索引时，保证表里互相不存在 冗余索引 。 比如：如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。
4、SQL编写
【强制】程序端SELECT语句必须指定具体字段名称，禁止写成 *。
【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。
【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。
【建议】INSERT INTO…VALUES(XX),(XX),(XX)… 这里XX的值不要超过5000个。 值过多虽然上线很快，但会引起主从同步延迟。
【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。
【建议】线上环境，多表 JOIN 不要超过5个表。
【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER
BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
【建议】对单表的多次alter操作必须合并为一次
对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整合在一起。 因为alter table会产生 表锁 ，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。
【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。
【建议】事务里包含SQL不超过5个。
因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX;否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。