数据库设计那些事学习笔记
什么是数据库设计？
简单的来说，数据设计就是根据业务系统的具体需要，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据存储模型。并建立好数据库中的表结构及表与表之间的关联关系的过程。使之能有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。
为什么要进行数据库设计？
高效的数据库设计：
1.减少数据的冗余
2.避免数据维护异常
3.节约存储空间
4.高效的访问
低效的数据库设计：
1.存在大量的数据冗余
2.存在数据更新、删除、删除异常
3.浪费大量的存储空间
4.数据访问低效
数据库设计的步骤：
数据分析=》逻辑设计=》物理设计=》维护优化
数据分析：即需求分析
逻辑设计：使用ER图对数据库进行逻辑建模，设计数据的逻辑存储结构（数据实体之间的逻辑关系，解决数据冗余和数据维护异常 ）
物理设计：根据所使用的数据库特点进行表结构设计
维护优化：对新的需求进行建表，索引优化，大表拆分
需求分析即数据分析：
需求分析，我们可以了解：
1.数据是什么？
2.数据有哪些属性？
3.了解数据的生命周期
对于实体而言，就是要了解实体特性，如：
存储特性：有些数据具有时效性，可采取过期清理或归档方式处理。
增长量特性：如日志类实体，数据增长量较快需要分库分表或者按实体进行归档处理（这些需要在物理设计过程实现但要在需求分析阶段考虑到）。
1.实体与实体之间的关系（一对一、一对多、多对多）
2.实体所包含的属性
3.哪些属性或哪些属性组合可以唯一标识实体
以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块：用户模块、商品模块、订单模块、购物车模块、供应商模块
用户模块：
用于记录注册用户信息：
包括属性：用户名、密码、电话、邮箱、身份证号、地址、姓名、昵称等
可选唯一标识属性：用户名、身份证号、电话
存储特点：随系统上线时间逐渐增减，需要永久存储
商品模块：
用于记录网站中所销售的商品信息：
包括属性：商品编码、商品名称、商品描述、商品种类、供应商名称、重量、有效期、价格等
可选唯一标识属性：（商品名称，供应商名称）、（商品编码）
存储特点：对于下线商品可以归档存储
订单模块：
用于用户订购商品的信息：
包括属性：订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型等
可选唯一标识属性：（订单号）
存储特点：永久存储（分表、分库存储）
购物车模块：
用于保存用户购物时选的商品：
包括属性：用户名、商品编号、商品名称、商品价格、商品描述、商品分类、加入时间、商品数量等
可选唯一标识属性；（用户名、商品编号、加入时间）、（购物车编号）
存储特点：不永久存储（设置归档，清理规则）
供应商模块：
用于保存所销售商品的供应商信息：
包括属性：供应商编号、供应商名称、联系人、电话、营业执照号、地址、法人等
可选唯一标识属性：（供应商编号）、（营业执照号）
存储特点：永久存储
各个模块之间的关系：
逻辑设计：
逻辑设计是做什么？
1.将需求转化为数据的逻辑模型
2.通过ER图的形式对逻辑模型进行展示
3.逻辑设计与所选用的具体的DBMS系统无关
常用的名词解释：
关系：一个关系对应通常所说的是一张表
元组：表汇总的一行即为一个元组
属性：表中的一列即为一个属性；每一个属性都有一个名称，称为属性名
候选码：表中单点某个属性组，它可以唯一确定一个元组
主码：一个关系有多个候选码，选定其中一个为主码
域：属性的取值范围
分量：元组中的一个属性值
ER图说明：
所以综上例子的ER图关系如下所示：
数据操作异常及数据冗余：
操作异常：
插入异常：如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个字体，那么这个表就存在插入异常
更新异常：如果更改表所对应的某个实体实例的单独属性时，需要将多行更新，那么就说这个表存在更新异常
删除异常：如果删除表的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常
数据冗余：
数据冗余：是指相同的数据在多个地方存在，或者说表中的某个列可以有其他列计算得到，这样就说表中存在数据冗余
什么是数据库设计范式：
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
常见的数据库设计范式包括：第一范式、第二范式、第三范式、BC范式
当然还有第四及第五范式，不过目前重点关注前面四个范式就可以了，这也是我们大多数数据库设计要遵循的范式。
第一范式：
定义：数据库表中的所有字段都是单一属性，不可再分，这个单一的属性是有基本的数据类型所构成的，如整数，字符串等；
换句话说，第一范式要求数据库中的表都是二维表。
如下格式的表就不符合第一范式：
更改为如下即可避免违反第一范式：
第二范式：
定义：数据库的表中不存在非关键字段对任意候选关键字段的部分函数依赖。
部分函数依赖是指存在着组合关键字中的某一个关键字决定非关键字的情况。
换句话说：所有单关键字段的表都符合第二范式
由于供应商和商品之间是多对多的关系，所以只有使用商品名称和供应商名称才可以唯一标识出一件商品。也就是商品名称和供应商名称是一组组合关键字。表中存在以下的部分函数依赖关系：
（商品名称）=》（价格，描述，重量，商品有效期）
（供应商名称）=》（供应商电话）
所以会存在以下异常：
插入异常：比如说饮料一厂如果没有给我们提供信息时，我们就无法饮料一厂的电话等信息（供应商电话是依赖于供应商名称的）
删除异常：如果把饮料一厂的可乐的信息删除时，就会把饮料一厂的电话给删除了，导致无法查找到饮料一厂的信息（供应商电话是依赖于供应商名称的）
更新异常：如果要更新饮料一厂的电话，那么我们就要更新所有饮料一厂的记录
数据冗余：如果插入多个饮料一厂的商品时，就会有饮料一厂的电话为重复的
根据以上我们可以将表拆分如下就可以避免违反第二范式了：
第三范式：
定义：第三范式是在第二范式的基础之上定义的，如果数据表中不存在非关键字段对任意候选关键字段的传递函数以爱则符合第三范式
存在以下传递函数依赖关系：
（商品名称）=》（分类）=》（分类描述）
也就是说存在分关键字段“分类描述”对关键字段“商品名称”的传递函数依赖
存在问题：
（分类，分类描述）对于每一个商品都会进行记录，所以存在着数据冗余，同时也还存在数据的插入，更新，删除异常
根据以上我们可以将表拆分如下就可以避免违反第三范式了
BC范式：
定义：在第三范式的基础之上，数据库表中如果不存在任何字段对任意候选关键字段的额传递函数依赖则符合BC范式，也就是说如果是组合关键字，则组合关键字之间也不能存在函数依赖关系
假定：供应商联系人只能受雇于一家供应商，每家供应商可以供应多个商品，则存在如下的决定关系：
（供应商，商品ID）=》（联系人，商品数量）
（联系人，商品ID）=》（供应商，商品数量）
因此就会存在如下的不符合BC范式：
（供应商）=》（供应商联系人）
（供应商联系人）=》（供应商）
根据以上我们可以将表拆分如下就可以避免违反BC范式了：
物理设计：
物理设计是做什么？
1.选择合适的数据库管理系统
2.定义数据库、表及字段的命名规范
3.根据所选的DBMS系统选择合适的字段类型
4.反范式化设计
常见的DBMS：
MySQL常用的数据库引擎：推荐使用Innodb
表及字段的命名规则：
1.可读性原则
使用大写和小写来格式化的库对象名字以获得良好的可读性，例如：使用CustAddress而不是custaddress来提高可读性（这里要注意有些DBMS系统对表明的大小写是敏感的）
2.表意性原则
对象的名字应该能够描述它所标识的对象。例如：对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程，存储过程名称应该能够体现存储过程的功能
3.长名原则
尽可能少使用或者不适用缩写，使用与数据库名之外的任一对象
数据库字段及类型选择原则：
列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询性能。等一个列 可以选择多种数据类型时，应该优先考虑数字类型，、其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该有限选择占用空间小的数据类型。
以上选择原则主要是从下面两个角度考虑：
1.在对数据进行比较（查询条件、JOIN条件及排序）操作时，同样的数据，字符处理往往比数字处理慢。
2.在数据库中，数据处理以页为单位，列的长度越小，利于性能的提升
下面是MySQL中常用数据类型的存储大小概览：
具体的类型选择原则
1.char与varchar的选择
原则1：如果列中要存储的数据长度差不多一致的（比如身份证号），则应该考录用char，否则应该考虑varchar
原则2：如果列中的最大数据长度小于50Byte，则一般也考虑使用char。（当然，如果这个列很少用，则给予节省空间和减少I/O的考虑，还是可以选择使用varchar）
原则3：一般不宜定于大于50Byte的char类型列
2.小数类型的选择
原则1：decimal用于存储精确数据，而float只能用于存储非精确数据。故精确数据只能使用decimal类型
原则2：由于float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，而精确到15位小数只需要8个字节），故非精确数据有限选择float类型
3.时间类型的选择
原则1：使用int来存储时间字段的优缺点：
优点：字段长度比datetime小
缺点：使用不方便，要进行函数转换
限制：只能存储到2038-1-19 11:14:07即2^32为2147483648
原则2：根据需要存储的时间粒度选择，比如只存储年，就直接用year，只需要一个字节（年 月 日 时 分 秒 周）
其他注意事项：
如何选择主键：
1.区分业务主键和数据库主键
业务主键用于标识业务数据，进行表与表之间的关联；数据库主键为了优化数据存储（Innodb会生成6个字节的隐含主键）（数据库主键指的是本身表的id，比如可以创建一个自增id当做数据库主键。业务主键是指对于本身表的业务逻辑关系组成的主键，比如你这张表示股票日行情表，业务主键就是股票+日期）
但是数据库不允许有两个主键，建议自增id设置为唯一键）
2.根据数据库的类型，考虑主键是否要顺序增长
有些数据库是按主键的顺序逻辑存储的
3.主键的字段类型所占空间要尽可能的小
对于使用聚焦索引方式存储的表，每个索引后都会附件主键信息
避免使用外键
1.降低数据导入的效率
2.增加维护成本
3.虽然不建议使用外键约束，但是相关联的列上一定要建立索引
避免使用触发器
1.降低数据导入的效率
2.可能会出现意想不到的数据异常
3.使业务逻辑变得复杂
关于预留字段
1.无法准确的知道预留字段的类型
2.无法准确的知道预留字段中所存储的内容
3.后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的
4.严禁使用预留字段
反范式化设计：
什么是反范式化？
反范式化是针对范式化而言的，在前面介绍了数据库设计的第三范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间。
符合范式化的设计：
如上，如果要查询订单信息：
SELECT b.用户名,b.电话,b.地址,a.订单ID,SUM(c.商品价格*c.商品数量) as 订单价格
from ‘订单表’ a
join '用户表' b ON a.用户ID = b.用户ID
join '订单商品表' c ON c.订单ID = b.订单ID
GROUP BY b.用户名,b.电话,b.地址,a.订单ID
反范式化设计：
经过反范式化设计后，如果需要查询订单信息：
SELECT a.姓名,a.地,a.订单价格,a.订单ID
FROM '订单表' a
为什么要反范式化？
1.减少表的关联数量
2.增加数据的读取效率
3.反范式化一定要适度
数据库的维护和优化
维护和优化中需要做什么？
1.维护数据字典
2.维护索引
3.维护表结构
4.在适当的时候对表进行水平拆分或垂直拆分
如何维护数据字典
1.使用第三方工具对数据字典进行维护
2.利用数据库本身的备注字段来维护数据字典，以MySQL为例：
create table customer(
cust_id int auto_increment not null comment '自增ID',
cust_name varchar(10) not null comment '客户姓名'，
primary key (cust_id)
)comment '客户表'
3.导出数据字典
select
a.table_name,b.TABLE_COMMENT,a.COLUMN_NAME,
a.COLUMN_TYPE,a.COLUMN_COMMENT from
information_schema.COLUMNS a
JOIN information_schema.TABLES b
ON a.table_schema = b.table_schema and
a.table_name = b.table_name where a.table_name = 'customer'
如何维护索引
如何选择合适的列建立索引？
1.出现在where从句，group by从句，order by从句中的列
2.可选择性高的要放在索引的前面
3.索引中不要包括太长的数据类型
注意事项
1.索引并不是越多越好，过多的索引不但会降低写效率，而且会降低读的效率
2.定期维护索引碎片
3.在SQL语句中不要使用强制索引关键字（在mysql可以通过force index关键字强制指定查询使用某个索引）
如何维护表结构：
注意事项
1.使用在线变更表结构的工具（MySQL5.5之前可以使用第三方工具pt-online-schema-change，MySQL5.6以后本身支持在线表结构的变更）
2.同时对数据字典进行维护
3.控制表的宽度和大小
数据库中适合的操作：
1.批量操作VS逐条操作
2.禁止使用select * 这样的查询
3.控制使用用户自定义的函数
4.不要使用数据库中的全文索引
数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。垂直切分一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面， 如下图：垂直切分的优缺点
表的垂直拆分：
为了控制表的宽度可以进行表的垂直拆分）（经常查询的列放在一起，text，blob等大字段拆分到附表中）
表的水平拆分：
为了控制表的大小可以进行表的水平拆分
