引言
作者:竹子爱熊猫
MySQL
数据库从1995
年诞生至今,已经过去了二十多个年头了,到2022.04.26
日为止,MySQL8.0.29
正式发行了GA
版本,在此之前版本也发生了多次迭代,发行了大大小小N
多个版本,其中每个版本中都有各自的新特性,所有版本的特性加起来,用一本书的篇幅也无法完全阐述清楚,因此本章主要会挑重点特性来讲,具体各版本的特性可参考MySQL
官网的开发手册:
-
《MySQL官网-5.6版手册》[1]
-
《MySQL官网-5.7版手册》[2]
-
《MySQL官网-8.0版手册》[3]
这里主要讲一下5.6、5.7、8.0
这三个版本,因为这三个版本属于MySQL
里程碑式的发行版,其实除开这三个版本外,5.1
版本也是早期最主流的版本,但迄今为止基本上都并不用该版本了,因此不再对其做过多描述。
一、MySQL5.6支持的新技术
MySQL5.6
是Oracle
的得意之作,该版本是让MySQL
真正走向高性能数据库的里程碑版本,在此之前的MySQL
版本中,虽然功能也较为丰富,但性能相较于同期的Oracle
数据库而言,其实表现并不佳。直到Oracle
公司接手MySQL
后,首先就对其进行了大刀阔斧的改进,从Oracle
中移植了很多特性过来,同时废弃一些原有的鸡肋功能,也优化了很多内部实现,最终打造出了MySQL5.6
这个版本。
但这里对于
MySQL5.6
中不重要的特性不会罗列,重点讲解一些较为重要的新特性。
1.1、支持只读事务(Read-Only)
在此之前的版本中,MySQL
默认会为每个事务都分配事务ID
,所有事务都一视同仁,但在5.6
版本中开始支持只读事务,MySQL
内部会有两个事务链表,一个是只读事务链表,一个是正常事务链表。
当一个事务中只有读操作时,
MySQL
并不会为这些事务分配ID
,默认全部为0
(但是会分配查询ID
),然后将其标记为一个只读事务,并加入只读事务链表中,直到当这个事务中出现变更数据的操作时,才会正式为其分配事务ID
,以及将其挪动到正常事务链表中。
这样做的好处在于:其他事务利用MVCC
机制读取数据时,生成的ReadView
读视图中的活跃事务链表会小很多很多,因此遍历的速度更快,同时也无需为其分配回滚段,从而进一步提升了MySQL
整体的查询性能。
1.2、InnoDB存储引擎增强
主要就说一下InnoDB
的核心:BufferPool
缓冲池相关的改进项,主要有两点:刷盘策略改进、缓冲池预热。
1.2.1、缓冲池刷盘策略优化
在之前的版本的InnoDB-BufferPool
缓冲池中,变更过的数据页会共用MySQL
后台的刷盘线程,也就是redo-log、undo-log、bin-log.....
一系列内存到磁盘的刷盘工作,都是采用同一批线程来完成。在MySQL5.6
版本中,BufferPool
引入了独立的刷盘线程,也就意味着缓冲池中变更过的数据页会由专门的线程来负责刷盘,这样能够提升缓冲池的刷盘效率,无需排队等待刷写。
同时
BufferPool
的刷盘线程,还支持开启多线程并发刷盘操作,这样在缓冲池较大的情况下,能够进一步提升刷盘的效率,从而让数据落盘的效率更快,从一定程度上也提升了数据的安全性,毕竟内存中的数据随时都有几率丢失,但落盘后基本上不是硬件损坏,都有可能恢复过来。
1.2.2、BufferPool缓冲池预热
缓冲池预热是一种特别好的机制,在之前版本的内存缓冲池中,当MySQL
关闭时,原本内存中的热点数据都会被清空,重启后所有的热点数据又需要经过时间的沉淀,然后才能留在内存中。但MySQL5.6
版本中,每次关闭MySQL
时都会将内存中的热点数据页保存到磁盘中,当重启时会直接从磁盘中载入之前的热点数据,避免了热点数据的重新“选拔”!
这样做的好处无疑是巨大的,举个例子就能够理解,如下:
现在整个村庄中选拔综合能力最强的男人,原本已经选拔了一批相对来说最优秀的人出来,但由于村庄临时出现变故需要集体外出,所以本次选拔赛会被搁浅,当处理好变故后再次开启选拔赛,又重新从头开始选拔,这显然十分影响效率。
而预热的含义是:出现意外变故时,先将上次选拔赛中最优秀的那批人,把对应的名字保存在一个名单中,处理完变故后,只需要找到这个名单,把名单上的人喊过来继续选拔即可,这样做显然将效率提升了N
倍。
1.3、新增Performance_Schema库监控全局资源
如果对MySQL
比较熟悉的小伙伴应该清楚,在之前的版本中存在一个名为information_schema
的库,其中会记录一些MySQL
运行期间需要用到的表,比如视图管理、存储过程与函数的管理、临时表的管理、会话的管理、触发器的管理、表分区的管理......,这些内容的信息都会被存储到information_schema
库中。
而在MySQL5.6
中,官方又加入了一个新的自带库:performance_schema
,这里面会记录:数据库整体的监控信息,比如事务监控信息、最近执行的SQL
信息、最近连接的客户端信息、数据库各空间的使用信息......,基于这个库可以在线上构建出一个完善的MySQL
监控系统:
-
Statements/execution stages
:MySQL
统计的一些消耗资源较高的SQL
语句。 -
Table and Index I/O
:MySQL
统计的那些表和索引会导致I/O
负载过高。 -
Table Locks
:MySQL
统计的表中数据的锁资源竞争信息。 -
Users/Hosts/Accounts
:消耗资源最多的客户端、IP
机器、用户。 -
Network I/O
:MySQL
统计的一些网络相关的资源情况。 -
.......
1.4、索引下推机制(Index Condition Pushdown)
Index Condition Pushdown
索引下推简称ICP
,它是MySQL5.6
版本以后引入的一种优化机制,以下述用户表为例,这张表上基于姓名、性别、密码字段建立了一个联合索引,此时先来看看下面的情况:
select * from `zz_users`;
+
| user_id | user_name | user_sex | password | register_time |
+
| 1 | 熊猫 | 女 | 6666 | 2022-08-14 15:22:01 |
| 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 |
| 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 |
| 4 | 猫熊 | 女 | 8888 | 2022-09-17 23:48:29 |
+
INSERT INTO `zz_users` VALUES(5,"竹竹","女","8888","2022-09-20 22:17:21");
SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";
首先为了更加直观的讲清楚索引下推,因此先再向用户表中增加一条数据。然后再来看看后面的查询SQL
,这条SQL
会使用联合索引吗?答案是会的,但只能部分使用,因为联合索引的每个节点信息大致如下:
{
["熊猫","女","6666"] : 1,
["竹子","男","1234"] : 2,
["子竹","男","4321"] : 3,
["猫熊","男","4321"] : 4,
["竹竹","女","8888"] : 5
}
由于前面使用的是模糊查询,但%
在结尾,因此可以使用竹
这个字作为条件在联合索引中查询,整个查询过程如下:
-
①利用联合索引中的
user_name
字段找出「竹子、竹竹」两个索引节点。 -
②返回索引节点存储的值「
2、5
」给Server
层,然后去逐一做回表扫描。 -
③在
Server
层中根据user_sex="男"
这个条件逐条判断,最终筛选到「竹子」这条数据。
有人或许会疑惑,为什么user_sex="男"
这个条件不在联合索引中处理呢?因为前面是模糊查询,所以拼接起来是这样的:竹x男
,由于这个x
是未知的,因此无法根据最左前缀原则去匹配数据,最终这里只能使用联合索引中user_name
字段的一部分,后续的user_sex="男"
还需要回到Server
层处理。
那什么又叫做索引下推呢?也就是将
Server
层筛选数据的工作,下推到引擎层处理。
以前面的案例来讲解,MySQL5.6
加入索引下推机制后,其执行过程是什么样子的呢?
-
①利用联合索引中的
user_name
字段找出「竹子、竹竹」两个索引节点。 -
②根据
user_sex="男"
这个条件在索引节点中逐个判断,从而得到「竹子」这个节点。 -
③最终将「竹子」这个节点对应的「
2
」返回给Server
层,然后聚簇索引中回表拿数据。
相较于没有索引下推之前,原本需要做「2、5
」两次回表查询,但在拥有索引下推之后,仅需做「2
」一次回表查询。
索引下推在
MySQL5.6
版本之后是默认开启的,可以通过命令set optimizer_switch='index_condition_pushdown=off|on';
命令来手动管理。
1.5、MRR(Multi-Range Read)机制
Multi-Range Read
简称为MRR
机制,这也是和索引下推一同在MySQL5.6
版本中引入的性能优化措施,那什么叫做MRR
优化呢?
一般来说,在实际业务中我们应当尽量通过索引覆盖的特性,减少回表操作以降低
IO
次数,但在很多时候往往又不得不做回表才能查询到数据,但回表显然会导致产生大量磁盘IO
,同时更严重的一点是:还会产生大量的离散IO
,下面举个例子来理解。
SELECT * FROM `zz_student_score` WHERE `score` BETWEEN 0 AND 59;
上述这条SQL
所做的工作很简单,就是在学生成绩表中查询所有成绩未及格的学生信息,假设成绩字段上存在一个普通索引,那思考一下,这条SQL
的执行流程是什么样的呢?
-
①先在成绩字段的索引上找到
0
分的节点,然后拿着ID
去回表得到成绩零分的学生信息。 -
②再次回到成绩索引,继续找到所有
1
分的节点,继续回表得到1
分的学生信息。 -
③再次回到成绩索引,继续找到所有
2
分的节点...... -
④周而复始,不断重复这个过程,直到将
0~59
分的所有学生信息全部拿到为止。
那此时假设此时成绩0~5
分的表数据,位于磁盘空间的page_01
页上,而成绩为5~10
分的数据,位于磁盘空间的page_02
页上,成绩为10~15
分的数据,又位于磁盘空间的page_01
页上。此时回表查询时就会导致在page_01、page_02
两页空间上来回切换,但0~5、10~15
分的数据完全可以合并,然后读一次page_01
就可以了,既能减少IO
次数,同时还避免了离散IO
。
而
MRR
机制就主要是解决这个问题的,针对于辅助索引的回表查询,减少离散IO
,并且将随机IO
转换为顺序IO
,从而提高查询效率。
那MRR
机制具体是怎么做的呢?MRR
机制中,对于辅助索引中查询出的ID
,会将其放到缓冲区的read_rnd_buffer
中,然后等全部的索引检索工作完成后,或者缓冲区中的数据达到read_rnd_buffer_size
大小时,此时MySQL
会对缓冲区中的数据排序,从而得到一个有序的ID
集合:rest_sort
,最终再根据顺序IO
去聚簇/主键索引中回表查询数据。
SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';
可以通过上述这条命令开启或关闭MRR
机制,MySQL5.6
及以后的版本是默认开启的。
1.6、主从数据同步的复制改进
随着互联网时代浪潮的兴起,系统中的并发量、数据量日益增长,传统的单库在有些情况下就有些乏力,最初只能通过不断升级硬件配置的手段来提升性能。可单机的性能无论如何升级硬件,总有达到瓶颈的一天,因此分布式的概念开始进入大家眼中,而MySQL5.6
中也针对于多节点部署的情况,其数据同步问题做了大幅度优化。
在
MySQL5.6
中,针对于主从数据同步的问题,主要引入了GTID
复制、无损复制(增强半同步复制)、延时复制、并行复制这四种技术,但由于目前《MySQL专栏》[4]还未开始发布“MySQL
高可用”相关的文章,因此这些内容会在后续的《主从复制篇》阐述。
1.7、MySQL5.6中的其他特性
前面介绍的几点,属于比较重要的一些知识,但除此之外,5.6
版本中还存在很多特性与优化项,如:
-
索引增强:全文索引支持
InnoDB
与亚洲语种分词、支持空间索引等。 -
表分区增强:单表分区数量最大可创建
8192
个、分区锁性能提升、支持cloumns
分区类型。 -
增强日期类型:
time、datetime、timestamp
精度提升到微秒级,datetime
容量缩减到5
字节。 -
日志增强:
Redo-log
文件大小限制由4G→512G
、Undo-log
文件可独立指定位置存储。 -
支持在线
DDL(Online DDL)
、对limit
语句做了优化......
除开上述外,还有很多很多与之前不同的变更,官方号称在5.6
中修复了1667
个Bug
,因此想要更为全面的了解,可阅读《MySQL官网-5.6版手册》[5]。
二、MySQL5.7引入的新特性
相较于前辈5.6
、后辈8.0
而言,MySQL5.7
版本就显的中规中矩,其中没有发生太多的改变,MySQL5.7
更偏向于5.6
的稳定版,毕竟5.6
版本中步子迈的太大,很多方面的技术都需要根据市场反馈做细微调整,因此成功的为MySQL5.7
做好了铺垫,一般的项目如果不选用MySQL8.0
,基本上都会选择MySQL5.7
而不是5.6
。
但
MySQL5.7
中也存在几个能令人眼前一亮的优化项,接着就重点聊一聊这些重点~
2.1、引入共享排他锁
在MySQL5.7
之前的版本中,数据库中仅存在两种类型的锁,即共享锁与排他锁,但是在MySQL5.7.2
版本中引入了一种新的锁,被称之为(SX)
共享排他锁,这种锁是共享锁与排他锁的杂交类型,至于为何引入这种锁呢?聊它之前需要先理解SMO
问题:
在
SQL
执行期间一旦更新操作触发B+Tree
叶子节点分裂,那么就会对整棵B+Tree
加排它锁,这不但阻塞了后续这张表上的所有的更新操作,同时也阻止了所有试图在B+Tree
上的读操作,也就是会导致所有的读写操作都被阻塞,其影响巨大。因此,这种大粒度的排它锁成为了InnoDB
支持高并发访问的主要瓶颈,而这也是MySQL 5.7
版本中引入SX
锁要解决的问题。
那想一下该如何解决这个问题呢?最简单的方式就是减小SMO
问题发生时,锁定的B+Tree
粒度,当发生SMO
问题时,就只锁定B+Tree
的某个分支,而并不是锁定整颗B+
树,从而做到不影响其他分支上的读写操作。
那
MySQL5.7
中引入共享排他锁后,究竟是如何实现的这点呢?先聊聊SX
锁的特性,它不会阻塞S
锁,但是会阻塞X、SX
锁,下面展开来说说。
在聊之前得搞清楚SQL
执行时的几个概念:
-
读取操作:基于
B+Tree
去读取某条或多条行记录。 -
乐观写入:不会改变
B+Tree
的索引键,仅会更改索引值,比如主键索引树中不修改主键字段,只修改其他字段的数据,不会引起节点分裂。 -
悲观写入:会改变
B+Tree
的结构,也就是会造成节点分裂,比如无序插入、修改索引键的字段值。
在MySQL5.6
版本中,一旦有操作导致了树结构发生变化,就会对整棵树加上排他锁,阻塞所有的读写操作,而MySQL5.7
版本中,为了解决该问题,对于不同的SQL
执行,流程就做了调整。
2.1.1、MySQL5.7中读操作的执行流程
-
①读取数据之前首先会对
B+Tree
加一个共享锁。 -
②在基于树检索数据的过程中,对于所有走过的叶节点会加一个共享锁。
-
③找到需要读取的目标叶子节点后,先加一个共享锁,释放步骤②上加的所有共享锁。
-
④读取最终的目标叶子节点中的数据,读取完成后释放对应叶子节点上的共享锁。
2.1.2、MySQL5.7中乐观写入的执行流程
-
①乐观写入之前首先会对
B+Tree
加一个共享锁。 -
②在基于树检索修改位置的过程中,对于所有走过的叶节点会加一个共享锁。
-
③找到需要写入数据的目标叶子节点后,先加一个排他锁,释放步骤②上加的所有共享锁。
-
④修改目标叶子节点中的数据后,释放对应叶子节点上的排他锁。
2.1.3、MySQL5.7中悲观写入的执行流程
-
①悲观更新之前首先会对
B+Tree
加一个共享排他锁。 -
②由于①上已经加了
SX
锁,因此当前事务执行过程中会阻塞其他尝试更改树结构的事务。 -
③遍历查找需要写入数据的目标叶子节点,找到后对其分支加上排他锁,释放①中加的
SX
锁。 -
④执行
SMO
操作,也就是执行悲观写入操作,完成后释放步骤③中在分支上加的排他锁。
如果需要修改多个数据时,会在遍历查找的过程中,记录下所有要修改的目标节点。
2.1.4、MySQL5.7中并发事务冲突分析
观察上述讲到的三种执行情况,对于读操作、乐观写入操作而言,并不会加SX
锁,共享排他锁仅针对于悲观写入操作会加,由于读操作、乐观写入执行前对整颗树加的是S
锁,因此悲观写入时加的SX
锁并不会阻塞乐观写入和读操作,但当另一个事务尝试执行SMO
操作变更树结构时,也需要先对树加上一个SX
锁,这时两个悲观写入的并发事务就会出现冲突,新来的事务会被阻塞。
但是要注意:当第一个事务寻找到要修改的节点后,会对其分支加上
X
锁,紧接着会释放B+Tree
上的SX
锁,这时另外一个执行SMO
操作的事务就能获取SX
锁啦!
其实从上述中可能得知一点:MySQL5.7
版本引入共享排他锁之后,解决了5.6
版本发生SMO
操作时阻塞一切读写操作的问题,这样能够在一定程度上提升了InnoDB
表的并发性能。
最后要注意:虽然一个执行悲观写入的事务,找到了要更新/插入数据的节点后会释放
SX
锁,但是会对其上级的叶节点(叶分支)加上排他锁,因此正在发生SMO
操作的叶分支,依旧是会阻塞所有的读写行为!
上面这句话啥意思呢?也就是当一个要读取的数据,位于正在执行SMO
操作的叶分支中时,依旧会被阻塞。
2.2、MySQL开始支持json格式
随着非结构化数据的存储需求持续增长,各类非关系型数据库应运而生,例如大名鼎鼎的MongoDB
,各大关系型数据库为了防止市场占用率流失,也开始弥补对于这块的支持,因此在MySQL5.7.8
版本中也支持了json
数据类型,并且为其提供了一系列操作的API
。
虽然说
MySQL
也支持了json
格式存储,但显然是亡羊补牢,为时已晚,自然无法抢过MongoDB
的市场占用率,从性能和存储容量来说,MySQL
也无法竞争过MongoDB
,但相较于其他非结构化数据库,MySQL
存储json
数据有两大优势:①对于json
数据的API
操作支持事务。②支持为一个表字段设置json
格式,也就意味着MySQL
中可以将结构化数据和非结构化数据共同存储。
2.3、MySQL5.7中的其他特性
除开上述两点值得展开叙说的特性外,其他的基本上是一些细微变化,因此就不对其进行展开叙述啦,这里稍微罗列一下MySQL5.7
版本中,其他的特性支持,如下:
-
临时表优化,临时表的写操作不记录
redo-log
、不为其生成缓冲数据页,减小资源占用。 -
多接点部署时,数据同步复制再次优化,支持多主/源复制、以及真正意义上的并行复制等。
-
引入了虚拟列的实现,类似于
Oracle
数据库中的函数索引。 -
移除了默认的
test
数据库,以及默认不会创建匿名用户,引入密码过期策略。 -
触发器增强,表上同一种事件、同一时机的触发器可创建多个,之前则只能允许创建一个。
-
推出了新的
mysqlpump
工具,用于数据的逻辑备份、引入了新的客户端工具mysqlsh
等。 -
支持通过
max_execution_time
限制一条SQL
的执行超时时间、支持innodb_deadlock_detect
死锁检测。 -
GIS
空间数据类型增强,使用Boost.Geometry
代替原有的GIS
算法,InnoDB
支持空间索引。 -
.......
除此之外其实也依旧存在其他很多细节方面的新特性支持和优化项,具体可参考《MySQL官网-5.7版手册》[6]。
三、MySQL8.0发生的大变更
在MySQL8.0
发行前,基本上所有使用MySQL
数据库的企业/个人项目,基本上都属于MySQL5.6/5.7
的钉子户,基本上不会再选择除此之外的发行版,但随着MySQL8.0
的发行,打破了这个局面,官方号称经压测后,8.0
比5.7
性能足足提升了200%
,这无疑对于MySQL
的忠实用户而言,显然是一个十分巨大的诱惑。
因为按照理论来说,只要你将项目中的
MySQL
,从原有的MySQL5.7
版本升级到MySQL8.0
版本,从数据库的性能上来说,无需你手动做任何优化,就能够让性能翻倍。
不过确实在MySQL8.0
版本中,再次推出了非常多重要的新特性,也推出了很多关于性能的优化项,因此接下来展开聊聊最新的MySQL8.0
系版本。
3.1、移除查询缓存(Query Cache)
Query Cahce
查询缓存的设计初衷很好,也就是利用热点探测技术,对于一些频繁执行的查询SQL
,直接将结果缓存在内存中,之后再次来查询相同数据时,就无需走磁盘,而是直接从查询缓存中获取数据并返回。
听起来似乎还不错呀,好像确实能带来不小的性能提升呢?但实则很鸡肋,为啥?看个例子。
select * from zz_users where user_id=1;
select * from zz_users where user_id = 1;
比如上述这两条SQL
语句,在我们看来是不是一样的?确实,都是在查询ID=1
的用户数据,但奇葩的事情出现了,MySQL
的查询缓存会把它当做两条不同的SQL
,也就是假如上面的第一条SQL
,其查询结果被放入到了缓存中,第二条SQL
依旧无法命中这个缓存,会继续走表查询的形式获取数据,Why
?
因为
MySQL
查询缓存是以SQL
的哈希值来作为Key
的,上面两条SQL
虽然一样,但是后面的查询条件有细微差别:user_id=1、user_id = 1
,也就是一条SQL
有空格,一条没有。
由于这一点点细微差异,会导致两条SQL
计算出的哈希值完全不同,因此无法命中缓存,是不是很鸡肋?还有多种情况:user_id =1、user_id= 1
,空格处于的前后位置不同,也会导致缓存失效。
也正是由于方方面面的原因,所以查询缓存在
MySQL8.0
中被完全舍弃了,即移除掉了查询缓存区,各方面原因如下:
-
①缓存命中率低:几乎大部分
SQL
都无法从查询缓存中获得数据。 -
②占用内存高:将大量查询结果放入到内存中,会占用至少几百
MB
的内存。 -
③增加查询步骤:查询表之前会先查一次缓存,查询后会将结果放入缓存,额外多几步开销。
-
④缓存维护成本不小,需要
LRU
算法淘汰缓存,同时每次更新、插入、删除数据时,都要清空缓存中对应的数据。 -
⑤查询缓存是专门为
MyISAM
引擎设计的,而InnoDB
构建的缓冲区完全具备查询缓存的作用。
因为上述一系列原因,再加上项目中一般都会使用Redis
先做业务缓存,因此能来到MySQL
的查询语句,几乎都是要从表中读数据的,所以查询缓存的地位就显得更加突兀,所以在8.0
版本中就直接去掉了,毕竟弊大于利,带来的收益达不到设计时的预期。
3.2、锁机制优化
在MySQL8.0
中的锁机制主要出现了两点优化,一方面对获取共享锁的写法进行了优化,如下:
SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR SHARE;
第二方面则支持非阻塞式获取锁机制,可以在获取锁的写法上加上NOWAIT、SKIP LOCKED
关键字,这样在未获取到锁时不会阻塞等待,使用SKIP LOCKED
未获取到锁时会直接返回空,使用NOWAIT
会直接返回并向客户端返回异常。用法如下:
select ... for update nowait;
select ... for update skip locked;
3.3、在线修改的系统参数支持持久化
在之前的版本中,通过set、set global
的形式修改某个系统变量时,这种方式设置的参数值都是一次性的,也就是修改过的参数并不会被同步到本地,当MySQL
重启时,这些调整过的参数又会回归默认值,如果想要让调整过的参数生效,就必须要手动停止MySQL
,然后去修改my.ini/my.conf
文件,修改完成后再重启数据库服务,这时才能让参数永久生效。
这种方式无疑是十分痛苦的,尤其是在做数据库线上调优时,修改参数后重启又会失效,有时重启忘记再次调整参数,最终导致数据库服务出现问题,这种体验令人很糟心。
而在MySQL8.0
中则彻底优化了这个问题,推出了在线修改参数后,支持持久化到本地文件的机制,也就是通过SET PERSIST
命令来完成,如下:
set transaction isolation level read uncommitted;
set global tx_isolation = "read-committed";
set persist global.tx_isolation = "repeatable-read";
通过set persist
命令持久化的参数,可以通过下述命令来查看:
select * from performance_schema.persisted_variables;
这条命令的本质其实是:基于MySQL
自带的performance_schema
监控库查询持久化过的参数。
其实参数持久化的原理也非常简单,当执行
set persist
命令时,会将改变过的参数写入到本地的mysqld-auto.cnf
文件中,MySQL
每次启动时都会读取这个文件中的值,如果该文件中存在参数,则会直接将其加载,从而实现了一次修改,永久有效。
但当你想要参数不再持久化到本地时,可以选择删除安装目录下的mysqld-auto.cnf
文件,或执行reset persist
命令来清除,但这两种方式都只对下次重启时生效,毕竟本次参数已经被载入内存了,所以只能通过再次手动修改的方式复原。
3.4、增强多表连接查询
在之前的MySQL
版本中,仅支持交叉连接、内连接、左外连接、右外连接四种连接类型,这四种连接都会采用默认的连接算法,而在8.0
版本中提供了哈希连接、反连接两种连接优化的支持。
3.1.1、哈希连接(Hash Join)
所谓的哈希连接其实并非一种新的连表方式,而是一种连表查询的算法,在关系型数据库中多表连查一般有三种算法:Hash-Join
哈希散列连接、Sort-Merge-Join
排序合并连接、Nest-Loop-Join
嵌套循环连接,这三种连表算法在Oracle
数据库中都支持,但MySQL
之前的版本中,所有的连表方式都仅支持Nest-Loop-Join
嵌套循环连接,对于这点在《SQL优化篇-以小驱大》中聊到过。
而到了
MySQL8.0.18
版本发布后,MySQL
对内连接的方式支持了哈希连接算法,那究竟啥叫哈希连接算法呢?和之前传统的循环连接算法又有啥区别呢?接下来一起展开聊一聊。
哈希连接算法和循环连接算法的区别
先来看看传统的循环连接算法的连表查询过程是怎么样的呢?如下:
在循环连接算法中,会首先选择一张小表作为驱动表,然后依次将驱动表中每一条数据作为条件,去被驱动表中做遍历,最终得到符合连接条件的所有数据,也就是会形成一个下述的伪逻辑:
for(数据 x : 驱动表){
for(数据 y : 被驱动表){
if (x == y){
}
}
}
这种循环连接的算法中,显然会造成巨大的开销,因为驱动表每条数据都需要和被驱动表的完整数据做一次遍历。也正是为了解决这个问题,因为MySQL8.0
中引入了哈希连接算法,过程如下:
在哈希连接算法中会分为两个阶段:
-
构建阶段:选择一张小表作为构建表,接着会基于连接字段做哈希处理,生成哈希值放入内存中构建出一张哈希表。
-
探测阶段:遍历大表的每一行数据,然后对连接字段做哈希处理,通过生成的哈希值与内存哈希表做比较,符合条件则放入结果集中。
对比之前的循环连接算法,这种哈希连接算法带来的性能提升直线提升N
倍,因为在循环连接算法中,需要遍历count(驱动表)
次,即驱动表中有多少条数据就要遍历多少次。而在这种算法中,只需要将大表遍历一次,伪逻辑代码如下:
Map hashTable = new HashMap();
for(数据 x : 构建表){
hashTable.put(x);
}
for(数据 y : 探测表){
if (hashTable.get(y) != null){
}
}
哈希连接对比循环连接算法而言,主要在两方面可以得到性能提升:
-
①哈希连接算法中,只需要将大表遍历一次,但循环连接算法需要遍历
N
次。 -
②哈希连接探测阶段,做连接判断时只需要先对数据做一次哈希处理,然后在内存中查找即可,复杂度仅为
O(1)
,但循环连接算法的复杂度为O(n)
。
哈希连接算法的致命问题
但虽然哈希连接算法能够带来卓越的性能提升,但也存在一个致命问题,就是内存中join_buffer_size
的容量无法完全载入构建表的哈希数据时怎么办呢?这里就有两种解决方案:
-
①分批处理,将构建表的数据拆分为几部分,每次载入一部分到内存,但这样会导致大表的遍历次数,随着分批次数变大而增多。
-
②利用磁盘完成,也就是首先将构建表的所有数据做哈希处理,放不下时将一部分处理好的哈希数据放入磁盘,在探测阶段遍历大表时,每次对大表数据生成哈希值后,做判断时从磁盘依次读取处理好的哈希值做判断。
而MySQL
中选择的是第二种,也就是当内存无法完全放下构建表的哈希数据时,会采用磁盘+内存混合的模式执行哈希连接。
MySQL什么情况下会选用哈希连接?
首先并不是多表连接的情况下都会使用哈希连接算法,该算法有几个硬性限制:
-
①目前哈希连接算法仅支持内连接的多表连查方式。
-
②哈希连接算法必须要求存在等值连接条件,即
a.id=b.id
才行,a.id>b.id
是不行的。 -
③如果连接字段可以走索引查询的情况下,默认依旧会采用循环连接算法。
第二点的原因在于:哈希连接算法生成的哈希值是无序的,所以必须要用等值连接才行。
第三点的原因在于:连接查询时走索引的效率并不低,哈希连接需要生成哈希表,因此需要时间,因此在能够走索引连表的情况下,哈希连接算法的效率反而比不上循环连接。
也就是说,当连表时存在等值连接条件,并且未命中索引的情况下,
MySQL
默认会采用哈希连接算法来完成连表查询,不过还有一种情况也会使用,就是笛卡尔积情况,即不指定连接条件的情况下也会使用哈希连接,此时MySQL
会直接对整条数据生成哈希表。
对于哈希连接算法,MySQL
是默认开启的,咱们可通过set optimizer_switch="hash_join=off";
的形式来手动控制开关。
3.1.2、反连接(Anti Join)
反连接是MySQL8.0
对于一些反范围查询操作的优化,主要针对于下述几种情况会做优化:
-
NOT IN (SELECT … FROM …)
-
NOT EXISTS (SELECT … FROM …)
-
IN (SELECT … FROM …) IS NOT TRUE
-
EXISTS (SELECT … FROM …) IS NOT TRUE
-
IN (SELECT … FROM …) IS FALSE
-
EXISTS (SELECT … FROM …) IS FALSE
在MySQL
早些版本中,使用NOT EXISTS、NOT IN、IS NOT...
这类操作时有可能会导致索引失效,而且也会让查询效率变低,因此MySQL8.0
版本中会对上述几类语句进行优化,当你的SQL
语句使用了上述语法检索数据时,在MySQL
内部会将其转变为反连接类型的查询语句。
也就是会将右边的子查询结果集,变为一张物理临时表,然后基于条件字段做连接查询,官方号称在某些场景下,能够让上述几类语句的查询性能提升
20%
,但对于这块我没做深入研究,因此就不展开叙述啦。
3.5、增强索引机制
在8.0
中官方再一次对索引机制动刀,首先对联合索引提供了一种跳跃扫描机制的支持,也就意味着使用联合索引时,就算未遵循最左前缀匹配原则,也可以使用联合索引来检索数据。除此之外,还有另外三种新的索引特性:隐藏索引、降序索引以及函数索引。
3.5.1、索引跳跃式扫描机制(Index Skip Scan)
在之前讲《索引应用篇》[7]时,咱们初次提到了最左前缀匹配原则,也就是SQL
的查询条件中必须要包含联合索引的第一个字段,这样才能命中联合索引查询,但实际上这条规则也并不是100%
遵循的。因为在MySQL8.0
版本中加入了一个新的优化机制,也就是索引跳跃式扫描,这种机制使得咱们即使查询条件中,没有使用联合索引的第一个字段,也依旧可以使用联合索引,看起来就像跳过了联合索引中的第一个字段一样,这也是跳跃扫描的名称由来。
但跳跃扫描究竟是怎么实现的呢?上个栗子快速理解一下。
比如此时通过(A、B、C)
三个列建立了一个联合索引,此时有如下一条SQL
:
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`;
按理来说,这条SQL
既不符合最左前缀原则,也不具备使用索引覆盖的条件,因此绝对是不会走联合索引查询的,但思考一个问题,这条SQL
中都已经使用了联合索引中的两个字段,结果还不能使用索引,这似乎有点亏啊对不?因此MySQL8.x
推出了跳跃扫描机制,但跳跃扫描并不是真正的“跳过了”第一个字段,而是优化器为你重构了SQL
,比如上述这条SQL
则会重构成如下情况:
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";
其实也就是MySQL
优化器会自动对联合索引中的第一个字段的值去重,然后基于去重后的值全部拼接起来查一遍,一句话来概述就是:虽然你没用第一个字段,但我给你加上去,今天这个联合索引你就得用,不用也得给我用。
当然,如果熟悉
Oracle
数据库的小伙伴应该知道,跳跃扫描机制在Oracle
中早就有了,但为什么MySQL8.0
版本才推出这个机制呢?还记得咱们在《MySQL架构篇》[8]中的闲谈嘛?MySQL
几经转手后,最终归到了Oracle
旗下,因此跳跃扫描机制仅是Oracle
公司:从Oracle
搬到了“自己的MySQL
”上而已。
但是跳跃扫描机制也有很多限制,比如多表联查时无法触发、SQL
条件中有分组操作也无法触发、SQL
中用了DISTINCT
去重也无法触发.....,总之有很多限制条件,具体的可以参考《MySQL官网8.0-跳跃扫描》[9]。
其实这个跳跃性扫描机制,只有在唯一性较差的情况下,才能发挥出不错的效果,如果你联合索引的第一个字段,是一个值具备唯一性的字段,那去重一次再拼接,几乎就等价于走一次全表。
对于索引跳跃扫描机制,可以通过set @@optimizer_switch = 'skip_scan=off|on';
命令来选择开启或关闭跳跃式扫描机制。
3.5.2、隐藏索引
隐藏索引并不是一种新的索引类型,而是一种对索引的骚操作,可以理解为对每个索引新增了一个开关按键,主要用于测试环境和灰度场景,在MySQL8.0
版本中,可以通过INVISIBLE、VISIBLE
来控制索引的开关:
-
当对一个索引使用
INVISIBLE
后,会关闭这个索引,优化器在执行SQL
时无法发现和使用它。 -
当对一个索引使用
VISIBLE
后,会将索引从隐藏状态恢复到正常状态。
所谓的隐藏索引,就是指将一个已经创建的索引“藏起来”,被藏起来的索引是无法被优化器探测到的,因此执行SQL
语句时,就算语句中显式使用了索引字段,优化器也不会选择走这条索引。
这个特性主要是针对于调优、测试场景而研发的,如果隐藏一个索引后,在压测场景下不会对业务产生影响,如果经过反复测试后依旧不影响
SQL
性能,那这条索引则可以被判定为无用索引,可以将其删除,隐藏索引的用法如下:
alter table 表名 alter index 索引名 invisible;
alter table 表名 alter index 索引名 visible;
3.5.3、降序索引
Descending index
降序索引是一种索引的特性,不知大家是否还记得之前定义索引的语句呢,如下:
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
在创建索引时,可以通过ASC、DESC
来定义一个索引是按升序还是降序存储索引键,但本质上这种语法,在MySQL8.0
之前,就算你手动写明了DESC
降序,在创建时依旧会默认忽略,也就是本质上还是按升序存储索引键的,当你要对某个倒序索引的字段做倒序时,依旧会发生filesort
排序的动作。
到了
MySQL8.0
官方正式支持降序索引,也就是当对一个字段建立降序索引后,做降序查询时不需要再次排序,可直接根据索引进行取值。
下面来基于MySQL5.1、MySQL8.0
举个简单的例子感受一下:
create table zz_table (
id1 int,
id2 int,
index idx1 (id1 ASC, id2 ASC),
index idx2 (id1 ASC, id2 DESC),
index idx3 (id1 DESC, id2 ASC),
index idx4 (id1 DESC, id2 DESC)
);
insert into zz_table values(1,2);
上述创建了一张索引的测试表zz_table
,其中对于两个字段建立四个索引:全升序、全降序、前降后升、前升后降,然后随便插入了一条数据,接着来看看SQL
执行情况:
explain select * from zz_table order by id1 asc, id2 desc;
+
| id | select_type | table | .... | Extra |
+
| 1 | SIMPLE | zz_table | .... | Using index; Using filesort |
+
explain select * from zz_table order by id1 asc, id2 desc;
+
| id | select_type | table | partitions | ..... | Extra |
+
| 1 | SIMPLE | zz_table | NULL | ..... | Backward index scan; Using index |
+
重点观察最后一个Extra
字段,在MySQL8.0
之前的版本中,虽然对id2
建立了倒序索引,但实际做倒序查询时依旧会发生Using filesort
排序动作,而MySQL8.0
中则直接是Backward index scan
反向索引扫描,并未触发排序动作。
3.5.4、函数索引
还记得在聊MySQL5.7
版本时,最后贴出的其他特性嘛?其中就有一条是引入了隐藏列,实现了类似于Oracle
中的函数索引,但其实功能并不健全,而在MySQL8.0
中真正的支持了函数索引,也就是基于函数去创建索引,如下:
alter table 表名 add index 索引名(函数(列名));
alter table t1 add index fuc_upper(upper(c1));
基于某个字段创建一个函数索引后,之后基于该字段使用函数作为查询条件时,依旧可以走索引,如下:
select * from t1 where upper(c1) = 'ABC';
上述这条SQL
语句,按照之前《索引应用篇-索引字段使用函数导致失效》[10]中聊到的理论,理论上这条语句由于在=
前面使用了函数,显然会导致索引失效,但在MySQL8.0
中可创建函数索引,可以支持条件查询时,在=
号之前使用函数。
不过有一点需要牢记:使用什么函数创建的索引,也仅支持相应函数走索引,比如上面通过了
upper()
函数创建了一个索引,因此upper(c1) = 'ABC'
这种情况可以走索引,但使用其他函数时依旧会导致索引失效,如:lower(c1) = 'abc'
。
3.6、CTE通用表表达式(Common Table Expression)
首先要搞明白这个名称,不是通用表达式,而是通用表、表达式,这个名称上有许多人都叫成通用表达式、公用表达式,这显然是不正确的,因为直接将其中的Table
省去了,所以大家在这里要牢记,正确的叫法应该是通用表表达式。
CTE
通用表表达式究竟是用来干什么事情的呢?CTE
是一个具备变量名的临时结果集,也就是可以将一条查询语句的结果保存到一个变量里面,后续在其他语句中允许直接通过变量名来使用该结果集,语法如下:
with CTE名称
as (查询语句/子查询语句)
select 语句;
上述的语法是一个普通的CTE
用法,同时还有另一种递归的CTE
用法,先举个简单的例子来认识一下最基本的用法:
select * from t1 where xx in (select xx from t2 where yy = "zzz");
with cte_query as
(select xx from t2 where yy = "zzz")
select * from t1 join cte_query on t1.xx = cte_query.xx;
观察上述例子,原本语句中需要使用in
来对子查询的多个结果集做匹配,使用CTE
后可以将子查询的结果集保存在cte_query
变量中,后续的语句中可以将其当作成一张表,然后来做连接查询。
其实看到这里,
CTE
表达式是不是有些类似于临时表的概念?但它会比临时表更轻,查询更快。
除开最基本的表达式外,还有一种名为递归CTE
表达式的概念,它是一种递归算法的实现,可以反复执行一段SQL
,比如当你要查询标签表中,某个顶级标签下所有的子标签时,它的下级可能也存在其它字标签.....,可能一个顶级标签下面有十八层子标签,这时通过传统的查询语句就无法很好实现,而使用递归的CTE
表达式就很简单啦。
递归CTE
表达式只需要使用with recursive
关键字即可,不过这里我不贴具体实现啦,大家感兴趣的可自行研究明白CTE
后再实现该需求。
CTE
表达式除开可以与select
语句嵌套外,还可以与其它类型的语句嵌套,例如:with delete、with update、with recursive、with with、insert with
等,大家感兴趣的也可自行研究,总之CTE
在很多情况下确实比较实用~
3.7、窗口函数(Window Function)
窗口函数可谓是MySQL8.0
中最大的亮点之一,但在尝试去学习时会发现很难理解,先来看看窗口函数的定义。
窗口函数是一种分析型的OLAP
函数,因此也被称之为分析函数,它可以理解成是数据的集合,类似于group by
分组的功能,但之前的MySQL
版本基于某个字段分组后,会将数据压缩到一行显示,如下:
select * from `zz_users`;
+
| user_id | user_name | user_sex | password | register_time |
+
| 1 | 熊猫 | 女 | 6666 | 2022-08-14 15:22:01 |
| 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 |
| 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 |
| 4 | 猫熊 | 女 | 8888 | 2022-09-17 23:48:29 |
+
select user_id from zz_users group by user_sex;
+
| user_id |
+
| 1,4 |
| 2,3 |
+
而窗口函数则不会将数据压缩成一行,也就是表中数据原本是多少行,分组完成后依旧是多少行,窗口函数的语法如下:
[window 窗口函数名 as (window_spec) [, 窗口函数名 AS (window_spec)] ...]
窗口函数名(窗口名/表达式)
over (
[partition_defintion]
[order_definition]
[frame_definition]
)
其实这个语法看起来不是特别能让人理解,所以结合具体的场景来举例,语法如下:
窗口函数 over([partition by 字段名 order by 字段名 asc|desc])
窗口函数 over 窗口名 ... window 窗口名
as ([partition by 字段名 order by 字段名 asc|desc])
一眼看下来,结果还是令人不理解,对吗?这先别急,看不懂也没关系,后面会举例说明,先来看看MySQL8.0
中提供了哪些窗口函数呢?如下:
- 序号函数:
-
row_number()
:按序排列,相同的值序号会往后推,如88、88、89
排序为1、2、3
。 -
rank()
:并列排序,相同的值序号会跳过,如88、88、89
排序为1、1、3
。 -
dense_rank()
:并列排序,相同的值序号不会跳过,如88、88、89
排序为1、1、2
。
-
- 分布函数:
-
percent_rank()
:计算当前行数据的某个字段值占窗口内某个字段所有值的百分比。 -
cume_dist()
: 小于等于当前字段值的行数与整个分组内所有行数据的占比。
-
- 前后函数:
-
lag(expr,n)
:返回分组中的前n
条符合expr
条件的数据。 -
lead(expr,n)
:返回分组中的后n
条符合expr
条件的数据。
-
- 首尾函数:
-
first_value(expr)
:返回分组中的第一条符合expr
条件的数据。 -
last_value(expr)
:返回分组中的最后一条符合expr
条件的数据。
-
- 其它函数:
-
nth_value(expr,n)
:返回分组中的第n
条符合expr
条件的数据。 -
ntile(n)
:将一个分组中的数据再分成n
个小组,并记录每个小组编号。
-
这样看过去似乎也有些令人迷糊,毕竟之前对窗口函数则这块接触比较少,因此下面来举个简单的例子切身感受一下(还是以之前的用户表为例),需求如下:
-
按性别分组,并按照
ID
值从大到小对各分组中的数据进行排序,最后输出。
这需求一听就知道一条SQL
绝对搞不定,在之前版本中需要创建临时表来实现,借助临时表来拆成多步完成,而在MySQL8.0
中则可以借助窗口函数轻松实现,如下:
select
row_number() over(
partition by user_sex order by user_id desc
) as serial_num,
user_id, user_name, user_sex, password, register_time
from
zz_users;
+
| serial_num | user_id | user_name | user_sex | password | register_time |
+
| 1 | 4 | 猫熊 | 女 | 8888 | 2022-09-17 23:48:29 |
| 2 | 1 | 熊猫 | 女 | 6666 | 2022-08-14 15:22:01 |
| 1 | 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 |
| 2 | 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 |
+
上述这条SQL
就是基于序号窗口函数的实现,其实发现会尤为简单,观察执行结果也会发现,使用窗口函数分组后,并不会将数据压缩到一行,而是将同一分组的数据在结果集中相邻显示。
上面的例子中就演示了最为简单的窗口函数用法:
函数名() over(数据处理)
,但实际情况中可以灵活的根据需求变动,但如若想要很好的使用窗口函数,那还需要大家多加以练习,对于这块大家可参考《MySQL官网-窗口函数示例》[11]、或《宋红康老师的视频教学》[12]。
3.8、MySQL8.0中的其他特性
在前面的内容中,就已经将MySQL8.0
中较为重要的变更和特性做了详细阐述,但MySQL8.0
整体的改变也比较大,因此这里再列出一些其它方面的特性,如下:
-
将默认的
UTF-8
编码格式从latin
替换成了utf8mb4
,后者包含了所有emoji
表情包字符。 -
增强
NoSQL
存储功能,优化了5.6
版本引入的NoSQL
技术,并完善了对JSON
的支持性。 -
InnoDB
引擎再次增强,对自增、索引、加密、死锁、共享锁等方面做了大量改进与优化。 -
支持定义原子
DDL
语句,即当需要对库表结构发生变更时,变更操作可定义为原子性操作。 -
支持正则检索,新增
REGEXP_LIKE()、EGEXP_INSTR()、REGEXP_REPLACE()、REGEXP_SUBSTR()
等函数提供支持。 -
优化临时表,临时表默认引擎从
Memory
替换为TempTable
引擎,资源开销少,性能更强。 - 锁机制增强,除开前面聊到的锁特性变更外,新引入了一种备份锁,获取/释放锁语法如下:
-
获取锁:
LOCK INSTANCE FOR BACKUP
、释放锁:UNLOCK INSTANCE
-
-
Bin-log
日志增强,过期时间精确到秒,利用zstd
算法增强了日志事务的压缩功能。 -
安全性提高,认证加密插件更新、密码策略改进、新增角色功能、日志文件支持加密等。
-
引入资源组的概念,支持按业务优先级来控制工作线程的
CPU
资源抢占几率。
.......
:更多请参考《MySQL官网-8.0版手册》[13]。
四、MySQL特性篇总结
在这章中咱们介绍了MySQL
几个重要的里程碑版本,显然每个版本中都对前版本中做了不小的优化,尤其是MySQL8.0
中的新特性和新优化点十分具备吸引力。但线上环境已有数据的情况下,也不适合再做迁移,毕竟数据迁移是一件十分麻烦的事情,有时候甚至比你重构一个Java
项目更累,因此如果你的数据库版本目前足以支撑系统的正常运转,那就没有必要去做版本迁移。
但如若你的项目数据库已经抵达瓶颈,并且升级数据库版本着实能够解决目前的困扰,这种情况下则可以尝试升级数据库版本,将旧版本的数据迁移到新版本的数据库中,但这类工作在有专业人士负责的情况下,最好不要自己去插手,毕竟一些企业中会有专门的
DBA
方向。
最后,在本章节中也仅介绍了一些较为重要的新特性和优化项,但MySQL
每个版本中都有一些修修补补的小特性、小改进,这里就不展开叙说了,大家感兴趣的可直接翻阅MySQL
官网提供的手册,以此来详细得知各个版本中的细节改进。