MySQL性能优化(五)业务设计

MySQL性能优化(五)业务设计

1.逻辑设计

1.1 范式设计

1.1.1 数据库设计的第一大范式

要求数据库表的每一列都是不可分割的原子数据项。

  • 数据库表中的所有字段都只具有单一属性
  • 单一属性的列是由基本数据类型所构成的
  • 设计出来的表都是简单的二维表

image.png

name-age列包含两个属性,这样就违反了第一范式,正确的做法是把name和age拆分成两列

1.1.2 数据库设计的第二大范式

  • 第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
  • 要求表中只具有一个业务主键,也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系

举例说明: image.png

在上图所示的情况中,同一个订单中可能包含不同的产品,因此主键必须是“订单号”和“产品号”联合组成,

但可以发现,产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关,但是订单金额和订单时间仅与“订单号”相关,与“产品号”无关,

这样就不满足第二范式的要求,调整如下,需分成两个表: image.png

1.1.3 数据库设计的第三大范式

  • 在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
  • 第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

image.png

上表中,所有属性都完全依赖于学号,所以满足第二范式,但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”,而不是主键“学号”,所以需做如下调整: image.png

这样以来,就满足了第三范式的要求。

1.2 范式设计实战

按要求设计一个电子商务网站的数据库结构

1.2.1 商品管理

image.png

image.png

  • 商品信息:一个商品可以属于多个分类,故,商品名称和分类应该是组合主键,会有大量冗余,不符合第二范式。应该把分类信息单独存放
  • 另外再建立一个中间表把分类信息和商品信息进行关联

最后的三张表如下: image.png

1.2.2 供应商管理功能

image.png

符合三大范式,不需要修改,但假如增加新的一列【银行支行】,这样随着银行账户的变化,银行支行也会编号,不符合第三大范式

image.png

1.2.3 在线销售功能

image.png

  • 有多个业务主键,不符合第二范式
  • 订单商品单价。订单数量,订单金额 存在传递依赖关系,不符合第三范式

拆分的结果如下

image.png

这时候,【订单商品分类】与【订单商品名】有依赖关联,故合并如下 image.png

表汇总 image.png

1.2.4 查询练习

编写SQL查询出每一个用户的订单总金额(用户名,订单总金额)

image.png

编写SQL查询出下单用户和订单详情(订单编号,用户名,手机号,商品名称,商品数量,商品价格)

image.png

问题:

  • 大量的表关联非常影响查询的性能

  • 完全符合范式化的设计有时并不能得到良好得SQL查询性能

1.3 反范式设计

image.png

1.3.1 商品信息反范式设计

下面是范式设计的商品信息表

image.png

商品信息和分类信息经常一起查询,所以把分类信息也放到商品表里面,冗余存放

1.3.2 在线销售功能反范式

下面是在线销售功能的范式设计

image.png

1.3.3 表的优化

首先来看订单表

  1. 查询订单信息要关联查询到用户表,但用户表的电话是可能改变的,而且查询订单的时候经常查询到用户的电话

  2. 查询订单经常会查询到订单金额,所以把订单金额也冗余进来

新设计的订单表如下

image.png

再来看订单关联表

  1. 和商品信息反范式设计一样,查询订单的时候经常查询商品分类,所以把商品分类和订单名冗余进来

  2. 商品的单价可能会编号,如果关联查询查询只能查询到最新的商品价格,而查询不到下订单时候的价格,并且商品单价经常会查询。 所以把订单单价也冗余进来

新设计的商品关联表如下

image.png

查询练习

  • 编写SQL查询出每一个用户的订单总金额

image.png

  • 编写SQL查询出下单用户和订单详情 image.png

总结

  • 不能完全按照范式得要求进行设计

  • 考虑以后如何使用表

  • 范式化设计优缺点

    • 优点:
    1. 可以尽量得减少数据冗余
    2. 范式化的更新操作比反范式化更快
    3. 范式化的表通常比反范式化的表更小
    • 缺点:
    1. 对于查询需要对多个表进行关联
    2. 更难进行索引优化
  • 反范式化设计优缺点

    • 优点:
    1. 可以减少表的关联
    2. 可以更好的进行索引优化
    • 缺点:
    1. 存在数据冗余及数据维护异常
    2. 对数据的修改需要更多的成本

2. 物理设计

2.1 命名规范

  • 数据库、表、字段的命名要遵守可读性原则

  • 使用大小写来格式化的库对象名字以获得良好的可读性 例如:使用custAddress而不是custaddress来提高可读性。

  • 数据库、表、字段的命名要遵守表意性原则 对象的名字应该能够描述它所表示的对象 例如: 对于表,表的名称应该能够体现表中存储的数据内容;对于存储过程 存储过程应该能够体现存储过程的功能。

  • 数据库、表、字段的命名要遵守长名原则 尽可能少使用或者不使用缩写

2.2 存储引擎选择

image.png

2.2.1 数据类型选择

  • 当一个列可以选择多种数据类型时

    • 优先考虑数字类型
    • 其次是日期、时间类型
    • 最后是字符类型
    • 对于相同级别的数据类型,应该优先选择占用空间小的数据类型
  • 浮点类型

image.png

注意float 和double 是非精度类型,如果是和金额相关尽量用decimal

image.png

select sum(c1),sum(c2),sum(c3) from test_numberic

image.png

  • 日期类型 面试经常问道 timestamp 类型 与 datetime区别

image.png

  • datetime类型在5.6中字段长度是5个字节
  • datetime类型在5.5中字段长度是8个字节

timestamp 和时区有关,而datetime无关

image.png

insert into  test_time  VALUES(NOW(),NOW(),NOW());
set time_zone="-10:00"

image.png

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×