阅读 1108

数据库结构的优化

数据库设计的步骤:

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

一、需求分析及逻辑设计

用户模块
  • 用户必须注册并等咯系统才能进行网上的交易
  • 同一时间一个用户只能在一个地方登陆
  • 用户的信息:{用户名,密码,手机号,姓名,注册日期,在线状态,出生日期}
商品模块
  • 商品信息:{商品名称,出版社名称,图书的价格,图书描述,作者}
  • 分类信息:{分类名称,分类描述}
  • 商品分类信息(对应关系表):{商品名称,分类名称}
供应商模块
  • 供应商的信息:{出版社名称,地址,电话,联系人,银行账号}
在线销售模块
  • 在线销售所需要的数据:{订单编号,下单用户名,下单日期,订单金额,订单商品分类,订单商品名,订单商品的单价,订单商品数量,支付金额,物流单号}
  • 订单表:{订单编号,下单用户名,下单日期,支付金额,物流单号}
  • 订单商品的关联表:{订单编号,订单商品分类,订单商品名,商品数量}
考虑性能的问题及商品价格发生变化:
  • 商品信息表的反范式化设计:

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

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

  • 在线销售表的反范式化设计:

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

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

  • 反范式化后的查询每个用户的订单的总金额的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的主键和业务主键可以不同

  • 建立数据库结构

  • 维护优化数据库

关注下面的标签,发现更多相似文章
评论