案例-慕课网数据库

• [L150 - 零基础入门 全角度解读企业主流数据库 MySQL8.0]

数据库结构设计

1. 业务分析
2. 逻辑设计
3. 物理设计
4. 选择数据类型
5. 对象命名

1. 业务分析

先把页面中不同的模块视分为不同的对象，再列出每个对象下面的具体属性，再进行逻辑设计

课程的属性：

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

课程列表的属性：

• 章节名，小节名，说明，小节时长，章节 URL，视频格式

讲师的属性：

• 讲师昵称，说明，性别，职位，经验，积分，关注人，粉丝数

问答评论属性：

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

笔记的属性

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

用户的属性

• 用户昵称，说明，性别，职位，经验，积分，关注人，粉丝数

评价的属性：

• 用户，课程主标题，内容，评分，发布时间

2. 逻辑设计

根据三大设计范式，将整理出来的对象和属性，加以分析，放到合适的表中。先确定唯一主键，再看其它属性之间的关系。列表每个模块的表之后，再考虑使用反范式化设计去优化表结构。

• 数据库设计范式
• 反范式化设计
• 物理设计

数据库设计范式

1. 第一范式，原子性：表中的所有字段都是不可再分的
2. 第二范式，业务主键：表中必须存在业务主键，并且非主键全部依赖于业务主键，不能只是部分依赖
3. 第三范式，冗余性，表中的非主键列之间不能相互依赖

反范式化设计

以空间换时间。考虑查询某一类数据，需要查询多少表。连表越多，性能越差。为了提供性能可以牺牲一些第三范式。

宽表模式

将一个对象的所有属性，都存在一个表中，这个表就是宽表模式

适合场景：

• 配合列存储的数据报表应用
• 数据都在一个表中，查询不需要关联多个表

宽表模式存在的问题：

• 数据冗余问题，相同的数据在表中出现多次
• 数据插入异常问题，失去主键信息而无法写入表中
• 数据更新异常问题，修改一行数据同时修改了多行
• 数据删除异常问题

3.物理设计

课程表：imc_course

列名	数据类型	命名
课程 ID(PK)	int unsigned	course_id
主标题(UK)	varchar(20)	title
副标题	varchar(50)	title_desc
课程方向 ID	smallint unsigned	type_id
课程分类 ID	smallint unsigned	class_id
课程难度 ID	smallint unsigned	level_id
上线时间	datetime	online_time
学习人数	int unsigned	study_cnt
课程时长	time	course_time
课程简介	varchar(200)	intro
课程需知	varchar(200)	info
课程收获	varchar(200)	harvest
讲师 ID	int unsigned	user_id
课程图片	varchar(200)	main_pic
内容评分	decimal(3,1)	content_score
简单易懂	decimal(3,1)	level_score
逻辑清晰	decimal(3,1)	logic_score
综合评分	decimal(3,1)	score

课程章节表：imc_chapter

列名	数据类型	命名
章节 ID(PK)	int unsigned	chapter_id
课程 ID(UK)	int unsigned	course_id
章节名称(UK)	varchar(50)	chapter_name
章节说明	varchar(200)	chapter_info
章节编号	tinyint(2) unsigned	chapter_no

课程小节表：

列名	数据类型
小节 ID(PK)	int unsigned
章节 ID(UK)	int unsigned
课程 ID(UK)	int unsigned
小节名称(UK)	varchar(50)
小节 URL	varchar(200)
视频格式	enum('avi','mp4','mpeg')
小节时长	time
章节编号	tinyint(2) unsigned

课程分类表：

列名	数据类型
课程分类 ID(PK)	smallint unsigned
分类名称(UK)	varchar(10)

用户表：

列名	数据类型
用户 ID(PK)	int unsigned
用户昵称(UK)	varchar(20)
密码	char(32)
性别	char(2)
省	varchar(20)
市	varchar(20)
职位	varchar(10)
说明	varchar(100)
经验值	mediumint unsigned
积分	int unsigned
关注人数	int unsigned
粉丝人数	int unsigned
讲师标识	tinyint unsigned
注册时间	datetime
用户状态	tinyint unsigned

