本文内容大部分来自于慕课网课程数据库设计那些事，记录下学习的内容

数据库设计那些事

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据库存储模型。

优良的设计 糟糕的设计 减少数据冗余 存在大量的数据冗余 避免数据维护异常 存在数据插入，更新，删除异常 节约存储空间 浪费大量的存储空间 高效的访问 访问数据低效

数据库设计步骤

需求分析

数据库需求的作用点：
1.数据是什么
2.数据有哪些属性
3.数据和属性各自的特点有哪些

要搞清的一些问题：

1.实体及实体之间的关系（1对1，1对多，多对多）
2.实体所包含的属性有什么
3.哪些属性或属性的组合可以唯一标识一个实体

逻辑设计

使用ER图对数据库进行逻辑建模，同所选用的具体的DBMS系统无关。

设计范式

对设计范式的理解可参考解释一下关系数据库的第一第二第三范式？

1NF：列不可分就满足1NF了。2NF：不存在部分依赖，比如 (A,B)→C。（消除非主属性对主属性的传递依赖，即完全依赖于主键）3NF：不存在传递依赖，比如A→B→C。（在2NF基础上消除了传递依赖）

第一范式

1NF的定义为：符合1NF的关系中的每个属性都不可再分

第二范式

定义：数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖。
部分函数依赖是指存在着组合关键字的某一关键字决定非关键字的情况。

例子，如下表：

在这里使用商品名称和供应商名称标识出一件商品
上表存在以下的部分函数依赖关系：
商品名称->价格、描述、重量、商品有效期
供应商名称->供应商电话

存在问题：插入异常、删除异常、更新异常、数据冗余

所以对上表进行拆分：

第三范式

3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖

例如，如下表：

存在以下传递函数依赖关系：
商品名称->分类->分类描述

修改为：

BCNF范式

在第三范式的基础上，数据库表中如果不存在任何任何字段对任一候选关键字段的传递函数依赖则符合BC范式。
也就是说如果是复合关键字，则复合关键字也不能存在函数依赖关系。

如下表：

假定：供应商联系人只能受雇于一家供应商，每家供应商可以供应多个商品，则存在如下决定关系：

（供应商，商品id）->（供应商联系人，商品数量）
（供应商联系人，商品id）->（供应商，商品数量）

所以有两种组合关键字的选择

存在以下关系不符合BCNF要求：
（供应商）->（供应商联系人）
（供应商联系人）->（供应商）
并且存在数据操作异常和数据冗余

所以要拆表：

物理设计

根据数据库自身的特点把逻辑设计转换为物理设计

1.选择合适的数据库管理体统

常见的DBMS系统：

• Oracle、SQLServer是商业数据库，更适合企业级的项目
• MySQL、PgSQL是开源数据库，适用于互联网项目
• Oracle比较适用大的事务操作
• SQLServer可以与.net更好的配合，只支持在Windows下

本例使用MySQL作为讲解

MySQL常用的存储引擎：

2.定义数据库、表及字段的命名规范

表及字段的命名规范：
1.可读性原则
使用大写和小写来格式化库对象名字以获取良好的可读性
例如：使用CustAddress而不是custaddress来提高可读性（这里要注意的是有些DBMS系统对表名的大小写是敏感的）
2.表意性原则，对象的名字应该能够描述它所标识的对象
3.长名原则，尽可能少使用或者不使用缩写，适用于数据库名之外的任一对象。

3.根据所选的DBMS系统选择合适的字段类型

字段类型的选择原则

例如，对于birthday这个字段可以有如下的选择：

列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其实是时间或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

MySQL的TIMESTAMP只能存储到2037年。

以上选择原则主要从下面两个角度考虑：
1.在对数据进行比较（查询条件、join条件及排序）操作时，同样的数据，字符处理往往比数字处理慢。
2.在数据库中，数据处理以页为单位，列的长度越小，利用性能越高。

char与archer如何选择？
1.如果列中要存储的数据长度差不多是一致的（例如电话或身份证号码），应该考虑用char，否则应该考虑用varchar
2.如果列中的数据最大长度小于50byte，则一般也考虑使用char（当然，如果这个列很少用，则基于节省空间和减少I/O的考虑，还可以使用varchar）
3.一般不宜定于大于50byte的char类型列

decimal与float如何选择？
1.decimal用于存储精确数据，而float只能由于存储非精确数据，故精确数据只能选择用decimal类型
2.由于float的存储空间开销一般比decimal下（精确到7位小数只需啊4个字节，而精确到15位小数只需要8字节），故非精确数据优先选择float类型

时间类型如何存储？
1.使用int来存储时间字段的优缺点
优点：字段长度比datetime小
缺点：使用不方便，要进行函数转换
限制：只能存储到2038-1-19 11:14:07 即 2^32

2.需要考虑存储的时间粒度
如存储是年还是月等

其它注意事项

如何选择主键？
1.区分业务主键和数据库主键
业务主键用于标识业务数据，进行表与表之间的关联
数据库主键为了优化数据存储（Innodb会生成6个字节的隐含主键）
2.根据数据库的类型，考虑主键是否要顺序增长
有些数据库是按主键的顺序逻辑存储的
3.主键的字段类型所占空间要尽可能的小
对于使用聚集索引方式存储的表，每个索引后都会附加主键信息

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

避免使用触发器
1.降低数据导入的效率
2.可能会出现意想不到的数据异常
3.使业务逻辑变的复杂

关于预留字段
1.无法准确的知道预留字段的类型
2.无法准确知道预留字段中所存储的内容
3.后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的
4.严禁使用预留字段

4.反范式化设计

反范式化是针对范式化而言的，在前面介绍了数据库设计的第三范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余
换句话说反范式化就是使用空间来换取时间

维护优化

维护优化的内容可能包括：新的需求进行建表、索引优化、大表拆分

1.维护数据字典

1.使用第三方工具对数据字典进行维护
2.利用数据库本身的备注字段来维护数据字典。以MySQL为例：

3.导出数据字典

2.维护索引

如何选择合适的列建立索引？
1.出现在where从句，group by从句，order by从句中的列
2.可选择性高的列要放到索引的前面
3.索引中不要包括太长的数据类型

注意事项
1.索引并不是越多越好，过多的索引不但会降低写效率而且会降低读的效率
2.定期维护索引碎片
3.在SQL语句中不要使用强制索引关键字

3.维护表结构

注意事项
1.使用在线变更表结构的工具
MySQL5.5之前可以使用pt-online-schema-change
MySQL5.6之后本身支持在线表结构的变更

2.同时对数据字典进行维护
3.控制表的宽度和大小

数据库中适合的操作
1.批量操作 VS 逐条操作
适合批量操作，逐条操作适合在程序中完成
2.禁止使用Select * 这样的查询
3.控制使用用户自定义函数
4.不要使用数据中的全文索引

4.在适当的时候对表进行水平拆分或垂直拆分

为了控制表的宽度可以进行表的垂直拆分

1.经常一起查询的列放在一起
2.text，blob等大字段拆分出到附加表中

为了控制表的大小可以进行表的水平拆分

可以通过主键哈希的方式