数据库物理设计


数据库设计
数据库逻辑设计
数据库物理设计
数据库维护和优化

物理设计要做什么

  1. 选择合适的数据库管理系统
  2. 定义数据库、表及字段的命名规范。
  3. 根据所选的DBMS系统选择合适的字段类型。
  4. 反范式化
    指的是在逻辑设计中已经确立好的非常规范的数据库结构模型,模型没有任何数据冗余,那么在物理设计中可能会为了读效率的考虑会在表中增加一些冗余来达到效率的提升,换句话说就是用空间换时间
    选择合适的DBMS系统

选择合适的DBMS系统


首先来说要考虑成本问题,对于Oracle和SQLServer来说它是属于商业数据库,这就不得不考虑版权的问题,从SQLServer2012之后就使用了和Oracle相同的基于服务器的核数来进行收费的策略,由于我们目前服务器的核数都比较多,在这种情况下我们使用商业数据库我们的成本是不得不去考虑的一个问题。
而MYSQL和PGSQL是常见的开源数据库,而对于开源数据库来说,只要我们在使用的时候是符合开源协议的,那么就不需要支付版权费用。
除了版权之外,我们还要对功能进行考虑,对于Oracle来说是属于业界口碑比较好的数据库系统,性能是非常高的,比较适合比较大的事物操作,因为在Oracle中它的事务成本是非常低的。
除了功能上我们使用的操作系统上也可以进行选择,像SQRServer数据库因为是微软的产品,所以它是只支持windows上面运行的。而Oracle、MYsql和Pgsql呢是可以同时运行在windows下和linux下的。
除了这些我们还要考虑开发使用的语言,如果在开发中使用的是.net这种语言,那么SQLServer对.net配合是更好的选择。
之后和还要考虑应用场景,对于目前来说mysql和pgsql这种开源数据库是比较常用于互联网项目,而Oracle和SQLServer更适用于企业级项目,比如说一些金融类的公司可能会更喜欢Oracle,因为它相对来说扩展比较容易,也相对也更加安全。SQLServer更倾向于一些中小企业的企业级数据库,比如说一些ERP系统。

Mysql常用的存储引擎

由于MySQL是一种开源型的数据库,所以它的存储也非常开放,只要符合mysql的存储协议,那么任何场上都可以实现自己的存储引擎。

mysql的常用存储引擎:

MyISAM是MySQL5.5以前默认使用的存储引擎,这种存储引擎的特点是不支持事务,但是他的读写效率相对来说更高,由于它不支持事务,所以它对一些事务处理上的开销也就更少,这些是它读写比较高效的原因,但是由于它不支持事务,这也面临着它在写的时候要对全表进行加锁,也就是说写并发非常大就有可能造成锁阻塞,所以它的使用场景是如果写很少读很多的时候可以使用这种引擎

MRG_MyISAMD这种存储引擎是和MyISAM存储引擎有一定的关系的,它是可以把多个结构相同的MyISAM表合并成一个表进行处理,这比较像视图或者分区的一种功能,但是由于是基于MyISAM的所以同样不支持事务,并且不再支持行级锁存储引擎。主要应用于分段归档和数据仓库这种需要把多个表变成一个表进行逻辑处理的可以使用这种存储引擎,因为它的写相对来说是非常少的,可能是基于某一时间去写,但大多数场景都是在读。并且这种存储引擎也不适用于那种全局查找过多的场景,和分区表一样如果全局查找太多就涉及到要在在多个分区中或者表中浸信会个扫描,这样效率会更低,如果全局查找太多也不适用于这种存储引擎

innodb是mysql5.5之后mysql默认使用的存储引擎,也是目前在大多数互联网应用中建立大家使用的引擎,首先这种引擎支持事务,并且支持多版本并法的行级锁,也就是说比较类似与Oracle这种事务特点,主要应用场景就是需要用到事务处理的场景,并且禁用场景目前没有,也就是大多数场景都可以用innodb引擎,由于是行级锁所以读写也是非常高效的,和MyISAM不同点是它不会有表级锁的存在,基本上都是对于行级锁进行处理,因此阻塞会更少

Archive的特点是也是行级锁,但是它支持insert和select操作,不支持update操作,这种存储引擎更倾向于日志的这种场景,因为日志只需要添加而并不需要更新,这种时候就可以使用在这种存储引擎,这种存储引擎的特点是它的存储需要的容量相对来说更小,也就是说innodb来进行存储新需要100m,而Archive可能只需要几十m或者十几m空间就能完成相同数量级的存储

Ndb cluster这种存储引擎是mysql集群所使用的存储引擎,这种存储引擎的特点是,首先支持事务,并且也是行级锁,主要应用场景是要使用mysql集群的情况下使用NDB存储引擎,由于mysql集群是内存型的一种集群,大部分数据要放到内存中,所以在大多数情况下如果我们数据量比较大超过内存的大小,这种情况下就不太适用于NDB集群,也就不可能再使用NDB集群的存储引擎