4.选择数据类型

为数据选择合适的数据类型：

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

• 谨慎使用ENUM，TEXT字符串类型

• 同财务相关的数值型数据，必需使用decimal类型

枚举的缺点是修改起来比较麻烦，修改表结构需要锁表，有风险。TEXT类型可以存储 2 万汉字通常情况下使用不了这么多。

5.对象命名

• 所有数据库对象名称必须使用小写字母，可选用下划线分割
• 禁止使用 MySQL 保留关键字
• 命名见名识意，最好不要超过 32 个字符
• 临时库表必须以tmp为前缀并以日期为后缀
• 用于备份的库，表必须以bak为前缀并以日期为后缀

MySQL 常见的存储引擎

• InnoDB

InnoDB

最常用的事务型存储引擎

特点：

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

SQL

DCL-访问控制

• 建立数据库账号: create user
• 对账号进行授权: grant
• 收回账号权限: revoke

查询

1. 首先确定我们要获取的数据存在哪些表中（确定 FROM 子句）
2. 其次确定我们要获取表中的哪些列（确定 SELECT 子句）
3. 确认是否需要对表中的数据进行过滤（确定 WHERE 子句）

连表查询

• 表与表之间要存在一定的关联关系，也就是存有对方表中的业务主键或数据主键，也叫做外键
• 使用JOIN关联多个表

JOIN

• INNER JOIN: 内关联
• OUTER JOIN: 外关联
  • LEFT JOIN: 左
  • RIGHT JOIN: 右

INNER JOIN

内关联只查询同时存在两个表中的数据

OUTER JOIN

外关联有左右区分

建立索引

根据具体的业务查询方式来建立索引

SQL 高级特性

公共表表达式 CTE

可以代替子查询使用。

特性：

• MySQL8.0 之后的版本才可以使用
• CTE 生成一个命名临时表，且只在查询期间有效
• CTE 临时表可以多次引用，自引用

窗口函数

SQL 开发中易犯的错误

• 使用COUNT(*)判断是否存在符合条件的数据。

  • 应该使用SELECT...LIMIT 1

• 在执行一个更新语句后，使用查询方式判断此更新语句是否有执行成功。

  • 应该使用ROW_COUNT()函数判断修改行数

• 试图在ON条件中过滤不满足条件的记录。

  • 应该在WHERE语句中过滤

SQL 优化

1. 发现问题
2. 分析执行计划
3. SQL 优化的手段

1.发现问题

• 用户或测试人员主动上报应用性能问题
• 分析慢查询日志发现存在问题的 SQL
  • 配置 MySQL 慢查询日志
  • 分析 MySQL 慢查询日志工具
• 数据库实时监控长时间运行的 SQL

2.分析执行计划

• 了解 SQL 如何访问表中的数据
• 了解 SQL 如何使用表中的索引
• 了解 SQL 所使用的查询类型
• EXPLAIN

3.SQL 优化的手段

两种手段：

• 优化 SQL 查询所涉及到的表中的索引
• 改写 SQL 以更好的利用索引的目的

优化索引

索引作用是告诉存储引擎如何快速查找所需数据，建立一个索引就像给一本书建立了一个目录

Btree 索引， B+树的结构存储索引数据

应该在什么列上建立索引？

• WHERE 子句中的列
• 在 ORDER BY、GROUP BY、DISTINCT 中的字段
• 多表 JOIN 的关联列
• 选择复合索引建的顺序，将区分度最高，最频繁，长度小的列放在最左侧

索引使用误区：

• 误区：索引越多越好
  • 不是越多越好
• 误区：使用 IN 列表查询不能用到索引
  • 其实 IN 列表也能用到索引
• 误区：查询过滤顺序必须同索引键顺序相同
  • 其实不必，MySQL 优化器会自动优化顺序

改写 SQL：

• 使用 outer join 代替 not in
• 使用 CTE 代替子查询
• 拆分复杂的大 SQL 为多个简单的小 SQL
• 巧妙利用计算列优化查询