MySQL8.0实战(二) - 数据库设计

0 Github

1 简介

数据库设计(Database Design)是指对于一个给定的应用环境,构造最优的数据库模式,建立数据库及其应用系统,使之能够有效地存储数据,满足各种用户的应用需求(信息要求和处理要求)。在数据库领域内,常常把使用数据库的各类系统统称为数据库应用系统。
数据库设计的设计内容包括:需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库的实施和数据库的运行和维护。

2 数据库建模五部曲

MySQL8.0实战(二) - 数据库设计

3 需求总结

◆ 课程的属性:{主标题,副标题,方向,分类,难度最新最热,时长,简介,人数,需知,收获,讲师名讲师职位,课程图片综合评分,内容实用,简洁易懂,逻辑清晰}

◆ 课程列表的属性:{章名,小节名, 说明,小节时长,章节URL,视频格式}

◆ 讲师的属性:{讲师昵称,说明,性别,省,市,职位说明,经验,积分,关注人数,粉丝人数}

◆ 问答评论属性:{类型,标题,内容,关联章节,浏览量,发布时间,用户昵称}

◆ 笔记的属性:{用户昵称,关联章节 笔记标题,笔记内容,发布时间}。

◆ 用户的属性:{用户昵称密码,说明,性别,省,市,职位,说明,经验,积分,关注人数粉丝人数}

◆评价的属性:{用户,课程主标题,内容,综合评分,内容实用,简洁易懂,逻辑清晰,发布时间}

# 4 宽表模式

  • 百度百科定义
    从字面意义上讲就是字段比较多的数据库表。通常是指业务主题相关的指标、维度、属性关联在一起的一张数据库表。由于把不同的内容都放在同一张表存储,宽表已经不符合三范式的模型设计规范,随之带来的主要坏处就是数据的大量冗余,与之相对应的好处就是查询性能的提高与便捷。这种宽表的设计广泛应用于数据挖掘模型训练前的数据准备,通过把相关字段放在同一张表中,可以大大提高数据挖掘模型训练过程中迭代计算时的效率问题。

◆ 课程的属性:{主标题,副标题,方向,分类难度最新,最热,时长,简介,人数,需知,收获,讲师名,讲师职位,课程图片综合评分,内容实用,简洁易懂,逻辑清晰}

  • 实例
    MySQL8.0实战(二) - 数据库设计

4.1 模式存在的问题

4.1.1 更新异常

修改一行中某列的值时,同时修改了多行数据

例如当使用
MySQL8.0实战(二) - 数据库设计
想修改其职位时,不止影响一条数据
那么,我们再加个限定条件
MySQL8.0实战(二) - 数据库设计
就可以只修改一行数据,因此我们可以将主标题作为该数据表的唯一标识,即主键!
通过主键更新数据,虽然可以避免数据的更新异常,但也可能会造成表中的数据不一致现象,比如该实例中,讲师的职称就会产生多义.

4.1.2 插入异常

部分数据由于缺失主键信息而无法写入表中

例如,我们想新增Java开发方向的课程
MySQL8.0实战(二) - 数据库设计
由于执行该语句时,PK为空,即违反了PK非空且唯一的约束条件,因此该语句无法成功.

4.1.3 删除异常

删除某一数据时不得不删除另一数据

例如,我们想删除数据库方向
MySQL8.0实战(二) - 数据库设计

我们只是单纯想删除数据库方向而已,但该语句却将许多课程也删除了,这并不符合我们的预期.

4.1.4 数据冗余

相同的数据在一个表中出现了多次

那么是不是这么多问题就意味着宽表一无是处呢?存在即合理!

4.2 模式的适用场景

配合列存储的数据报表应用

由于宽表中,所有数据存在于一个表中,因此在查询时,无需多表查询,SQL执行效率较高,且存在的上述问题在报表应用中都不是大问题

既然宽表不适合我们的当前业务,那么怎么寻找合适的方法呢?

5 数据库设计范式

5.1 第一范式

表中的所有字段都是不可再分的

例如以下实例中的联系方式是一个复合属性,明显就违反了该范式,在数据库中是无法分离出来的

MySQL8.0实战(二) - 数据库设计

我们只需对其进行简单的改动即可
MySQL8.0实战(二) - 数据库设计

即标准的二维表.

5.2 第二范式

前提