数据库表及字段的命名规范

所有对象命名应该遵循下述规则:
1)可读性原则
使用大写和小写来格式化的库对象名字以获得良好的可读性。
例如:使用CustAddress而不是custaddress来提高可读性。(这里要注意有些DBMS系统对表名的大小写是敏感的)

2)表意性原则
对象的名字应该能够描述它所标识的对象。
例如,对于表,表的名称应该能够体现表中存储的数据内容;对于存储过程,存储过程名称应该能够体现存储过程的功能。

3)长名原则
尽可能少使用或者不使用缩写,适用于数据库(DATABASE)名之外的任一对象。

字段类型的选择原则


比如生日这个字段至少可以使用四种类型进行存储
第一种char类型,1978-03-01大概需要十个字节就够了
第二种使用varchar变长字段来进行存储,就varchar存储的好处是可以存1978-03-01 或者 19780301 都是可以的,因为字符串长度不一样所使用的存储空间也是不一样的
第三种最常见的是使用日期时间类型来进行存储
最后一种是使用unix时间辍来进行存储,unix时间辍是一个整型,数字代表的意思也是978-03-01,它是自1971-01-01 00:00:00 到现在的秒数

列的数据类型一方面影响数据存储空间的开销,另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时,应该优先考虑数字类型,其次是日期或二进制类型,最后是字符串类型。对于相同级别的数据类型,应该优先选择则占用空间小的数据类型。

比如上面例子就应该优先选择int类型其次是datetime在其次是char和varchar

以mysql为例,各数据类型占用的存储空间大小:

以上选择原则在主要是从下面两个角度考虑:
1) 在对数据进行比较(查询条件、JSON条件及排序)操作时:同样的数据,字符处理往往比数字处理慢。
这是因为字符串类型的处理是与我们选择的排序规则有关的,也就是说它要去查询我们当前所使用排序规则的字典顺序来确定字符串的比较和排序顺序,而数字的二进制类型不需要参考这种数据字典,因此在进行数据的排序和比较过程中使用二进制或者int类型的效率要高于字符串类型。

2)在数据库中,数据处理以页为单位,列的长度越小,利于性能提升。
因为不同的关系型数据库页的大小是不一样的,如SQLServer是8k一页,而mysql在innodb中默认是16k字节一页,而列的长度越小,我们在一个页中所能存储的数据量就越多,这样再加载相同的数据的时候如果我们的列越小我们加载的页数越小,这样IO的性能就会提高,现在数据库最大瓶颈并不是CPU,而是磁盘IO瓶颈,也就是说如果我们优化了IO瓶颈就是优化了我们数据库的性能

char与varchar如何选择

原则:
1)如果列中要存储的数据长度差不多是一致的,则应该考虑用char;否则应该考虑用varchar。

2)如果列中的最大数据长度小于50Byte,则一般也考虑用char。
(当然,如果这个列很少用,则基于节省空间和减少IO的考虑,还是可以选择用varchar)

3)一般不宜定义大于50Byte的char类型列。

例如身份证号或者说电话这种字符串,它的长度基本上都是一致的,那么这个时候就可以选择char来进行存储,而不是varchar。如果内容最大的数据长度小于50个字节也一般可以使用char来存储,因为每一个varchar的列除了存储数据所需要的数据长度之外还需要额外的在字节来存储变长数据的字典,而在检索数据的时候也要确定数据存取的起始位置,所以长度太小就不适合用varchar来进行存储。
如果我们所存储的这个数据是大于50个字节的那么就最好使用varchar,在mysql中不同的字符编码占用的字节是不一样的,比如UTF-8每个字符占用三个字节,也就是说50/3,也就是字符串大于15个字符就要考虑使用varchar而不是char

decimal和float如何选择

原则:

1) decimal用于存储精确数据,而float只能用于存储非精准数据。故精确数据只能选择decimal类型。

2) 由于float的存储空间开销一般比decimal小(精确到7位小数只需要4个字节,而精确到15位小数只需要8字节)故非精确数据优先选择float类型

时间类型如何存储

1)使用int类存储时间字段的优缺点
优点:字段长度比datetime小
缺点:使用不方便,要进行函数转换。
限制:只能存储2038-1-19 11:14:07及2^32为2147483648

如果存储订单的日期那么最好使用datetime,因为要时常要进行时间范围判断查询来判断订单是否到期

2)需要存储的时间粒度
年 月 日 小时 分 秒 周

比如之存储到年那么可以用year,year之战用一个字节

