浅谈数据库设计

第一章 需求分析

设计简介

根据业务需要，结合选用的DBMS，设计出最有的数据存储模型并建立好数据库中的表结构及表与表之间的关系使之有效的存储和高效的访问。 

数据库设计

在系统设计开始就应该对数据库进行良好的设计，这样才能保证以后对业务发展的需要进行改进，保证系统的稳定性。 

设计步骤

    需求分析：数据库需求的作用点（数据是什么，数据有哪些属性，数据属性的特点）

    逻辑设计：用ER图进行建模

    物理设计：选择数据库管理系统，根据数据库自身的特点把逻辑设计转换为物理设计

    维护优化：对新需求进行见表，索引优化，大表拆分

需求分析重要性

    1、了解系统中所要存储的数据

    2、了解数据存储的特点

    3、了解数据的生命周期

需要了解的问题：

    实体与实体之间的关系（一对一，一对多，多对多）

    实体所包含的属性

    哪些属性或属性的组合可以唯一标识一个实体

实例：小型电子商务网站

模块：用户模块，商品模块，订单模块，购物车模块，供应商模块

用户模块：用于记录注册用户信息

    包括属性：用户名、密码、电话、邮箱、身份证号、地址、姓名、昵称

    可选唯一标识属性：用户名、身份证、电话

    存储特点：随系统上线时间逐渐增加，需要永久存储

商品模块：用于记录网站中所有销售的商品信息

    包括属性：商品编码、商品名称、商品描述、商品品类、供应商名称、重量、有效期、价格。。。

    可选唯一标识属性：（商品名称、供应商名称）组合、（商品编码）

    存储特点：对于下线商品可以归档存储

订单模块：用于用户订购商品的信息

    包括属性：订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型。。。

    可选唯一标识属性：订单号

    存储特点：永久存储（分表、分库存储）

购物车模块：用于 保存用户购物时选择的商品

    包括属性：用户名、商品编号、商品名称、商品价格、商品描述、商品分类、加入时间、商品数量。。。

    可选唯一标识：（用户名、商品编号、加入时间）、（购物车编号）

    存储特点：不用永久存储（设置归档、清理规则）

供应商模块：用于保存所销售商品的供应商信息

    包括属性：供应商编号、供应商名称、联系人、电话、营业执照号、地址、法人。。。

    可选唯一标识：（供应商编号），（营业执照号）

    存储特点：永久存储

第二章 逻辑设计

E-R图

逻辑设计是做什么的

    1、将需求转化为数据库的逻辑模型

    2、通过E-R图的形式对逻辑模型进行展示

    3、同所选用的具体的DBMS系统无关

名词解释

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

    元组：表中的一行即为一个元组

    属性：表中的一列即为一个属性；每一个属性都有一个名称，称为属性名

    候选码：表中的某个属性组，它可以确定一个元组

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

    域：属性的取值范围

    分量：元组中的一个属性值

E-R图例说明 

实例演示（加下划线的是主键）

设计范式概要

什么是数据库设计范式

常见的数据库设计范式包括：第一范式，第二范式，第三范式，BC范式，第四、第五范式

这也是目前我们大多数数据库设计所要遵循的范式

数据操作异常及数据冗余

  操作异常：

    插入异常：如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常

    更新异常：如果更改表所对应的某个实体实例的单独属性时，需要将多行更新，那么就说这个表存在更新异常。

    删除异常：如果删除表中的某一行来反应某实体实例，失效时导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常。

  数据冗余：

    是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到，这样就说表中存在着数据冗余。

第一范式（1NF）：

定义：数据库表中的所有字段都是单一属性，不可再分的。这个单一属性是由基本的数据类型所构成的，如整数，浮点数，字符串等；

换句话说  第一范式要求数据库中的表都是二维表。

第二范式（2NF）

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

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

换句话说：所有单关键字段的表都符合第二范式

由于供应商和商品之间是多对多的关系，所以只有使用商品名称和供应商名称才可以唯一标识出一件商品。也就是商品名称和供应商名称是一组组合关键字。

上表中存在以下的部分函数依赖关系

（商品名称）->（价格，描述，重量，商品有效期）

（供应商名称）->（供应商电话）

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

第三范式（3NF） 

定义：第三范式是在第二范式的基础上定义的，如果数据表中不存在非关键字段，对任意候选关键字段的传递函数依赖则符合第三范式。

存在以下传递函数依赖关系：

