2 数据库结构优化

2.1 数据库结构优化的目的

• 减少数据冗余
• 尽量避免数据维护中出现更新,插入和删除异常
  • 插入异常 如果表中的某个实体随着另一个实体而存在 先看一个表结构

  

  为学号,课程名称列定义主键约束,即一个学生只能选相同的课程一次 看看数据

  

  存在数据冗余 插入一门新课试试

  

  由于主键约束的存在,在没有学生选择这门课时无法将新课程插入到表中

  • 更新异常 如果更改表中的某个实体的单独属性时,需要对多行进行更新

  

  更新了2行数据,数据越多,同时更新的也就越多,可看出和数据冗余有很大联系

  • 删除异常 如果删除某一个实体会导致其他实体的消失
• 节约数据库存储空间
• 提高查询效率

2.2 数据库结构设计的步骤

2.3 数据库设计范式

有时需要反范式

符合第一范式

将上个表拆成两个表,即符合第二范式

上面的study学生信息表,学号可以确定学院,而学院地址又与学院有关系,所以学院地址和学号传递依赖关系,所以对其拆分符合第三范式

学生表数据

学生信息表

学院信息表

2.4 数据库需求分析及逻辑设计实例

需求说明

需求分析及逻辑设计,设定用户名为主键

分析

一本书可能在多个分类中,所以联合主键商品名称和分类名称,不符合第二范式,所以拆分

只有一个主键,满足三范式

需要拆分

实际性能

2.5 反范式化(空间换时间)

对前面的案例进行反范式化改造

想查一个商品的信息,必须同时关联这三张表,而第三张表更是几乎每次查询都会用到确认某分类下某商品

所以对商品信息表反范式化即增加分类名称的冗余数据,为避免插入异常,保留分类信息表,这样不会发生丢失分类信息情况

订单表变换

2.6 反范式化后查询

只需查订单表,不再需要对四张表查询

由于冗余了用户手机号,商品价格在订单表和订单商品表冗余处理

2.7 范式和反范式优缺点

2.7.1范式化

优点

优点看起来很完美,提高了写操作但是损失了读操作性能

缺点

2.7.2反范式化

优点

缺点

image.png

2.8 物理设计

2.8.1概念

2.8.2定义数据库,表,字段的命名规范

2.8.3选择合适存储引擎

2.8.4为字段选择合适的数据类型

原则

2.8.4.1整数类型

2.8.4.2实数类型

示例表

示例表数据

看出计算不准确

一种精确地实数类型

看出计算精确

2.8.4.3字符串类型

2.8.4.3.1 varchar

定义的宽度是字符单位,存储才是字节单位

varchar类型存储特点

varchar长度选择

适用场景

2.8.4.3.2 char

存储特点

适用场景,身份证性别等

2.8.4.4日期类型

2.8.4.4.1DATETIME

最通用时间类型

2.8.4.4.2TIMESTAMP

2.8.4.4.3时区问题

设置时区-第十时区

建表

TIMESTAMP时间随时区变化

2.8.4.4.4微秒问题

表数据

2.8.4.4.5自动更新问题

建表

只有第二列自动更新时间,默认只有第一个TIMESTAMP列自动更新

2.8.4.4.6

MySQL5.7前存储时间可选方式

MySQL5.7后date,time类型横空出世

image.png

2.8.4.4.7注意事项!!!

后者不仅实质是int,而且也是时间戳

2.8.4.4.物理设计总结

第三条以前两条为基准