程序员必知必会-MySql8.0详解

技术选型之数据库选型

SQL VS NOSQL

数据的操作方式来看

• SQL：通常是事务性数据库，且使用 SQL 操作数据
  • MySQL
  • Oracle
  • SQLServer
  • PostGreSQL
• NO SQL：通常是非关系型数据库，不是用 SQL 操作数据
  • HBase
  • MongoDB
  • Redis
  • Hadoop

关系型数据库的特点

• 数据结构化存储在二维表中随着数据库的发展，也支持将 JSON 这种非结构化的存储在表中（仅限使用 JSON 类型的列）
  | 姓名 | 姓名 | 生日 |
  | --- | --- | --- |
  | 张三 | 男 | 1980-01-28 |
  | 李四 | 男 | 1991-02-03 |

• 支持事物的 ACID 特性

  • A：原子性
  • C：一致性
  • I：隔离性
  • D：持久性

• 支持使用 SQL 语言对存储在其中的数据进行操作

关系数据库的使用场景

• 数据之间存在一定关系，需要关联查询数据的场景如：一个订单，有多个商品
• 需要事物支持的业务场景如：电商、金融类的场景
• 需要使用 SQL 语言灵活操作数据的场景

非关系数据库的特点

• 存储结构灵活，没有固定的结构

{"姓名":"张三","性别"："男"}
{"姓名":"张三","性别"："男","生日":"2010-02-01"}

• 对事物的支持比较弱，但对数据的并发处理性能高
• 大多不使用 SQL 语言操作数据

非关系数据库的适用场景

• 数据库结构不固定的场景

比如：某些产品有长宽属性，某些产品有颜色、味道等属性

• 对事物要求不高，但读写并发比较大的场景

比如：日志监控类、用户行为分析类

• 对数据的处理操作比较简单的场景

由于不支持 SQL 语言，在一定程度上没有 SQL 这么灵活查询，表关联等

关系数据库选型原则

• 数据库使用的广泛性

当出现问题的时候，有足够多的资料支持，使用广泛，表示认可程度较高。

• 数据库的可扩展性

数据库性能一般决定了系统的性能，是否有足够的性能和可扩展性，尤为重要

MySQL可扩展性

• 支持基于二进制日志的逻辑复制
• 存在多种第三方数据库中间层，支持读写分离及分库分表

MySQL安全性和稳定性

• MySQL 主从复制集群可达 99% 的可用性
• 配合主从复制高可用架构可达 99.99% 的可用性
• 支持对存储在 MySQL 的数据进行分级安全控制比如：对数据库实例、表等层面进行权限的控制

MySQL所支持的系统

• Linux 系统
• windows 系统

MySQL的使用成本

• 社区版本免费
• 使用人员众多，可以方便的获取技术支持

笔者忽略Linux、Mysql安装等具体步骤

关于数据库设计

先是「业务分析」，然后进行「逻辑表设计」，再为字段选择合适的「数据类型」和一个良好的「对象命名」，最后在数据库中「建立库表」
本章以慕课网「免费课程」来进行实战的对象（不一定是慕课现在使用的方案）

实战项目需求分析

进入上面的页面，可以分析出：

1. 条件筛选：方向、分类、难度
2. 课程信息：列表页中：主标题、副标题、图标、学习的人数。进入详情页后：学习难度、时长、学习人数、综合评分、讲师等信息。章节列表：名称、说明、小节名称、小节时长
3. 讲师信息：点击讲师信息后的页面性别、省份、职业、描述、经验、积分、关注、粉丝。还有讲师所讲课程列表
4. 问答评论评论类型：评论、问答，未解决、精华标识信息；问答标题、浏览数量、关联的章节信息等。
5. 同学笔记昵称（用户信息）、关联的章节、内容、发布时间等信息
6. 用户信息：点击用户的头像，查看有哪些信息可以看到与讲师信息类似
7. 用户评价对课程的评分、评价、评论内容、评论时间等信息

实战项目需求总结

业务分析总结，有上述观察总结出以下信息：
某课网免费课程前端项目：

• 课程的属性：
• 课程列表属性：
• 讲师属性：
• 问答评论属性：
• 同学笔记：
• 用户属性：
• 评价属性：

以上是项目的所有涉及到的对象属性，下面开始表逻辑设计

宽表模式

宽表模式：字面意思上来讲，一行数据的列比较多，则为宽表；（实际是把不同的内容放到同一张表中）
以「课程属性」来看，至少有 19 个左右的属性，把这些属性都放在「课程表」中，那么这就是一个宽表了。那么这就要考虑这样存储是否会有一些问题了？

主标题	副标题	方向	分类	难度	讲师名	讲师职位	综合评分	...
MySQL 面试指南	中高级 IT 开发人员晋升加薪的必备佳品	数据库	MySQL	中级	sqlsercn	高级 DBA	10
MyCat + MyCat	MyCat 高可用数据库架构	数据库	MySQL	中级	sqlsercn	高级 DBA	10
MySQL 架构设计	高性能可扩展 MySQL 架构设计与优化	数据库	MySQL	中级	sqlsercn	高级 DBA	9.15

宽表模式存在的问题

• 数据冗余：相同的数据在一个表出现了多次

比如：可以看到上表中有很多重复的数据，比如：讲师名、职位。
缺点：占用更多的存储空间、数据维护时需要维护多次

• 数据更新异常：修改一行中某列的值时，同时修改了多行数据

这个其实还是数据维护时需要维护多次的问题，比如：

update 课程表 SET 讲师职位 = ‘MySQL 架构师’ where 讲师名 = ‘sqlercn’
# 上述语句会更新到多行语句，如果把 主标题作为 主键 sql 变为
update 课程表 SET 讲师职位 = ‘MySQL 架构师’ where 讲师名 = ‘sqlercn’ and 主标题 = ‘MySQL 面试指南’
# 这就会造成其他数据中的讲师职位数据不一致的问题
# 同时还会带来其他的问题：如主键不能为 null 的问题

• 数据插入异常：部分数据由于缺失主键信息而无法写入表中

比如：方向一列在页面中赛选项时的展示，不止有数据库，如果想要新增一个方向「后端开发」，但是还没有后端的课程，使用如下 sql

INSERT INTO 课程表(方向) VALUES('后端开发')
# 由于主键是主标题：这里就无法只插入 方向 数据了

• 数据删除异常：删除某一数据时不得不删除另一数据

类似的，要删除一个方向，使用如下语句

DELETE FROM 课程表 方向 = ‘数据库’

宽表有这么多的异常，其实只是宽表不适合我们这里的业务场景

宽表模式的应用场景

配合列存储的数据报表应用：不同的内容冗余在每行数据中，不需要关联多表查询，性能上有一定的优势
那么这里我们的业务需求不适合使用宽表，要怎么来设计呢？其实数据库的设计有一定的规则来参考的

数据库设计范式

一般来说只要符合前三个范式，一般就足够了

第一范式

表中的所有字段都是不可再分的
比如用户表中的联系方式

姓名 |  性别	|							联系方式
						 | 手机		| 固话		| 邮箱
张三 |	男		 | 123		| 456		 | 789@qq.com			

联系方式是一个符合属性，这是在数据库中无法创建出来的。可以进行拆分

姓名 |  性别	|	 手机		| 固话		| 邮箱

每一列都无法再分了，满足了第一范式的要求，通常来说：第一范式的表都是标准的「二维表」
那么我们设计的表，在满足第一范式的基础上，还需要满足第二范式，它是在第一范式的基础之上定义的

第二范式

表中必须存在业务主键，并且非主键依赖于全部业务主键
上述定义分成两段：

• 第一段关键词「业务主键」：可以通过该主键定位出唯一的一行数据
• 第二段：如果使用的是「复合组件」，比如使用「姓名、手机号」能定位到唯一一行数据，那么除了这两列以外的数据，都必须要遵守这两列的定义，不能只符合姓名或则手机号。

一个拥有良好逻辑的表，还要符合第三范式，同样是在前两范式基础上定义的

第三范式

表中的非主键列之间不能相互依赖
这个这样来理解

主标题( PK)	副标题	方向	分类	难度	讲师名	讲师职位	综合评分	...
MySQL 面试指南	中高级 IT 开发人员晋升加薪的必备佳品	数据库	MySQL	中级	sqlsercn	高级 DBA	10

如果指定为主标题为主键，每一本书有独一无二的名称，这个是满足第 2 范式的。那么其他的列，比如 讲师职位依赖了讲师名称，毕竟，先由讲师，才能给讲师对应的职位，他们之间有了相互依赖。
要解决这个问题，就是将有冲突的列独立出去，比如这里独立为一个讲师表

												课程表																						讲师表
主标题(pk) | 副标题	| 方向	|	分类	|	难度	|	讲师名	|	综合评分				讲师名（PK） | 讲师职位

面向对象设计

那么根据数据库三范式，来对我们的项目属性进行建模

课程对象逻辑建模

