这是我参与「第三届青训营 -后端场」笔记创作活动的的第4篇笔记。

简介

总结使用MySQL中遇到的一些问题，利用事务优化视频上传功能。

关键词：表设计索引 InnoDB引擎事务

表设计

status字段

视频表中存在status字段，数据类型为int

feed流获取视频时只获取status为0的视频数据

status=0，表示该视频正常展示
status=1，表示该视频被删除
status=2，表示该视频正在审核
status=3，表示该视频审核失败

create_date字段与update_date字段

两个字段的数据类型都是datetime

update_date字段选择根据当前时间戳更新，数据的任意字段更新时，update_date字段将会自动更新。

数据类型	说明
DATE	表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD
DATETIME	DATE和TIME的组合
TIMESTAMP	功能和DATETIME相同（但范围较小）
TIME	格式为HH:MM:SS

评论表示例

字段名	数据类型	说明
id	int	主键
fk_user_id	int	外键，发表评论的用户的id
fk_video_id	int	外键，被评论的视频的id
content	varchar	评论的内容
status	int	评论的状态
create_time	datetime	评论的创建时间
update_time	datetime	评论的修改时间

索引

简介

一种帮助MySQL提高查询效率的数据结构

两个缺点：1.占用磁盘空空间 2.对表中的数据进行增、删、改时，索引也要动态的维护

五种类型：主键索引、单列索引、复合索引、唯一索引，全文索引（fulltext）

MySQL在5.6前只有MyISAM引擎支持fulltext，5.6之后InnoDB引擎和MyISAM引擎都支持了fulltext，但是要求字段的数据类型为char、varchar、text等。

只有拥有索引的字段在查询时才能使用索引

复合索引查询时遵循最左前缀原则。为了更好地利用索引，MySQL在查询过程中会动态调整字段的顺序。

create index name_age_bir_index on t_user(name, age, bir);

name bir age，mysql会将其动态调整为name age bir，可以利用索引
bir age，动态调整后为age bir，还是不符合左前缀原则，不可以利用索引

图文详解Mysql索引的最左前缀原则_Mysql_脚本之家 (jb51.net)

底层实现

底层使用B+树实现

放入数据时，根据加索引的字段对数据进行排序。然后以链表的形式，把数据连接起来

给排序好的数据分页

最顶层常驻内存，不动用磁盘I/O。
层与层之间的查找动用磁盘I/O，浪费时间
两层的b+树，基于主键查询，动用一次磁盘I/O，因为最顶层目录常驻内存

图片出处：【编程不良人】这一次彻底搞定MySQL索引、从此不在恐惧面试

B树与B+树

B树：叶子节点有相同的深度，节点是排序的，索引元素从左到右是递增的，一个节点可以存多个元素。

B＋树：只有叶子节点才有数据，并且排好了顺序，其他节点只存储索引。叶子节点有双向指针连接，提高了访问性能。

Mysql索引底层及优化_摸索前行。的博客-CSDN博客

B+ Tree Visualization (usfca.edu)

B-Tree Visualization (usfca.edu)

B+树 - 百度百科

B-树 - 百度百科

为什么使用b+数而不使用b树？

b树中的每个节点中不止包含key的值，还会包含数据信息；b+数只有叶子节点才有存储数据信息，非叶子节点值存储key值。而InnoDB存储引擎中页的大小固定为16KB，所以使用b+树时树的深度会更小，减少查询时磁盘I/O的次数，提高查询的效率。粗略计算，一个深度为3的B+树可以存储近10亿条数据。

聚簇索引与非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行

innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

聚簇索引与非聚簇索引（也叫二级索引） - 简书 (jianshu.com)

InnoDB引擎

InnoDB的存储文件有两个，后缀名分别是 .frm和 .idb，frm是表的定义文件，idb是个数据文件，包括了数据和索引。

MySQL8.0中不再单独提供.frm，而是合并在.ibd文件中。

.frm在MySQL8中不存在了，那去哪里了呢？_imagpie的博客-CSDN博客_mysql 没有frm文件

如果用InnoDB引擎的话，都会建议建一个主键，并且是整型的自增主键，而不是用UUID（ UUID - 百度百科）。不自己建主键的话，mysql会在你的数据里找一列来自动建主键，多做很多事情，降低性能浪费资源。

为什么InnoDB表必须有主键，并且推荐使用整形的自增主键？

必须有主键：因为没有主键就组织不起来B+树，也就不能形成文件保存下来，就算你平时不加主键也可以建表成功，是因为它底层帮我们找了一个唯一的列数据做主键，如果找不到唯一的，就帮我们额外建一个字段Rowid的整形数值。

推荐整形：因为如果用UUID字符串做主键，数据库在查找数据的时候需要做比较，肯定是整形数值比较速度快，字符串还要转换成ASCII码再比较大小，且字符串占用空间大。

推荐自增主键：因为如果很多个节点存满了，这时候插一个中间点的数据进去，那该节点就会分裂，还会大量导致其他节点也分裂，而且B+树为了保持自身特性，还要进行平衡操作，很费时间。而用自增的话，每次都往后面的节点去插入，那每次分裂的节点都经常是后面的几个，大量的节点分裂和平衡操作的情况是很少的。 InnoDB和MyISAM索引区别_黄贞辉的博客-CSDN博客_myisam和innodb的索引区别

InnoDB支持事务，MyISAM不支持

InnoDB提供一组用来记录事务性活动的日志文件

InnoDB支持外键，MyISAM不支持

InnoDB的update更快，MyISAM的查询更快