（商品名称）->（分类）->（分类描述)

也就是说存在非关键字段“分类描述”

对关键字段“商品名称”的传递函数依赖

存在问题：（分类，分类描述）对于每一个商品都会进行记录，所以存在着数据冗余。同时也存在着数据的插入，更新及删除异常

拆分

BC范式

Boyce.Codd范式（BCNF）

定义：在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。

也就是说如果是复合关键字，则复合关键字之间也不能存在函数依赖关系。

（以商品同供应商的关系表来说明BCNF）

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

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

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

存在下列关系因此不符合BCNF要求：

（供应商）->（供应商联系人）

（供应商联系人）->（供应商）

并且存在数据操作异常及数据冗余

拆分

第三章 物理设计

物理设计要做什么

    1、选择合适的数据库管理系统

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

    3、根据所选的DBMS系统选择合适的字段类型（效率，功能，需求）

    4、反范式化设计（冗余）

选择哪种数据库

 

成本、版权、功能（性能）、操作系统、开发语言、应用场景

mysql常用的存储引擎

开源数据库，只要符合MySQL存储协议，任何人都可以开发存储引擎

主要使用Innodb存储引擎

mysql常用存储引擎

表及字段的命名规范

所有对象命名应该遵循下述原则：

    1、可读性原则：使用大写和小写格式化的库对象名字已获得良好的可读性。

例如：使用CustAddress而不是custaddress来提高可读性。

（这里要注意有些DBS系统对表名的大小写是敏感的）

    2、表意性原则：对象的名字应该能够描述它所标识的对象。

例如：对于表，表的名称应该能够体现表中存储的数据内容；

对于存储过程，存储过程名称应该能够体现存储过程的功能。

    3、长名原则：尽可能少使用或者不使用缩写，适用于数据库（DATABASE）名之外的任一对象。

字段类型选择原则

生日可选类型

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

各种类型所占存储空间

如何具体选择字段类型

char与varchar如何选择

    原则：

        1、如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar。

        2、如果劣种的最大数据长度小于50Byte，则一般也考虑用char。

        3、一般不宜定义大于50Byte的char类型列。

utf8每个字符占三个字节

decimal与float类型如何选择

    原则：

        1、decimal用于存储精确数据，而float只能用于存储非精确数据。

        2、由于float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，而精确到15位小数只需要8字节）故非精确数据优先选择float类型。

数据库设计的其他注意事项

时间类型存储：

    1、使用int来存储时间字段的优缺点

        优点：字段长度比datetime小。

        缺点：使用不方便，要进行函数转换。

        限制：只能存储到2038-1-19  11：14：07即2^32为2147483648

    2、需要存储的时间粒度

        年  月  日  时  分  秒  周

数据库设计其他注意事项

    如何选择主键

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

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

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

    避免使用外键约束

        1、降低数据导入的效率

        2、增加维护成本

        3、虽然不建议使用外键约束，但是相关联的列上一定要建立索引

    避免使用触发器

        1、降低数据导入的效率

        2、可能会出现意想不到的数据异常。

        3、使业务逻辑变得复杂

    关于预留字段

        1、无法准确的知道预留字段的类型。

        2、无法准确的知道预留字段中所存储的内容。

        3、后期维护预留字段所需要的成本，同增加一个字段所需要的成本是相同的。

        4、严禁使用预留字段

反范式化表设计

    什么是反范式化

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

    为什么反范式化

        1、减少表的关联数量

        2、增加数据的读取效率

        3、反范式化一定要适度

第四章 维护和优化

维护和优化要做什么

    1、维护数据字典

    2、维护索引

    3、维护表结构

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

如何维护数据字典

    1、使用第三方工具对数据字典进行维护

    2、利用数据库本身的备注字段来维护数据字典。以mysql为例

CREATE TABLE costomer(

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_COMMENTFROM

information_schema.COLUMENS a JOIN information_schema.

TABLE 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语句中不要使用强制索引关键字

数据库中适合的操作

如何维护表结构

注意事项：

    1、使用在线变更表结构的工具

        MySql5.5之前可以使用pt-online-schema-change

        MySql5.6之后本身支持在线表结构的变更

    2、同时对数据字典进行维护

    3、控制表的宽度和大小

数据库中适合的操作

    1、批量操作VS逐条操作

    2、禁止使用SELECT *这样查询

    3、控制使用用户自定义函数

    4、不要使用数据库中的全文索引

表的垂直拆分和水平拆分