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、建立数据库表结构
