良好的数据库逻辑设计和物理设计是数据库高性能的基础,所以对于数据库结构优化是很有必要的
数据库结构优化目的:
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、建立数据库表结构