标准的二维表,即第一范式成立

表中必须存在业务主键,并且非主键依赖于全部业务主键

例如如下博客表实例
MySQL8.0实战(二) - 数据库设计

  • 使用用户字段作为PK是否可行呢?
    显然一个用户会对应多个博客记录,且章节标题也能为多个用户编辑,所以单列字段PK失效
  • 使用<用户,章节,标题>的复合PK
    然而用户积分字段也只和用户字段依赖,并不依赖于整体的PK,所以依旧不符合第二范式
  • 拆分将依赖的字段单独成表
    MySQL8.0实战(二) - 数据库设计

MySQL8.0实战(二) - 数据库设计

从上面,我们也可以发现:

  • 若表的PK只有一个字段组成,那么它本就符合第二范式
  • 若是多个字段组成,则需考量是否符合第二范式

5.3 第三范式

表中的非主键列之间不能相互依赖

依旧看看课程表
MySQL8.0实战(二) - 数据库设计

首先,一个字段的PK显然符合第二范式,大部分字段也只依赖于PK,然而对于讲师职称字段其实是依赖于讲师名的,所以不符合第三范式.

  • 将不与PK形成依赖关系的字段直接提出单独成表即可
    MySQL8.0实战(二) - 数据库设计

MySQL8.0实战(二) - 数据库设计

6 课程实体的逻辑建模

属性

{主标题,副标题,方向,分类,难度,最新,最热,时长,简介,人数,需知,收获,讲师名讲师职位,课程图片综合评分,内容实用,简洁易懂,逻辑清晰}

我们显然可以将其拆分如下:

课程表

主标题(PK),副标题,方向,分类,难度,上线时间,学习人数,时长,简介,需知,收获,讲师昵称,课程图片,综合评分,内容实用,简洁易懂,逻辑清晰

讲师表

讲师名及讲师的职称

其中最新属性即对应着上线时间计算得出,业务上可规定时间段判断是否为最新
最热属性即可以学习人数字段排序来反映

课程方向表

课程方向名称(PK) : 在课程表中有对应的方向字段
添加时间

课程分类表

分类名称(PK) : 在课程表中有对应的方向字段
添加时间

课程难度表

课程难度(PK) : 在课程表中有对应的方向字段
添加时间

7 课程列表实体的逻辑建模

属性

[章节名,小节名](联合PK)
说明,小节时长,章节URL,视频格式

其中,说明其实只依赖于章节名
小节时长小节URL,视频格式都只依赖于小节名
违反第二范式,所以需要拆分字段

课程章节表

章节名(PK),说明,章节编号

课程与章节的联系表

主标题,章节名

课程小节表

小节名称(PK),小节视频url,视频格式,小节时长,小节编号

课程章节与小节的联系表

主标题,章节名,小节名

8 讲师实体的逻辑建模

属性

讲师名,密码,性别,省,市,职称,说明,经验,积分,关注数,粉丝数

讲师表

讲师名(PK),密码,性别,省,市,职称,说明,经验,积分,关注数,粉丝数

9 用户实体的逻辑建模

属性

用户昵称,密码,性别,省市,职位,说明,经验,积分,关注数,粉丝数

用户表V1.0

用户昵称(PK),密码,性别,省市,职位,说明,经验,积分,关注数,粉丝数

和讲师表基本相同,且讲师其实也是一种用户,讲师的信息就会被存储两次,造成数据的冗余.,于是就难以保持数据一致性!考虑合并!

用户表V2.0

用户昵称(PK),密码,性别,省市,职位,说明,经验,积分,关注数,粉丝数,讲师标识

10 问答评论实体的逻辑建模

属性

类型,标题,内容关联章节,浏览量,发布时间,用户昵称

其中标题文字是共享的,无法保持一致
同一用户在不同章节提出的问题也可能相同
因此决定采用标题+用户昵称+关联章节作为PK

评论表

如何记录关联章节字段呢?
是不是只能用课程章节的PK来记录呢?
因此,不得不将课程章节的关联表PK加入
MySQL8.0实战(二) - 数据库设计

[标题,课程主标题,课程章名,小节名称,用户呢称](PK)
父评论(被回复的问题/标题)
标题,内容,类型,浏览量,发布时间

11 笔记实体的逻辑建模

属性

用户昵称,关联章节,笔记标题,笔记内容,发布时间

