如何设计表结构

在工作中不可避免的就要针对新需求进行表结构设计, 那应该将表结构设计成什么样, 又该依据什么准则设计呢?

带着这些问题, 一起看下如何进行表结构设计.

表结构目的

我们应该带着什么样的目标, 或者说设计成什么样才算是合理的设计呢?

主要有两点:

1.减少数据冗余, 节约数据存储空间, 提高sql执行效率;

这个目的非常好理解, 数据越少, 存储空间也就越少, 能存储的数据也就越多.

在满足同样的业务需求时, 效率也就越高.

2. 尽量避免数据维护时出现插入, 更新和删除异常;

插入异常: 表中的实体随另外的实体存在而存在;

以下表为例

选课表:{学号, 学生姓名, 课程号, 课程名称, 学分}
主键:{学号, 课程号}

在增加课程时, 会因为没有学号对应信息出现异常, 这个异常就是插入异常.

更新异常:更改表中单独属性时, 会更新表中的多行

同样以选课表为例

在修改语文学分时, 会影响多行数据, 就被称为更新异常.

删除异常: 如果删除某个实体,会导致其他实体的消失

同样以选课表为例

删除语文课程, 导致相关的学生信息也一同被删掉, 这时称为删除异常.

好的设计是要尽量避免这些数据维护异常; 今天就一起看下, 如何做好表设计.

表结构设计步骤

知道了设计目标之后, 在一起看下, 如何才能达到这个目标.

主要步骤有如下四点:

1.需求分析

反复沟通充分了解需求, 设计高效的数据库模型.

存储需求: 存储什么样数据, 数据特点;

数据处理需求: 如何读取, 更新, 批量处理等;

数据的生命周期等;

2.表设计

数据实体的逻辑关系, 解决数据冗余以及数据维护异常问题.

3.物理设计

选择合适的数据库, 存储引擎以及数据字段类型.

例如库类型可以分为关系型数据库与非关系型数据库两种,

关系型数据库: mysql, oracle

非关系型数据库: mongoDB, hbase

4.根据实际情况对索引, 存储结构进行优化

由于篇幅原因, 本文主要说明下如何进行合理的表设计, 物理设计以及索引优化后续会再详细讲解.

表设计

如何才能做好表设计呢, 有什么设计依据呢? 通常会参考数据库范式进行设计.

首先数据库设计范式是为了设计出没有冗余以及数据维护异常的数据库结构. 通常从严格要求程度分为三个级别, 也叫三范式.(实际上范式种类很多)

第一范式(1NF): 列不可再分

表中所有属性都不能在分解为更基本的数据单位时, 称为第一范式. 它是表设计的最低要求.

例如图书信息表

图书信息表: {书号, 书名, 出版社ID, 出版社名称, 出版社地址, 作者姓名, 作者年龄, 作者地址}

可以发现表有如下特点:

(1) 表中所有字段都是单一属性;

(2) 每一列都是基本数据类型;

(3) 设计出来的表是二维表结构;

第二范式(2NF): 属性完全依赖于主键

在第一范式的基础上, 所有非主属性都完全依赖于主键属性时, 称为第二范式.

这里主要解决复合主键的问题, 不能存在列依赖部分主键的情况.

图书信息表

图书信息表: {书号, 书名, 出版社ID, 出版社名称, 出版社地址, 作者名称}
主键: {书号, 出版社ID}

那作者信息是只和图书有关系, 和出版社信息是没有关系的. 所以我们重新设计下

图书信息表: {书号, 书名, 出版社ID, 作者名称, 作者年龄, 作者地址}
主键: {书号}
出版社信息表: {出版社ID, 出版社名称, 出版社地址}

第三范式(3NF): 属性直接依赖于主键

在2NF基础上, 属性不依赖于其它非主属性, 属性直接依赖于主键.

上述例子中, 作者地址等信息并不直接依赖图书信息, 移除掉.

按3NF整理后, 表结构如下.

图书信息表: {书号, 书名, 出版社ID, 作者ID }
出版社信息表: {出版社ID, 出版社名称, 出版社地址}
作者信息表: {作者ID , 作者姓名, 作者年龄, 作者地址}

三大范式只是设计数据库的基本理念, 可以建立冗余较小, 结构合理的数据库.

但是, 通常我们展示图书信息时, 是会将作者姓名和出版社名称也一起展示的, 这时查询时就需要使用多表的联合查询.

select  b.书号, b.书名, c.出版社名称, z.作者姓名
from 图书信息b
left join 出版社信息c on b.出版社ID = c.出版社ID
left join 作者信息z on z.作者ID=b.作者ID

联合查询需要MySQL从多个表中加载数据,会产生大量随机IO, 无疑是加重了mysql负担, 降低了性能.

那有更好的设计方式么?

反范式

为了提高运行效率, 就必须降低范式标准, 适当保留冗余数据.

降低范式标准的工作放到物理数据模型设计时考虑. 降低范式就是增加字段, 减少了查询时的关联, 提高查询效率.

所以上述图书信息表中将出版社名称和作者姓名冗余进来,

冗余后的图书信息表

图书信息表: {书号, 书名, 出版社ID, 出版社名称, 作者ID , 作者姓名}

这时再进行查询时, 是单表查询, 顺序IO, 性能就会高很多了.

这里是不是会产生疑问, 那都使用1NF不就可以了么?

其实并不是, 例如出版社地址信息, 业务上就不是很关心, 就不需要也冗余到图书表中.

就可以减少表的列宽度, 提高mysql每页读取的数据量, 降低IO次数, 提高性能.

再假设一种场景, 业务上合作的出版社很少, 可能就十几个, 这时上面的设计又可以怎样设计优化呢?

这里可以去掉出版社名称列,

图书信息表

图书信息表: {书号, 书名, 出版社ID, 作者ID , 作者姓名}

可以将出版社信息缓存到内存中, 在读取图书信息后, 在程序内存中拼接出版社名称信息.

这样图书信息表的列长度又变短, 无形中又提高了系统性能.

范式化设计可以尽可能减少冗余, 因为表更小, 所以更新也会更快. 但业务上需要多表联合查询时, 因为随机IO, 又会降低读性能. 同时也很难用索引优化.

反范式设计正好是弥补了范式化设计的缺点, 有更好的读性能, 容易用索引优化, 同时提高了写成本.

正如上面所举列子, 要根据实际业务情况才能设计出合理的表结构, 一味的追求范式和反范式都是不合理的. 同样, 通常会被忽略的需求分析也就变的重要起来了.