如何选择主键
1)区分业务主键和数据库主键
业务主键用于标识业务数据,进行表与表之间的关联;
数据库主键为了优化数据库存储(innodb会生成6个字节的隐含主键)

一些情况下业务主键和数据库主键是相同的,但是在一些情况下业务主键和数据库主键又是不同的,为什么要定义主键呢?因为在一些数据库的表中,比如innodb是要求每个表中必须要有一个主键的,因为它是按照主键的顺序进行逻辑存储的,如果没有主键的话innodb会优先选择具有所有非空列的唯一索引来进行主键,因为没有定义主键唯一索引的话innodb会生成6个字节的隐含列来作为主键,所以使用innodb的话最好人为的去定义一些主键,而不要使用隐含的主键

2)根据数据库的类型,考虑主键是否要顺序增长
有些数据库是按主键的顺序逻辑存储的

因为innodb是按照主键的逻辑来进行存储,那么最好是需要主键是可以顺序增长的,这样的话它就不会进行数据的逻辑迁移,对我们的IO很有好处,

3)主键的字段类型所占空间要尽可能的小
对于使用聚集索引方式存储的表,每个索引后都会附加主键信息。

另外因为数据库都是按页来存储数据的,那么主键也要尽可能的小,因为主键越小页中所存储的主键的数量就越多,这样在查询的时候就会装载更少的数据,这样也能对IO性能带来一些好处

避免使用外键约束

1)降低数据导入的效率

2)增加维护的成本

3)虽然不建议使用外键约束,但是相关联的列上一定要建立索引

有时经常会听到说外键是用来保持数据完整性的一种方式,但是在这种高并法的互联网网站中如果使用外键会给我们带来一些负面的影响。首先对于数据的写入操作的时候,如果我们使用了外键,那么每写入一条数据都会去查询是否符合外键约束,如果符合才能插入进数据如果不符合就会被拒绝掉,那么检查这个外键约束是否符合的过程是十分的耗时的,可能一般情况下感觉不到,但是在高并法的情况下我们就能深有体会。所以一般建议上在高并法的互联网企业最好不要使用外键约束来进行数据库一致性的保证。但是不使用外键并不意味着我们并不会在外键上建立索引,因为所谓的外键约束它是一种约束,而索引是为了提高查询效率而存在的,就是如果我们进行表关联,这样虽然我们没有建立外键约束,但是我们同样要在关联键上建立索引。

避免使用触发器

1)降低数据导入的效率。

2)可能会出现意想不到的数据异样。

3)使用业务逻辑变得复杂。

关于预留字段

1) 无法准确的知道预留字段的类型。

2) 无法准确的知道预留字段中所存储的内容。

3) 后期维护预留字段所要的成本,同增加一个字段所需要的成本是相同的。

4) 严禁使用预留字段。

预留字段时你不可能具体字段的类型,并且字段名不会见名思意特别准确,另外后期给列重命名,对于数据库来说就是等于新建了一个列

什么是反范式化

反范式化是针对范式化而言的,在在前面介绍了数据库设计的第三范式,所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反,而允许存在少量的数据冗余,换句话说反范式化就是使用空间来换取时间。

例子:

符合范式化的设计:

如果要查询订单信息:

这条sql关联了三张表并且还进行了sum操作才汇总出各项订单信息。
可以看出表关联还是非常多的,并且进行了汇总操作所以效率也不会太高

如果要查出订单详情:

反范式化操作:

这样虽然增加了一部分冗余数据但是查询变得更加简单明了并且查询效率更高,只需要订单表和商品表就能查出原来需要四张表的订单详情

如果要查询订单信息就更简单了:

在写入的时候多占用了一些空间,但是在读取的时候可以提效率。在互联网项目中读写比例一般是三比一或者四比一的关系,所以读远远高于写,所以如果写的时候增加了一些数据冗余但是能大大提高读的效率的话还是比较化算的

为什么反范式化

1)减少表的关联数量
2)增加数据的读取效率
3)反范式化一定要适度


文章作者: 拾年
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 拾年 !
评论
 上一篇
数据库维护和优化 数据库维护和优化
数据库设计数据库逻辑设计数据库物理设计数据库维护和优化 由于项目中需求是不断变化的,所以数据库中的结构也是需要不断的做相应的改变 维护和优化中要做什么1)维护数据字典 数据字典对于应用是否便于维护是非常重要的,如果我们不清楚表中每一个列或
2020-05-11
下一篇 
数据库逻辑设计 数据库逻辑设计
数据库设计数据库逻辑设计数据库物理设计数据库维护和优化 逻辑设计是做什么的 将需求转化为数据库的逻辑模型 通过ER图的形式对逻辑模型进行展示 同所选用的具体的DBMS系统无关 所谓的逻辑设计就是根据需求分析之所了解到的应用中所需要
2020-05-09
  目录