数据库设计

1. 基础

1.1. 数据库设计步骤

1. 需求分析（根据数据的属性和特点设置数据类型）；
2. 逻辑设计（ER 图）；
3. 物理设计（选择开发环境）；
4. 维护优化（新建表、索引、拆分）；

1.2. 术语

关系

一个关系对应通常所说的一张表；

元组

表中的一行为一个元组；

属性

表中的一列为一个属性；每一个属性有一个名称为属性名（表字段）；

候选码

表中的某个属性级，它可以唯一确定一个元组；

主码

一个关系有多个候选码，选定其中一个为主码；

域

属性的取值范围（e.g.: 真假：Y/N...）；

分量

元组中的一个属性值；

1.3. ER 图注

矩形：实体集； 
菱形：关系集； 
椭圆：属性； 
线：关系；

2. 需求分析

2.1. 要点

• 存储数据是什么；
• 数据存储特点（时效性数据【定期清理机制】；没有时效性的数据）；
• 数据的生命周期（增长速度快，量大，应采用拆表分表方式设计）

2.2. 表的实体关系

• 实体及实体关系（1：1，1：N，N：N）；
• 实体所包含属性（存储特点，1次1行、N行，DIU结合操作存储...）；
• 那些属性或属性组合可以唯一标识一个实体（主键，外键关系）；
• 存储特性（是否永久存储）；

合理分库、分表。按期归档，使数据库高效运转。

3. 逻辑设计

3.1. 数据冗余

数据冗余

指相同的数据在多个地方存在（多个表存在，一个表多个字段意义相同），或者说某个列可以由其他列计算得到，这样就说表中存在着数据冗余（不符合范式要求既有数据冗余）。

可包括——

• 插入异常 (insert)；
• 更新异常 (update)；
• 删除异常 (delect)；

3.2. 设计范式

3.2.1. 第一范式 (1NF)

定义

数据库表中所有字段均为 单一属性，且 不可再分；

补充

单一属性指由基本的数据类型（如：整数、浮点数、字符串等）构成；

释义

表都必须是二维表；不可表中套表；

3.2.2. 第二范式 (2NF)

定义

数据库表中不存在非关键字段对任一候选关键字段的 部分函数依赖；

补充

部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况；

释义

表都必须是单关键字段的表；

问题

不符合 2NF 时可能产生的问题：插入异常、更新异常、删除异常、数据冗余；

解决

拆分成单关键字的表（两个表 + 一个关系表）；

3.2.3. 第三范式 (3NF)

定义

若数据表不存在非关键字段、也不存在对任意候选关键字段的 传递函数依赖 则符合 3NF；

释义

不存在非主属性部分函数依赖于码，同时不传递依赖与码；

问题

不符合 3NF 时可能产生的问题：插入异常、更新异常、删除异常、数据冗余；

解决

拆分成单关键字的表（两个表 + 一个关系表）；

3.2.4. BC 范式 (BCNF)

定义

在 3NF 基础上，若不存在任何字段对任一候选关键字段的传递函数依赖则符合 BCNF；

释义

若有复合关键字，则复合关键字之间不能存在函数依赖关系（不可一词多义）；

3.2.5. 第四范式

暂无简介

3.2.6. 第五范式

暂无简介

4. 物理设计

4.1. 设计步骤

1. 选择合适的数据库管理系统： 
   a. 商业数据库（企业级项目）：Oracle、SQL Server； 
   b. 开源数据库（互联网项目）：MySQL、PgSQL；
2. 设定数据库表及字段命名、数据库设计规范；
3. 由所选 DBMS 选用合适的数据类型；
4. 反范式设计：过分要求范式设计必定会增加关系度的复杂，应在范式与简约节时的原则上找到平衡；4.2. MySQL 初步

4.2.1. 存储引擎

大多数互联网应用建议使用 Innodb；

4.2.2. 命名规范

• 某些 DBMS 对大小写敏感，可以设置；
• 语义化；
• 长命名以消除歧义；

4.2.3. 字段类型选用

1. 数据查询性能：对数据进行比较（查询条件、JOIN 条件及排序分组等）操作时，同样的数据，数字比字符处理要快；
2. 存储空间开销：在数据库中，数据处理以页为单位，列的长度越小，字节越小，利于 I/O 性能提升（单页 SQL Server: 8K、MySQL: 16K）；
3. 数据类型优先级：数字 > 日期 = 二进制类型 > 字符类型；相同级别的数据类型，优先选择占用空间小的数据类型；

4.2.4. 字符：char/varchar