MySQL 存储引擎 InnoDB 与 Myisam 的六大区别

INNODB是当前版本myql的默认引擎

MYSQL的存储引擎为什么大部分情况下都用innodb不用myisam_油光发亮的小猛的博客-CSDN博客_mysql为什么使用innodb

事务

事务（计算机术语）_百度百科 (baidu.com)

什么是事务

事务是一个完整的业务逻辑，是一个不可再分的最小工作单元

eg：A给B转账100元
1. 用update将A账户的钱减去100
2. 用update将B账户的钱加上100

以上两个update语句要求同时成功或者同时失败

有DML（增insert、删delete、改update）时就需要考虑事务问题

一句话总结：事务就是多条SQL语句同时成功，或者同时失败

MySQL中默认事务策略

MySQL默认情况下是自动提交事务，即每执行一条DML语句，提交一次事务。而在一些情况下，我们的一个业务需要多条DML语句（如上面提到的转账）,为了保证数据的安全，必须要求同时一个业务的DML语句都成功后（执行转账的1和2两个update语句都成功）再提交事务。

所以我们可以用start transaction;关闭MySQL的自动提交事务

MySQL开启事务的方式_Mysql_脚本之家 (jb51.net)

事务原理

InnoDB提供一组用来记录事务性活动的日志文件

事务开启;
insert;
insert;
delete;
update;
事务结束;

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务commit;：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着，事务的结束。并且是一种全部成功的结束。
回滚事务rollback;：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件。回滚事务标志着，事务的结束。并且是一种全部失败的结束。

老杜带你学_mysql入门基础（mysql基础视频+数据库实战)

UndoLog浅析_黄子何的博客-CSDN博客_undolog

事务的特性

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。使用Undo Log（逻辑日志）实现
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。使用锁实现

举例：抖音红包雨：1.从抖音的账户上扣除一个小目标 2.给羊老师的账户加上一个小目标

原子性问题：抖音的账户上扣了一个亿之后，假设服务器挂了，还没来得及给羊老师账户上加一个亿
一致性问题：假设抖音的账户上只有0.5个亿，但是扣减1个亿的操作成功了
隔离性问题：羊老师从抖音抢了一个亿红包，又从头条抢了一个亿红包，两个转账同时进行，假设他们都以为是从零开始更新羊老师的账户余额，羊老师最后得到了一个亿
持久性问题：抖音的账户上扣了一个亿，然后羊老师账户上加了一个小目标，但都还没写到磁盘上。这个时候，如果服务器挂了

隔离级别

隔离级别	脏读	不可重复读	幻读（Phantom Read）	备注
未提交读（Read uncommitted）	可能	可能	可能	最低隔离级别
已提交读（Read committed）	不可能	可能	可能	Oracle的默认隔离级别
可重复读（Repeatable read）	不可能	不可能	可能	MySQL的默认隔离级别
可串行化（Serializable）	不可能	不可能	不可能	最高隔离级别

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

本小结来源：Innodb中的事务隔离级别和锁的关系

项目实践

视频上传前情提示

在Gin中上传视频到七牛云 | 青训营笔记

方案一：上传视频到七牛云的formUploader.Put()和将视频数据插入数据库的mysql.InsertVideo(newVideo)是同步的。

问题：

formUploader.Put()函数上传视频的过程太慢了，用户要在客户端等着formUploader.Put()函数执行完成才能干其他事。
后进行的操作如果失败，先进行的操作就会产生冗余数据。例如先mysql.InsertVideo(newVideo)再formUploader.Put()，如果formUploader.Put()失败，就会在数据库中产生一条冗余的视频信息数据，因为该视频信息对应的视频没有上传到云端。

方案二：起一个协程，在协程中调用formUploader.Put()函数，实现实现异步上传视频，减少用户在客户端的等待时间。

问题：

formUploader.Put()函数出错，但是视频信息已经加入了数据库，所以获取feed流时也能从数据库中查到这条视频的信息，可是因为formUploader.Put()函数出错了，所以没有视频的画面。
formUploader.Put()函数没出错，因为视频formUploader.Put()和mysql.InsertVideo(newVideo)是异步的，而向数据库中插入数据肯定快于上传视频到云端，所以在视频数据已经插入数据库而视频还没上传到云端这段时间里，获取feed流时也能从数据库中查到这条视频的信息，但是没有视频画面。

从小白到高手，10 图教你同步与异步 (baidu.com)

使用事务优化视频上传

开启事务dbWithTransaction := db.Begin()
执行插入视频数据的sql语句
调用formUploader.Put()函数
- 如果Put()执行失败就回滚事务dbWithTransaction.Rollback()
- 如果Put()执行成功就提交事务dbWithTransaction.Commit()

由于MySQL的默认隔离级别为可重复读，不会出现脏读的现象(读到未提交数据)，所以如果Put()函数没有成功执行，即没有成功提交事务，获取视频列表时就不会获取到这条视频。

这里把视频上传功能抽象为一个只含两个操作（插入数据库的sql和formUploader.Put()函数）的事务，两个操作必须同时成功或者同时失败。

使用事务优化评论

添加评论时，先开启事务，然后执行插入的sql，然后把评论添加进redis，如果添加到redis失败就回滚事务，成功就提交事务。
删除评论时，先开启事务，然后执行更新的sql（更新status字段为1，代表着删除）,然后把评论从redis中删除，如果从redis中删除失败就回滚事务，成功就提交事务。

总结MySQL的使用 | 青训营笔记

简介

表设计