课程的属性：{主标题，副标题，方向，分类，难度，最新，最热，时长，简介，人数，需知，收获，讲师名，讲师职位，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰}
按照三范式设计课程表
课程表：{主标题(PK)，副标题，方向(记录其它表的业务主键），分类(记录其它表的业务主键），难度(记录其它表的业务主键），上线时间，学习人数，时长，简介，需知，收获，讲师名，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰}

最新可以由上线时间计算获得
最热可以由学习人数计算获得

讲师表(简)：{讲师名，讲师职位}

课程方向表：{课程方向名称（pk），添加时间}

不放入主表的原因，假设添加新的课程方向但却还未有新开设的课程会导致（宽表模式的问题3）

课程分类表：{分类名称（pk），添加时间}

同上

课程难度分级表：{难度名称（pk），添加时间}

同上

课程列表对象

课程列表的属性：{章节名，小节名，说明，小节时长，章节url，视频格式}

组合主键章节名+小节名，因为章节下有很多小节，每章节的小节名称有可能相同都叫第一节，所需要使用组合主键。才能保证主键唯一性

说明只与章节名有关
小节时长，章节url，视频格式依赖于小节名
课程章节表：{章节名，说明，章节编号（pk）}
课程同章节的联系表：{课程主标题，课程章节名}
// 为什么不用章节编号
课程小节表：{小节名，小节时长，章节url，视频格式，小节编号（pk）}
课程章节同小节关系表：{课程主标题，课程章节名，小节名称} // 为什么不用章节编号＋小节编号（小节名）

讲师对象

讲师的属性:{讲师昵称(业务主键)，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数}
讲师表:{讲师昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数}

用户对象

用户的属性:{用户昵称(业务主键)，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数}
用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数}

用户对象

用户表和讲师表进行合并，否则会数据冗余
用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数，讲师标识}

问答评论对象

问答评论属性:{类型，标题，内容，关联章节，浏览量，发布时间，用户昵称}
问答评论对象的业务主键为组合主键: 标题+关联章节+用户昵称
关联章节使用课程得列表对象的业务主键，课程名+章节名+小节名
问答评论表:{标题，课程主标题，课程章名，小节名称，用户昵称，父评论标题，内容，类型，浏览量，发布时间}
父评论标题，评论回复的标题

笔记对象

笔记的属性:{用户昵称，关联章节，笔记标题，笔记内容，发布时间}
用户昵称，关联章节，笔记标题作为组合业务主键来唯一标识笔记
关联章节: 课程名+章节名+小节名
笔记表: {笔记标题，课程主标题，课程章名，小节名称，用户昵称，笔记内容，发布时间}

评价对象

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

作为业务主键
评价表: {用户昵称，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间}
用户选课表:{用户昵称，课程主标题，选课时间，累积听课时长}
联合主键: 用户昵称，课程主标题

逻辑设计总结
课程表：{主标题(PK)，副标题，方向(记录其它表的业务主键），分类(记录其它表的业务主键），难度(记录其它表的业务主键），上线时间，学习人数，时长，简介，需知，收获，讲师名，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰}
课程章节表：{章节名，说明，章节编号（pk）}
课程同章节的联系表：{课程主标题，课程章节名}
课程小节表：{小节名，小节时长，章节url，视频格式，小节编号（pk）}
课程章节同小节关系表：{课程主标题，课程章节名，小节名称}
课程方向表：{课程方向名称（pk），添加时间}
课程分类表：{分类名称（pk），添加时间}
课程难度分级表：{难度名称（pk），添加时间}
用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数，讲师标识}
问答评论表:{标题，课程主标题，课程章名，小节名称，用户昵称，父评论标题，内容，类型，浏览量，发布时间}
笔记表: {笔记标题，课程主标题，课程章名，小节名称，用户昵称，笔记内容，发布时间}
评价表: {用户昵称，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间}
用户选课表:{用户昵称，课程主标题，选课时间，累积听课时长}\

范式化设计结果思考

以上根据数据库的三范式设计出来了 13 张表。这样是否合理了？这就需要来思考一些场景使用（这里可以套用一些在页面上展示出来的功能）：
如何获取一门课程包括所有章节和小节的信息？
这里所需要的信息，分别存放在 课程表、章节表、小节表 且为还需要使用 课程章节表关联关系表、章节表与小节表关联关系表，一共需要关联 5 张表。
对于数据库性能来说，关联越多的表，性能越差，而且在使用场景上来看，每个用户要学习课程，点击进入的课程信息页面，就需要获取一次，这个场景应该是比较频繁的。
虽然我们按照范式化完成了对表结构的设计，但是出于对业务查询性能的考虑，还需要范式化设计的表进行优化，通常优化后的表或多或少的会违反一些范式化的规定和冗余一些信息，这种方式叫做 反范式化设计
在进行反范式化设计时，多数是使用以 空间换时间、

例: 课程表和章节表(1--N)
课程章节表:{课程章节名，说明，章节编号}
课程同章节的联系表:{课程主标题，课程章名}
课程章节表:{课程主标题，课程章名，章节说明，章节编号}

课程小节表
例: 课程章节表和课程小节表(1--N)

课程小节表:{小节名称，小节视频url，视频格式，小节时长，小节编号}
课程章同小节关系表:{课程主标题，课程章节名，小节名称}
课程小节表:

此时获取出一门课程包括所有章节和小节的信息只需要查询三张表
课程表 / 课程章节表 / 课程小节表

反范式化设计总结

课程表：{主标题(PK)，副标题，方向(记录其它表的业务主键），分类(记录其它表的业务主键），难度(记录其它表的业务主键），上线时间，学习人数，时长，简介，需知，收获，讲师名，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰}
课程章节表:{课程主标题，课程章名，章节说明，章节编号}

联合业务主键: 课程主标题，章节名称

课程小节表:{课程主标题，课程章名，小节名称，小节视频url，视频格式，小节时长，小节编号}

联合业务主键: 课程主标题，课程章名，小节名称

课程方向表：{课程方向名称（pk），添加时间}
课程分类表：{分类名称（pk），添加时间}
课程难度分级表：{难度名称（pk），添加时间}
用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数，讲师标识}
问答评论表:{标题，课程主标题，课程章名，小节名称，用户昵称，父评论标题，内容，类型，浏览量，发布时间}
笔记表: {笔记标题，课程主标题，课程章名，小节名称，用户昵称，笔记内容，发布时间}
评价表: {用户昵称，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间}
用户选课表:{用户昵称，课程主标题，选课时间，累积听课时长}
到目前为止，我们完成了数据库逻辑的设计，还需要对数据库进行 物理设计，也就是要设计表的存储结构，包括：

• 每一列所使用的列类型
• 如何对库表进行命名
• 选择表的存储引擎（对于 MySQL 来说）

物理设计

MySQL 常见的存储引擎

引擎名称	事物	说明
MyIsam	N	MySQL 5.6 之前的默认引擎，最常用的非事务型存储引擎
CSV	N	以 CSV格式存储的非事务型存储引擎
Archive	N	只允许查询和新增数据而不允许修改的非事务型存储引擎
Memory	N	是一种易失性非事务型存储引擎
InnoDB	Y	最常用的事务型存储引擎

还有其他的等等不常用的存储引擎

InnoDB 存储引擎的特点

• 事务型存储引擎支持 ACID不要混合使用事物和非事务型的表，否则会导致事务不完整
• 数据按主键聚集存储那么在选择什么数据作为主键就显得至关重要了。非主键的叶子节点所指向的都是物理行的主键而不是位置（这个是聚簇索引的特点），因此主键的大小就影响到索引查找数据的性能。数据是按主键的逻辑顺序进行存储的，因此建议使用自增 ID 作为主键。对于之前设计的业务主键，可以在这些列上新增唯一索引，来保证它的查询效率
• 支持行级锁及 MVCC非常适合在高并发的读写混合场景下使用
• 支持 Btree 和自适应 Hash 索引

自适应 Hash 索引：由 InnoDb 根据数据的统计信息，自动建立的 hash 索引，只适合等值查找

• 支持全文和空间索引（MySQL 5.7 之后）

根据 InnoDB 的特点，我们需要对我们的项目表进行优化，如，由于自增列的主键比实际的业务主键小很多，性能上有一定的优势，主键使用自增列，业务关联关系也使用自增列的主键

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

• 课程表（课程主表）：{课程 ID（PK 自增 ID）、主标题（唯一索引）、副标题、方向 ID、分类 ID、难度 ID、上线时间、学习人数、时长、简介、人数、需知、收获、讲师 ID、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰}将表中属性关联其他表都改成了对于表的自增列 ID，并按原来的业务主键，主标题建立唯一索引；同样的没有一种方案是完美的，这里使用了 ID 关联，有点和缺点显而易见，需要关联表查询得到关联信息，同时也是缺点，需要关联；那么是需要冗余字段还是关联查询呢？是需要根据具体的业务场景和需求进行
• 课程章节表：
• 课程小节表：
• 课程方向表：
• 课程分类表：
• 课程难度表：
• 用户表：
• 问答评论表：
• 同学笔记表：
• 评价表：
• 用户选课表：

MySQL 数据类型

流程：业务需求调研 → 面向对象设计 → 范式化设计 → 反范式化设计 → 物理设计 → 数据类型选择
要选择合适的数据类型，就需要学习 MySQL 的数据类型有哪些？

常用的整数类型

注意：使用 int(n) 并不会降低该类型的占用空间

#实数类型（浮点数）

可以存储小数的类型称为实数类型

列类型	存储空间	是否精确类型
fload	4 个字节	否
double	8 个字节	否
decimal	每 4 个字节存 9 个数字，小数点占一个字节	是

精确：存在小数部分的数值经过计算之后，是不精确的，与我们所认为的正确的值存在一定的偏差
decimal 举例： 123456789.987654321 = decimal(18,9) 占用 9 字节
下面对是否精确来进行一个演示说明

-- 首先先创建一个测试库，和创建一个 t 的测试表
USE test；
CREATE TABLE t(d1 DOUBLE, d2 DECIMAL(10,3));

-- 两列插入相同的两个数值，共插入 4 行数据
INSERT INTO t VALUES(11.2,11.2),(2.56,2.56),(9.01,9.01),(132.33,132.33)

-- 进行计算，查看结果
SELECT SUM(d1),SUM(d2) FROM t

SUM(d1)							SUM(d2)
155.10000000000002	155.100
-- 可以看到两列结果并不一样

常用的时间类型

类型	存储空间	格式	范围
DATA	3 字节	YYYY-MM-DD	从 ‘1000-01-01’ 到 ‘9999-012-31’
TIME	3~6 字节	HH:MM:SS[.微秒值]	从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR	1 字节	YYYY	从 1901 到 2155
DATETIME	5~8 字节	YYYY-MM-DD HH:MM:SS[.微秒值]	从 ‘1000-01-01 00:00:00’ 到 ‘9999-02-31 23:59:59’
TIMESTAMP	4~7 字节	YYYY-MM-DD HH:MM:SS[.微秒值]	从 ‘1970-01-01 00:00:00’ 到 ‘2038-01-19 03:14:07’ UTC

TIME 类型：可以存储 1 月 2 号 到 1 月 3 号之间的时间，这就大于了一天的 24 小时

微妙	存储空间
0	0 byte
1,2	1 byte
3,4	2 byte
5,6	3 byte

每 2 位数，占用 1 字节。如下所示

数据行	保留毫秒位数	数据值
d0	time	16:18:29
d2	time(2)	16:18:29.72
d4	time(4)	16:18:29.7182
d6	time(6)	16:18:29.718216

关于 TIMESTAMP 的时区信息演示

-- 创建一张表
CREATE TABLE t_timestamp(dt TIMESTAMP);
INSERT INTO t_timestamp SELECT NOW();

-- 查看数据
SELECT * FROM t_timestamp;

dt
2020-04-19 14:43:31

-- 修改时区信息
SET time_zone = '+10:00';
SELECT * FROM t_timestamp;
dt
2020-04-19 16:43:31
-- 可以看到修完时区之后，看到的时间就变了

常用的字符串类型

类型	范围	说明
Char(M)	M=1~255 个字符	固定长度
VarChar(M)	一行中所有 varchar 类型的列所占用的字节数不能超过 65535 个字节；比如 UTF8mb4 一个字符占用 4 个字节，那么 varchar(10) = 40 字节	存储可变长度的 M 个字符
TinyText	最大长度 255 个字节	可变长度
Text	最大长度 65535 个字节	可变长度
MediumText	最大长度 16777215 个字节	可变长度
LongText	最大长度 42949667295 个字节	可变长度
Enum	集合最大数目为 65535	只能插入列表中的值

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

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

• 如：将一个 BigInt 的数据，存储在一个 int 的无符号类型中（当然你的需求是在该范围内）
• 如：将 IP 字符串转换成 int 类型

INET_ATON('255.255.255.255') = 4294967295
INET_NATON(4294967295) = '255.255.255.255'

• 如果使用字符串来保存，需要 15 字节左右，而使用 int 则只需要 4 字节（注意使用无符号的 int）。
• 谨慎使用 ENUM、TEXT 字符串类型一定要根据你当前的业务需求，与当前表编码来换算实际存储字节，来细致选择某一种数据类型。这与 mysql 一些实现有关系，如内存临时表不支持 text 类型，在排序等场景下，就无法使用内存临时表，性能降低索引方面是有长度的，使用 TEXT 只能索引一部分；
• 同财务相关的数值型数据，必须使用 decimal 类型

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

流程：业务需求调研 → 面向对象设计 → 范式化设计 → 反范式化设计 → 物理设计 → 数据类型选择 → 对象命名

• 所有数据库对象名称必须使用小写字母可选用下划线分割和表文件存储有关系，大小写敏感与文件系统有关，此类建议阅读笔者另外的笔记「MySQL 高性能」
• 所欲数据库对象名称定义禁止使用 MySQL 保留关键字如果使用关键词，有可能就会导致 MySQL 解析出现问题，关键词可以通过 官方手册来查阅有哪些
• 数据库对象的命名要能做到见名识义，并且最好不要超过 32 个字32 不是 MySQL 的限制，根据经验来制定，如果名称太长，也增加网络传输负担
• 临时库表必须以 tmp 为前缀并以日期为后缀临时表使用完成后，往往不能及时清理，导致时间长了之后，不清楚这个表是否是临时表
• 用于备份的库，表必须以 bak 为前缀并以日期为后缀与临时表类似
• 所有存储相同数据的列名和列类型必须一致

本章总结

• 数据库的逻辑设计规范一个良好的表逻辑设计需要遵循表的三范式，为了查询性能，需要对标进行反范式化的设计，以空间换时间的策略，提高查询性能。适当的进行反范式化需要根据业务场景进行考虑
• MySQL 的常用存储引擎及其选择方法
• MySQL 的常用数据类型及其选择方法
• 如何为表选择适合的存储类型满足我们需求的最小的数据类型
• 如何为表起一个好名做到见名识义

日常工作 DCL、DDL

本章品味独特，剑指 Geek Style。围绕核心是“工作”，针对日常工作常用的知识。包含工作基本功+工作必备技， 两大部分： 【工作基本功】DCL& DDL； 【工作必备技 】常用函数。

初识 SQL

• 什么是 SQL：一种描述性语言
• SQL 语言的作用：对存储在 RDBMS 中的数据进行增删改查等操作
• 常用的 SQL 语言的种类：DCL、DDL、DML、TCL
  • DCL：数据库管理语句
  • DDL：数据定义类语句
  • DML：数据操作语句
  • TCL：事物控制类语句

访问控制语句 DCL

访问数据库必须要有相应权限的账户，root 是一个超级账户，需要为程序建立一般账户，这类语句就属于 DCL（Data Control Language）

• 建立数据库账户：create user
• 对用户授权：grant
• 收回用户权限：revoke

更详细的请参考 官网文档，下面进行一些常用知识的讲解；
主要就是针对账户权限的一些操作

创建数据库账户 create user

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']
    
-- 上面的语法怎么看？有中括号的是可选的，
-- 中括号里面的是啥意思？记得查看上面的官方文档，在语法的下面部分就有详细的说明
auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}
-- ssl 加密链接配置
tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}
-- 资源限制
resource_option: {
    MAX_QUERIES_PER_HOUR count		-- 该用户每小时查询数量
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]  -- 密码过期时间
  | PASSWORD HISTORY {DEFAULT | N}		-- 历史密码重复使用次数
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

lock_option: {
    ACCOUNT LOCK		-- 账户锁定，锁定后无法登录
  | ACCOUNT UNLOCK
}

-- 还可以通过客户端执行 help 语句查询语法
mysql> help create user;

• user：由两部分组成用户名@访问控制列表

访问控制列表：决定用户可以从哪些客户端访问

-- 建立程序使用的数据库账户
-- @ 后面的 % 号就是通配符，比如 mysql 里面的 like 语法中的 % 
CREATE USER mc_class@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' by '1234567';

-- 示例 1：创建一个测试账户
-- 这里没有指定密码插件，会使用默认的，这个在上述语法中有写明不加插件的语法
CREATE USER mc_test@'%' IDENTIFIED BY '123456'

-- 示例 2：创建带有资源限制的账户
-- 这里限制该用户只能建立一个连接
CREATE USER mc_test1@'%' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 1;

-- 示例 3：测试上面带资源限制的账户，
-- 使用 mysql 客户端连接
[root@study ~]# mysql -u mc_test1  -p
Enter password: 
-- 连接成功一个后，换一个窗口再次连接
[root@study ~]# mysql -u mc_test1  -p
Enter password: 
ERROR 1226 (42000): User 'mc_test1' has exceeded the 'max_user_connections' resource (current value: 1)
-- 看到被拒绝了

数据库用户授权 grant

MySQL 的常用权限

• Insert：向表中插入数据的权限
• Delete：删除表中数据的权限
• update：修改表中数据的权限
• Select：查询表中数据的权限
• Execute：执行存储过程的权限

GRANT 使用

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...  -- 权限列表，可以通过  show privileges 获取所有的权限
    ON [object_type] priv_level		-- 在哪个对象中授权
    TO user_or_role [, user_or_role] ... -- 把权限授予哪个用户或则是角色
    [WITH GRANT OPTION]

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user
  | role
}

下面进行示例练习，现在由于还没有到创建库表知识的章节，先以 mysql 库中的 user 表来演示。
user 表记录了 mysql 账户相关的设置，比如上面创建的账户就存储在这个表里面

-- mc_test 账户新建的时候没有授权，因此登录后，只能看到 information_schema 库
-- 范例 1：给 mc_test 授权，只能查询 mysql.user 表中的 user 和 host 列
mysql> GRANT select(user,host) ON mysql.user TO mc_test@'%';

-- 范例 2：给账户授权 mysql.user 表的所有列的 select 权限
mysql> GRANT select ON mysql.user TO mc_test@'%';
-- 上述设置之后，用 GUI 程序登录后，就可以看到 mysql 库中的 user 表，也能查询到信息了

-- 可以通过以下语句查看某一个用户授予的权限
mysql> show grants for 'mc_test'@'%';
+--------------------------------------------------------------------------+
| Grants for mc_test@%                                                     |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mc_test`@`%`                                      |
| GRANT SELECT, SELECT (`host`, `user`) ON `mysql`.`user` TO `mc_test`@`%` |
+--------------------------------------------------------------------------+
-- 上面就能看到我赋予的两个 select 权限

-- 范例 3：赋予该账户 mysql 库下的所有表的查询权限
mysql> GRANT select ON mysql.* TO mc_test@'%';

-- 范例 4：同时赋予该账户 select 和 update 权限
-- 多个使用逗号分隔
mysql> GRANT select,update ON mysql.* TO mc_test@'%';

GRANT 命令注意事项

• mysql 8.0 后，使用 grant 授权的数据库账户必须存在
• 使用 grant 命令授权的账户，必须具有 grant optional 的权限就是说，只能授予自己拥有的权限
• 获取命令帮助信息 /h grant 或 help grant

数据库用户授权收回 revoke

REVOKE
    priv_type [(column_list)]			-- 这里与 GRANT 中一致，只能收回已有的
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user_or_role [, user_or_role] ...

REVOKE PROXY ON user_or_role
    FROM user_or_role [, user_or_role] ...

REVOKE role [, role ] ...
    FROM user_or_role [, user_or_role ] ...

user_or_role: {
    user
  | role
}

练习

-- 查看该账户拥有的权限
mysql> show grants for 'mc_test'@'%';
+--------------------------------------------------------------------------+
| Grants for mc_test@%                                                     |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mc_test`@`%`                                      |
| GRANT SELECT ON `mysql`.* TO `mc_test`@`%`                               |
| GRANT SELECT, SELECT (`host`, `user`) ON `mysql`.`user` TO `mc_test`@`%` |
+--------------------------------------------------------------------------+

-- 收回 SELECT (`host`, `user`) 的权限
mysql> REVOKE SELECT(`host`, `user`) ON mysql.user from mc_test@'%';
-- 再次查看就发现被收回了
mysql> show grants for 'mc_test'@'%';
+-------------------------------------------------+
| Grants for mc_test@%                            |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `mc_test`@`%`             |
| GRANT SELECT ON `mysql`.* TO `mc_test`@`%`      |
| GRANT SELECT ON `mysql`.`user` TO `mc_test`@`%` |
+-------------------------------------------------+

创建数据库对象 DDL

Data Definition language
常用的数据库定义有：

• 建立/修改/删除数据库：create/alter/drop database
• 建立/修改/删除表：create/alter/drop table
• 建立/删除索引：create/drop index
• 清空表：truncate table相当于 drop table + create table 所以是 DDL 命令
• 重命名表：rename table还可以在同一个实例的不同数据库之间移动表
• 建立/修改/删除视图：create/alter/drop view

数据库操作

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name			-- 字符集
  | [DEFAULT] COLLATE [=] collation_name					-- 字符集排序

-- 修改数据库
ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
    
alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

-- 删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

-- 创建数据库
-- 不使用其他参数，则使用默认的
CREATE DATABASE imc_db;

表操作

创建表

创建表的语法很多，下面讲解简单的，完整的语法请参考官网文档

-- TEMPORARY 临时表，只能当前线程可见，线程退出，表也消失
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)				-- 表定义
    [table_options]
    
create_definition:
    col_name column_definition		-- 列命令和列定义，索引
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (key_part,...) 
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...) reference_definition
  | CHECK (expr)

-- 列定义
column_definition:
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]	-- 数据类型
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] -- 主键、唯一索引等
      [COMMENT 'string']		-- 备注信息
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [reference_definition]
  | data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      
-- 表的选项
table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name
  | UNION [=] (tbl_name[,tbl_name]...)

可以看到创建表的语法是很复杂的。这里只是一些基础的语法

修改表

在业务发展或开发中，会常常进行数据列的新增或删除，数据类型的修改调整等

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]
    
alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition		-- 新增一列
        [FIRST | AFTER col_name]		-- 指定新列在表中的位置
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (col_name,...)
        reference_definition
  | ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}  -- 修改列默认值
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition  -- 修改列名并修改列定义
        [FIRST|AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE|ENABLE} KEYS
  | {DISCARD|IMPORT} TABLESPACE
  | DROP [COLUMN] col_name	-- 删除列，数据也会同时被删除
  | DROP {INDEX|KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition  -- 在不修改列名的情况下，修改列的定义
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX|KEY} old_index_name TO new_index_name
  | RENAME [TO|AS] new_tbl_name
  | {WITHOUT|WITH} VALIDATION
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
  | UPGRADE PARTITIONING

删除表

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...   -- 可以删除多张表，用逗号分隔
    [RESTRICT | CASCADE]

建立所有的项目表

-- 课程表
CREATE TABLE `imc_course` (
	`course_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程ID',
	`title` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '课程主标题',
	`title_desc` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '课程副标题',
	`type_id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程方向ID',
	`class_id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程分类ID',
	`level_id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程难度ID',
	`online_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '课程上线时间',
	`study_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '学习人数',
	`course_time` time NOT NULL DEFAULT '00:00' COMMENT '课程时长',
	`intro` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '课程简介',
	`info` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '学习需知',
	`harvest` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '课程收获',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '讲师ID',
	`main_pic` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '课程主图片',
	`content_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '内容评分',
	`level_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '简单易懂',
	`logic_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '逻辑清晰',
	`score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '综合评分',
	PRIMARY KEY ( `course_id` ),
UNIQUE KEY `udx_title` ( `title` ) 
) COMMENT = '课程主表';
-- 这里为了优化查询性能，把列定义为了非空属性，同时使用了默认值
-- 这是为了以后使用索引打下基础，因为索引对空不支持

-- 章节表
CREATE TABLE `imc_chapter` (
	`chapter_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '章节ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`chapter_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '章节名称',
	`chapter_info` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '章节说明',
	`chapter_no` TINYINT ( 2 ) UNSIGNED ZEROFILL NOT NULL DEFAULT '00' COMMENT '章节编号',
	PRIMARY KEY ( `chapter_id` ),
UNIQUE KEY `udx_couseid` ( `course_id`, `chapter_name` ) 
) COMMENT = '课程章节';
-- UNIQUE KEY 定义了一个联合索引

-- 小节表
CREATE TABLE `imc_subsection` (
	`sub_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '小节ID',
	`chapter_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '章节ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`sub_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '小节名称',
	`sub_url` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '小节URL',
	`video_type` enum ( 'avi', 'mp4', 'mpeg' ) NOT NULL DEFAULT 'mp4' COMMENT '视频格式',
	`sub_time` time NOT NULL DEFAULT '00:00:00' COMMENT '小节时长',
	`sub_no` TINYINT ( 2 ) UNSIGNED ZEROFILL NOT NULL DEFAULT '00' COMMENT '章节编号',
	PRIMARY KEY ( `sub_id` ),
UNIQUE KEY `udx_chapterid_courseid_subname` ( `chapter_id`, `course_id`, `sub_name` ) 
) COMMENT = '课程小节表';


-- 课程分类表
CREATE TABLE `imc_class` (
	`class_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程分类ID',
	`class_name` VARCHAR ( 10 ) NOT NULL DEFAULT '' COMMENT '分类名称',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
PRIMARY KEY ( `class_id` ) 
) COMMENT = '课程分类表';
-- CURRENT_TIMESTAMP 以当前时间作为添加时间的默认值

-- 课程方向表
CREATE TABLE `imc_type` (
	`type_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程方向ID',
	`type_name` VARCHAR ( 10 ) NOT NULL DEFAULT '' COMMENT '课程方向名称',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '填加时间',
PRIMARY KEY ( `type_id` ) 
) COMMENT = '课程方向表';

-- 课程难度表
CREATE TABLE `imc_level` (
	`level_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程难度ID',
	`level_name` VARCHAR ( 10 ) NOT NULL DEFAULT '' COMMENT '课程难度名称',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '填加时间',
PRIMARY KEY ( `level_id` ) 
) COMMENT = '课程方向表';

-- 用户表
CREATE TABLE `imc_user` (
	`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
	`user_nick` VARCHAR ( 20 ) NOT NULL DEFAULT '慕课网' COMMENT '用户昵称',
	`user_pwd` CHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '密码',
	`sex` CHAR ( 2 ) NOT NULL DEFAULT '未知' COMMENT '性别',
	`province` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '省',
	`city` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '市',
	`Position` VARCHAR ( 10 ) NOT NULL DEFAULT '未知' COMMENT '职位',
	`mem` VARCHAR ( 100 ) NOT NULL DEFAULT '' COMMENT '说明',
	`exp_cnt` MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '经验值',
	`score` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '积分',
	`follow_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '关注人数',
	`fans_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '粉丝人数',
	`is_teacher` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '讲师标识,0:普通用户,1:讲师用户',
	`reg_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
	`user_status` TINYINT UNSIGNED NOT NULL DEFAULT '1' COMMENT '用户状态 1:正常 0:冻结',
	PRIMARY KEY ( `user_id` ),
UNIQUE KEY `udx_usernick` ( `user_nick` ) 
) COMMENT = '用户表';

-- 问答评论表
CREATE TABLE `imc_question` (
	`quest_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评论',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`chapter_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '章节ID',
	`sub_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '小节ID',
	`replyid` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '父评论ID',
	`quest_title` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '评论标题',
	`quest_content` text COMMENT '评论内容',
	`quest_type` enum ( '问答', '评论' ) NOT NULL DEFAULT '评论' COMMENT '评论类型',
	`view_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '浏览量',
	`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
PRIMARY KEY ( `quest_id` ) 
) COMMENT = '问答评论表';

-- 用户笔记表
CREATE TABLE `imc_note` (
	`note_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '笔记ID',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`chapter_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '章节ID',
	`sub_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '小节ID',
	`note_title` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '笔记标题',
	`note_content` text COMMENT '评论内容',
	`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
PRIMARY KEY ( `note_id` ) 
) COMMENT = '笔记表';

-- 课程评价表
CREATE TABLE `imc_classvalue` (
	`value_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评价ID',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`content_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '内容评分',
	`level_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '简单易懂',
	`logic_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '逻辑清晰',
	`score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '综合评分',
	`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
PRIMARY KEY ( `value_id` ) 
) COMMENT = '课程评价表';

-- 选课表
CREATE TABLE `imc_selectcourse` (
	`select_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '选课ID',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`select_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间',
	`study_time` time NOT NULL DEFAULT '00:00:00' COMMENT '累积听课时间',
PRIMARY KEY ( `select_id` ) 
) COMMENT = '用户选课表';

把以上建表语句，创建在 imc_de 数据库中

索引维护语句

在创建表的时候，可以创建索引语句，在创建表之后，往往在业务发展中，或则调优中，也需要进行索引的维护

-- 创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

index_type:
    USING {BTREE | HASH}

-- 删除索引
DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...

本章讲解怎么维护索引，下章讲解怎么使用索引

其他的 DDL 语句

前面讲解的是常用的 DDL 语句，再介绍一些

• 清空表：TRUNCATE TABLE 表采用先删除表，再重建表的方式，且不记录二进制日志，所以速度很快
• 重命名表：RENAME TABLE imc_note TO bak_imc_note;

DML语句

什么是 DML？

DML：Data Manipulation language
用来操作存储在数据中的数据，常用的 DML 语句有：

• 新增表中的数据：insert into
• 删除表中的数据：delete
• 修改表中的数据：update
• 查询表中的数据：select

insert 语句

语法如下

 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)] 		-- 列清单
    {VALUES | VALUE} (value_list) [, (value_list)] ...	-- 值清单
    [ON DUPLICATE KEY UPDATE assignment_list] -- 当出现主键或唯一索引冲突事件的行为，比如更新旧数据

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

编写 Insert 语句的思路

• 确认要把数据插入到哪个表中?
• 确认表的数据库结构，哪些列不能为 NULL，对于不能为 NULL 的列是否有默认值

-- 可以通过命令语句查看表结构
mysql> SHOW CREATE TABLE imc_db.imc_class \G;
*************************** 1. row ***************************
       Table: imc_class
