mysql

DesignDB-4-1_逻辑设计_(反)范式化

目的

  • 减少数据冗余
  • 尽量避免数据维护中出现更新,插入和删除异常
    • 插入异常: 表中的某个实体随着另外一个实体而存在
    • 更新异常: 如果更改表中某个实体的单独属性时,需要对多行进行更新
    • 删除异常: 如果删除表中某个实体会导致其他实体的消失
  • 节约数据库的存储空间
  • 提高查询效率

数据库设计的步骤

  1. 需求分析: 全面了解产品设计的存储需求
    • 存储需求
    • 数据处理需求
    • 数据安全性需求和完整性
  2. 逻辑设计: 设计数据的逻辑存储结构
    • 数据实体间的逻辑关系,解决数据冗余和数据维护异常
  3. 物理设计: 根据所使用的数据库特点进行表结构设计
    • 关系型数据库: Oracle,SQLServer,MySQL,postgresSQL
    • 非关系型数据库: mongo,Redis,Hadoop
    • 存储引擎: InnoDB
  4. 维护优化: 根据实际情况对索引.存储结构进行优化

需求分析和逻辑设计

按需求设计一个电子商务网站数据库

  1. 本网站只销售图书类商品
  2. 需具有以下功能
    • 用户登录
    • 商品展示
    • 供应商管理
    • 用户管理
    • 商品管理
    • 在线销售

用户登录及用户管理功能

  • 用户必须注册并登录才能进行交易(用户名做业务主键)
  • 同一个时间一个用户只能单点登录
  • 用户信息: 用户名,密码,手机,姓名,注册日期,在线状态,出生日期

只有1个业务主键,符合第二范式。 没有属性和业务主键存在传递依赖,符合第三范式

商品展示和商品管理功能

  • 商品信息: 商品名称,分类名称,出版社信息,图书价格,图书描述,作者
    • 商品信息表
      • 商品名称,出版社信息,图书价格,图书描述,作者
    • 分类信息表
      • 分类名称,分类描述
    • 商品分类(对应关系表)
      • 商品名称,分类名称

供应商管理功能

  • 供应商信息: 供应商名称,地址,电话,联系人,银行账户

在线销售功能

  • 在线销售: 订单编号,下单用户名,下单日期,订单金额,订单商品分类,订单商品名称,订单商品单价,订单商品数量,支付金额,物流单号

    只有订单编号1个主键,符合第二范式
    订单商品单价,订单商品数量,订单编号存在传递依赖,不符合第三范式
    存在数据冗余, 订单商品信息和商品信息表中的信息

  • 在线销售:

    • 订单编号,下单用户名,下单日期,订单金额,订单商品分类,订单商品名,订单商品单价,订单商品数量,支付金额,物流编号

  • 订单表:

    • 订单编号,下单用户名,下单日期,支付金额,物流编号

  • 订单商品关联表

    • 订单编号,订单商品分类,订单商品名,商品数量

      按上面设计会设计的问题

      • 关联表过多
      • 如果下单商品金额和下单时不一样,金额会有偏差

查询每个用户的订单总金额

1
2
3
4
5
6
select 下单用户名,sum(d.商品价格*b.商品数量)
from 订单表 a join 订单商品关联表b on a.订单编号=b.订单编号
              join 商品分类管理表c on c.商品名称=b.商品名称
                                    and c.分类名称=b.商品分类名称
              join 商品信息表d on d.商品名称=c.商品名称
group by 下单用户名

反范式化设计

目的: 用磁盘空间换时间

描述:为了提高读取效率和查询性能,适当违反数据库设计范式,允许部分数据冗余

商品展示和商品管理功能

  • 商品信息: 商品名称,分类名称,出版社信息,图书价格,图书描述,作者
    • 商品信息表
      • 商品名称,分类名称,出版社信息,图书价格,图书描述,作者
    • 分类信息表
      • 分类名称,分类描述

在线销售功能

  • 在线销售:
    • 订单编号,下单用户名,下单日期,订单金额,订单商品分类,订单商品名,订单商品单价,订单商品数量,支付金额,物流编号
  • 订单表:
    • 订单编号,下单用户名,支付金额,手机,下单日期,支付金额,物流编号
  • 订单商品关联表
    • 订单编号,订单商品分类,订单商品名,商品数量,商品单价

反范式化查询语句

带冗余查询优化结果

查询每个用户的订单总金额

1
2
3
select 下单用户名,sum(订单金额)
from 订单表
group by 下单用户名;

编写SQL查询下单用户和订单详情

1
2
select a.订单编号,a.用户名,a.手机号,b.商品名称,b.商品单价,b.商品数量 from 订单表a
join 订单关联表b on a.订单编号=b.订单编号;

范式化和反范式化优缺点

范式化优缺点

优点:
- 很少和几乎没有冗余数据
- 范式化更新操作比反范式更快
- 范式化的表通常比较小

缺点:
- 对查询需要关联多个表
- 更难进行索引优化

反范式化优缺点

优点:
- 可减少表操作
- 可用更好的进行索引优化

缺点:
- 表结构存在数据冗余和数据维护异常
- 更新操作需要更多的成本