数据库设计与优化

数据库设计
什么是数据库设计?
简单来说，数据库设计就是跟据业务系统的具体需要，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据库存储模型。并建立好数据库中的表结构及表与表之间的关联关系的过程。
使之能有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。
常用的关系型数据库有：MYSQL、Orcale、SQLServer、PgSql
常用的非关系型数据库有：Mongodb、Memcache、Redis
重点：有效的存储，高效的访问

为什么要进行数据库设计？
数据库系统是应用系统存储数据的关键部分，是系统稳定运行的基础，决定着系统能否高效的运行。
优良的设计带来的好处：减少数据冗余，避免数据维护异常，节约存储空间，高效访问

数据库设计步骤：需求分析–》逻辑设计–》物理设计–》维护优化

需求分析的重点在于：
1.数据是什么
2.数据有哪些属性
3.数据和属性各自的特点有哪些
逻辑设计：使用ER图对数据库进行逻辑建模
物理设计：根据数据库自身的特点把逻辑设计转换为物理设计

维护优化重点：
1.新的需求进行建表
2.索引优化
3.大表拆分
为什么要进行需求分析？
1.系统中要存储的数据有哪些
例如：哪些数据需要存储到数据库
2.数据的存储特点是什么
例如：一些有时效性的数据，或者文本数据
3.数据的生命周期
例如：对于一些非核心的数据如日志这些定期的清理，归档
数据库存储的对象
1.实体于实体之间的关系（1对1,1对多，多对多）
2.实体所包含的属性有哪些
3.哪些数据线或属性的组合可以标识一个实体

实例演示
以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块：用户模块、商品模块、订单模块、购物车模块、供应商模块
1.用户模块：用于记录注册用户信息包括的属性有：用户名、密码、电话、邮箱、身份证号、地址、姓名、年龄等等
可选的唯一标识属性：用户名、身份证、电话、邮箱
存储特点是随系统上线时间的增加，用户会渐渐的增加，当然这些用户的数据是需要永久的存储的。所以在设计用户模块的时候要考虑到对于用户信息的分库分表
2.商品模块：用于记录网站中所销售的商品信息，包括的属性有，商品编码、商品名称、商品描述、商品品类、供应商名称、重量、价格、有效期等等
可选唯一标识属性：商品编码，（商品名称和供应商名称）
存储的特点是对于下线的商品可以归档存储，比如一些供应商不在提供某一件商品的时候，对于这个商品就可以实现下线归档，但是切记，商品数据不可删除，因为这是商品的明细都是和订单关联的
3.订单模块：用于用户订购商品的信息包括属性：订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型等等
可选的唯一标识属性：订单号
存储特点：永久存储（分表，分库存储）
4.购物车模块：用于保存用户购物时选对的商品包括属性：用户名、商品编号、商品名称、商品价格、商品描述、商品分类、商品数量等等
可选唯一标识属性：购物车编号
存储特点：不用永久存储（设置归档、清理规则）
5.供应商模块：用于保存所销售商品的供应商信息包括属性：供应商编号、供应商名称、联系人、电话、营业执照号、地址、法人等等
可选唯一标识属性：营业执照号、供应商编号
存储特点：永久存储
分析一下各个模块之间的关系如下图：

逻辑设计是做什么的？
1.将需求转换为数据库的逻辑模型
2.通过ER图的形式对逻辑模型进行展示
3.同所选用的具体DBMS系统无关
ER图例说明
矩形：表示实体集，矩形内写实体集的名字
菱形：表示关系集
椭圆：表示实体的属性
线段：将属性连接到实体集，或将实体集连接到关系集
ER图例如下

设计规范
数据库范式理解：
什么是范式：简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些
规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。
操作异常分为：
插入异常：如果某个实体随着另一个实体的存在而存在，即使缺少某个实体时无法表示这个实体，那么这个表就存在插入异常
更新异常：如果更改表所对应的某个实体实例的单独属性时，需要经多行更新，那么就说这个表存在更新异常
删除异常：如果删除表的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常
数据冗余是指相同的数据在多个地方存在，或者说表中的某列可以由其它列计算得到，这样就说表中存在着数据冗余

什么是三大范式：
第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。



第二范式存在的问题：
1.插入异常：如果饮料一厂又添加了一种饮料，但是不对我们提供电话，这个时候我们就没有饮料一厂的信息了，也就是找不到饮料一厂的电话了
2.删除异常：饮料一厂只为我们提供了可乐这一种饮料，如果我们把可乐这一件商品给删除了，那么我们就找不到饮料一厂的信息了
3.更新异常：如果饮料一厂，提供了多件商品，我们如果只想更新可乐这一件饮料的电话的话，那么就会更新所有的电话，这个时候就存在更新异常了
4.数据冗余：饮料一厂提供多件商品，那么我们就会多次保存供应商的信息，这个时候就造成了数据冗余
符合规范后为：

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.



第三范式存在的问题：
1.数据冗余：如图，如果我们在提供一些饮料，酒水那么每一件酒水饮料的分类和分类描述都是一样的，都会存储到数据库中，所以这个时候就造成了数据冗余
2.插入异常：如果我们表中不存在任何的酒水饮料，那么我们就查询不到任何的分类信息和分类描述了无法给用户展示分类描述
3.更新异常：如果我们要根据酒水饮料进行更新，那么所有的分类名称是酒水饮料的数据都会被更新，所以这个时候就存在，更新异常了
4.删除异常：同理如果我们要根据酒水饮料下的所有商品进行删除，那么所有的分类名称是酒水饮料就展示不出来了
符合规范后