Create Table: CREATE TABLE `imc_class` (
  `class_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程分类ID',
  `class_name` varchar(10) NOT NULL DEFAULT '' COMMENT '分类名称',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='课程分类表'

• 确认对应插入列的插入值的清单

insert 语句示例

比如下面就指定了一列名称，并往课程分类表中插入了多条数据

INSERT INTO imc_class ( class_name )
VALUES
	( 'MySQL' ),
	( 'REDIS' ),
	( 'MongoDB' ),
	( '安全测试' ),
	( 'Oracle' ),
	( 'SQL Server' ),
	( 'Hbase' );

在创建表的时候，没有为 class_name 添加唯一索引，这里使用上节提到的创建索引语句来增加

CREATE UNIQUE
	INDEX uqx_classname ON imc_class ( class_name );
-- 创建 union 的索引，并取名为 uqx_classname ，在 imc_class 的 class_name 列上创建

唯一索引创建好之后，再执行上述的插入语句就会出现以下的提示错误信息

1062 - Duplicate entry 'MySQL' for key 'uqx_classname', Time: 0.001000s

ON DUPLICATE KEY UPDATE 示例

由于添加了唯一索引，插入失败，这个时候可以使用 ON DUPLICATE 语句吧 add_time 列的数据修改

select 语句

在 DML 中完成查询的语句是 select 语句，使用很频繁，且为复杂的一个知识点

SELECT  -- SELECT 是唯一必选的
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      SQL_NO_CACHE [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...] -- 列或表达式
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] 
      | LOCK IN SHARE MODE]]

-- 列或表达式是说：下面这样直接写值，还有一个表达式，就可以执行查询
-- 在该语句下，select 从句就如同打印语句的功能
SELECT 'hello','MySql', 2018+1

查询结果如下：
列：hello	MySql	2018+1
值：hello	MySql	2019

where 语句

查询出所有课程中包含 MYSQL 的课程的课程名称

SELECT
	title 
FROM
	imc_db.imc_course 
WHERE
	title LIKE '%mysql%'

编写简单查询语句的思路

• 确定 FROM 子句：首先确定我们要获取的数据存在哪些表中
• 确定 SELECT 子句：其次是确定我们要取现表中的哪些列
• 确定 WHERE 子句：确认是否需要对表中的数据进行过滤

下面是着重对 select 中能使用的一些知识进行讲解

MySQL 的比较运算符

• = > < >= <= <> !+： <> 和 -= 都表示不等于
• BETWEEN min AND max：列值大于等于最小值，小于等于最大值
• IS NULL、IS NOT NULL：判断列的值是否为 null
• LIKE、NOT LIKE：% 代表任何数量的字符，_ 代表任何一个字符
• IN、NOT IN：判断列的值是否在指定的值列表中

-- 列中是否包含 mysql 关键词
SELECT 'this is mysql course' LIKE '%mysql%';

-- 下划线的用法，这条语句是能查询到的
SELECT 'Mysql' LIKE '_ysql'; 

-- 如果是这样就不行了，因为只表示任意 一个字符匹配。 前面有好几个字符
SELECT 'xxxMysql' LIKE '_ysql'; 

MySQL 的逻辑运算符

• AND、&&： AND 运算符两边的表达式都为真，返回的结果才为真
• OR、||：OR 运算符两边的表达式有一条为真，返回结果就为真
• XOR：一真一假时返回真

TIP
任何运算符和 NULL 值运算结果都为 NULL，所以我们之前在定义表的时候，都定位为了非空

-- 需求：  查询出课程标题中包含 MySQL 关键词并且学习人数小于 5000，
--  			课程标题中不包含 MySQL 关键词但学习人数大于 5000 的课程
--  			显示：课程标题和学习人数

-- 方法 1： 编写两条语句，然后使用 UNION ALL 组合起来
SELECT
	title,
	study_cnt 
FROM
	imc_db.imc_course 
WHERE
	title LIKE '%MySQL%' 
	AND study_cnt < 5000 UNION ALL SELECT title, study_cnt FROM imc_db.imc_course WHERE title NOT LIKE '%MySQL%' AND study_cnt > 5000
	
-- 方法 2：使用 异或 XOR
SELECT
	title,
	study_cnt 
FROM
	imc_db.imc_course 
WHERE
	title LIKE '%MySQL%' 
	XOR study_cnt > 5000 
-- 别懵逼，记住 一真一假 为真：
-- 也就是说：要么这一条记录 「包含 MySQL 但是学习人数 小于 5000」
-- 				 要么这一条记录「不包含 MySQL 但是学习人数 大于 500」
-- 这个描述就刚好符合我们的需求

从多个表中获取数据join

前面查询都是单表查询，如何从多表中查询呢？表与表之间要存在一定的关联关系。
如下 3 张表，他们的关系就是都有课程 ID，在逻辑上来说，课程有多个章节，章节有多个小节，这就是关系

课程表：课程 ID
课程章节表：课程 ID
课程小节表：课程 ID、章节 ID

使用 JOIN 关键字来关联两张表，又分为：

• INNER JOIN 内连接：两张表的交集
• OUTER JOIN 外连接：又细分为两个
  • LEFT JOIN 左外连接：并集：以左边表为主，即使右边变没有匹配的数据，左边的数据都会出来；补集：还可以通过加 WHERE 子句（判断 B 表中必有值的数据为 null），查询只存在 A 表中，而不存在 B 表中的数据，如下面的练习，查询没有任何章节的课程
  • RIGHT JOIN 右外连接：与左外链接相反

-- 内连接练习：查询每一门课程的 课程 ID、课程名称和章节名称
SELECT
	a.course_id,
	a.title,
	b.chapter_name 
FROM
	imc_course a
	JOIN imc_chapter b ON b.course_id = a.course_id
-- 使用 join 关联两个表，使用 ON 来声明两个表中的关联关系

-- 新增一条课程数据
INSERT INTO `imc_db`.`imc_course`(`title`, `title_desc`, `type_id`, `class_id`, `level_id`, `online_time`, `study_cnt`, `course_time`, `intro`, `info`, `harvest`, `user_id`, `main_pic`, `content_score`, `level_score`, `logic_score`, `score`) VALUES ('关联测试', '关联测试', 10, 5, 1, '2020-04-20 07:26:23', 0, '00:00:00', '', '', '', 0, '', 0.0, 0.0, 0.0, 0.0);

-- 使用上面的内连接查询新插入的数据，会发现没有结果出来
SELECT
	a.course_id,
	a.title,
	b.chapter_name 
FROM
	imc_course a
	JOIN imc_chapter b ON b.course_id = a.course_id
	WHERE a.title = '关联测试'
	
-- 	可以使用外链接来查询到，即使该课程没有任何章节
SELECT
	a.course_id,
	a.title,
	b.chapter_name 
FROM
	imc_course a
	LEFT JOIN imc_chapter b ON b.course_id = a.course_id
	WHERE a.title = '关联测试'
	
-- 左外连接还有一个查询技巧：就是查询没有任何章节的课程
-- 上面演示的 SQL 由于我知道这条数据没有章节，如果去掉就会显示还包含有章节的课程了
-- 这里只要加上 where 条件，判定章节中的必有字段为 null 即可
SELECT
	a.course_id,
	a.title,
	b.chapter_name 
FROM
	imc_course a
	LEFT JOIN imc_chapter b ON b.course_id = a.course_id
	WHERE b.course_id IS NULL
-- 响应的结果
course_id	| title |	chapter_name
101				|	关联测试 |  null
-- 此种方式，比使用 not in（子查询） 来达成，性能要好

group by、having 分组语句

GROUP BY...HAVING 子句的作用：

• 把结果集按某些列分成不同的组，并对分组后的数据进行聚合操作
• 可以通过 having 对分组后的数据进行过滤，where 语句只能用在分组前

--范例 1： 查询每个难度的课程有多少门课程
SELECT
	b.level_name,  -- 分组键可以出现在 select 语句中，非分组键则不能
	count( * )  -- 还可以包含一个或多个聚合函数
FROM
	imc_course a
	JOIN imc_level b ON a.level_id = b.level_id 
GROUP BY
	b.level_name 
	
-- 查询响应数据	
level_name	count( * )
入门	22
初级	28
高级	24
中级	27

-- 如果非分组列出现在了 select 中则会出现  sql_mode=only_full_group_by 类似的错误信息
-- 这是因为 group by 的使用时受 sql_mode 的影响，可以查看当前的 sql_mode 值
SHOW VARIABLES  LIKE 'sql_mode'

-- 从响应的数据中可以看到：的确有 ONLY_FULL_GROUP_BY 的配置
Variable_name	Value
sql_mode	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

对分组后的语句进行过滤

-- 统计每个分类下课程大于 3 门的难度有哪些
SELECT
	b.class_name,
	c.level_name,
	count( * ) 
FROM
	imc_course a
	JOIN imc_class b ON b.class_id = a.class_id
	JOIN imc_level c ON c.level_id = a.level_id 
GROUP BY
	b.class_name,
	c.level_name 
HAVING
	count( * ) > 3   -- 在分组后，进行过滤

-- 响应数据
class_name	level_name	count( * )
Hbase	初级	4
Oracle	初级	4
Hbase	高级	5
运维	高级	4
MySQL	高级	4
Redis	初级	4
MongoDB	入门	4
HadoopDock	中级	4
Docker	中级	4
运维	中级	4

常用的聚合函数

• count(*)、count(col)：统计函数* 表示的这一行数据，也可以选择具体的列，因为有些列有值或则没有值，和具体的聚合函数的用途有关
• sum(col_name)：累加函数计算表中符合条件的数值列的合计值，此列需要是数值类型
• avg(cole_name)：平均计算表中符合条件的数值列的平均值（就是 sum/count）
• MAX(cole_name)：计算表中符合条件的任意列中数据的最大值
• MIN(cole_name)：计算表中符合条件的任意列中数据的最小值

count统计范例

-- 统计函数 范例：统计课程表的总课程数量 与 课程中老师的数量
SELECT
	count( course_id ),  -- 统计课程 ID,就是课程数量
	count( DISTINCT user_id ) -- 统计该课程老师数量，因为一个老师可以负责多个课程，需要去重
FROM
	imc_course
	
-- 响应数据
count( course_id )	count( DISTINCT user_id )
101	6

avg范例

-- 一个复杂点的 sql：利用课程评价表中的评分，更新课程表中的课程的评分
-- 课程评价表中的数据是每个用户对课程的评分
-- 课程表中的评分是需要 课程评价表中的评分的平均值，作为一个综合评分

对于复杂的语句，我们需要分段来写 sql，先实现一段，再继续另一端，再合并起来，这里还没有学习更新语句，后半段就先留着后续来补充

-- 查询：统计每门课程的平均分
SELECT
	course_id,
	avg( content_score ) AS content_score,
	avg( level_score ) AS level_score,
	avg( logic_score ) AS logic_score,
	avg( score ) AS avg_score 
FROM
	imc_classvalue 
GROUP BY
	course_id
	
-- 响应数据
course_id	content_score	level_score	logic_score	avg_score
8	8.98000	9.14000	8.58000	0.00000
17	9.10000	9.10000	9.45000	0.00000
59	8.85000	8.90000	8.82500	0.00000

max范例

-- 查询出学习人数最多的课程
SELECT
	title 
FROM
	imc_course 
WHERE
	study_cnt = ( SELECT MAX( study_cnt ) FROM imc_course )
-- 需要分两步查询出来
-- 第一步：先查询出来人数最多的列，就是这个字查询，会显示出人数
-- 第二步：以该人数为条件，查询出对应的课程信息

order by排序

每个存储引擎默认的排序方式是不同的，InnoDB默认是使用主键的顺序进行排序。那么使用order by子句是对查询结果进行排序的最安全的方法。

-- 查询出每门课程的学习人数，并按学习人数从高到底排列
SELECT
	title,
	study_cnt 
FROM
	imc_course 
ORDER BY
	study_cnt DESC
	-- ORDER BY 2 DESC 还可以使用 slect 列 的位置来引用排序
	
-- 响应数据
title	study_cnt
PostgreSQL课程-64209	9939
SQLServer课程-68633	9731
MySQL课程-86475	9716
PostgreSQL课程-33460	9588

Limit限制返回结果集行数

常用语数据列表分页，一定要和 order by 子句配合使用，是因为：由于分页过程中每次分页查询都会重复的执行查询语句，所以要保证排序顺序是一样的，否则就可能出现错误的数据
语法：limit 起始偏移量,结果集的行数

SELECT
	course_id,
	title
FROM
	imc_course 
ORDER BY study_cnt DESC
LIMIT 0,10  -- 起始数据从 0 开始，返回 10 行数据
						-- 如果是第 2 页的数据，则是 limit 10,10

创建视图

视图中不存储数据

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement  -- 我们熟悉的查询语句
    [WITH [CASCADED | LOCAL] CHECK OPTION]

-- 定义一个包含课程 ID、课程名称、课程分类、课程方向以及课程难度的视图
-- 第一步：先写出这些数据的查询语句
SELECT
	a.course_id,
	a.title,
	b.class_name,
	c.type_name,
	d.level_name 
FROM
	imc_course a
	JOIN imc_class b ON b.class_id = a.class_id
	JOIN imc_type c ON c.type_id = a.type_id
	JOIN imc_level d ON d.level_id = a.level_id
-- 响应数据
course_id	title	class_name	type_name	level_name
1	MySQL课程-79889	大数据	算法&数学	入门
2	MySQL课程-69546	Hbase	云计算&大数据	初级
3	PostgreSQL课程-43917	大数据	前端开发	高级
...

-- 定义视图,取名 vm_course
CREATE VIEW vm_course AS 
SELECT
  a.course_id,
  a.title,
  b.class_name,
  c.type_name,
  d.level_name 
FROM
	imc_course a
	JOIN imc_class b ON b.class_id = a.class_id
	JOIN imc_type c ON c.type_id = a.type_id
	JOIN imc_level d ON d.level_id = a.level_id
	
-- 查询视图
SELECT * FROM  vm_course
-- 可以看到，视图就类似一章虚拟表，把我们编写的 sql 语句保存起来了的感觉

delete按条件删除数据

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

删除语法中也有where等语句，与select中的效果类似

-- 范例 1：删除课程表中没有章节信息的课程
-- 第一步：先写出没有章节信息的课程信息
SELECT
	* 
FROM
	imc_course a
	LEFT JOIN imc_chapter b ON a.course_id = b.course_id 
WHERE
	b.course_id IS NULL
	
-- 第二步：使用 delete 语法
DELETE a -- 删除哪个表中的数据，因为下面关联了两张表
FROM
	imc_course a
	LEFT JOIN imc_chapter b ON a.course_id = b.course_id 
WHERE
	b.course_id IS NULL

-- 范例 2：删除课程方向表中重复的课程方向，并保留方向 ID 最小的一条，并在方向名称上增加唯一索引
-- 01. 首先查看 方向表 的结构
SHOW CREATE TABLE imc_type;
-- 响应的结构
CREATE TABLE `imc_type` (
  `type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程方向ID',
  `type_name` varchar(10) NOT NULL DEFAULT '' COMMENT '课程方向名称',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '填加时间',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='课程方向表'
-- 可以看到 type_name 上没有唯一索引，所以会导致重复的数据进入

-- 02. 先查看下有哪些数据是重复的（注意，前面给出的数据表，里面没有重复的数据，需要自己手动新增几条）
SELECT
	type_name,
	count( * ) 
FROM
	imc_type 
GROUP BY
	type_name 
HAVING
	count( * ) > 1
	
-- 03. 找出保留 id 最小的数据
SELECT
	type_name,
	count( * ),
	min( type_id ) AS min_type_id   -- 由于分组后相同的只会出现一条数据，直接追加最小值就是正确的
FROM
	imc_type 
GROUP BY
	type_name 
HAVING
	count( * ) > 1
-- 响应的数据
type_name	count( * )	min_type_id
数据库	2	8
游戏	2	10

-- 04. 找出要删除的数据，使用子查询
SELECT
	* 
FROM
	imc_type AS a
	JOIN ( SELECT type_name, count( * ), min( type_id ) AS min_type_id FROM imc_type GROUP BY type_name HAVING count( * ) > 1 ) AS b ON a.type_name = b.type_name 
	AND a.type_id > b.min_type_id
-- 响应的数据，可以看到查询出来的都是比 min_type_id 大的数据
type_id	type_name	add_time	type_name(1)	count( * )	min_type_id
11	游戏	2020-04-20 10:39:12	游戏	2	10
12	数据库	2020-04-20 10:39:36	数据库	2	8

-- 05. 最终形成的删除数据
DELETE a 
FROM
	imc_type AS a
	JOIN ( SELECT type_name, count( * ), min( type_id ) AS min_type_id FROM imc_type GROUP BY type_name HAVING count( * ) > 1 ) AS b ON a.type_name = b.type_name 
	AND a.type_id > b.min_type_id
	
-- 06. 创建唯一索引
CREATE UNIQUE INDEX uqx_typename ON imc_type(type_name)

update修改语句

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]  -- 过滤出需要修改的数据
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

对于 order by 和 limit 的参数一般不常用，但是可以用在大批量更新的场景：比如要更新 10 万行数据，如果一次性全部更新，可能会导致主从复制延迟的问题，所以可以采用他们来实现一定顺序的分页更新，每次只更新一万行

编写update语句的思路

• 确定要更新的数据存储在哪张表中？：update 子句通常可以先写出一个 select 语句，查找出来要修改的数据，然后再修改为 update 语句
• 确定要更新的列与值：set 语句
• 确认更新数据的条件：where 语句

常用更新语句实例

范例1：冻结用户的账户：冻结昵称为沙占的用户

-- 先写查询语句
SELECT
	user_nick,
	user_status 
FROM
	imc_user 
WHERE
	user_nick = '沙占'
-- 改写成 update 语句

UPDATE imc_user 
SET user_status = 0 
WHERE
	user_nick = '沙占'

范例 2：随机推荐 10 门课程

-- 给该表新增一列，是否推荐的列，由于真实的推荐是一个很复杂的计算的出来的结果，这里简单的模拟下
-- 使用修改表的语句 alter 来新增一列
ALTER TABLE imc_course ADD is_recommand TINYINT DEFAULT 0 COMMENT '是否推荐，0 不推荐，1 推荐';

-- 随机查询出 10 条语句
SELECT
	course_id 
FROM
	imc_course 
ORDER BY RAND( )   -- 使用了 rand 函数来排序
	LIMIT 10

-- 改写成 update 语句
UPDATE imc_course 
SET is_recommand = 1 
ORDER BY
	RAND( ) 
	LIMIT 10;
	
-- 查询刚刚推荐的数据
SELECT course_id,title from imc_course WHERE is_recommand = 1

范例3：完成前面svg里面剩下的sql作业，更新：

-- 一个复杂点的 sql：利用课程评价表中的评分，更新课程表中的课程的评分
-- 课程评价表中的数据是每个用户对课程的评分
-- 课程表中的评分是需要 课程评价表中的评分的平均值，作为一个综合评分

-- 查询：统计每门课程的平均分
SELECT
	course_id,
	avg( content_score ) AS content_score,
	avg( level_score ) AS level_score,
	avg( logic_score ) AS logic_score,
	avg( score ) AS avg_score 
FROM
	imc_classvalue 
GROUP BY
	course_id

-- 改成更新语句
UPDATE imc_course a
JOIN (
	SELECT
		course_id,
		avg( content_score ) AS content_score,
		avg( level_score ) AS level_score,
		avg( logic_score ) AS logic_score,
		avg( score ) AS avg_score 
	FROM
		imc_classvalue 
	GROUP BY
		course_id 
	) b ON a.course_id = b.course_id 
	SET a.content_score = b.content_score,
	a.level_score = b.level_score,
	a.logic_score = b.logic_score,
	a.score = avg_score

系统常用函数

使用系统函数能大大提高我们的工作效率，将原来在程序中完成的工作，可以由MySql的函数来完成。

常见的时间函数

当前日期：CURDATE、CURTIME、NOW

• CURDATE()、CURTIME()：返回当前日期/返回当前时间
• NOW()：返回当前日期时间

SELECT
	CURDATE( ),
	CURTIME( ),
	NOW( )
-- 响应的数据
CURDATE( )	CURTIME( )	NOW( )
2020-04-20	11:51:22	2020-04-20 11:51:22

日期格式化：DATE_FORMAT

DATE_FORMAT(date,fmt)：按照 fmt 的格式，对日期 date 进行格式化
常见的fmt格式有：

• %Y：四位的年
• %m：月份，00~12
• %d：天，00~31
• %H：小时，00~24
• %i：分钟，00~59
• %s：秒， 00~59

SELECT
	DATE_FORMAT( NOW( ), '%Y-%m-%d %H:%i:%s' )

-- 响应数据
DATE_FORMAT( NOW( ), '%Y-%m-%d %H:%i:%s' )
2020-04-20 11:59:57

秒小时互转：

• SEC_TO_TIME(seconds)：把秒数转换为 小时:分:秒
• TIME_TO_SEC(time)：把时间 小时:分:秒 转换为秒数

他们是一对相反的函数

SELECT
	SEC_TO_TIME( 3600 ),
	TIME_TO_SEC( '1:00:00' )

-- 响应数据
SEC_TO_TIME( 3600 )	TIME_TO_SEC( '1:00:00' )
01:00:00	3600

返回两个日期相差的天数

DATEDIFF(date1,date2):返回两个日期相差的天数

-- 计算每门课程，上线时间距离当前时间的天数
SELECT
	title,
	DATEDIFF( now( ), online_time ) 
FROM
	imc_course

-- 响应的数据
title	DATEDIFF( now( ), online_time )
MySQL课程-88343	625
PostgreSQL课程-42349	496
PostgreSQL课程-69538	708

日期加减：DATE_ADD

DATE_ADD(date,INTERVAL expr unit)：对给定的日期增加或减少指定的时间单元（unit）:

• DAY ：天
• HOUR：小时
• MINUTES：分钟
• SECOND：秒

SELECT
	NOW( ),
	DATE_ADD( NOW( ), INTERVAL 1 DAY ),-- 当前时间加 1 天
	DATE_ADD( NOW( ), INTERVAL 1 YEAR ),-- 当前时间加 1 年
	DATE_ADD( NOW( ), INTERVAL - 1 DAY ),-- 当前时间减 1 天
	DATE_ADD( NOW( ), INTERVAL '1:30' HOUR_MINUTE ) -- 当前时间减 1 小时 30 分
-- 响应数据
NOW( )	DATE_ADD( NOW( ), INTERVAL 1 DAY )	DATE_ADD( NOW( ), INTERVAL 1 YEAR )	DATE_ADD( NOW( ), INTERVAL - 1 DAY )	DATE_ADD( NOW( ), INTERVAL '1:30' HOUR_MINUTE )
2020-04-20 12:36:14	2020-04-21 12:36:14	2021-04-20 12:36:14	2020-04-19 12:36:14	2020-04-20 14:06:14

日期提取：EXTRACT

EXTRACT(unit FROM date)：返回日期date的指定部分

-- 提取出年份
SELECT EXTRACT(YEAR FROM '2019-07-02');
        -> 2019
-- 提取出 年月 
SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
        -> 201907
-- 提取出分钟
SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03');
        -> 20102
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
        -> 123

UNIX_TIMESTAMP

• UNIX_TIMESTAMP(): 返回 unix 时间戳
• FROM_UNIXTIME()：把 unix 时间戳转换为日期时间

现在不建议使用他们了。

常用的字符串函数

符串连接 CONCAT & CONCAT_WS

• CONCAT(str1,str2,...)：把字符串 str1.. 连成一个字符串
• CONCAT_WS(sep,str1,str2..)：用指定的分隔符 sep 链接字符串

