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

0 Github

1 简介

数据库设计(Database Design)是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求（信息要求和处理要求）。在数据库领域内，常常把使用数据库的各类系统统称为数据库应用系统。

数据库设计的设计内容包括：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库的实施和数据库的运行和维护。

2 数据库建模五部曲

3 需求总结

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

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

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

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

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

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

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

4 宽表模式

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

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

• 实例
  

4.1 模式存在的问题

4.1.1 更新异常

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

例如当使用

想修改其职位时,不止影响一条数据

那么,我们再加个限定条件

就可以只修改一行数据,因此我们可以将主标题作为该数据表的唯一标识,即主键!

通过主键更新数据,虽然可以避免数据的更新异常,但也可能会造成表中的数据不一致现象,比如该实例中,讲师的职称就会产生多义.

4.1.2 插入异常

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

例如,我们想新增Java开发方向的课程

由于执行该语句时,PK为空,即违反了PK非空且唯一的约束条件,因此该语句无法成功.

4.1.3 删除异常

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

例如,我们想删除数据库方向

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

4.1.4 数据冗余

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

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

4.2 模式的适用场景

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

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

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

5 数据库设计范式

5.1 第一范式

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

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

我们只需对其进行简单的改动即可

即标准的二维表.

5.2 第二范式

前提

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

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

例如如下博客表实例

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

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

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

5.3 第三范式

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

依旧看看课程表

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

• 将不与PK形成依赖关系的字段直接提出单独成表即可
  
  

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加入

[标题,课程主标题,课程章名,小节名称,用户呢称](PK)

父评论(被回复的问题/标题)

标题,内容,类型,浏览量,发布时间

11 笔记实体的逻辑建模

属性

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

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

笔记表

[笔记标题,课程主标题,课程章名,小节名称,用户呢称](PK)

内容,发布时间

12 评价实体的逻辑建模

属性

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

评价表

[用户呢称;课程主标题](PK)

内容,综合评分,内容实用,简洁易懂,逻辑清晰，发布时间

只有选择/购买了课程的用户才能评价!!! 需要用户与所选课程的关联关系表

用户选课表

[用户呢称;课程主标题](PK)

选课时间,累积听课时长

13 小结

14 范式化暴露的问题

如果我们想要查询出一门课程包括所有章节和小节的相关信息

那么这些信息又是如何存储的呢,需要查询哪些表呢?如下所示

我们就要关联5个表,查询效率极低!且查询课程信息的需求很大!

为了提高性能,我们还需要对表结构进行优化操作

15 反范式化设计

空间换时间的思想

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

上述表存在一对多的关系

所以可以并不需要关联关系表,而是呢可以直接把课程表和课程&章节联系表合并

成为新的课程章节表

[主标题,章节名](PK),说明,章节编号

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

经过反范式化后,我们只需要查询三个表即可

15.2 反范式化设计小结

课程相关表数量 5 -> 3

16 常用存储引擎

17 InnoDB存储引擎的特点

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

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

通过数据冗余避免数据不一致

课程章节表:{章节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 常用的整数类型

20 常用的浮点类型

• 例如:
  
  实战实数类型的特点
• 建立测试数据库
  
• 新建表
  
• 插入数据至t表中
  

• 查询和
  
• 和的结果
  
  所以只有decimal是精确的浮点类型

21 常用的时间类型

实战时间类型的特点

• 新建表
  
• 插入数据
  
• 查询结果
  
• 由于北京时间是东八区,因此我们更改时区
  
• 新的查询结果
  
  这就是timestamp具有时区性的特点22 字符串类型的特点
  

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 课程表

24.2 章节表

24.3 小节表

24.4 课程分类表

24.5 课程难度表

24.5 课程方向表

24.6 用户表

24.7 问答评论表

24.8 笔记表

在这里插入图片描述

24.9 用户选课表

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

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

31 总结

工程师的必备技能

1、前奏：【业务分析】欲善其事，必三思而行；

2、高潮：【逻辑设计】范式化VS反范式化；

3、结束：【物理设计】存储引擎&数据类型&命名规约。

内容综述

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

参考

数据库设计

MySQL慎用 ENUM 字段

更多内容请关注JavaEdge公众号