数据库专题(二) ——数据库设计

数据库专题(二)——数据库设计

(原创内容,转载请注明来源,谢谢)

一、数据库设计规范——范式

数据库设计,需要遵循设计原则,最主要的设计原则是范式。范式是遵循一定规则的数据库设计原则,一共有8种范式:1NF,2NF,3NF,BCNF,4NF,5NF,DKNF,6NF。其中对数据库设计要求逐步提高,即满足2NF的数据库设计必须满足1NF。

通常数据库设计到3NF或BCNF,部分情况下还需要反范式。

1、1NF

第一范式(1NF)是数据库设计的基本要求,它要求每一个字段都具有原子性,不能再分割。

例如记录个人信息表,有id、姓名、性别等。当姓名包括中文名和英文名时,就不能单独用姓名一个字段来保存信息,而需要将姓名字段拆成中文名和英文名。

1NF是关系型数据库的基本原则,即便后面提到反范式通常也不会反1NF,否则就不是关系型数据库了。

2、2NF

第二范式要求在满足1NF的前提下,表中必须要有主键,且其他非主键的字段要完全依赖于主键。

例如学生成绩表,包括学生id、学生姓名、学校名称、学院名称、课程id、成绩。这个表的冗余信息过大,因为从逻辑上来说,知道学生id就可以知道学生姓名、学校、学院,这些都属于重复的信息。另外,如果只有这一个表,无法完全表示学校每个学院的信息,因为如果有个新学院,还没招人,则无法建立数据。另外,如果某个学院的学生id全部删除,表示这个学院目前暂时没有学生,但是在表中却没有了数据。

因此,需要拆成两表,一个表是学生id、学生姓名、课程id、成绩,另一个表是表id、学校名称、学院名称、学生id。

3、3NF

第三范式要求在满足2NF的前提下,表中的非主键字段必须直接依赖于主键,而不是通过其他非主键字段间接依赖于主键。

例如购物车表,字段包括用户id、商品id、商品名称、商品单价。则此时商品名称和商品单价是通过商品id来与用户id关联的,则属于冗余的情况,需要把表拆成用户购物表:用户id、商品id,商品表:商品id、商品名称、商品单价。

但是在很多大数据量情况下,常常会出现反3NF的情况。

4、BCNF

此范式的要求高于3NF,属于3NF之上的范式要求。在2、3NF中都只对非主键字段有要求,但是没有考虑到多个主键的情况下,主键之间的关系。BCNF是用来解决此问题的。

例如此时有一个仓库信息表,要求如下:公司有多个仓库,每个仓库仅一个管理员,每个管理员仅在一个仓库工作,每个仓库有多种商品,每种商品也可以放在多个仓库。

仓库信息表的字段是仓库名、管理员名、物品名、物品数量。因为仓库、管理员、物品都是唯一的标识,因此联合主键仓库、管理员、物品,物品的数量与仓库、管理员、物品都有关系。

现如果要新增一个仓库,还没指派管理员也还没物品,则无法新增,因为仓库、管理员、物品都是主键;如果要删除仓库的全部物品,则仓库本身和管理员信息都被删除;要修改仓库管理员的信息,需要把仓库所有的物品数据都改一遍。

以上三种情况分别称为插入异常、删除异常、更新异常。

此时,将表拆成两个表。

表1是仓库表,包含仓库id、仓库名称、管理员名称,仓库id是主键。表2是仓库物品表,包括仓库id、物品id、物品名称、物品数量。

5、文章推荐

知乎上有一篇对此描述很详细,https://www.zhihu.com/question/24696366。

二、反范式

1、背景

范式是在20世纪70-80年代提出的概念,当时的一个前提是硬盘容量少,因此除了理清数据表的关系外,还需要节约珍贵的硬盘存储空间。而在完全遵守范式的情况下,设计出来的表将没有冗余,做到最大限度的节约空间。

但是,现在硬盘容量已经很大,相比之下,瓶颈不在于此。当大量数据查询请求下,如果完全遵守范式,需要多个连表查询,对于报表等字段众多的数据表,甚至要达到数十次的连表查询,这对性能的消耗非常大,会导致查询速度缓慢。

2、基本原则

范式的设计是有其正确性,因此在非必需的情况下,还是要遵守范式的数据库设计原则,节约存储空间,且每个表的信息单一,对表的操作相对简单,逻辑清晰。

1)核心信息

核心信息必须遵守范式,如用户信息表,用户的信息在其他表里出现通常只会出现用户id,不会出现其他信息,此为对用户信息的保密。且保证信息的完全一致性。

2)弱一致性需求

当对数据完整性要求不大时,为了保证查询速度的前提下,可以反范式。反范式造成的冗余,如果在历史数据表中,且这些数据不常使用,则可以定时去更新,不必要实时更新。

实际上冗余表就是拿空间换取时间的操作,冗余表一般也要遵守1NF和2NF。

三、数据库分表

分表有水平分表和垂直分表两种。

1、水平分表

当表的数据量非常大时,通常会用到水平分表。

例如交易信息表,其数据量巨大时,可以将表水平拆分。交易信息的拆分通常是通过日期,将经常查询的当月信息放在一个表中,本年每个季度的信息存在季度的表中,三年内的其余信息存在一个表中,剩余的历史数据则放在历史表中。这种历史表通常放在磁带库中,仅作备份使用,通常也不支持用户查询。

再例如,用户信息表,用户量大的情况下,可以将表拆成10个表,通过用户id除以10取余数,按照余数0-9分别将用户信息放在9个表中。

2、垂直分表