-- 出于 seo 优化的目的，需要合并显示课程分类名称和课程标题
SELECT
	CONCAT_WS( ',', b.class_name, a.title ) 
FROM
	imc_course a
	JOIN imc_class b ON a.course_id = b.class_id

-> MySQL,MySQL课程-79889
-> Redis,MySQL课程-69546

字符串长度：CHAR_LENGTH

• CHAR_LENGTH(str)：返回字符串 str 的字符个数
• LENGTH(STR)：返回字符串 str 的字节个数

SELECT
	LENGTH( 'word hello' ),
	CHAR_LENGTH( 'word hello' ),
	LENGTH( 'my 数据库教程' ),
	CHAR_LENGTH( 'my 数据库教程' )

-> 10	10	18	8
-- 可以看到字节和字符在中英字符下还是相差挺多的

数字式化：FORMAT

FORMAT(X,D[,locale])：将数字 N 格式化为格式，如 #,###,###.###，并舍如到 D 位小数

SELECT
	FORMAT( 123456.789, 4 )
	
-> 123,456.7890			-- 格式化为 4 位小数，不够的用 0 补齐

字符串截取：LEFT/RIGHT

• LEFT(str,len)：从字符串的左边起返回 len 长度的子字符串
• RIGHT（str,len）：从右边起

SELECT LEFT
	( 'www.baidu.com', 3 ),
	RIGHT ( 'www.baidu.com', 3 )
	
-> www	com

字符串截取：SUBSTRING

SUBSTRING（str,pos[,len]）：从字符串 str 的 pos 位置起返回长度为 len 的子串

SELECT
	SUBSTRING( 'www.baidu.com', 1 ),
	SUBSTRING( 'www.baidu.com', 1, 3 )

-> www.baidu.com	www
-- 位置是正常的正数，不像 JAVA 中的是从 0 开始，还包含头不包含尾。

按分隔符截取：SUBSTRING_INDEX

SUBSTRING_INDEX(str,delim,count)：按 delim 分割字符串 str 后，取 count 个子字串

SELECT
	SUBSTRING_INDEX( 'www.baidu.com', '.', 2 ),  -- 正数：从左边起 2 个
	SUBSTRING_INDEX( 'www.baidu.com', '.', - 2 ) -- 负数：从右边起 2 个
	
-> www.baidu	baidu.com

查找字符串出现的位置：LOCATE

LOCATE(substr,str)：在字符串 str 中返回子串 substr 第一次出现的位置

SELECT
	LOCATE( 'b', 'www.baidu.com' ),  -- b 是第 5 个字符
	LOCATE( 'w', 'www.baidu.com' )
	
-> 5	1

-- 截取课程标题里中横线之前的部分
-- 使用两个函数进行配合

SELECT
	title,
	LOCATE( '-', title ),
	SUBSTRING( title, 1, LOCATE( '-', title ) - 1 ) 
FROM
	imc_course

-> MySQL课程-04045	8	MySQL课程
-- 当然，还可以使用前面提到过的 SUBSTRING_INDEX 等函数

两段删除/去空格 TRIM

SELECT TRIM([remstr FROM] str)：从字符串 str 两端删除不需要的字符 remstr，常见用来删除字符串两端的空格

SELECT
	TRIM( '     mrcode  ')
	
-> mrcode

SELECT
	TRIM( 'x' FROM 'xxmrcodexx' )

-> mrcode

其他常用函数

• ROUND（X,D）：对数值 x 进行四舍五入保留 D 位小数
• RAND（）：返回一个在 0 和 1 之间的随机数前面使用该函数用在了 order by 中，这个是不建议的，因为在大量数据的表中，性能特别差

数据流控制:CASE WHEN...THEN

用于实现其他语言中的case.. when功能，提供数据流控制，语法如下：

CASE WHEN [condition]
	 THEN result
	[WEEN [condition] THEN resule ...] 
	[ELSE resule] 
END

由于是用函数实现的，可以出现在 form 、where 等语句中

-- 显示每个用户的昵称与性别
SELECT
	user_nick,
	sex,
CASE
		
		WHEN sex = 1 THEN
		'男' 
		WHEN sex = 0 THEN
		'女' 
	END AS '性别'  - 取
FROM
	imc_user

->
  user_nick	sex	性别
  沿舟侨	1	男
  窦貌	0	女
 
-- 显示每个用户的昵称与性别，把 case 语句用在 where 子句中
SELECT
	user_nick,
	sex,
CASE
		WHEN sex = 1 THEN
		'男' 
		WHEN sex = 0 THEN
		'女' 
	END AS '性别' 
FROM
	imc_user 
WHERE
CASE
		WHEN sex = 1 THEN
		'男' 
		WHEN sex = 0 THEN
	'女' 
	END = '男'  -- 只查询男

MD5函数

MD5(str)：返回 str 的 MD5 值

SQL高级特性

公用表表达式：CTE(WITH)

前面说到子查询，但是没有仔细说，是因为出现了更好的替代方式，就是公用表表达式 CTE（Common Table Expressions）

• MySQL 8.0 之后版本可用
• CTE 生成一个命名临时表，并且只在查询期间有效
• 与子查询的区别：CTE 临时表在一个查询中可以多次引用及自引用

WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
SELECT  * FROM cte_name