和评论实体差不多,分析不再赘述

笔记表

[笔记标题,课程主标题,课程章名,小节名称,用户呢称](PK)
内容,发布时间

12 评价实体的逻辑建模

属性

用户呢称;课程主标题,内容,综合评分,内容实用,简洁易懂,逻辑清晰,发布时间

评价表

[用户呢称;课程主标题](PK)
内容,综合评分,内容实用,简洁易懂,逻辑清晰,发布时间

只有选择/购买了课程的用户才能评价!!!

需要用户与所选课程的关联关系表

用户选课表

[用户呢称;课程主标题](PK)
选课时间,累积听课时长

13 小结

MySQL8.0实战(二) - 数据库设计
MySQL8.0实战(二) - 数据库设计
MySQL8.0实战(二) - 数据库设计
MySQL8.0实战(二) - 数据库设计

14 范式化暴露的问题

如果我们想要查询出一门课程包括所有章节和小节的相关信息
MySQL8.0实战(二) - 数据库设计
那么这些信息又是如何存储的呢,需要查询哪些表呢?如下所示
MySQL8.0实战(二) - 数据库设计

我们就要关联5个表,查询效率极低!且查询课程信息的需求很大!
为了提高性能,我们还需要对表结构进行优化操作

15 反范式化设计

空间换时间的思想

15.1 课程章节表反范式化设计

MySQL8.0实战(二) - 数据库设计
上述表存在一对多的关系

所以可以并不需要关联关系表,而是呢可以直接把课程表和课程&章节联系表合并
MySQL8.0实战(二) - 数据库设计
成为新的课程章节表
[主标题,章节名](PK),说明,章节编号

虽然违反了第二范式,但是减少了一个表的查询,提高了查询性能,在频繁查询操作的系统中,这很值得!

经过反范式化后,我们只需要查询三个表即可
MySQL8.0实战(二) - 数据库设计

15.2 反范式化设计小结

课程相关表数量 5 -> 3
MySQL8.0实战(二) - 数据库设计

16 常用存储引擎

MySQL8.0实战(二) - 数据库设计

17 InnoDB存储引擎的特点

  • 事务型存储引擎支持ACID
  • 数据按主键聚集存储
  • 支持行级锁及MVCC
  • 支持Btree和自适应Hash索引
  • 支持全文和空间索引

18 根据 InnoDB特性优化后的表逻辑结构

通过数据冗余避免数据不一致
MySQL8.0实战(二) - 数据库设计

课程章节表:{章节ID(PK),课程ID,章节名称,章节说明,章节编号}

课程小节表:{小节ID(PK),课程ID,章节ID,小节名称,小节视频url,视频格式,小节时长,小节编号}。

课程方向表:{课程方向ID(PK),课程方向名称,填加时间}

课程分类表:{课程分类ID(PK),分类名称,填加时间}

课程难度表:{课程难度ID(PK) ,课程难度,填加时间}

用户表:{用户ID(PK),用户昵称,密码,性别,省市,职位,说明,经验,积分,关注
人数,粉丝人数,讲师标识}

问答评论表:{评论ID(PK),父评论ID ,课程ID,章节ID,小节ID ,评论标题,用户
ID,内容,类型,浏览量,发布时间}

笔记表:{笔记ID(PK),课程ID,章节ID,小节ID笔记标题,用户呢称,笔记内容,
发布时间}

评价表:{评价ID(PK),用户ID,课程ID,内容综合评分,内容实用,简洁易懂,逻
辑清晰,发布时间}

用户选课表:{用户选课ID(PK),用户ID,课程ID,选课时间,累积听课时长}

19 常用的整数类型

MySQL8.0实战(二) - 数据库设计

20 常用的浮点类型

MySQL8.0实战(二) - 数据库设计

  • 例如:
    MySQL8.0实战(二) - 数据库设计

实战实数类型的特点

  • 建立测试数据库
    MySQL8.0实战(二) - 数据库设计
  • 新建表
    MySQL8.0实战(二) - 数据库设计
  • 插入数据至t表中
    MySQL8.0实战(二) - 数据库设计

MySQL8.0实战(二) - 数据库设计

MySQL8.0实战(二) - 数据库设计

  • 查询和
    MySQL8.0实战(二) - 数据库设计
  • 和的结果
    MySQL8.0实战(二) - 数据库设计

