MySQL系列(三)--数据库结构优化

良好的数据库逻辑设计和物理设计是数据库高性能的基础,所以对于数据库结构优化是很有必要的

数据库结构优化目的:

1、减少数据的冗余

2、尽量避免在数据插入、删除和更新异常

例如:有一张设计不得当的学生选课表

CREATE TABLE selectcourse(
stu_no INT(11) NOT NULL COMMENT '学号',
stu_name VARCHAR(10) NOT NULL COMMENT '学生姓名',
birth_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '生日',
course_name VARCHAR(10) NOT NULL COMMENT '课程名称',
score INT(11) DEFAULT NULL COMMENT '成绩',
course_point INT(11) NOT NULL COMMENT '学分',
PRIMARY KEY(stu_no, course_name)
) ENGINE = INNODB DEFAULT CHARSET=utf8

查询结果:

1	sam	1994-06-22 16:19:39	数学		10
1 sam 2019-06-22 16:21:02 英语 9
2 jesen 1995-06-22 16:19:39 数学 10
2 jesen 1995-06-22 16:22:27 英语 9

插入异常:

  如果表中某个实体依赖另一个实体而存在,想添加一门语文课,但是依赖学生、学号等

更新异常:

  更新表中某个实体的单独属性时,需要对多行进行更新,如果把数学的学分进行更新,就需要更新多条数据

删除异常:

  删除表中某个实体,导致其他实体也被删除,想要删除英文这门课,但是也会被选择语文课的学生信息也删除

3、解决数据库存储空间

4、提高查询效率

结构优化设计步骤:

1、需求分析:存储需求、数据处理需求、安全性要求等

2、逻辑设计:

  1、设计数据的逻辑存储结构

  2、数据实体之间的逻辑关系,解决数据冗余和数据存储异常

3、物理设计:

  根据使用的数据库特点设计表结构

4、维护优化:

  根据实际情况对索引、存储结构进行优化

数据库三范式:

第一范式:

  数据库表中字段都是单一属性,不能再进行细化分解

第二范式:

  表中只有一个业务主键,不能存在非主键列对主键存在部分依赖,如果主键是单一列肯定符合,但是如果是复合主键,就肯定可能

不满足这一要求。

  例如上面的selectcourse表,主键:PRIMARY KEY (`stu_no`,`course_name`),学分只是依赖课程列,学生姓名只是依赖学号,

所以为了满足第二范式要求,需要对表进行拆分

  拆分为:学生表、课程表、学生选课表

第三范式:

  每个非主属性不能对业务主键存在依赖传递,消除了第二范式基础上非主键列对主键的传递依赖

  例如,现在学生表里面存在着学院,学院电话等信息,学院电话通过学院与学生表产生传递依赖,我们需要再拆分成学员信息表

满足三范式,基本就解决了数据冗余和数据异常的问题,但是不是一定要满足,看实际需求的,有时候需要反范式设计

需求分析和逻辑设计实战:

设计出下面电商网站的数据库结构

需求:

1、只销售图书类商品

2、需要以下功能

  1).用户登录

  2).商品展示

  3).供应商管理

  4).用户管理

  5).商品管理

  6).在线销售

需求分析:

用户登录:

  1、用户必须注册并登录系统才能进行交易

  2、同一时间只能一个用户在一个地方登录

  3、用户信息:{用户名(主键),密码,手机号,姓名,注册信息,在线状态,生日}

商品展示和管理功能:

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

  但是这样设计不符合三范式,如果增加一个分类,但是没有商品,是无法添加的,所以需要表拆分

  商品信息表:{商品名称,出版社名称,价格,描述,作者}

  分类信息表:{分类名称,分类描述}

  商品分类对应表:{商品名称、分类名称}

供应商管理:

  供应商信息:{出版社名称,地址,电话,联系人,银行账户}

在线销售:

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

  1、只有一个业务主键,符合第二范式

  2、订单编号,订单商品数量,订单商品单价存在传递依赖关系,不符合第三范式

  3、数据冗余:订单商品信息和商品信息表的数据

  拆分:

  订单表:{订单编号,订单用户名,订单日期,支付金额,物流单号}

  订单商品关联表:{订单编号,订单商品分类,订单商品名,支付金额,商品数量}

现在查询一个用户订单总金额的SQL:

select 订单用户名,sum(d.商品价格*d.商品数量) from 订单表 a join 订单商品关联表 b on a.订单编号 = b.订单编号

    join 商品分类关联表 c on c.商品名称 = b.商品名称 and c.分类名称 = b.订单商品分类

    join 商品信息表 d on d.商品名称 = c.商品名称

group by 下单用户名

我们发现管理的表太多,会导致性能变得的很差

查询下单用户和订单详情:SQL也会关联的很复杂

总结:完全符合三范式,可能导致SQL查询性能变差,所以需要反范式化设计

反范式化:

  为了性能和效率的考虑适当的违反范式化设计要求,允许存在少量的数据冗余,就是以空间换时间

对商品信息进行反范式化设计:

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

  分类信息表:{分类名称,分类描述}

现在查询一个用户订单总金额的SQL:

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

  订单表:{订单编号,订单用户名,手机号,订单日期,支付金额,物流单号,订单金额}

  订单商品关联表:{订单编号,订单商品分类,订单商品名,商品单价,商品数量}

范式化设计和反范式化设计的对比:

  1、范式化可以尽量的减少数据冗余

  2、范式化的更新操作比反范式化更快

  3、范式化的表通常比反范式化的表要小

  4、反范式化减少表的关联

  5、反范式化相比范式化可以更好的对索引进行优化,例如使用覆盖索引

物理设计:

  1、定义数据库、表和字段的命名规范

  命名蹲守可读性原则、表意性原则、长名原则,注意MySQL是区分大小写的

  2、选择合适的存储引擎

  3、为表中的字段设计合适的数据类型

  参考我之前写过的一篇文章:MySQL系列(一)--数据类型

  4、建立数据库表结构

上一篇:Spark Accumulators


下一篇:[JS] Topic - this is ”closure“