数据库设计

数据库设计简介

为什么要进行数据库设计？
需求分析 -> 逻辑设计 -> 物理设计 -> 维护优化
1：新的需求进行建表
2：索引优化
3：大表拆分

需求分析

为什么要进行需求分析？ 如果接受到需求，并不会需求分析，而是马上写表，这个往往避免不是最优化。 1：了解系统中所要储存的数据 2：了解数据的储存特点 时效性，时效性过期 清理 归档 3：了解数据的生命周期 归档清理规则 不是核心数据，数据量增长大，分库分表 储存之前定义清理规则和归档规则

要搞清楚的一些问题 1：实体及实体之间的关系（1对1，1对多，多对多） 2：实体所包含的属性有什么？ 3：那些属性或属性的组合可以唯一标识一个实体

实例演示： 以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块： 用户模块，商品模块，订单模块，购物车模块，供应商模块。
实例演示–用户注册模块 用于记录注册用户信息 包括属性：用户名、密码、电话、邮箱、身份证号、地址、姓名、昵称… 可选唯一标识属性：用户名、身份证、电话
储存特点： 随系统上线时间逐渐增加，需要永久储存

商品模块： 用于记录网站中所销售的商品信息
包括属性：商品编码、商品名称、商品描述、商品品类、供应商名称、重量、有效期、价格…… 可选唯一标识属性：（商品名称、供应商名称）商品编码
储存特点：对于下线商品可以归档储存

订单模块： 用于用户订购商品的信息 包括属性：订单号、用户姓名、用户电话、收获地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型… 可选唯一标识属性：（订单号） 储存特点：永久储存（分表、分库储存）

购物车模块： 用于保存用户购物时选对的商品 包括属性：用户名、商品编号、商品名称、商品价格、商品描述、商品分类、加入世界、商品数量…
可选唯一标识：（用户名、商品编号、加入时间）（购物车编号） 储存特点：不用永久储存 （设置归档、清理规则）

供应商模块： 用于保存所销售商品的供应商信息 包括属性：供应商编码、供应商名称、联系人、电话、营业执照号、地址、法人… 可选唯一标识：（供应商编号），（营业执照号） 储存特点：永久储存 不会永远增加下去

商品和商品之间的关系

用户 <-1对多-> 订单 <-多对多->商品 <-多对多->供应商 用户<-1对多->购物车 <-多对多->商品

逻辑设计

1：将需求转化为数据库的逻辑模型
2：通过ER图的型式对逻辑模型进行展示
3：同所选用的具体的DBMS系统无关

名词解释

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

数据库设计范式
常见的数据库设计范式：第一范式，第二范式，第三范式，BC范式

物理设计

物理设计要做什么
表结构
1：选择合适的数据库管理系统。
Oracle ，SQLSEerver。mysql，PgSQl
应用的特点以及成本去考虑 2：定义数据库、表以及字段的命名规范。
3：根据所选的DBMS系统选择合适的字段类型。 varchar还是char类型 达到 4：反范式化设计 空间换时间

MySQL 常用的储存引擎 MyISAM MRG_MYISAM Innodb Archive Ndb cluster

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

字段类型的选择原则

birthday 的字段选择 Char(10): ‘xxx-xx-x’ Varchar(20): Datetime: int 时间戳

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

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

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 即2^32 2：需要储存的事件粒度 年 月 日 小时 分 秒 周

如何选择主键： 1：区分业务主键和数据库主键 业务主键用于标识业务数据，进行表与表之间的关联； 数据库主键为了优化数据储存（Innodb会生成6个字节的隐含主键）

2：跟踪数据库的类型，考虑主键是否要顺序增长 有些数据库的按主键的顺序逻辑储存的

3：主键的字段类型所占空间要尽可能的小 对于使用聚焦索引方式存储的表，每个索引后都会附加主键信息。

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

高并发的时候有外键会影响效率。

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

关于预留字段 1：无法准确的知道预留字段的类型。 2：无法准确的知道预留字段中所储存的内容。
3：后期维护预留字段所要的成本，同一个增加所需要的成本是相同的。
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 ‘客户表’ 3:导出数据字典 2：维护索引
如何选择合适的列简历索引？ 1：出现在where 从句group by 从句 ,orider by 从句中的列
2：可选择性高的列要放在索引的前面
3：索引中不要包括太长的数据类型

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

3：维护表结构
如何维护表结构 1：使用在线变更表结构的工具 因为担心更改表的时候会锁表的缘故，影响应用的使用。
Mysql5.5 之前可以使用pt-online-scheme-change
Mysql5.6 之后本身支持在线表结构的变更
2：同时对数据字典进行维护
3：控制表的宽度和大小

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

4：在适当的时候对表进行水平拆分或者垂直拆分
表的垂直拆分 为了控制表的宽度可以进行表的垂直拆分
1：经常一起查询的列放在一起
2：text，blob等大字段拆分出到附加表中

表的水平拆分
为了控制表的大小可以进行表的水平拆分  
通过主线Hash key 的方式