数据库设计

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

1.新的需求进行建表

2.索引优化

3.大表拆分

需求分析

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

2.了解数据的存储特点

3.了解数据的生命周期

要搞清楚的一些问题

1.实体及实体之间的关系(1对1,1对多,多对多)

2.实体所包含的属性有什么?

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

逻辑设计

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

2.通过ER图的形式对逻辑模型进行展示

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

名词解释

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

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

属性:表中的一列即为一个属性;每一个属性都有一个名称,称为属性名

候选码:表中的某个属性组,它可以唯一确定一个元组

主码:一个关系有多个候选码,选定其中一个为主码

域:属性的取值范围

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

E-R图

图例说明

 数据库设计范式

目前常用:第一范式,第二范式,第三范式和BC范式

数据操作异常及数据冗余

操作异常:

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

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

3.删除异常:如果删除表的某一行来反映某实体实例失效时导致另一个不同实体的实例信息丢失,那么这个表中就存在删除异常.

 数据冗余

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

第一范式(1NF)

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

第一范式要求数据库中的表都是二维表

 第二范式(2NF)

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

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

所有单关键字段的表都符合第二范式

 解释:由于供应商和商品之间是多对多的关系,所以只有使用商品名称和供应商名称才可以唯一标识出一件商品,也就是商品名称和供应商名称是一组组合关键字,表中存在以下的部分函数依赖关系:

(商品名称)→(价格,描述,重量,商品有效期)

(供应商名称)→(供应商电话)

表中存在的问题:插入异常,删除异常,更新异常,数据冗余

修改后:

 第三范式(3NF)

定义:如果数据表中不存在非关键字段,对任意候选关键字段的传递函数依赖则符合第三范式

 存在以下转递函数依赖关系:

(商品名称)→(分类)→(分类描述)

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

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

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

修改后

Boyce.Codd范式(BCNF)

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

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

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

(供应商,商品ID)→(联系人,商品数量)

(联系人,商品ID)→(供应商,商品数量)

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

(供应商)→(供应商联系人)

(供应商联系人)→(供应商)

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

修改后

物理设计

物理设计要做什么

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

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

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

4.反范式化设计

MySQL常用的存储引擎

 表及字段的命名规则

所有对象命名应该遵循下属原则:

1.可读性原则:使用大小写来格式化的库对象名字以获得`良好的可读性

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

.3.长名原则:尽可能少使用或者不使用缩写(怕有歧义)

 字段类型的选择原则

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

 以上选择原则主要是从下面两个角度考虑:

1.在对数据进行比较(查询条件、JOIN条件及排序)操作时:同样的数据,字符处理往往比数字处理慢.

2.在数据库中,数据处理以页为单位,列的长度越小,利于性能提升

char与varchar如何选择

 decimal与float如何选择

 时间类型如何存储

如何选择主键

避免使用外键约束

1.降低数据导入的效率

2.增加维护成本

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

避免使用触发器

1.降低数据导入的效率

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

3.使业务逻辑变得复杂

关于预留字段

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

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

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

4.严禁使用预留字段

反范式化

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

符合范式化的设计

 如何查询订单信息

 如何查询订单详情

 反范式化的设计

 为什么反范式化

1.减少表的关联数量

2.增加数据的读取效率

3.反范式化一定要适度

维护优化

 维护和优化中要做什么

1.维护数据字典

2.维护索引

3.维护表结构

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

如何维护数据字典

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

2.利用数据库本身的备注字段来维护数据字典

 3.导出数据字典

如何维护索引

 如何选择合适的列建立索引

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.不要使用数据库中的全文索引

表的垂直拆分

表的水平拆分

使用 哈希操作