所以只有decimal是精确的浮点类型

21 常用的时间类型

MySQL8.0实战(二) - 数据库设计

MySQL8.0实战(二) - 数据库设计

MySQL8.0实战(二) - 数据库设计

实战时间类型的特点

  • 新建表
    MySQL8.0实战(二) - 数据库设计
  • 插入数据
    MySQL8.0实战(二) - 数据库设计
  • 查询结果
    MySQL8.0实战(二) - 数据库设计
  • 由于北京时间是东八区,因此我们更改时区
    MySQL8.0实战(二) - 数据库设计
  • 新的查询结果
    MySQL8.0实战(二) - 数据库设计

这就是timestamp具有时区性的特点

22 字符串类型的特点

MySQL8.0实战(二) - 数据库设计

23 如何为数据选择合适的的数据类型

23.1 优先选择符合存储数据需求的最小数据类型

INET_ATON( '255.255.255.255' ) = 4294967295
INET_ NTOA(4294967295) ='255.255.255.255'

23.2 谨慎使用ENUM,TEXT字符串类型

23.2.1 ENUM 的迁移

数据迁移的时候,它几乎不可能被其他数据库所支持,如果 ENUM 里面是字符串,对于其他数据库来说就更郁闷了,还不能设为tinyint等类型的字段

23.2.2 ENUM 的索引

纯数字类型的不建议用枚举类型,这是因为在 ENUM 内部维护有一个隐形的索引,也是按数字排列的,容易混淆;添加枚举值也是一个问题,如果添加在最后还好,如果添加在中间什么位置的话,原来的隐藏索引将不再起作用

23.2.3 ENUM 字段 的NULL 值

ENUM 字段默认是可以插入 NULL 值的,这个就比较尴尬了,而且没有办法优化

23.2.4 插入的值

如果插入的值比ENUM设定的值大,会默认保存成接近的那个值;插入的值不能包含函数,不能传递参数

所以如果插入的值是数字型的,建议用tinyint,如果插入的值是字符型的,建议用char。如果真想用 ENUM 也是可以得,前提是要了解到 ENUM 的弊端,就可以有效规避这些问题

23.4 同财务相关的数值型数据,必需使用decimal类型。

24 为项目表们选择合适的数据类型

24.1 课程表

MySQL8.0实战(二) - 数据库设计
MySQL8.0实战(二) - 数据库设计

24.2 章节表

MySQL8.0实战(二) - 数据库设计

24.3 小节表

MySQL8.0实战(二) - 数据库设计

24.4 课程分类表

MySQL8.0实战(二) - 数据库设计

24.5 课程难度表MySQL8.0实战(二) - 数据库设计

24.5 课程方向表

MySQL8.0实战(二) - 数据库设计

24.6 用户表

MySQL8.0实战(二) - 数据库设计
MySQL8.0实战(二) - 数据库设计

24.7 问答评论表

MySQL8.0实战(二) - 数据库设计

24.8 笔记表

MySQL8.0实战(二) - 数据库设计

24.9 用户选课表

MySQL8.0实战(二) - 数据库设计

30 如何为表和列选择合适的名字

  • 所有数据库对像名称必须使用小写字母可选用下划线分割
  • 所有数据库对像名称定义禁止使用MySQL保留关建字
  • 数据库对像的命名要能做到见名识义,并且最好不要超过32个字
  • 临时库表必须以tmp为前缀并以日期为后缀
  • 用于备份的库,表必须以bak为前缀并以日期为后缀
  • 所有存储相同数据的列名和列类型必须一致。

31 总结

工程师的必备技能

1、前奏:【业务分析】欲善其事,必三思而行;
2、高潮:【逻辑设计】范式化VS反范式化;
3、结束:【物理设计】存储引擎&数据类型&命名规约。

内容综述

  • 数据库的逻辑设计规范
  • MySQL的常用存储引擎及其选择方法
  • MySQL的常用数据类型及其选择方法
  • 如何为表选择适合的存储类型
  • 如何为表起一个好名

参考

数据库设计
MySQL慎用 ENUM 字段

更多内容请关注JavaEdge 公众号

上一篇:Dubbo 新增本地 IDE 插件,快速创建样例工程


下一篇:HawkEye:一种高效率的精细化大页管理方案