数据库设计-三大范式

讲到数据库设计，就需要先说一下数据库设计的三大范式（Normal Form）。

三大范式

第一范式(1NF)：列属性的原子性

• 确保表中列属性的原子性；
• 数据库表中的每一列，都不可被继续拆分为其他列；
• 最基本范式，也是绝大多数数情况下会遵从的范式；

反示例：

会员编号	会员名称	手机号	邮箱	地址	创建时间
C00001	张三	13800000001	zhangsan@test.com	上海市浦东区博云路2号	2020-01-20
C00002	李四	13800000002	lisi@test.com	上海市浦东区博云路1号	2020-01-22

正示例：

会员编号	会员名称	手机号	邮箱	省	市	区	街道	创建时间
C00001	张三	13800000001	zhangsan@test.com	上海	上海市	浦东区	博云路2号	2020-01-20
C00002	李四	13800000002	lisi@test.com	浙江	杭州市	西湖区	余杭塘路866号	2020-01-22

第二范式（2NF）：行记录的唯一性

• 确保表中的每一行都具有实体的唯一性
• 联合主键情况下，每一列都和主键相关，而不是和主键的某一部分相关；
• 不可以把多种数据保存在同一张数据表中；

反示例：会员编号+账户编号作为联合主键

会员编号	会员名称	手机号	邮箱	账户编号	账户余额	创建时间
C00001	张三	13800000001	zhangsan@test.com	ACC001	200.00	2020-01-20
C00001	张三	13800000001	zhangsan@test.com	ACC002	300.00	2020-01-20
C00002	李四	13800000002	lisi@test.com	ACC003	100.00	2020-01-22

正示例：拆分为会员表和账户表，会员编号作为账户表外键进行关联

会员编号	会员名称	手机号	邮箱	创建时间
C00001	张三	13800000001	zhangsan@test.com	2020-01-20
C00002	李四	13800000002	lisi@test.com	2020-01-22

账户编号	会员编号	账户类型	账户余额	创建时间
ACC001	C00001	现金	200.00	2020-01-20
ACC002	C00001	红包	300.00	2020-01-20
ACC003	C00002	现金	100.00	2020-01-22

第三范式(3NF)：字段的冗余性

• 确保表中的每一列都与主键直接关联，而不是间接关联；
• 即不存在传递依赖情况发生；

反示例：存在依赖传递关系 会员编号 - 所属团队 - 团队名称 - 团队主管，更改团队名称或主管时，所有记录都要更新

会员编号	会员名称	手机号	邮箱	所属团队	团队名称	团队主管	创建时间
C00001	张三	13800000001	zhangsan@test.com	T01	云腾	小红	2020-01-20
C00002	李四	13800000002	lisi@test.com	T02	天翼	小明	2020-01-22
C00003	王五	13800000003	wangwu@test.com	T02	天翼	小明	2020-01-24
C00004	赵六	13800000004	zhaoliu@test.com	T01	云腾	小红	2020-01-24

正示例：

会员编号	会员名称	手机号	邮箱	所属团队	创建时间
C00001	张三	13800000001	zhangsan@test.com	T01	2020-01-20
C00002	李四	13800000002	lisi@test.com	T02	2020-01-22
C00003	王五	13800000003	wangwu@test.com	T02	2020-01-24
C00004	赵六	13800000004	zhaoliu@test.com	T01	2020-01-24

团队编号	团队名称	团队主管	创建时间
T01	云腾	小红	2020-01-20
T02	天翼	小明	2020-01-22

遵从范式的优点

• 重复数据量少，无冗余数据；
• 更新操作更快；
• 范式化后表体积更小；

遵从范式的缺点

• 查询时经常需要进行多表关联查询，导致性能降低；
• 更难进行索引的优化；

数据库设计的一些建议

1. 合理使用冗余

• 对于更新频率不高，但查询频率过高的字段，做冗余数据处理；
• 有效的减少关联查询所带来的性能问题；

订单编号	订单金额	订单状态	会员编号	会员名称	创建时间
ORD00001	300.00	成功	C00001	张三	2020-01-20
ORD00002	500.00	成功	C00002	李四	2020-01-22

2. 超长字段拆分

• 存储超长字段时，将字段从主表中拆出，避免因超长字段导致的查询效率问题；
• 拆出的字段，通过ID与主表内容进行关联，通过纵向逻辑关系进行关联查询；
• 纵向逻辑关系，可提升数据的易读性和维护性；

3. 不使用外键与级联（阿里巴巴开发手册）

• 一切外键概念，通过应用层处理，不依赖外键关系；
• 使用外键可能导致业务变动时，增加额外的修改数据库成本；
• 外键对插入和更新有性能影响；

4. 合适的数据类型(MySQL)

• 数据量相同时，使用更小的数据类型所占用的空间更少；
• 检索同样的数据所产生的IO消耗越低；
• 运算和排序，越简单的数据类型操作性能越高；

详细内容点击：MySQL数据类型

类型	大小	范围(有符号)	范围(无符号)	用途
TINYINT	1 bytes	(-128，127)	(0，255)	小整数值
INT或INTEGER	4 bytes	(-2^31-1 ，2^31-1)	(0，2^32-1)	大整数值
BIGINT	8 bytes	(-2^63 ，2^63)	(0，2^64 - 1)	极大整数值
DECIMAL	对DECIMAL(M,D) ， 如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

类型	大小	范围(有符号)	格式	用途
DATETIME	8 bytes	1000-01-01 00:00:00 / 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4 bytes	1970-01-01 00:00:00/2038 ~ 结束 北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

5. 表中应建立唯一标识

• 区分数据库主键和业务主键；
• 合理使用ID作为自增长主键，有利于更新、删除、查找时的准确性；
• 主键数据类型根据业务需要，选用最小数据类型，有利于提高性能；

6. 表中不应有重复意义的字段

• 迭代过程中，不应重复建立相同意义字段；
• 详尽的阅读数据库设计，是每个开发人员对系统了解的基础；

7. 命名规范（阿里巴巴开发手册）

• 表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（ 1表示是，0表示否）；
• 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字；
• 表名不使用复数名词，应该仅仅表示表里面的实体内容；
• 禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字；
• 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名；
• 小数类型为decimal，禁止使用float和double；超过decimal的范围，拆成整数和小数分开存储；
• 表必备三字段：id、gmt_create(date_time)、 gmt_modified(date_time)；