垂直分表通常是因为表的字段众多,且有几个字段是大字段(如text类型)。则此时对表的增删改查的时候,如果那些大字段和其他字段不用同时出现时,可以将大字段专门放到一个表里。此时即本可以放在一个表中的数据放到了两个表,不太符合设计原理,但是却保证大数据量下的效率与稳定性。

四、数据库分区

数据库的数据是存放在文件中,以此来保存在硬盘里。数据库的表的分区,可以理解为将逻辑上是在一个表的数据,在物理层面上存放在不同的文件中。数据库引擎在收到分区命令后,在存放数据时,会根据用户定义的分区规则,自动将数据按照规则存放在不同的文件内。在用户读取数据时,会根据用户读取的条件(如where条件),去不同的文件里面找相应的信息。

Mysql的分区有Range、List、Hash、Key、复合分区。复合分区即对前面几种分区方式生成的子分区,再次使用前面几种分区方式的任一种进行二次分区。

1、Range:每个分区包含那些分区表达式的值位于一个给定的连续区间内的行

订单信息表,按年进行分区时,如下。

createtable range(
id int(11),
moneyint(11) unsigned not null,
datedatetime
)partition by range(year(date))(
partitionp2017 values less than (2018),
partitionp2016 values less than (2017),
partitionp2015 values less than (2016)
partitionpother values less than (2015)
);

2、List:每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值

用户信息表,根据id和4的取余,将用户分在两个表中

createtable user(
id int(11),
name int(11)
)(
partition by list (id%4)
partitionp0 values in (1,3),
partitionp1 values in (0,2)
     );

上面的这个创建list分区时,如果有主銉的话,分区时主键必须在其中,不然就会报错。

3、Hash:主要用来确保数据在预先确定数目的分区中平均分布,基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。

下面用年的字段作为哈希,分割成4个表。

createtable hash(
a int(11),
b datetime
)(
partition by hash (YEAR(b))
partitions 4;
)

4、Key:类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MySQL 服务器提供。

createtable t_key(
a int(11),
b datetime
)(
partition by key (b)
partitions 4;
)

5、分区的维护

删除:

ALTER TABLE table_nameDROP PARTITION partition_name;

新增:

ALTER TABLE table_nameADD PARTITION (PARTITION partition_name VALUES partition_rule);

合并:

ALTER TABLE table_name
REORGANIZE PARTITION partition_name1, partition_name2, partition_name3,
INTO
(
PARTITION partition_name1 VALUES partition_rule,
PARTITION partition_name2 VALUES partition_rule,
PARTITION partition_name3 VALUES partition_rule
);

——written by linhxx 2017.07.29

原文发布于微信公众号 - 决胜机器学习(phpthinker)

原文发表时间:2017-07-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

最近的几个技术问题总结和答疑(九)(r10笔记第16天)

最近的琐事比较多,而提问题的朋友还是不少,很多消息都没有来得及回复,各种事情一堆起来,不少问题想起来已经过了好几天了,所以还是来整理一篇技术问答为好。 ...

3264
来自专栏编程之路

学校报修管理系统设计

需求分析:学校报修类别有两种,常规报修和电教报修。教师提交报修填写报修单后,自动派单到维修人员,维修人员接单维修,维修后结束报修单,报修教师对维修人员进行评价。...

1253
来自专栏数据和云

经典文档:Oracle Database 12.2新特性概览解读下载

在2017 OOW大会上,关于Oracle Database 12.2 数据库的新特性介绍仍然引人瞩目,会后公布了 Oracle VP Swonger的文档,我...

2957
来自专栏杨建荣的学习笔记

自动化平台开发小结(四)

今天对备份恢复和元数据的功能点进行了改进,突然发现需要做的事情远比想象的要多。 技术方面,目前Django的框架使用开始有一些需求的瓶颈了,因为有些需求从业务的...

3385
来自专栏杨建荣的学习笔记

一个复杂数据需求的MySQL方案

前些天处理了一个需求,当时的数据库环境是Oracle,我算是想尽了Oracle相关的方案,而且在问题的处理过程中,还在不断的琢磨,如果失败了还有什么其他的...

2878
来自专栏架构师之路

巧用CAS解决数据一致性问题

缘起:在高并发的分布式环境下,对于数据的查询与修改容易引发一致性问题,本文将分享一种非常简单但有效的优化方法。 一、业务场景 业务场景为,购买商品的过程要对余额...

3027
来自专栏编程之路

学校报修管理系统设计

需求分析:学校报修类别有两种,常规报修和电教报修。教师提交报修填写报修单后,自动派单到维修人员,维修人员接单维修,维修后结束报修单,报修教师对维修人员进行评价。...

1486
来自专栏SAP最佳业务实践

SAP最佳业务实践:使用看板的生产制造(233)-2生产计划

image.png 一、创建计划独立需求和物料需求计划匿名预测和物料需求计划 (145) 计划独立需求用于执行需求管理功能。计划独立需求包含一个计划数量和日期,...

3575
来自专栏13blog.site

Spring+SpringMVC+MyBatis+easyUI整合进阶篇(八)线上Mysql数据库崩溃事故的原因和处理

前文提要 承接前文《一次线上Mysql数据库崩溃事故的记录》,在文章中讲到了一次线上数据库崩溃的事件记录,建议两篇文章结合在一起看,不至于摸不着头脑。 由于时间...

3288
来自专栏后端之路

Mysql count(*) 大表优化

背景 目前数据稽计逻辑报表库存在大量数据 单表已经突破千万级。 在innodb引擎上目前count(*) 是通过实时查询 因此耗时比较多 比如 select ...

2847

扫码关注云+社区