mysql数据结构优化_mysql梳理笔记数据结构优化（实例分析）

需求说明

按照下面的需求设计一个电子商务网站的数据库结构

1.本网站只销售图书类商品

2.需要具有以下功能&＃xff1a;

用户登录

商品展示

供应商管理

用户管理

商品管理

在线销售

用户登录及用户管理功能

用户必须登录系统才能进行网上交易&＃xff1a;用户名做用户信息的业务主键(唯一)

同一时间&＃xff0c;统一用户只能在同一个地方登录

用户信息&＃xff1a;用户名/密码/手机号/姓名/注册日期/在线状态/出生日期

只有一个业务主键&＃xff0c;一定符合第二范式

没有属性和业务主键存在传递依赖关系&＃xff0c;符合第三范式

商品展示

商品信息&＃xff1a;商品名称/分类名称/出版社名称/图书价格/图书描述/作者。商品信息和分类名称同时作为业务主键。

分类名称不符合第三范式。如果添加一个分类&＃xff0c;但是没有商品信息&＃xff0c;数据会有问题。所以需要拆分表。

商品信息&＃xff1a;商品名称/出版社名称/图书价格/图书描述/作者&＃xff0c;商品名称做主键。

分类信息&＃xff1a;分类名称/分类描述&＃xff0c;分类名称做主键。

商品分类(对应关系表)&＃xff1a;分类名称和商品名称联合做业务主键

供应商管理

供应商信息&＃xff1a;出版社名称/地址/电话/联系人/银行账号。出版社名称做主键。

这个表只有一个主键&＃xff0c;不涉及引用关系&＃xff0c;所以符合范式要求。

在线销售功能

在线销售&＃xff1a;订单编号/下单用户名称/下单日期/订单金额/订单商品分类/订单商品名/订单商品单价/订单商品数量/支付金额/物流单号。订单编号做主键

只有一个业务主键&＃xff0c;符合第二范式&＃xff1b;订单商品单价/订单商品数量/订单编号/ 存在传递依赖关系&＃xff0c;不符合第三范式

数据冗余&＃xff1a;订单商品信息和商品信息表中的数据

拆分

订单表&＃xff1a;订单编号/下单用户名称/下单日期/支付金额/物流单号

订单商品关联表&＃xff1a;订单编号/订单商品分类/订单商品名称/商品数量。主键是订单编号/订单商品分类/订单商品名称

查询分析

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

select 下单用户名,sum(d.商品价格*b.商品数量)

from 订单表 a join 订单商品关联表 b on a.订单编号 &＃61; b.订单编号

join 商品分类关联表 c on c.商品名称 &＃61; b.商品名称

and c.分类名称 &＃61; b.订单商品分类

join 商品信息表 d on d.商品名称 &＃61; c.商品名称

group by 下单用户名

表结构分析

符合范式话要求

关联表结构过多

商品价格变动时&＃xff0c;无法查看到当时的价格

不能得到良好的SQL查询功能

反范式话设计

反范式化是针对范式化而言的&＃xff0c;在前面介绍了数据库设计的范式&＃xff0c;所谓的反范式化就是为了性能和读取效率的考虑&＃xff0c;而适当的对数据库设计范式的要求进行违反&＃xff0c;而

允许存在少量的数据冗余&＃xff0c;换句话来说反范式化使用空间来换取时间

商品信息/分类信息/商品分类关系表合并

商品信息&＃xff1a;商品名称/分类名称/出版社名称/图书价格/图书描述/作者

分类信息&＃xff1a;分类名称/分类描述

订单表/订单商品关联表冗余

订单表&＃xff1a;订单编号/下单用户名称/下单日期/支付金额/物流单号/手机号/订单金额。对手机号和订单金额进行冗余。首先是这两项查询频繁。同一个订单手机号

理论上页应该一致。对于订单金额&＃xff0c;这里要保留下来&＃xff0c;使之不随着之后订单单价对变化而变化。

订单商品关联表&＃xff1a;订单编号/订单商品分类/订单商品名/商品数量/商品单价。这里同样将商品单价和商品数量进行冗余。

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

select 下单用户名,sum(订单金额)

from 订单表

group by 下单用户名

总结

不能完全按照范式化的要求进行设计

考虑以后如何使用表