数据库设计那些事

什么是数据库设计?

数据库设计就像建设大楼的过程,整体结构设计-图纸-施工。
数据库设计就是根据业务系统的具体需要,结合需用的DBMS数据库管理系统,为这个业务构造出最优的数据存储模型,并建立好数据库中表的结构以及表与表之间的关联关系的过程。使之能有效的对应用系统中的数据进行存储,并可以高效的对已经存储的数据进行访问。

为什么要数据库设计?

减少数据冗余,避免数据维护异常,节约存储空间,高效的访问。

数据库设计步骤

数据库设计步骤:需求分析→逻辑设计→物理设计→维护优化

数据库需求分析

数据是什么?数据有哪些属性?数据和属性各自的特点都有什么?
需求分析了解的问题
1.了解所要存储的数据
2.数据的存储特点
3.数据的生命周期
时效性比较短的数据要定期清理或者归档。
增长速度快、量大的非核心数据先定好数据库的归档和清理规则。
要搞清楚的问题:
1.实体与实体之间的关系 1对1 1对多 多对多
2.实体所包含的属性
3.哪些属性或属性的组合可以唯一标识一个实体
实体与数据库中的表的关系:可能一个实体对应一个表,也可能多个实体对应一个表。

逻辑设计

ER图工具对数据库进行逻辑建模
需求转换为数据库的逻辑模型
ER图对逻辑模型进行展示(与所选DBMS无关)。
操作异常:插入异常,删除异常,更新异常
插入异常:某实体随另一个实体的存在而存在,缺少某个实体时无法表示这个实体。
更新异常:更改表锁定所对应的某个实体的单独属性时,需要多行更新。
删除异常:删除表的某一行来反映实体实例失效时,导致另一个实体实例信息丢失。
数据冗余:指相同的数据在多个地方存在,或者说表中的某个列可以由其它列计算得到。数据冗余容易造成数据表维护的困难。
数据库设计范式:第一范式、第二范式、第三范式以及BC范式。
第一范式:数据库表中的所有字段都是单一属性,单一属性数据类型是基本数据类型。即第一范式要求数据库中的表都是二维表
第二范式:在第一范式基础上,非关键字段不能对候选关键字段部分函数依赖(组合关键字中的某一关键字决定非关键字),即所有单关键字段都符合第二范式。
第三范式:在第二范式基础上定义的,非关键字段不能对任意候选关键字段有传递依赖。
BC范式:复合关键字之间不能存在函数依赖关系。

物理设计

考虑使用的数据库管理系统DBMS是什么?根据选择的DBMS特点把逻辑设计转化成物理设计。

选择合适的DBMS
定义数据库、表及字段的命名规范

所有对象命名应该遵循的原则:
(1)可读性原则。使用大写和小写来格式化库对象名字。
(2)表意性原则。对象的名字能够描述所标识的对象。
(3)长名原则。尽可能少使用和不使用缩写。

根据选择的DBMS选择合适的字段类型

字段类型选择原则:列的数据类型一方面影响数据存储的空间开销,另一方面也影响数据查询的性能。当一个列可以选择多种数据类型时,应该优先考虑数字类型,其次是日期或者二进制类型,最后是字符类型。对于相同级别的数据类型,应该优先选择占用空间小的数据类型。
列类型 存储空间
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT 4字节
BIGINT 8字节
DATE 3字节
DATETIME 8字节
TIMESTAMP 4字节
CHAR(M) M字节,1=<M<=255
VARCHAR(M) L+1字节,L<=M,1=<M<=255
以上规则从两个角度考虑:
(1)在对数据进行比较(查询条件、join、排序)时,同样的数据字符处理比数字处理要慢。
(2)在数据库中,数据处理以页为单位,列长度越小,每页存储的数据越多,页数越少,有利于性能提升。
Char 和Varchar如何选择:
(1)如果列中存储的数据长度差不多一直,应该采用char,否则考虑varchar
(2)如果列中最大数据长度小于50Byte,一般考虑char。如果该列很少用,基于节省空间和较少I/O考虑还是可以选用varchar的。
(3)一般不宜定义大于50Byte的char类型。utf8每个字符占3个字节,15个字符以上用varchar。

反范式化设计

数据表中增加冗余,达到效率的提升,以空间换时间。为了性能和读取效率的考虑,适当对第三范式的要求进行违反,允许存在少量的数据冗余。换句话说,反范式化就是用空间来换时间。好处是减少了表关联的数量,增加数据读取的效率,但反范式化一定要适度。

decimal和float如何选择:

(1)decimal用于存储精确数据,float用于存储非精确数据。
(2)由于float的存储空间开销小于decimal,故非精确数据有限选择float类型。

时间类型如何存储:

如果很少被使用,使用INT类存储;否则经常查询使用的话,使用datetime类型来存储。

如何选择主键?

区分也业务主键和数据库主键。业务主键用于标识业务数据,进行表与表之间的关联。数据库主键为了优化数据存储。一般情况下,业务主键与数据库主键相同,有时也不同。INNODB引擎的数据库要求每个表都有一个主键,按照主键的顺序进行逻辑存储,没有主键会自动生成6个字节的隐含主键,最好主键是顺序增长的。主键的字段类型所占的空间尽可能小,每页存储的主键越多,对性能越好。
高并发的互联网网站避免使用外键,触发器以及预留字段。相关联的列上一定要建立索引。

维护优化

根据新的需求进行建表,索引优化以及大表拆分。优化工作中继续按照上述三步进行优化设计、建表工作。
(1)维护数据字典 加comment注释,然后命令导出数据字典.
(2)维护索引。如何选择合适的列建立索引?出现在WHERE从句、GROUP BY、ORDER BY从句中的列。可选择性高的列放在索引前面。索引中不要包括太长的数据类型。MySQL支持前置性索引的,对Text类型数据的前16个字节索引。注意:索引不是越多越好,过多的索引不但降低写的效率而且会降低读的效率;要定期维护索引碎片;在SQL语句中不要使用强制索引关键字。
(3)维护表结构。同时对数据字典进行维护并控制表的宽度的大小(数据表字段过大考虑垂直拆分)。数据库中适合批量操作,在数据库查询时少使用SELECT * 语句,控制用户使用自定义函数,不要使用数据库中的全文索引,最好使用搜索引擎工具来代替完成。
(4)在适当时候对表进行水平拆分或者垂直拆分。经常一起查询的列放在一起,text、blob等大字段拆分出都附加表中。如果一张大表数据量达到上亿行,可能需要水平拆分(把一张表分成多份),通过主键Hash方式进行。