Mysql8.0变更特性

乱糟

于 2023-08-08 18:19:57 发布

阅读量249

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43001336/article/details/132170707

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

性能翻倍

账户与安全

用户的创建和授权
在MySQL之前的版本，创建用户和给创建的用户授权可以一条语句执行完成：

grant all privileges on *.* to 'test'@'%' identified by 'suibowen@kuangtu6';

在MySQL 8中，创建用户和授权需要分开执行，否则会报错，执行不成功：

-- 创建用户
create user 'test'@'%' identified by 'suibowen@kuangtu6';
-- 授权
grant all privileges on *.* to 'test'@'%';

认证插件
5.7 版本的默认认证插件是 mysql_native_password ，而 8.0 版本的默认认证插件是 caching_sha2_password 。
caching_sha2_password 这个认证插件带来的问题是，我们直接在客户端连接MySQL会连不上，比如用Navicat
我们可以临时修改一下认证插件为 mysql_native_password ，再看一下是否能连接上，修改命令为：

alter user 'zhangsan'@'%' identified with mysql_native_password by 'Fawai@kuangtu6';

在线修改的系统参数支持持久化

在之前的版本中，通过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;

其实参数持久化的原理也非常简单，当执行set persist命令时，会将改变过的参数写入到本地的mysqld-auto.cnf文件中，MySQL每次启动时都会读取这个文件中的值，如果该文件中存在参数，则会直接将其加载，从而实现了一次修改，永久有效。

移除查询缓存

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版本中就直接去掉了，毕竟弊大于利，带来的收益达不到设计时的预期。

锁机制优化

在MySQL8.0中的锁机制主要出现了两点优化，一方面对获取共享锁的写法进行了优化，如下：

-- MySQL8.0之前的版本
SELECT ... LOCK IN SHARE MODE;

-- MySQL8.0及后续的版本
SELECT ... FOR SHARE;

第二方面则支持非阻塞式获取锁机制，可以在获取锁的写法上加上NOWAIT、SKIP LOCKED关键字，这样在未获取到锁时不会阻塞等待，使用SKIP LOCKED未获取到锁时会直接返回空，使用NOWAIT会直接返回并向客户端返回异常。用法如下：

select ... for update nowait;
select ... for update skip locked;

索引增强

在8.0中官方再一次对索引机制动刀，首先对联合索引提供了一种跳跃扫描机制的支持，也就意味着使用联合索引时，就算未遵循最左前缀匹配原则，也可以使用联合索引来检索数据。除此之外，还有另外三种新的索引特性：隐藏索引、降序索引以及函数索引。
索引跳跃式扫描机制（Index Skip Scan）
最左前缀匹配原则，也就是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优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍
但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发…，总之有很多限制条件，具体的可以参考《MySQL官网8.0》。
对于索引跳跃扫描机制，可以通过set @@optimizer_switch = ‘skip_scan=off|on’;命令来选择开启或关闭跳跃式扫描机制。
隐藏索引
隐藏索引并不是一种新的索引类型，而是一种对索引的骚操作，可以理解为对每个索引新增了一个开关按键，主要用于测试环境和灰度场景，在MySQL8.0版本中，可以通过INVISIBLE、VISIBLE来控制索引的开关：

当对一个索引使用INVISIBLE后，会关闭这个索引，优化器在执行SQL时无法发现和使用它。
当对一个索引使用VISIBLE后，会将索引从隐藏状态恢复到正常状态。

所谓的隐藏索引，就是指将一个已经创建的索引“藏起来”，被藏起来的索引是无法被优化器探测到的，因此执行SQL语句时，就算语句中显式使用了索引字段，优化器也不会选择走这条索引。
这个特性主要是针对于调优、测试场景而研发的，如果隐藏一个索引后，在压测场景下不会对业务产生影响，如果经过反复测试后依旧不影响SQL性能，那这条索引则可以被判定为无用索引，可以将其删除
降序索引
在创建索引时，可以通过ASC、DESC来定义一个索引是按升序还是降序存储索引键，但本质上这种语法，在MySQL8.0之前，就算你手动写明了DESC降序，在创建时依旧会默认忽略，也就是本质上还是按升序存储索引键的，当你要对某个倒序索引的字段做倒序时，依旧会发生filesort排序的动作。
到了MySQL8.0官方正式支持降序索引，也就是当对一个字段建立降序索引后，做降序查询时不需要再次排序，可直接根据索引进行取值。

ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

函数索引
在MySQL8.0中真正的支持了函数索引，也就是基于函数去创建索引，如下

alter table 表名 add index 索引名(函数(列名));

-- 比如：创建一个将字段值全部转为大写后的索引
alter table t1 add index fuc_upper(upper(c1));

基于某个字段创建一个函数索引后，之后基于该字段使用函数作为查询条件时，依旧可以走索引，如下：

select * from t1 where upper(c1) = 'ABC';

不过有一点需要牢记：使用什么函数创建的索引，也仅支持相应函数走索引，比如上面通过了upper()函数创建了一个索引，因此upper(c1) = 'ABC’这种情况可以走索引，但使用其他函数时依旧会导致索引失效，如：lower(c1) = ‘abc’

通用表表达式

CTE通用表表达式究竟是用来干什么事情的呢？CTE是一个具备变量名的临时结果集，也就是可以将一条查询语句的结果保存到一个变量里面，后续在其他语句中允许直接通过变量名来使用该结果集，语法如下：

with CTE名称
as (查询语句/子查询语句)
select 语句;

上述的语法是一个普通的CTE用法，同时还有另一种递归的CTE用法，先举个简单的例子来认识一下最基本的用法：

-- MySQL8.0版本之前的子查询语句
select * from t1 where xx in (select xx from t2 where yy = "zzz");

-- MySQL8.0中使用CTE表达式来代替
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表达式除开可以与select语句嵌套外，还可以与其它类型的语句嵌套，例如：with delete、with update、with recursive、with with、insert with等

窗口函数

窗口函数可谓是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]
)

一眼看下来，结果还是令人不理解，对吗？这先别急，看不懂也没关系，后面会举例说明，先来看看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() 序号窗口函数
    row_number() over(
        -- 基于性别做分组，然后基于 ID 做倒序
        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就是基于序号窗口函数的实现，其实发现会尤为简单，观察执行结果也会发现，使用窗口函数分组后，并不会将数据压缩到一行，而是将同一分组的数据在结果集中相邻显示

其他特性

在前面的内容中，就已经将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

更多请参考《MySQL官网-8.0版手册》