BC范式存在的问题：
1.插入异常：如果饮料一厂，没有对我们提供任何的商品，那么我们就看不到饮料一厂的任何信息了，找不到饮料一厂的联系人，造成数据丢失
同样，情况也存在更新异常和删除异常，数据冗余也是很明显的，每次都会报存供应商的所有信息造成数据冗余
注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
数据库操作异常及数据冗余

名称解释：
关系：一个关系对应通常所说的一张表
元组：表中的一行即为一个元组
属性：表中的一列即为一个属性；每一个属性都有一个名称，称为属性名
候选码：表中的某个属性组，它可以唯一确定一个元组
主码：一个关系有多个候选码，选定其中一个为主码
域：属性的取值范围
分量：元组中的一个属性值

数据库物理设计要做什么？
1.要选择合适的数据库管理系统（mysql,oracle）
常见的DBMS系统可分为两类1.商业数据库（Oracle、SQLServer）2.开源数据库（MYSQL、PgSQL）
第一考虑使用数据库的成本，然后就是考虑数据库功能，和数据库的运行环境，开发所使用的语言
数据库应用的场景，商业数据库更适合企业级的项目，开源数据库更适合互联网项目
MYsql的存储引擎

建议使用innoDB他不管是对于效率还是维护上相对于其他的引擎都比较好用

2.定义数据库、表及字段的命名规范
所有的对象命名应该遵循以下原则
1.可读原则：使用大写和小写来格式化的库对象名字来获得良好的可读性。例如：使用CustAddress而不是使用custaddress来提高可对性（注意有些DBMS系统是对表名的大小写敏感的）
2.表意性原则：对象的名字应该能够描述它所标识的对象。例如：对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程，存储过程名称应该能够体现存储过程的功能。
3.长名原则:尽可能的少使用或者不使用缩写，使用与数据库（DATABASE）名之外的任一对象。

3.根据所选的DBMS系统选择合适的字段类型
列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型的时候，应该优先考虑数字类型，其次是日期或者二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

以上现在原则只要是从下面两个角度考虑：
1.在对数据进行比较（查询条件、JOIN条件及排序）操作时：
同样的数据，字符处理往往比数字处理的慢。
2.在数据库中，数据处理是以页为单位，列的长度越小，利于性能提升（以现有的场景来考虑，数据库带给我们的并不是CPU带来的问题，而是磁盘空间(I\O)带来的问题如果能优化磁盘的I\O性能，，那么就能优化数据库的性能）

如何选择同种类型的数据类型
比如char和varchar
原则：
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为2147483648
2.需要存储的时间粒度
年月日小时分秒周
如果存储的日期经常被查询到，那么对于可读性的话，还是使用日期类型来存储，如果只是被存储不会被查询，那么最好使用时间戳来存储

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

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

避免使用触发器
1.降低数据导入的效率
2.可能会出现意想不到的数据异常
3.使业务逻辑变的复杂
比如：设置完触发器后，程序变更了，修改完程序后，触发器还是一样做着之前的操作，可能就会导致业务异常，我之前就碰到过这个问题，简单说一下：在应收应付表中建立了一个触发器。可能当时是为了防止，重复生成应收应付数据，所以加了一个触发器，大概是这样设置的，就是在数据插入的时候，同一时间同一个操作人，不能同时插入2条数据，后来由于功能改造，做了个自动扣款，对于当天的业务，在晚上进行批量收款，同时需要插入相关表，方便财务同事进行报表统计，当时测试的时候用的是1条数据，都没问题，到预生产灰度测试的时候，使用了3条数据，就出现触发器异常了，因为不知道之前的业务设计初衷，所以再保存原业务逻辑的基础上，我把操作人修改为循环插入的数据的下标加原操作人，这样在同一时间内的操作人就不会重复了。

关于预留字段
1.无法准确的知道预留字段的类型。
2.无法准确的知道预留字段中所存储的内容
3.后期维护预留字段所要的成本，同时增加一个字段所需要的成本是相同的
4.严禁使用预留字段
比如：在设计数据库时，开发同事考虑到后来程序的扩展可能会预留字段，来以供后续程序扩展时需要，但是这个预留字段起不到增加程序扩展性，减少数据库维护成本的目的，建议是以后不使用预留字段，因为这个预留字段是没有任何意义的。

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

这个时候查询订单信息

反规范化以后设计表

再查下订单信息

为什么反范式化
1.减少表的关联数量
2.增加数据的读取效率
3.反范式化一定要适度

维护和优化要做什么
1.维护数据字典
如何维护数据字典
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 '客户表'

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 * 这样的查询（造成I\O浪费）
3.控制使用用户自定义函数（可能会对索引造成影响）
4.不要使用数据库中的全文索引（需要另外的建立文件，会增加数据库的负担）

4.在适当的时候对表进行水平拆分或垂直拆分
垂直拆分：将一张大表分为多张小表，多张小表的数据等于之前表的数据
水平拆分：将一张大表分为多张大表，每张表中的数据不同，数据比较平均