1. 如果列中要存储的数据长度差不多一致，则使用 char；否则考虑 varchar（e.g.: 手机号/身份证号码）；
2. 如果列中的最大数据长度小于 50 Byte，一般考虑用 char（如果这个列很少用，则基于节省空间和减少 I/O 的考虑，也可以用 varchar）；
3. 一般不宜定义大于 50 Byte 的 char 类型列；

4.2.5. 数字：decimal/float

1. decimal 用于存储精确数据，而 float 只能用于存储非精度数据；
2. 由于 float 的存储空间开销一般比 demimal 小；故非精度数据优先选择 float；

4.2.6. 时间：int/datetime**

1. int：字段长度比 datetime 小；使用不方便，要进行函数转换，且只能存储到 2038-01-19 11:14:07；使用、查询少宜用 int；
2. datetime：查询频繁的时间戳；
3. 需要存储的时间粒度：年月日时分秒周；

4.2.7. 其它

字符

计算机中使用的文字和符号；

字节

计量单位；

1. ASCII 编码中，一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间。一个二进制数字序列，在计算机中作为一个数字单元，一般为 8 位二进制数，换算为十进制。最小值 0，最大值 255。
2. UTF-8 编码中，一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。
3. Unicode 编码中，一个英文等于两个字节，一个中文（含繁体）等于两个字节。符号：英文标点占一个字节，中文标点占两个字节。举例：英文句号“.”占 1 个字节的大小，中文句号“。”占 2 个字节的大小。
4. UTF-16 编码中，一个英文字母字符或一个汉字字符存储都需要 2 个字节（Unicode 扩展区的一些汉字存储需要4个字节）。
5. UTF-32 编码中，世界上任何字符的存储都需要 4 个字节。

4.2.8. 主键选用

选择原则

• 区分业务主键和数据库主键：业务主键用于标识业务数据，进行表与表之间的关联；数据库主键为了优化数据存储。
• 根据数据库的类型，考虑主键是否要顺序增长：有些数据库是按主键的顺序逻辑存储的。
• 主键的字段类型所占用空间要尽可能的小：对于使用聚集索引方式存储的表，每个索引后都会附加主键信息。

避免使用外键约束

• 降低数据导入的效率；
• 增加维护成本；
• 虽然不建议使用外键约束，但是相关联的列上一定要建立索引；

避免使用触发器

• 降低数据导入的效率；
• 可能会出现意想不到的数据异常；
• 使业务逻辑变的复杂；

关于预留字段

• 无法准确的知道预留字段的类型；
• 无法准确的知道预留字段中所存储的内容；
• 后期维护预留字段需要成本；
• 严禁使用预留字段；

4.2.9. 反范式化设计

为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，允许存在少量的数据冗余；即以空间换时间。

• 减少表的关联数量；
• 增加数据的读取效率；
• 反范式化一定要适度；

5. 维护优化

5.1. 维护要点

• 维护数据字典；
• 维护索引；
• 维护表结构；
• 在适当的时候对表进行水平拆分或垂直拆分；

5.2. 维护数据字典

• 使用第三方工具；
• 利用数据库本身的备注字段来维护数据字典；

5.3. 维护索引

• 建立索引 
  
  • 出现在 where 从句，group by 从句，order by 从句中的列；
  • 可选择性高的列要放到索引的前面；
  • 索引中不要包括太长的数据类型（尽量数字，日期，单字节字符类型等）；
• 维护索引 
  
  • 索引不是越多越好，多余的索引会降低读写效率；
  • 定期维护索引片段；
  • SQL 语句中不要使用强制索引关键字（索引会随着数据量的变化而变得不适应）；

5.4. 维护表结构

注意事项

1. 使用在线变更表结构工具； 
   MySQL 5.5 之前以使用 pt-online-schema-change； 
   MySQL 5.6 之后本身支持在线表结构的变更；
2. 同时对数据字典进行维护；
3. 控制表的宽度和大小（表字段的大小控制，表数据量的分区，拆分处理等）；

适合的操作

1. 批量操作和逐条操作；
2. 禁止使用 select * 这样的查询（把不必要的字段也查询出来，浪费 I/O）；
3. 控制使用用户自定义函数（索引失效）；
4. 不要使用数据库中的全文索引；

5.5. 表的拆分

• 垂直拆分

  • 经常一起查询的列放到一起
  • Text、Blob 等大字段拆分到附加表中

• 水平拆分

  • Hash Key 拆分；
  • 时区段域拆分；
  • 表业务类型拆分；

文章转自：https://www.zybuluo.com/EncyKe/note/210656#避免使用外键约束