数据库结构的优化

最新推荐文章于 2020-04-22 20:55:20 发布

weixin_34166847

最新推荐文章于 2020-04-22 20:55:20 发布

阅读量104

点赞数

文章标签：数据库

原文链接：https://juejin.im/post/5a02c2a36fb9a04525778b87

版权

数据库设计的步骤：

需求分析：全面了解产品设计的存储需求
逻辑设计：设计数据的逻辑存储结构
物理设计：根据所使用的数据库特点进行表结构的设计
维护优化：根据实际的情况对索引、存储引擎的优化 ###数据库的范式：
第一范式：数据库中的所有字段都只是具有单一的属性
第二范式：在第一范式的条件下，要求一个表中只具有一个业务主键，每个表只做一件事情
第三范式：在第二范式基础上，消除表中的传递依赖关系

一、需求分析及逻辑设计

用户模块

用户必须注册并等咯系统才能进行网上的交易
同一时间一个用户只能在一个地方登陆
用户的信息:{用户名，密码，手机号，姓名，注册日期，在线状态，出生日期}

商品模块

商品信息:{商品名称，出版社名称，图书的价格，图书描述，作者}
分类信息：{分类名称，分类描述}
商品分类信息（对应关系表）：{商品名称，分类名称}

供应商模块

供应商的信息：{出版社名称，地址，电话，联系人，银行账号}

在线销售模块

在线销售所需要的数据：{订单编号，下单用户名，下单日期，订单金额，订单商品分类，订单商品名，订单商品的单价，订单商品数量，支付金额，物流单号}
订单表：{订单编号，下单用户名，下单日期，支付金额，物流单号}
订单商品的关联表：{订单编号，订单商品分类，订单商品名，商品数量}

考虑性能的问题及商品价格发生变化：

商品信息表的反范式化设计：

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

分类信息：{分类名称，分类描述}
在线销售表的反范式化设计：

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

订单商品的关联表：{订单编号，订单商品分类，订单商品名，商品数量，商品单价}
反范式化后的查询每个用户的订单的总金额的SQL语句：

select 下单用户名,sum(订单金额) from 订单表 group by 下单用户名；
反范式化后的查询下单用户和订单详情的SQL语句：

select a.订单编号，a.用户名，a.手机号，b.商品名称，b.商品的单价，b.商品数量 from 订单表 a join 订单商品的关联表 b on a.订单编号=b.订单编号； ###范式化的优缺点

范式化的优点：

可以尽量的减少冗余数据
范式的更新操作比反范式化要快
范式化的表通常比反范式化的表更小

范式化的缺点：

对查询需要对多个表进行关联
更难进行索引优化 ###反范式化的优缺点

反范式化的优点：

可以很好的减少表的关联
可以对查询进行索引优化

反范式化的缺点：

存在数据冗余及数据维护异常
对数据的修改需要更多的成本

二、数据库的物理设计阶段

定义数据库、表及字段的命名规范（可读性原则、表意性原则、长名原则）
选择合适的存储引擎

为表中的字段选择合适的数据类型（当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期类型或者二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型）

整数类型：

  tinyint(1个字节)、smallint(2个字节)、
  mediumint(3个字节)、int(4个字节)、bigint(8个字节)
复制代码

实数类型：

  float(4个字节，不为精确类型)、double(8个字节，不为精确类型)、
  decimal(每4个字节存9个数字，小数点占一个字节，为精确类型)
复制代码

varchar和char类型：

  varchar类型存储特点：
  	用于存储变长字符串，只占用必要的存储空间；
  	列的最大长度小于255时则只占用一个额外字节用于记录字符串长度；
  	列的最大长度大于255则，要占用两个额外字节用于记录字符串长度

  如何对varchar列选择合适的宽度：

  	使用最小的符合需求的长度；
  	varchar(5)和varchar(200)存储MySQL字符串性能不同

  varchar的适用场景：

  	字符串列的最大长度比平均长度大很多；
  	字符串列很少被更新的字符串的列；
  	使用了多字符集存储字符串

  char类型存储特点：

  	char类型是定长的；
  	字符串存储在char类型的列中会删除末尾的空格；
  	char类型存储的最大的宽度是255

  char类型的适用的场景：

  	适合存储所长度近似的值（eg:md5的值、手机号、身份证号）
  	适合存储长度短小的字符串
  	适合存储存储经常更新的字符串
复制代码

日期类型：

  datatime类型以YYYY-MM-DD HH:MM:SS[.fraction]格式存储数据
  datatime类型与时区无关，占用8个字节存储空间
  存储的时间范围：1000-01-01 00：00:00到9999-12-31 23:59:59

  timestamp类型存储从1970年1月1日到当前的秒数，以YYYY-MM-DD HH:MM:SS[.fraction]显示，占用4个字节存储空间
  timestamp类型显示依赖于所指定的时区
  timestamp类型在行数据修改时可以自动修改timestamp列的值
  timestamp存储的时间范围1970-01-01到2038-01-19

  date类型和time类型（mysql5.7之后加入）：
  	date类型占用的字节数比使用字符串、datatime、int存储要少，使用date类型只需要3个字节；
  	date类型使用Date类型还可以利用日期时间函数进行日期之间的计算；
  	date类型存储的日期范围1000-01-01到9999-12-31之间的日期

  	time类型用于存储时间数据：HH:MM:SS
  存储日期时间类型的注意事项：
  	不要使用字符串类型来存储日期时间数据；
  		日期时间类型通常比字符串类型所占用的存储空间小；
  		日期时间类型在进行查找过滤时可以利用日期来进行对比；
  		日期时间类型有丰富的处理函数，可以方便的对时间类型的进行日期计算
  	使用Int存储日期时间不如使用Timestamp类型
复制代码

InnoDB如何选择主键：主键应该尽可能的小；主键应该是顺序增长的（以减少随机IO），增加数据的插入效率； InnoDB的主键和业务主键可以不同