WITH cte (col1, col2) AS   -- 这里给列取名
(		-- 这里组装了一个临时表一共两列
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte; -- 下面用 select 语句从 cte 里面查询数据

->
  col1	col2
  1	2
  3	4

使用课程数据来练习

WITH cte AS(
SELECT  -- 查询学习人数大于 2000 人
	title,
	study_cnt,
	class_id 
FROM
	imc_course 
WHERE
	study_cnt > 2000
)
SELECT * FROM cte
UNION ALL				-- 它可以被多次引用，这里再次使用
SELECT * FROM cte
ORDER BY title -- 为了看着方便，按 title 排序就会出现两条一模一样的数据

->
  title	study_cnt	class_id
  MySQL课程-04045	6646	2
  MySQL课程-04045	6646	2

用 CTE 递归生成序列

WITH RECURSIVE test as (  -- 使用 RECURSIVE 声明可以递归调用自己
	SELECT 1 AS n	
	UNION ALL
	SELECT 1+n FROM test    -- 这里引用了自身
		WHERE n < 10  -- 当 n 大于 10 就退出递归调用
)
SELECT * FROM test

-> 
  n
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

这个递归，不太好想明白逻辑

-- 递归查询课程评论信息
-- 01. 先查看评论表的数据结构
SELECT * FROM imc_question WHERE replyid > 0

-> 
  quest_id	user_id	course_id	chapter_id	sub_id	replyid	quest_title	quest_content	quest_type	view_cnt	add_time
  2111	3080	5	55	387	788	用户:3080对于 用户:1217关于 PostgreSQL课程-42349第 9章第 2节的评论的	用户:3080对于 用户:1217关于 PostgreSQL课程-42349第 9章第 2节的评论的回复	评论	47	2019-01-28 18:10:44
  2112	25	54	695	5105	873	用户:25对于 用户:2755关于 MySQL课程-21930第 6章第 3节的评论的回复	用户:25对于 用户:2755关于 MySQL课程-21930第 6章第 3节的评论的回复	评论	1	2019-01-28 18:10:44
 
可以看到 quest_id=2111 是对 replyid= 788 的一个回复

-- 要安装树形结构查询出数据

-- 02. 先找到这门课程的所有的问题
SELECT
	* 
FROM
	imc_question 
WHERE
	course_id = 59 
	AND replyid = 0  -- 回复 ID 等于 0 的，说明是一个问题
	
-- 03. 最终语句
WITH RECURSIVE replay ( quest_id, quest_title, user_id, replyid, path ) AS (
	SELECT-- 查询出所有的问答，没有回复的
	quest_id,
	quest_title,
	user_id,
	replyid,
	cast( quest_id AS CHAR ( 200 ) ) AS path 
	FROM
		imc_question 
	WHERE
		course_id = 59 
		AND replyid = 0
  UNION ALL-- 下面开始递归查找他们的评论
	SELECT
		a.quest_id,
		a.quest_title,
		a.user_id,
		a.replyid,
		CONCAT( b.path, ' >> ', a.quest_id ) AS path 
	FROM
		imc_question a -- a 表的数据是 b 表的回复
		JOIN replay b ON a.replyid = b.quest_id 
	) 
SELECT
	* 
FROM
	replay

-> 可以看到：path 字段，表示了一个层级了
quest_id	quest_title	user_id	replyid	path
...
1866	用户:3002关于 MySQL课程-98454第 15章第 1节的评论	3002	0	1866
1888	用户:324关于 MySQL课程-98454第 11章第 6节的问答	324	0	1888
2246	用户:2991对于 用户:324关于 MySQL课程-98454第 4章第 2节的问答的回复	2991	1471	1471 >> 2246
2257	用户:2832对于 用户:2214关于 MySQL课程-98454第 10章第 2节的问答的回复	2832	1379	1379 >> 2257
2320	用户:2476对于 用户:2832对于 用户:2214关于 MySQL课程-98454第 10章第 	2476	2257	1379 >> 2257 >> 2320

窗口函数

一组记录，符合记录的每一条记录都会执行这个函数，不会改变记录的函数。

-- 窗口函数的基本语法
function name([exp])
OVER(    -- 指定了窗口的范围
	[PARTITION BY exp [,...]]  -- 把查询的结果集分成大小不同的分组
  [ORDER BY exp [ASC|DESC] [,...]]  -- 按照哪些字段排序，按照排序后的记录进行编号
)

排名函数：RANK、DENSE_RANK

• 聚合函数：比如 avg、count 等函数都可以作为窗口函数使用
• ROW_NUMBER（）：返回窗口分区内数据的行号
• RANK()：类似与 row_number ，只是对于相同数据会产生重复的行号，之后的数据行号会产生间隔比如：两个数据并列第一，那么下一个数据的行号为 3
• DENSE_RANK（）：类似于 rank ，区别在于当组内某行数据重复时，虽然行号会重复，但后续的行号不会产生间隔比如：两个数据并列第一，那么下一个数据的行号为 2

下面使用一个例子来查看 row_number、rank、dense_rank 的区别

-- 使用 cte 定义一张临时表出来
WITH test(study_name,class_name,score) AS (
	SELECT 'sqlercn','MySQL',95
  UNION ALL
  SELECT 'tom','MySQL',99
  UNION ALL
  SELECT 'Jerry','MySQL',99
  UNION ALL
  SELECT 'Gavin','MySQL',98
  UNION ALL
  SELECT 'sqlercn','PostGreSQL',99
  UNION ALL
  SELECT 'tom','PostGreSQL',99
  UNION ALL
  SELECT 'Jerry','PostGreSQL',98
)
SELECT * FROM test

定义出来的临时表就是学生的学习课程分表数据，执行结果如下

study_name	class_name	score
sqlercn	MySQL	95
tom	MySQL	99
Jerry	MySQL	99
Gavin	MySQL	98
sqlercn	PostGreSQL	99
tom	PostGreSQL	99
Jerry	PostGreSQL	98

下面使用 row_number、rank、dense_rank 查询数据，然后看他们的不同

WITH test(study_name, class_name, score) AS (
    SELECT 'sqlercn', 'MySQL', 95
    UNION ALL
    SELECT 'tom', 'MySQL', 99
    UNION ALL
    SELECT 'Jerry', 'MySQL', 99
    UNION ALL
    SELECT 'Gavin', 'MySQL', 98
    UNION ALL
    SELECT 'sqlercn', 'PostGreSQL', 99
    UNION ALL
    SELECT 'tom', 'PostGreSQL', 99
    UNION ALL
    SELECT 'Jerry', 'PostGreSQL', 98
)
SELECT study_name,
       class_name,
       score,
       row_number() over (
           partition by class_name order by score desc
           ) as rw,
       rank() over (
           partition by class_name order by score desc
           ) as rk,
       dense_rank() over (
           partition by class_name order by score desc
           ) as dr
FROM test
order by class_name, rw
-- 上面三个窗口函数都使用 class_name 来分组，并按照 score 进行降序

查询结果如下

study_name	class_name	score	rw	rk	dr
tom	MySQL	99	1	1	1
Jerry	MySQL	99	2	1	1
Gavin	MySQL	98	3	3	2
sqlercn	MySQL	95	4	4	3
sqlercn	PostGreSQL	99	1	1	1
tom	PostGreSQL	99	2	1	1
Jerry	PostGreSQL	98	3	3	2

首先上面查询语句的窗口函数功能都是先按照课程名称进行分组，然后在组中按分数进行降序。下面一个一个解说：

• rw：tom 和 Jerry 的分数都是 99，但是它的行号都是按照顺序排列的
• rk：tom 和 Jerry 的分数都是 99，他们并列第一，下一个行号中断了，从 3 开始
• dr：tom 和 Jerry 的分数都是 99，他们并列第一，下一个行号没有中断，从 2 开始

业务练习

范例 1：按学习人数对课程进行排名，并列出每类课程学习人数排名前 3 的课程名称，显示学习人数以及名次

-- 先查询出按学习人数对课程进行排名的课程
select ic.class_name,
       a.title,
       a.study_cnt,
       rank() over (
           partition by class_name order by study_cnt desc
           ) as rk
from imc_course a
         join imc_class ic on a.class_id = ic.class_id

结果如下，下面是一部分数据

class_name	title	study_cnt	rk
Docker	PostgreSQL课程-33460	9588	1
Docker	SQLServer课程-90092	6818	2
Docker	SQLServer课程-41163	6252	3
Docker	SQLServer课程-60895	4585	4
Docker	MySQL课程-57732	3562	5
Docker	PostgreSQL课程-85484	3104	6
Docker	PostgreSQL课程-55484	551	7
Docker	PostgreSQL课程-27822	192	8
HadoopDock	SQLServer课程-09426	6529	1
HadoopDock	PostgreSQL课程-71352	6100	2
HadoopDock	PostgreSQL课程-00924	3819	3

可以看到 Docker 分类就有 8 个课程，我们要求是只要每类课程排名前 3 的课程

with tmp as (
    select ic.class_name,
           a.title,
           a.study_cnt,
           rank() over (
               partition by class_name order by study_cnt desc
               ) as rk
    from imc_course a
             join imc_class ic on a.class_id = ic.class_id
  	-- 在这里无法使用 where rk  <= 3，语法错误
)
select *
from tmp
where rk <= 3
-- 需要使用公共表达式搞成一个临时表

输出结果如下

class_name	title	study_cnt	rk
Docker	PostgreSQL课程-33460	9588	1
Docker	SQLServer课程-90092	6818	2
Docker	SQLServer课程-41163	6252	3
HadoopDock	SQLServer课程-09426	6529	1
HadoopDock	PostgreSQL课程-71352	6100	2
HadoopDock	PostgreSQL课程-00924	3819	3

范例2：每门课程的学习热数占本类课程总学习人数的百分比

with tmp as (
-- 再次提示，这里出来的 class_total 不能直接使用，转成公共表
    select class_name, title, study_cnt, 
    sum(study_cnt) over (partition by class_name) as calss_total
    from imc_course ic
             join imc_class i on ic.class_id = i.class_id)
select *, concat(study_cnt / calss_total * 100, '%') as percentage
from tmp
-- 以上关联 class 表是为了获取它的名称
-- 重点是 sum 与 over 一起使用， over 分组，然后 sum 统计该分组

数据如下

class_name	title	study_cnt	calss_total	percentage
Docker	PostgreSQL课程-85484	3104	34652	8.9576%
Docker	PostgreSQL课程-33460	9588	34652	27.6694%
Docker	SQLServer课程-41163	6252	34652	18.0422%
Docker	PostgreSQL课程-55484	551	34652	1.5901%
Docker	SQLServer课程-60895	4585	34652	13.2316%
Docker	PostgreSQL课程-27822	192	34652	0.5541%
Docker	MySQL课程-57732	3562	34652	10.2793%
Docker	SQLServer课程-90092	6818	34652	19.6756%
HadoopDock	PostgreSQL课程-00924	3819	21755	17.5546%
HadoopDock	SQLServer课程-63911	1498	21755	6.8858%
HadoopDock	SQLServer课程-52859	3513	21755	16.1480%

可以看到，每一条数据上都会出现分组统计的总数量，相同分类的数量还都是一致的。再以公共表为基础进行除法运算

总结

在SQL中开发中容易犯的错误

• 使用 count(*) 判断是否存在符合条件的数据可以使用 SELECT ... LIMIT 1 来，因为有优化注意：笔者这里没有 get 到点，到底说的是怎么判断？
• 在执行一个更新语句后，使用查询方式判断此更新语句是否有执行成功可以使用 ROW_COUNT() 函数判断修改的行数
• 试图在 ON 条件中过滤不满足条件的记录

-- 查询出分类 iD = 5 的课程名称和分类名称
select ic.class_id, i.title, ic.class_id
from imc_class ic
         left join -- 这里使用了左外连接
         imc_course i on ic.class_id = i.class_id 
         and i.class_id = 5 -- 并在 ON 后面使用了 and 过滤
-- 这里其实是一个坑，左外连接，左边全都出来，这里判定了一个右表的数据，所以就不满足我们的要求了

  
  | class\_id | title | class\_id |
  | :--- | :--- | :--- |
  | 5 | MySQL课程-04097 | 5 |
  | 5 | MySQL课程-84741 | 5 |
  | 5 | SQLServer课程-84646 | 5 |
  | 5 | SQLServer课程-73954 | 5 |
  | 5 | MySQL课程-56069 | 5 |
  | 5 | PostgreSQL课程-49682 | 5 |
  | 5 | MySQL课程-53657 | 5 |
  | 1 | NULL | 1 |
  | 2 | NULL | 2 |
  | 3 | NULL | 3 |
  
  可以看到，不为 5 的也会出现，但是只有为 5 的 title 有数据，其他的没有是因为生效的是右边的过滤数据。右边都过滤掉了，这行数关联的部分就不会显示出来了
  
  **避免这种误操作的情况：**在 where 中过滤
  
- 在使用 IN 进行子查询的判断时，在列中未指定正确的表名

  如：`select A1 from A where A1 in (select A1 from B)`，这时尽管 B 中并不存在 A1 列的数据，也不会报错，而是会列出 A 表中的所有数据。

  ```sql
  -- 查询课程名称在 imc_user 中存在的 title；我们知道 在 imc_user 中根本就没有 title
  select * from imc_course where title in (select title from imc_user)

这个错误不太好查找，因为不会报错。
避免：使用表名来限定列名称，如imc_user.title这就会报错了，该表中的确没有这个列

• 对于表中定义的具有 not null 和 default 值的列，在插入数据时直接插入 NULL 值

知识点回顾

• 如何使用 DCL 语句来管理数据库的访问
• 如何使用 DDL 语句来建立数据库对象
• 如何使用 DML 语句来操作数据库中的数据
• MySQL 常用的系统函数
• MySQL 8.0 新增的通用表达式和窗口函数
  • 通用表达式：代替子查询
  • 窗口函数：适合用在统计分析类的场景，提高效率
• 在 SQL 开发中容易犯的错误

SQL优化

【专家视角】揭开SQL优化神秘面纱【适用于升职加薪】
武以快为尊。同理，快速高效工作，同样的工作时长，却创造更多企业价值，凸显个人价值，才能立于不败之地。 本章将从专家的视角，为你揭开SQL优化神秘面纱，解锁SQL优化的升职加薪技能。让你在工作中比别人技高一筹， 助你在工作中对SQL优化，慢查询优化能有独到的企业级解决方案，为你的高薪保驾护航。...
笔者总结，本章实际讲解：讲解慢查询如何配置、如何分析、怎么使用 explain 查看 SQL 执行计划。

优化SQL的一般步骤

• 发现问题
• 分析执行计划
• 优化索引
• 改写SQL：如果索引优化解决不了，就考虑改写另外的SQL
• 数据库垂直切分：如果上述都无法解决，那么就需要考虑分库分表了
• 数据库水平切分

常见问题发现渠道

• 用户主动上报应用性能问题：被动
  • 一般是用户发现某些功能发现问题或则缓慢
• 分析慢查询日志发现存在问题的SQL
  • 可以指定一定的实际阀值，超过该值的SQL将会被主动记录在慢查询日志中
• 数据库实时监控长时间运行的SQL
  • 比如数据库压力徒增，需要实时监控有问题的SQL

配置慢查询日志

可以通过以下语句动态开关

set global slow_query_log = [ON | OFF]

可以通过以下语句指定慢查询日志的位置，如果不指定则默认会写在 data 目录下

set global slow_query_log_file = /sql_log/slowlog.log

通过如下语句设置条件，满足此条件则记录

-- 设置超过多少时间的查找则记录
-- 设置为 0 则记录有的查询
set global long_query_time = xxx.xxxx 秒 
-- 设置未使用索引的语句记录
set global log_queries_not_using_indexes = [No | OFF]

分析MySQL慢查询日志

当条件设置得比较宽泛时，会产生很多的慢查询日志SQL，就需要借助工具来分析了：

• mysqldumpslow [OPTS...] [LOGS...] ：mysq 官网提供一般把日志文件作为参数
• pt-query-digest [OPTIONS][FILES][DSN]：更加好用的一款工具，是 Percona toolkit 工具包中的

他们都有把多次出现的 SQL 抽象为一个 SQL 来分析，大大提高和简化了分析

Percona toolkit 的使用

Percona toolkit，在下载页面中选择 percona-toolkit-3.2.0-1.el7.x86_64.rpm
在存 储库页面 中找到存储库的安装方式

[root@study local]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@study local]# yum install percona-toolkit-3.2.0-1.el7.x86_64.rpm 
# 上述下载的包如果安装不了，就只能在线安装了
[root@study local]# yum install percona-toolkit

配置MySQL慢查询日志

-- 先查询目前的时间
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
-- 我们本机开发呢，设置为 0 记录是由的 sql
-- 工作中，一般设置为 0.001 秒
mysql> set global long_query_time = 0;
-- 查询慢查询日志位置
mysql> show variables like 'slow_query_log_file';
-- 查询和打开慢查询日志
mysql> show variables like 'slow_query_log';
mysql> set global slow_query_log = on;

配置好了之后，就随意执行几条SQL就可以在日志文件中看到了，下面列举一个SQL执行记录：

/* ApplicationName=DataGrip 2020.1.2 */ select count(*) from imc_course;
# Time: 2020-04-29T15:35:20.396723Z
# User@Host: root[root] @ gateway [192.168.56.1]  Id:    10
# Query_time: 0.000164  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0

1. 查询语句，前面的注释是用工具查询自动生成的，表示了是来自哪个工具
2. 查询时间
3. 哪个用户，来自那台主机，线程 ID = 10
4. 查询耗时、锁定时间

mysqldumpslow

[root@study ~]# cd /usr/local/mysql/sql_log
[root@study sql_log]# mysqldumpslow slowlog.log 

Reading mysql slow query log from slowlog.log
Count: 1  Time=0.04s (0s)  Lock=0.01s (0s)  Rows=501.0 (501), root[root]@gateway
  /* ApplicationName=DataGrip N.N.N */ SELECT t.*
  FROM imc_db.imc_chapter t
  LIMIT N
...
有该条 sql 执行的次数、执行的时间、锁定时、返回的行数、哪个用户执行的

pt-query-digest

[root@study sql_log]# pt-query-digest slowlog.log 
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
...
# 180ms user time, 30ms system time, 29.25M rss, 242.34M vsz
# Current date: Wed Apr 29 23:59:18 2020
# Hostname: study.centos.mrcode
# Files: slowlog.log
# Overall: 108 total, 18 unique, 0.14 QPS, 0.00x concurrency _____________
# Time range: 2020-04-29T15:31:41 to 2020-04-29T15:44:24
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           93ms    52us    47ms   858us     1ms     4ms   194us
# Lock time           11ms       0     6ms    99us       0   632us       0
# Rows sent            536       0     501    4.96    0.99   46.62       0
# Rows examine       2.68k       0   1.10k   25.42       0  156.98       0
# Query size         6.25k      13     856   59.28   72.65  132.77   36.69

# Profile
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0x217EA386BFC0686D9D58C106714...  0.0467 50.3%     1 0.0467  0.00 SELECT imc_db.imc_chapter
#    2 0xE77769C62EF669AA7DD5F6760F2...  0.0117 12.6%     2 0.0058  0.00 SHOW VARIABLES
#    3 0xA86446692DE3CA32943798A0901...  0.0063  6.8%    30 0.0002  0.00 SHOW WARNINGS
#    4 0xB1D4F88B1115C3B5D8D05AAE016...  0.0055  6.0%     2 0.0028  0.00 SELECT imc_course
#    5 0x83C1E5898A01E697921459BF4F1...  0.0043  4.7%    10 0.0004  0.00 SELECT
#    6 0x7A2B64842DCEDE58C32EFD50D94...  0.0034  3.7%     9 0.0004  0.00 SELECT
#    7 0xDBF338083F09B28EB07A6B495E6...  0.0030  3.3%     3 0.0010  0.00 SELECT
#    8 0xA11944C87A6A5C16FB38455BF70...  0.0030  3.2%     6 0.0005  0.00 SELECT
#    9 0xE8390778DC20D4CC04FE01C5B31...  0.0018  1.9%    16 0.0001  0.00 ADMIN PING
#   10 0xE16545BB40117FC1F9F96162CEE...  0.0014  1.5%     6 0.0002  0.00 
#   11 0xCFA134076E32C4608D274F9D0D1...  0.0014  1.5%     6 0.0002  0.00 
# MISC 0xMISC  

# Query 7: 0.01 QPS, 0.00x concurrency, ID 0xDBF338083F09B28EB07A6B495E60CF2A at byte 15710
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-04-29T15:31:41 to 2020-04-29T15:41:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2       3
# Exec time      3     3ms   222us     2ms     1ms     2ms   886us   515us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       3       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    40   2.51k     856     856     856     856       0     856
# String:
# Databases    imc_db
# Hosts        gateway
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ################################
#  10ms
# 100ms
#    1s
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
/* mysql-connector-java-8.0.15 (Revision: 79a4336f140499bd22dd07f02b708e163844e3d5) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout\G

上面的日志信息字段说明

# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2       3
# Exec time      3     3ms   222us     2ms     1ms     2ms   886us   515us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       3       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    40   2.51k     856     856     856     856       0     856
横轴的表头是下面所有公用的：
 pct：占这份日志中总的百分比，这个需要配合 attribute 来看。
 比如 count：执行次数占日志中的总次数百 2%，总共执行了 3 次
 exec time：执行时间占总的 3%，总耗时 3ms、最小 222us、最大 2ms、平均耗时 1 ms、百分之 95 的耗时是 2ms

详细的字段含义

第一部分：总体统计结果

Overall：总共有多少条查询
Time range：查询执行的时间范围
unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
total：总计   min：最小   max：最大  avg：平均
95%：把所有值从小到大排列，位置位于 95% 的那个数，这个数一般最具有参考价值
median：中位数，把所有值从小到大排列，位置位于中间那个数
  
# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency ________________
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性                总计      最小     最大    平均     95%  标准    中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time             3s   640ms      2s      1s      2s   999ms      1s
# 锁占用时间
# Lock time            1ms       0     1ms   723us     1ms     1ms   723us
# 发送到客户端的行数
# Rows sent              5       1       4    2.50       4    2.12    2.50
# select语句扫描行数
# Rows examine     186.17k       0 186.17k  93.09k 186.17k 131.64k  93.09k
# 查询的字符数
# Query size           455      15     440  227.50     440  300.52  227.50

第二部分：查询分组统计结果

Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询对象

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xF9A57DD5A41825CA  2.0529 76.2%     1 2.0529  0.00 SELECT
#    2 0x4194D8F83F4F9365  0.6401 23.8%     1 0.6401  0.00 SELECT wx_member_base

第三部分：每一种查询的详细统计结果

由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
ID：查询的ID号，和上图的Query ID对应
Databases：数据库名
Users：各个用户执行的次数（占比）
Query_time distribution ：查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables：查询中涉及到的表
Explain：SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-22 06:11:40
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     76      2s      2s      2s      2s      2s       0      2s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent     20       1       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size     3      15      15      15      15      15       0      15
# String:
# Databases    test
# Hosts        192.168.8.1
# Users        mysql
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G

实时获取需要优化的SQL

通过监控实时发现问题

前面通过慢查询日志来获取有问题的SQL，这种方式需要一定的周期。想要快速的获取，就需要通过监控来获取。
information_schema.PROCESSLIST：是一个视图，该视图中存储了当前正在执行的 SQL 信息

select id, user, host, db, command, time, state, info
from information_schema.PROCESSLIST
where time >= 60

-- time 是 sql 执行的时长，比如上面是大于 60 秒的执行 sql

获取SQL的执行计划

为什么要关注执行计划？

• 了解 SQL 如何访问表中的数据
  • 是使用全表扫描、还是索引等方式来获取的
• 了解 SQL 如何使用表中的索引
  • 是否使用到了正确的索引
• 了解 SQL 锁使用的查询类型
  • 是否使用到了子查询、关联查询等信息

如何获取执行计划（EXPLAIN）

可通过EXPLAIN来获取到SQL的执行计划

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

-- explainable_stmt：要分析的静态 SQL 语句
-- FOR CONNECTION connection_id：可以分析正在执行的 SQL 语句，这里的 connection_id 就是上面 information_schema.PROCESSLIST 中的 id

执行计划内容分析 explain

下面使用查询学习人数大于3000的查询SQL，来了解下explain的相关信息

explain
select course_id, title, study_cnt
from imc_course
where study_cnt > 3000

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_course	NULL	ALL	NULL	NULL	NULL	NULL	100	33.33	Using where

id列

id会有两种值

• 数值：查询中的SQL数据对数据库对象操作的顺序
• NULL：这一行数据是由另外两个查询进行union后产生的

执行顺序：

• 当ID相同时由上到下执行
• 当ID不同时，由大到小执行

第一个SQL：由于只有只有一行执行计划，下面来看一个复杂一些的执行计划

-- 查询课程的分类名称、难度等级、课程名称、学习人数
explain
select course_id, class_name, level_name, title, study_cnt
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
where study_cnt > 3000

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	c	NULL	ALL	PRIMARY	NULL	NULL	NULL	4	100	NULL
1	SIMPLE	a	NULL	ALL	NULL	NULL	NULL	NULL	100	3.33	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	b	NULL	eq_ref	PRIMARY	PRIMARY	2	imc_db.a.class_id	1	100	NULL

id是相同的，由上到下来分析计划，第一行的table = c：指以该表为主表作为循环嵌套来查询的，虽然我们用a来作为主表来查询的，实际上执行是以c为主表。
第二个SQL：再来看下id不同的计划分析：

explain
select a.course_id, a.title
from imc_course a
where a.course_id not in (select b.course_id from imc_chapter b)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	a	NULL	index	NULL	udx_title	62	NULL	100	100	Using where; Using index
2	DEPENDENT SUBQUERY	b	NULL	index_subquery	udx_couseid	udx_couseid	4	func	13	100	Using index

第三个SQL：带union查询的计划

explain
select course_id, class_name, level_name, title, study_cnt
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
where study_cnt > 3000
union
select course_id, class_name, level_name, title, study_cnt
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
where class_name = 'MySQL'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	c	NULL	ALL	PRIMARY	NULL	NULL	NULL	4	100	NULL
1	PRIMARY	a	NULL	ALL	NULL	NULL	NULL	NULL	100	3.33	Using where; Using join buffer (Block Nested Loop)
1	PRIMARY	b	NULL	eq_ref	PRIMARY	PRIMARY	2	imc_db.a.class_id	1	100	NULL
2	UNION	b	NULL	ALL	PRIMARY	NULL	NULL	NULL	13	10	Using where
2	UNION	a	NULL	ALL	NULL	NULL	NULL	NULL	100	10	Using where; Using join buffer (Block Nested Loop)
2	UNION	c	NULL	eq_ref	PRIMARY	PRIMARY	2	imc_db.a.level_id	1	100	NULL
NULL	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

select_type列

• SIMPLE：不包含子查询或是 UNION 操作的查询
• PRIMARY：查询中如果包含任何子查询，那么最外层的查询则被标记为 PRIMARY
• SUBQUERY：SELECT 列表中的子查询
• DEPENDENT SUBQUERY：依赖外部结果的子查询
• UNION：union 操作的第二个或是之后的查询的值为 union
• DEPENDENT UNION：当 UNION 作为子查询时，第二或是第二个后的查询的 select_type 值
• UNION RESULT：UNION 产生的结果集
• DERIVED：出现在 FROM 子句中的子查询

刚刚第一个 SQL 中，使用了 join 查询，但是是 SIMPLE 的类型、第二个 SQL 中，id=1 的计划就讲 a 表标记为了 PRIMARy

table列

表示执行计划表中的数据是由哪个表输出的

• 表名：指明从哪个表中获取数据，有原始表名，或则别名
• <unionM,N>：表示由 ID 为 M.N 查询 union 产生的结果集
• <derived N>/<subquery N>：由 ID 为 N 的查询产生的结果集

partitions 列

只有在查询分区表的时候，才会显示分区表的ID

type列

通常通过type可以知道查询使用的连接类型

TIP
在 MySQL 中，并不是只有通过 join 产生的关联查询才叫关联查询。
就算只查询一个表，也会认为是一个连接查询

type按照性能从高到低排列如下：

• system （性能最高）
  • 这是 const 连接类型的一个特列，当查询的表只有一行时使用
• const
  • 表中有且只有一个匹配的行时使用，如对住建或是唯一索引的查询，这是效率最高的连接方式
• eq_ref
  • 唯一索引或主键引查找，对于每个索引键，表中只有一条记录与之匹配；
  • 是以前面表返回的数据行为基础，对于每一行数据都会从本表中读取一行数据
• ref
  • 非唯一索引查找、返回匹配某个单独值的所有行
• ref_or_null
  • 类似于 ref 类型的查询，但是附加了对 NULL 值列的查询
• index_merge
  • 该连接类型表示使用了索引合并优化方法；mysql 5.6 之前，一般只支持一个索引查询，后来支持索引合并之后，可以支持多个索引查询
• range
  • 索引范围扫描，常见于 between、>、< 这样的查询条件
• indexFULL index Scan
  • 全索引扫描，同 ALL 的区别是，遍历的是索引树
• ALL （性能最底）
  • FULL TABLE Scan 权标扫表这是效率最差的连接方式

possible_keys 列

指出查询中可能会用到的列

key列

possible_keys 列出的是可能使用到的索引，key 则是表示实际使用到的索引

• 如果为 NULL，则表示该表中没有使用到索引
• 如果出现的值，没有存在 possible_keys 中，查询可能使用到的是覆盖索引

key_len列

实际使用索引的最大长度
注意：比如在一个联合索引中，如果有 3 列且总字节长度是 100 字节，key_len 可能少于 100 字节的，比如只有 30 个字节，这就说明在查询中没有到联合索引中的所有列，而是只使用到了联合索引中的部分列。
注意： 这一列的字节计算方式是以表中定义列的字节方式，而不是数据的实际长度

ref列

指出哪些列或常量被用于常量查找
下面通过一个简单的语句来说明下现在所知道的列信息：

explain
select *
from imc_user a
where user_id = 1

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	NULL	const	PRIMARY	PRIMARY	4	const	1	100	NULL

只出现了一行：

• 是 SIMPLE 类
• 从 a 表中输出的数据
• 不是查询的分区表
• type=const：是常量，user_id = 1 我们用了一个具体的数值 1
• 有可能使用到的索引有：PRIMARY
• 实际使用到的索引：PRIMARY （主键索引）
• 索引的长度是 4 ，这个表定义的 id 就是占用 4 字节
• 使用的是常量过滤

rows列

有两方面的含义：

• 根据统计信息预估的扫描的行数
• 在关联查询中，也表示内嵌循环的次数每获取一个值，就要对目标表进行一次查找，循环越多，性能就越差

filtered 列

与 rows 有一定的关系，表示返回结果的行数占需要读取行数（rows）的百分比。
也是一个预估值，不太准确，但是可以在一定程度上可以预估 mysql 的查询成本。
该值越高，效率也越高

Extra列

包含了不适合在其他列中显示的一些额外信息。常见的值有如下：

• Distinct优化
  • distinct 操作，在找到第一匹配的元组后即停止找同样值的动作
• Not exists
  • 使用 not exists 来优化查询
• Using filesort
  • 使用文件来进行排序，通常会出现在 order by 或 group by 查询中
• Using index
  • 使用了覆盖索引进行查询；
  • 覆盖索引：查询中使用到的信息是完全可以通过索引信息来获取的，而不用去从表中的数据进行访问
• Using temporary
  • MySQL 需要使用临时表来处理查询，常见于排序、子查询、和分组查询
• Using where
  • 需要在 MySQL 服务器层使用 Where 条件来过滤数据
• select tables optimizedaway
  • 直接通过索引来获得数据，不用访问标

explain 列含义汇总整理

• id当 ID 相同时由上到下执行，当 ID 不同时，由大到小执行
  • 数值：查询中的 SQL 数据对数据库对象操作的顺序
  • NULL：这一行数据是由另外两个查询进行 union 后产生的
• select_type ： 查询类型
  • SIMPLE：不包含子查询或是 UNION 操作的查询
  • PRIMARY：查询中如果包含任何子查询，那么最外层的查询则被标记为 PRIMARY
  • SUBQUERY：SELECT 列表中的子查询
  • DEPENDENT SUBQUERY：依赖外部结果的子查询
  • UNION：union 操作的第二个或是之后的查询的值为 union
  • DEPENDENT UNION：当 UNION 作为子查询时，第二或是第二个后的查询的 select_type 值
  • UNION RESULT：UNION 产生的结果集
  • DERIVED：出现在 FROM 子句中的子查询
• table ：表示执行计划表中的数据是由哪个表输出的
  • 表名：指明从哪个表中获取数据，有原始表名，或则别名
  • <unionM,N>：表示由 ID 为 M.N 查询 union 产生的结果集
  • /：由 ID 为 N 的查询产生的结果集
• partitions：只有在查询分区表的时候，才会显示分区表的 ID
• type：通常通过 type 可以知道查询使用的连接类型。type 按照性能从高到低排列如下：
  • system （性能最高）这是 const 连接类型的一个特列，当查询的表只有一行时使用
  • const表中有且只有一个匹配的行时使用，如对住建或是唯一索引的查询，这是效率最高的连接方式
  • eq_ref唯一索引或主键引查找，对于每个索引键，表中只有一条记录与之匹配；是以前面表返回的数据行为基础，对于每一行数据都会从本表中读取一行数据
  • ref非唯一索引查找、返回匹配某个单独值的所有行
  • ref_or_null类似于 ref 类型的查询，但是附加了对 NULL 值列的查询
  • index_merge该连接类型表示使用了索引合并优化方法；mysql 5.6 之前，一般只支持一个索引查询，后来支持索引合并之后，可以支持多个索引查询
  • range索引范围扫描，常见于 between、>、< 这样的查询条件
  • indexFULL index Scan 全索引扫描，同 ALL 的区别是，遍历的是索引树
  • ALL （性能最底）FULL TABLE Scan 权标扫表这是效率最差的连接方式
• possible_keys：指出查询中可能会用到的索引
• key：表示实际使用到的索引
  • 如果为 NULL，则表示该表中没有使用到索引
  • 如果出现的值，没有存在 possible_keys 中，查询可能使用到的是覆盖索引
• key_len：实际使用索引的最大长度这一列的字节计算方式是以表中定义列的字节方式，而不是数据的实际长度
• ref：指出哪些列或常量被用于索引查找
• rows：有两方面的含义：
  • 根据统计信息预估的扫描的行数
  • 在关联查询中，也表示内嵌循环的次数每获取一个值，就要对目标表进行一次查找，循环越多，性能就越差
• filtered：表示返回结果的行数占需要读取行数（rows）的百分比。
• Extra：包含了不适合在其他列中显示的一些额外信息常见的信息有：优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的动作
  • Distinct
  • Not exists使用 not exists 来优化查询
  • Using filesort使用文件来进行排序，通常会出现在 order by 或 group by 查询中
  • Using index使用了覆盖索引进行查询；覆盖索引：查询中使用到的信息是完全可以通过索引信息来获取的，而不用去从表中的数据进行访问
  • Using temporaryMySQL 需要使用临时表来处理查询，常见于排序、子查询、和分组查询
  • Using where需要在 MySQL 服务器层使用 Where 条件来过滤数据
  • select tables optimizedaway直接通过索引来获得数据，不用访问标

SQL的索引优化

SQL 优化的手段一般有两个方向：

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

索引的作用是什么？

告诉存储引擎如何快速的查找到所需要的数据，不同的存储引擎实现的索引是不同的。
这里来讲解常用的 Innodb 支持的索引类型

Innodb支持的索引类型

• Btree 索引：常用的类型
• 自适应 HASH 索引：自动维护
• 全文索引：对中文支持不太好
• 空间索引

Btree索引的特点

以B+树的结构存储索引数据。
本身是一种平衡的二叉树，每一个叶子节点到根的距离都是相同的，并且记录的所有节点都是按键值的大小、顺序放在同一层的叶子节点上的，并且每一个叶子节点是通过指针来连接的。

• Btree 索引适用于全值匹配的查询。如

class_name='mysql';
class_name in('mysql','postgreSQL');
-- in 也会使用到索引，不过 in 中数据较多时，sql 优化器可能会采用全表扫描的方式来查询

• Btree 索引适合处理范围查找

study_cnt between 1000 and 3000
study_cnt > 3000

• Btee 索引从索引的最左侧列开始匹配查找列
• 对于多个列组合成的索引，只能从左开始查找，如

create index idx_title_studyCnt on imc_course(title,study_cnt)

a. study_cnt > 3000			 -- 无法用到上述的索引
b. study_cnt > 3000 and title = 'MySQL'   -- 顺序不对，但是可以用到索引
c. title = 'MySQL'  -- 也可以使用到

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

这个没有统一的答案，这个需要结合表中的数据和表中的索引进行分析。
通常可以在以下列中建立索引：

• WHERE子句中的列
  • 不是所有的都要建立索引，要结合其他的条件，如：是否有很好的筛选性。
  • 筛选性：重复数据较多，就是筛选性较差。要在筛选性较好的列上建立索引
• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段

可以提高排序的性能，避免使用到临时表，只有满足下列条件时：

• 索引列的顺序与order by子句的顺序要完全一致
• 索引列的方向要与order by中的完全一致（asc、desc）
• 在多个表的关联查询中，order by中的字段要全部在关联表的第一张表中
• 多表JOIN的关联列

WHERE子句中的列，索引示例

-- 查询出 2019 年 1 月 1 日以后注册的男性会员的昵称
explain
select user_nick
from imc_user
where sex = 1
  and reg_time > '2019-01-01'

执行计划是

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_user	NULL	ALL	NULL	NULL	NULL	NULL	2530	3.33	Using where

可以看到没有使用到索引，并且是使用 ALL 全表扫描了 2530 行的数据，过滤比是 3.33 %，使用到了 where 子句来过滤。
查询条件有两列，那么在哪一列上面建立索引呢？可以通过计算他们的筛选性来得出

-- 计算这两列的赛选性
select sexCount, regTimeCount, sexCount / total, regTimeCount / total
from (
         select count(distinct sex)                               as sexCount,
                count(distinct date_format(reg_time, '%Y-%m_%d')) as regTimeCount,
                count(*)                                          as total
         from imc_user) as temp

去重，并计算他们的百分比，日期格式化是为了去掉时分秒或者毫秒这样的，否则不同的就太多了。

sexCount	regTimeCount	sexCount / total	regTimeCount / total
2	454	0.0008	0.1794

对于这两列来说，注册时间的可筛选性大于性别一列，在注册时间列上建立索引

create index idx_regtime on imc_user(reg_time);

再来执行上面的查询语句得到的计划如下

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_user	NULL	range	idx_regtime	idx_regtime	5	NULL	516	10	Using index condition; Using where

从type开始，使用到了范围查询，预计使用的索引是idx_regtime，实际使用的索引是 idx_regtime，索引长度为 5 字节，扫描了 516 行，过滤比为 10%，使用到了索引条件。建立索引后比没有建立效率提高了很多。
再来删除掉注册时间的索引，在性别上添加索引，看看是否会使用到这个索引。

drop index idx_regtime on imc_user;
create index idx_sex on imc_user(sex);

执行计划为

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_user	NULL	ALL	idx_sex	NULL	NULL	NULL	2530	3.33	Using where

可以看到没有被用到性别列上的索引

一个复杂一点的查询语句索引优化

-- 查询课程的分类、难度、方向、标题，综合评分信息
-- 这里关联了 4 张表
explain
select course_id, b.class_name, d.type_name, c.level_name, title, score
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
         join imc_type d on a.type_id = d.type_id
where c.level_name = '高级'
  and b.class_name = 'MySQL'

执行计划为，在执行计划之前，为了能和视频中的一致，这里添加一个索引

-- 注意：这里笔者也不知道到低是哪里给添加上索引的，但是视频中的确是有这个索引
create index uqx_classname on imc_class(class_name);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b	NULL	ref	PRIMARY,uqx_classname	uqx_classname	32	const	1	100	Using index
1	SIMPLE	c	NULL	ALL	PRIMARY	NULL	NULL	NULL	4	25	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	a	NULL	ALL	NULL	NULL	NULL	NULL	100	1	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	d	NULL	eq_ref	PRIMARY	PRIMARY	2	imc_db.a.type_id	1	100	NULL

id都是1，从上往下看。以b表为驱动，只有b表用到了索引的，他们的查询次数是rows相乘（因为这个是一个嵌套循环，row就代表了嵌套循环的次数），大概是400次查询，也就是说从b表中获取1条数据，需要在c表中获取4条数据，在a表中获取400条数据，在d表中获取一条数据

其中查询次数最多的是a表，需要查询100次，先来看看这张表的索引定义。

show create table imc_course;

CREATE TABLE `imc_course` (
  `course_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程ID',
  `title` varchar(20) NOT NULL DEFAULT '' COMMENT '课程主标题',
  `title_desc` varchar(50) NOT NULL DEFAULT '' COMMENT '课程副标题',
  `type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '课程方向ID',
  `class_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '课程分类ID',
  `level_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '课程难度ID',
  `online_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '课程上线时间',
  `study_cnt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '学习人数',
  `course_time` time NOT NULL DEFAULT '00:00:00' COMMENT '课程时长',
  `intro` varchar(200) NOT NULL DEFAULT '' COMMENT '课程简介',
  `info` varchar(200) NOT NULL DEFAULT '' COMMENT '学习需知',
  `harvest` varchar(200) NOT NULL DEFAULT '' COMMENT '课程收获',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '讲师ID',
  `main_pic` varchar(200) NOT NULL DEFAULT '' COMMENT '课程主图片',
  `content_score` decimal(3,1) NOT NULL DEFAULT '0.0' COMMENT '内容评分',
  `level_score` decimal(3,1) NOT NULL DEFAULT '0.0' COMMENT '简单易懂',
  `logic_score` decimal(3,1) NOT NULL DEFAULT '0.0' COMMENT '逻辑清晰',
  `score` decimal(3,1) NOT NULL DEFAULT '0.0' COMMENT '综合评分',
  `is_recommand` tinyint(4) DEFAULT '0' COMMENT '是否推荐，0 不推荐，1 推荐',
  PRIMARY KEY (`course_id`),
  UNIQUE KEY `udx_title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8 COMMENT='课程主表'

-- 只有一个主键索引，和 title 的唯一索引

也就是说，在a表与b、c表上的关联列上没有索引，前面说到，要在关联列上建立索引。

根据这个关联语句
from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
         join imc_type d on a.type_id = d.type_id
         
得到我们的联合索引，索引的排列顺序为可筛选性排列

create index idx_classid_typeid_levelid on imc_course (class_id, type_id, level_id);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b	NULL	ref	PRIMARY,uqx_classname	uqx_classname	32	const	1	100	Using index
1	SIMPLE	c	NULL	ALL	PRIMARY	NULL	NULL	NULL	4	25	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	a	NULL	ref	idx_classid_typeid_levelid	idx_classid_typeid_levelid	2	imc_db.b.class_id	7	10	Using index condition
1	SIMPLE	d	NULL	eq_ref	PRIMARY	PRIMARY	2	imc_db.a.type_id	1	100	NULL

可以看到在a表上使用了索引，循环的行数从100降低到了7行。从400降低到了28次了，但是优化还没有完，c表上还没有用到索引。
观察可见

from imc_course a
         join imc_class b on a.class_id = b.class_id
         join imc_level c on a.level_id = c.level_id
         join imc_type d on a.type_id = d.type_id
where c.level_name = '高级'

-- c 表通过 level_id 与 a 表关联，并且 where 中用到了 leve_name 进行过滤
-- 这里为 c 表的 level_name 建立索引，同时知道：二级索引会带上主键。所以也满足关联主键的做法
create index idx_levelname on imc_level(level_name);

执行计划如下

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b	NULL	ref	PRIMARY,uqx_classname	uqx_classname	32	const	1	100	Using index
1	SIMPLE	c	NULL	ref	PRIMARY,idx_levelname	idx_levelname	32	const	1	100	Using index
1	SIMPLE	a	NULL	ref	idx_classid_typeid_levelid	idx_classid_typeid_levelid	2	imc_db.b.class_id	7	10	Using index condition
1	SIMPLE	d	NULL	eq_ref	PRIMARY	PRIMARY	2	imc_db.a.type_id	1	100	NULL

现在c表也用上了索引，从4次变成了1次。最终从400次，降到了7次。
以上就是一个 SQL 优化的思路和过程，目的是减少循环次数、增加过滤的百分比。

如何选择符合索引键的顺序？

在前面的演示中已经说过了，这里总结下
1.区分度最高的列放在联合索引的最左侧
2.使用最频繁的列放到联合索引的最左侧
3.尽量把字段长度小的列放在联合索引列的最左侧
以上顺序是要保证上一条满足的情况下，再遵循下一条

Btree索引的限制

• 只能从最左侧开始按索引键的顺序使用索引，不能跳过索引键

如一个a_b_c的联合索引，在过滤的时候使用a和c列，那么就只能用到a列的索引

• NOT IN和 <>（不等于）操作无法使用索引
• 索引列上不能使用表达式或是函数

索引使用的误区

• 索引越多越好

绝对不是越多越好

• 使用IN列表查询不能用到索引

当in中的值较多的时候，有可能就会被认定为全表查找 优化器就会选择不走索引查找
查询过滤顺序必须同索引键顺序相同才可以使用到索引上面演示过了，mysql 会自动优化的。

SQL改写的原则

SQL当索引优化无效的时候就需要考虑改写SQL来适应正确的索引了。

• 使用 outer join 代替 not in

前面说到过，当在 where 中使用 not in 或则是 <> 时，将无法使用索引。

• 使用 CTE 代替子查询

公共表表达式，前面讲解过了

• 拆分复杂的大 SQL 为多个简单的小 SQL

一个 SQL 只能用到一个 CPU 核心查询，不能并发的执行。在 8.1.5 中，只有部分有一些改进了，但是还不太好。

• 巧用计算列优化查询

使用 outer join 代替 not in 的示例

-- 添加一条数据
INSERT INTO `imc_db`.`imc_class`(`class_id`, `class_name`, `add_time`) VALUES (14, 'AI', '2020-04-30 04:55:55');

-- 查询出不存在课程的分类名称
explain
select class_name
from imc_class
where class_id not in (select class_id from imc_course)

执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	imc_class	NULL	index	NULL	uqx_classname	32	NULL	14	100	Using where; Using index
2	DEPENDENT SUBQUERY	imc_course	NULL	index_subquery	idx_classid_typeid_levelid	idx_classid_typeid_levelid	2	func	7	100	Using index

这种情况下，都用上了索引，下面先改写SQL，再讲解原因改写成外关联的方式为

explain
select a.class_name
from imc_class a
         left join imc_course b on a.class_id = b.class_id
where b.class_id is null;

执行计划为

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	NULL	index	NULL	uqx_classname	32	NULL	14	100	Using index
1	SIMPLE	b	NULL	ref	idx_classid_typeid_levelid	idx_classid_typeid_levelid	2	imc_db.a.class_id	7	100	Using where; Not exists; Using index

可以看到这里和上面的not in的执行计划中用到的索引是一样的，这是因为MySQL8.0已经自动优化掉了not in。

巧用计算列优化查询的示例

-- 查询对于内容，逻辑和难度三项评分之和大于 28 分的用户评分。
explain 
select *
from imc_classvalue
where (content_score + level_score + logic_score) > 28

执行计划为

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_classvalue	NULL	ALL	NULL	NULL	NULL	NULL	300	100	Using where

可以看到没有用到索引，就算在这三列上创建了联合索引，也不能用到它
计算列是 MySQL 5.7 新增的一个功能，可以利用表中的一些列，生成另外一个列

-- 需要在列定义上声明，这里新增一个列
-- as 后面的则是计算表达式
ALTER TABLE imc_classvalue
    ADD COLUMN total_score DECIMAL(3, 1) as (content_score + level_score + logic_score);

-- 再为这个计算列增加索引
create index idx_total_score on imc_classvalue (total_score);

-- 利用计算列来查询
explain
select *
from imc_classvalue
where total_score > 28;

执行计划为

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_classvalue	NULL	range	idx_total_score	idx_total_score	3	NULL	51	100	Using where

可以看到，使用上了索引

总结-本章知识点

• SQL 优化的一般步骤
• 如何发现存在性能问题的 SQL
• 如何分析 SQL 的执行计划
• 索引的作用以及如何为表建立合适的索引

事务与并发控制

本章紧扣数据库宕机这一企业痛点问题，讲解“高并发”下数据库企业级解决方案。主要包含四个模块：

• 【关于问道】事务是什么；
• 【善于发现】高并发中的隐患；
• 【不得不知】事务隔离级别；
• 【解决之道】阻塞与死锁。

在数据库的数据你绝不对感到孤单，总会有人在同一时间和你操作同一条数据，允许多个人同时操作相同的数据是为了增强并发性，并发能提高吞吐量，但是会带来一些问题。比如：同时操作同一条数据，很容易破坏数据的一致性。
为了解决并发带来的数据争用和数据一致性问题，数据库提供了事物和基于锁的多版本并发机制。

什么是事务？

• 事务是数据库执行操作的最小逻辑单元
• 事务可以由一个 SQL 组成也可以由多个 SQL 组成
• 组成事务的 SQL 要么全执行成功要么全执行失败

START TRANSACTION / BEGIN    -- 开启一个事务
	SELECT...
	UPDATE...
	INSERT...
COMMIT / ROLLBACK  -- 提交或回滚事务

在事务中，不能执行 DDL 操作，如修改表结构之类的，因为执行这类型的语句，会自动执行 COMMIT 操作。

事务的特性

• 原子性 （A）

一个事务中的所有操作，要么全部完成，要么全部不完成

• 一致性（C）

在事务开始之前和事务结束以后，数据库的完整性没有被破坏

• 隔离性（I）

事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见。

• 持久性（D）

事务一旦提交了，其结果就是永久性的，就算发生了宕机等事故，数据库也能将数据恢复。

并发带来的问题

• 脏读

一个事务读取了另一个事务未提交的数据。
这里怎么理解一个事务未提交的数据？
A 读取到 96，B 修改成 97（还未提交），A 再次读取到了 97 分，然而 B 回滚了数据，A 事务读到的是一条脏数据。

• 不可重复读

一个事务前后两次读取的同一数据不一致。A 读到 96，B 修改成 97 并提交了，A 再次读取到了 97，A 事务两次读取到了不一样的数据。

• 幻读

一个事务两次查询的结果集记录数不一致。不可重复读是单条记录，幻读是读取一个范围。两次读到的不一致

INNODB的隔离级别

隔离级别	脏读	不可重复读	幻读	隔离性	并发性
顺序读（SERIALIZABLE）	N	N	N	最高	最低
可重复读（REPEATABLE READ）	N	N	N
读以提交（READ COMMITTED）	N	Y	Y
读未提交（READ UNCOMMITTED）	Y	Y	N	最低	最高

设置事务的隔离级别

SET [PERSIST|GLOBAL|SESSION]
	TRANSACTION ISOLATION LEVEL
	{
		READ UNCOMMITTED
	  | READ COMMITTED
	  | REPEATABLE READ
	  | SERIALIZABLE
	}

• PERSIST：对当前的 SESSION 以及后续连接到服务器的连接生效，并且重启服务器后也不会丢失修改
• GLOBAL：对连接到服务器新的连接有效，重启服务器后丢失修改
• SESSION：当前 session 生效，长期后丢失，常用的是该级别

serializable 级别两个事物并发的情况演示

在一个操作中开启事物、

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
-- 修改后，可以通过变量来显示当前的隔离级别
-- transaction_isolation,REPEATABLE-READ (这是默认级别)
-- transaction_isolation,SERIALIZABLE
SHOW VARIABLES LIKE '%iso%';

-- session 1 启用事物,并执行一个查询
begin;
select course_id, title
from imc_course
where score > 9.6;

-- session 2 更新一个值以符合 score > 9.6
begin;
update imc_course
set score=9.8
where course_id = 34;
-- 执行完之后就会发现，一直在转圈圈。并没有执行成功
-- 知道 session 1 执行 commit 命令后，这里才会执行成功。
-- 记得 session 2 使用 ROLLBACK 回滚掉。

REPEATABLE READ 可重复读的演示

-- session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

SHOW VARIABLES LIKE '%iso%';

begin;
select course_id, title
from imc_course
where score > 9.6;
-- 这里先去执行 session 2 的修改操作
-- 执行完成之后，再来查询下上面的语句，看看能不能查询到 session 2 提交的修改
-- 可以发现，在这个事务中多次读取都没有读取到 session2 提交的修改

-- session 2
begin;
update imc_course
set score=9.8
where course_id = 34;
-- 可以看到直接执行成功了，没有被阻塞。我们直接提交下
commit;

事务阻塞的产生

不只是在 顺序读（SERIALIZABLE）下会产生阻塞，REPEATABLE READ 可重复读也会产生阻塞。

-- session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

SHOW VARIABLES LIKE '%iso%';

-- session 1 开启一个事务，并把分数 - 0.1 
begin;
update imc_course
set score=score - 0.1
where course_id = 34;
-- 不提交事务，这个时候去 session 2 执行更新语句
commit; 

-- session 2 ，执行相同条件的语句，把分数 + 0.1
begin;
update imc_course
set score=score + 0.1
where course_id = 34;
-- 就会发现，一直转圈圈，被阻塞了。
-- 这个时候只能等待 session 1 的事务提交后，这里才会继续执行下去

为什么产生了阻塞？这就关系到innodb中的锁机制了

InnoDB中的锁

• 查询需要对资源加共享锁（S）
  • 被加锁的对象只能被持有锁的事务读取，但不能修改。其他事务也不能修改，但是也可以加共享锁进行读取
• 数据修改需要对资源加排它锁（X）
  • 被加锁的对象只能被持有锁的事务读取/修改，其他事务无法读取和修改。

通过上述说明，锁关系整理如下：

|
| 排它锁 | 共享锁 |
| --- | --- | --- |
| 排它锁 | 不兼容 | 不兼容 |
| 共享锁 | 不兼容 | 兼容 |

不兼容就是互斥的意思。
通过锁解决了事务的隔离性问题，但是带来了新的问题，最主要的是带来阻塞和死锁问题。

什么是阻塞？

由于不同锁之间的兼容关系，造成一事务需要等待另一个事务释放其所占用的资源的现象

如何发现阻塞？

在 MySQL 8 中可以通过如下的 SQL 来查询阻塞，sys.innodb_lock_waits 表中记录了 InnoDB 中所有的等待事件

select waiting_pid             as '被阻塞的线程',
       waiting_query           as '被阻塞的 SQL',
       blocking_pid            as '阻塞的线程',
       blocking_query          as '阻塞的 SQL',
       wait_age                as '阻塞时间',
       sql_kill_blocking_query as '建议操作'
from sys.innodb_lock_waits
where (unix_timestamp() - unix_timestamp(wait_started)) > 30
-- 条件为大于 30 秒的会被查询出来

显示这个也很简单，前面有阻塞的例子，可以自行执行。可以通过以下语句拿到当前的 线程 id，方便在上表中查询出来的进行对比

select connection_id();

我这里测试如下

被阻塞的线程	被阻塞的 SQL	阻塞的线程	阻塞的 SQL	阻塞时间	建议操作
47	/* ApplicationName=DataGrip 20 ... ore + 0.1 where course_id = 34	42	NULL	00:00:07	KILL QUERY 42

可以看到被阻塞的线程和被阻塞的 SQL。（我这里把条件修改小了的，所以能看到阻塞时间 7 秒也会被查询出来）。
并且给出了建议为杀掉 42 的线程

kill 42;  -- 杀掉这个线程

如何处理事务中的阻塞

• 终止占用资源的事务
  • 使用 kill 命令杀掉线程链接。
• 优化占用资源事务的 SQL，使其尽快释放资源。
  • 正确的方法则是需要优化 SQL，kill 是治标不治本的方法。

什么是死锁

并行执行的多个事务相互之间占有了对方所需要的资源。
mysql 内部会自动监控死锁，会回滚影响资源较少的事务，让另外一个事务进行下去。虽然死锁对 mysql 来说，影响不会太大，但是会影响到我们的业务执行时间。就需要监控死锁，然后进行优化。

如何发现死锁

有好几种方法，下面使用 mysql 的错误日志，将死锁记录在错误日志中。

set global innodb_print_all_deadlocks=on;

上图显示了一个死锁的信息（数据人为格式化之后的效果），有两个事物分别是 1704 和 1706，包括他们的活动（active）的秒数。
以上两个事物的 SQL 语句各自更新了一个表，看似乎没有什么关联会导致死锁。貌似没有什么资源竞争，这是因为：这里记录的并不是这个事务中所有的 SQL，而是产生死锁时正在执行的 SQL，所以通常来说，我们需要利用这些信息去查阅程序中，定位到产生死锁的事务所有的 sql ，然后解决它

如何处理死锁？

数据库自行回滚占用资源少的事务。所以不需要我们再数据库层面做什么操作。
但是需要我们来改变我的程序，让 并发事务按相同顺序占用资源。这是什么意思呢？

-- session 1
begin;
update imc_course set score=9.8 where course_id=35;

-- session 2
begin;
update imc_user set score=score+10 where user_id = 10;

下面继续回去操作第二条语句

-- session 1
update imc_user set score=score+10 where user_id = 10;

-- session 2
update imc_course set score=9.8 where course_id=35;

session 1 执行的语句顺序是 A B，session 2 执行的顺序是 B A， 事务开始后，各自占用了对方的锁，所以只要任意一方执行第 2 条语句时，自己就会被阻塞住，并且自己等待对方释放所，但是对方一执行第二条语句也会阻塞。这就造成了死锁。除非一方放弃执行，也就是回滚。
那么 顺序执行 是指，session 1 和 session 2 都以 A B 方式执行语句，最多一方被阻塞，只要另一方执行完成，自己就会继续执行下去

总结

我们学习了以下知识点：

• 什么是事物以及事物的特征
• 并发带来的数据问题
• INNODB 的四种事物隔离级别
• 事物的阻塞和死锁