MySQL8.0新特性(巨细)学习笔记

最新推荐文章于 2024-08-08 21:10:03 发布

古月映痴人

最新推荐文章于 2024-08-08 21:10:03 发布

阅读量447

点赞数 1

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_61947390/article/details/130283101

版权

MySQL5.6的版本已经停止维护了
MySQL5.7的版本在2023年10.31号也会停止维护
MySQL8的性能很好

- 全内存的访问，可以跑到200W的QPS
- IO访问16W

同时mysql8新增了很多功能，什么功能嘞，我们一点一点看

账户与安全

用户的创建和授权：

在我们MySQL5.6/5.7版本中，我们创建用户和授权用一条SQL就可以了（可以合并，一起执行）
在我们MySQL8的版本中，我们创建用户和授权必须拆分成两步单独去做

MySQL8：

grant all privileges on *.* to 'lijin'@'%' identified by 'Lijin@2022';

我们会发现在MySQL8中创建用户和授权同时执行会报错

create user 'lijin'@'%' identified by 'Lijin@2022';
grant all privileges on *.* to 'lijin'@'%'；

我们会发现创建用户和授权单独执行成功了

MySQL5.7

grant all privileges on *.* to 'lijin'@'%' identified by 'Lijin@2022';

我们可以看到在mysql5.7版本中创建用户和授权同时执行成功了

认证插件更新：

MySQL8.0版本中默认的身份认证插件为caching_sha2_password
MySQL5.7/5.6版本中身份认证的插件为mysql_native_password
MySQL8.0的身份认证是更安全的

查看命令

show variables like 'default_authentication%';

MySQL5.7

MySQL8.0

select user, host,plugin from mysql.user;

问题：

MySQL8.0更新了身份认证的插件之后，随之带来了一个问题，就是如果客户端没有更新（老的客户端），就连接不上了

那如果我们想要使用老的navicat for MySQL也想访问怎么办呢？

解决：

修改参数：

我们可以通过修改MySQL的服务端找到my.cnf文件，把相关参数进行修改（这一行默认是注释掉的，打开就行）（不过要重启MySQL才生效）

手动更改：

当然，如果不想或者没办法重启服务，还有一种动态方式，就是我们手动去修改我们的身份认证方式

alter user 'lijin'@'%' identified with mysql_native_password by 'Lijin@2022';
select host,user from mysql.user;

密码管理

在MySQL5.7的时候我们修改密码，是不允许重复设置的（如 123456 -> 123456是不被允许的）
在MySQL8.0中是开始允许我们进行重复设置的
同时MySQL8.0还新加入了密码修改管理功能

show variables like 'password%';

修改重复密码策略password_history：

我们可以看到第一个参数password_history为0，默认是允许密码重复的，但是我i们可以进行修改

修改策略（全局级）：

set persist password_history=3;        --修改密码不能和最近3次一致

修改策略（用户级）：

alter user 'lijin'@'%' password history 3; -- 修改密码不能和前三次一致
select user, host,Password_reuse_history from mysql.user; -- 查看用户的密码修改策略

测试：

用重复密码修改用户密码（指定lijin用户）

alter user 'root'@'localhost' identified by '789456'; -- 参数为0
alter user 'lijin'@'%' identified by 'Lijin@2022'; --参数为3 以重复

全局参数为0：

全局参数为3（并重复）：

password_require_current(是否校验旧密码)

是否需要校验旧密码

- OFF(不校验)（默认）
- ON（校验）

针对的是非root用户
也就是说在用户修改密码二次登陆的时候是否需要输入旧密码进行验证

set persist password_require_current=on;

password_reuse_interval

按照天数来限定密码重复策略的

- 0则是不限制，可以重复
- 3则是不允许与三天内的密码相同

索引增强

隐藏索引

隐藏索引就是不可见的

- 隐藏索引不会被我们的优化器（explain）使用对我们来说也就是不可见的

但是我们经常呢又会使用到

- 软删除

- - 就是我们在线上会频繁的创建和删除索引（尤其是在sql优化的过程中），如果是以前的版本，我们如果删除了索引，后面发现删错了，我又需要创建一个索引，这样的话呢就非常影响性能，在MySQL8.0中我们就可以将我们想要删除的索引变成隐藏索引（索引就不可用了，查询优化器也用不上了），最后确定要删除这个索引的时候呢，我们才会进行删除操作（同理如果我们不想删除了，想继续用这个索引，我们可以将这个隐藏索引打开，变成正常索引）

- 灰度发布

- - 和软删除类似，我们想在线上进行一些测试，可以先创建一个隐藏索引，不会影响当前的生产环境，然后我们通过一些附加的测试（包含但不限于，在当前会话可以查看并使用隐藏索引），发现这个索引没问题，那么就直接把这个索引改成正式的索引，让线上环境生效

备注： 不能把主键设置成不可见的索引（隐藏索引）（mysql做i了限制）

案例：

灰度发布：

首先我们创建表和索引

create table t1(i int,j int);  --创建一张t1表
create index i_idx on t1(i);  --创建一个正常索引
create index j_idx on t1(j) invisible;  --创建一个隐藏索引

接下来我们可以查看索引信息

show index from t1\G         --查看索引信息

我们可以使用查询优化器分别执行两条sql（索引i and 索引j）

explain select * from t1 where i=1;
explain select * from t1 where j=1;

我们可以看到正常索引命中了，但是隐藏索引没有命中（不会被使用）
我们可以通过优化器的开关，打开我们的系统设置，以方便我们对隐藏索引进行设置

select @@optimizer_switch\G;   --查看 各种参数

红色的部分就是默认查询优化器对隐藏列不可见，我们可以通过参数进行修改，确保我们可以用隐藏列进行测试（配置成当前会话，不要配置全局）（只对我们当前测试会话可以看到）

set session optimizer_switch="use_invisible_indexes=on';   --在会话级别设置查询优化器可以看到隐藏索引

在使用查询优化器查看一下我们的隐藏索引

explain select * from t1 where j=1;

我们发新命中了，也就是说我们在测试过程中可以将隐藏索引对我们当前会话打开，我们先在测试环境进行测试，等测试成功之后在发布到正式环境

隐藏索引 and 正常索引切换

alter table t1 alter index j_idx visible;   --变成可见
alter table t1 alter index j_idx invisible;   --变成不可见(隐藏索引)

降序索引

MySQL8.0开始真正支持降序索引（descendingindex），只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引，另外MySQL8.0不再对group by操作进行隐式排序

--隐式排序
SELECT pid,appName from T group by appName asc 或 SELECT pid,appName from T group by appName order by appName asc; -- 隐式排序
--显式排序
SELECT pid,appName from T group by appName order by appName desc;

案例：

在MySQL中建一个t2表

create table t2(c1 int,c2 int,index idx1(c1 asc,c2 desc));

show create table t2\G

我们可以看到在mysql8.0中是有显示我们索引的排序的在key列

而在MySQL5.7中则是没有显示升序还是降序信息的

这里我们在表中插入一些数据，后面演示降序索引的使用

insert into t2(c1,c2) values(1,100),(2,200),(3,150),(4,50);

查询数据并查看索引的使用情况

explain select * from t2 order by c1,c2 desc;

我们可以看到MySQL8.0，是用到了索引，而且没有什么额外的操作

对比一下MySQL5.7的，虽然也用到了索引，但是额外做了一个排序操作

- 备注： 这个额外说明是讲，需要额外的一个排序操作才能把索引利用上

我们把查询语句更改一下，排序位置做一个调换

explain select * from t2 order by c1 desc,c2 ;

我们可以看到MySQL8.0中使用了反向索引扫描，直接通过反向扫描，命中了索引

另外一点就是group by语句在MySQL8.0之后不在支持默认排序了

select count(*),c2 from t2 group by c2;

我们可以看到，在MySQL5.7及之前，我们使用group by函数，MySQL会自动给我们生成一个隐式排序，MySQL8.0的时候就不会默认排序了
在MySQL8.0中要排序的话，就要手动把排序语句加上了

select count(*),c2 from t2 group by c2 order by c2;

函数索引

函数索引在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。
之前我们讲到过，不要在索引列上面用函数，不然会导致索引失效的。然而我们的MySQL8.0给我们解决了这个问题，引入了函数索引
MySQL8.0.13中开始支持在索引列中使用函数（表达式/计算式）的值，支持兼续索引，支持json数据的索引，函数索引基于虚拟列实现

- 也就是说MySQL的底层创建了一个我们看不到的列，将函数字段的值都放了进去，我们通过函数取数据的之后，直接通过这个列定位到我们想要查看的数据，如（where 字段1 + 字段2 = 5），MySQL会自动给我们创建一个列存这个5，直接通过这个列定位到我们的数据

语法： create index 索引名 on table（表名）（（函数（列）））

create index func_idx on t3( (UPPER(c2)) );   --一个大写的函数索引
ALTER TABLE t_func ADD INDEX idx_log_time ( ( date( log_time ) ) ), -- 日期对比
ADD INDEX idx_u1 ( ( rank1 + rank2 ) ), -- 字段相加
ADD INDEX idx_suffix_str3 ( ( RIGHT ( str3, 9 ) ) ),  --取末尾字段
ADD INDEX idx_substr_str1 ( ( substr( str1, 5, 9 ) ) ), -- 取5-9中间字段
ADD INDEX idx_str2 ( ( CAST( str2 ->> '$.name' AS CHAR ( 9 ) ) ) ); -- json格式的value

演示：

我们创建一个t3表，同时建一个普通索引，一个函数索引

create table t3(c1 varchar(10),c2 varchar(10));
create index idx_c1 on t3(c1);   --普通索引
create index func_idx on t3( (UPPER(c2)) );   --一个大写的函数索引

这时候我们来查看我们的索引

show index from t3\G

- 我们可以看到，普通索引和函数索引的区别在Expression列，普通索引值为NULL，函数索引列为函数（字段）

我们使用upper（大写）函数来查分别查看我们的c1和c2字段

explain select * from t3 where upper(c1)='ABC' ;  
explain select * from t3 where upper(c2)='ABC' ;

- 我们可以看到使用了相同的函数后，我们的普通索引是索引失效的，函数索引生效了

使用函数索引（JSON）

create table t4(data json，index((CAST(data->>'$.name' as char(25)) )));
explain select * from t4 where CAST(data->>'$.name' as char(25)) = 'lijin ';

通用表达式（CTE common table expression）（通用的表的表达式）

MySQL8.0开始支持通用表表达式，即with语句
通用表表达式与派生表类似，就像语句级别的临时表或者视图，CTE可以在查询中多次引用，可以引用其他CTE，可以递归，CTE支持SELECT/INSERT/UPDATE/DELETE语句！！！！
个人理解就是生成了一张临时表
其实就是递归，根据条件实现自己调自己，业务需求在如：用户有层级关系，组织架构，0级用户为最高级，id为2的用户，上级id是1，类似于lims中代理商的需求，根据顺序查出每个用户以及他的层级关系

个人理解

个人理解就是生成了一张临时表用来存放数据，WITH层指定的（如：CTE）表名就是创建了临时表，内置第一层select语句就是将查询出来的语句放入表中，union all下面的select语句就是关联临时表（视图），通过条件取出数据

*第一个红框标记是没有使用表格（创建的临时表）
第二个红框标记说的是递归操作，证明第二条select语句是在进行递归

简单入门（案例）

案例1（通用递归）

以下sql就是一个简单的CTE表达式，类似于递归调用，这段SQL中，首先执行select 1然后得到查询结果后，把这个值送入union all 下面的select n+1 from cte where n < 10，然后一直递归调用，n<10就是递归条件

WITH recursive cte(n) as 
( select 1
  union ALL
  select n+1 from cte where n<10
)
select * from cte;

案例2（业务模拟）

现在我们有一个staff表，里面有id，有name，还有一个m_id（对应的上级id），数据如下：

我们现在想查询出每一位员工的上下级关系，怎么办呢？可以使用以下方式：

with recursive staff_view(id,name,m_id) as -- 相当于建了一个临时表（staff_view视图）
( 
  select id,name,cast(id as char(200))
  from staff where m_id = 0
  union all
  select s2.id ,s2.name,cast(s1.m_id,'-',s2.m_id)
  from staff_view as s1 join staff as s2 
  on s1.id = s2.m_id
)
select * from staff_view order by id

查询结果如下

使用通用表表达式的好处就是上下级层级就算有n层，都可以帮助我们遍历出来，而老的方式写SQL语句就要调整

函数式窗口（分析函数 )

MySQL8.0支持窗口函数（Window Function），也称分析函数，窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果
我们常用的聚合窗口函数有 SUM/AVG/MAX/MIN... 等等等等
个人理解：

- 窗口函数其实就是根据不同的函数、根据业务需求为我们多生成一列（不在表中的）我们需要的数据
- 生成的这一列就叫做窗口

fw_error_www

案例

我们现在有一张sales表，数据如下：

窗口函数（分组并计算综合）

普通的分组，聚合函数（以国家统计）来查询sum数量的总和

SELECT country,sum(sum)
FROM sales
GROUP BY country
order BY country;

那我们现在有个需求，将所有表中列查询出来，然后每个列后面多加一列，根据国家展示sum列的总和，这时候就用到我们的函数窗口了，sql如下（根据国家分组）：

select year,country,product,sum,
	sum(sum) over (PARTITION by country) as country_sum
 from sales
order by country,year,product,sum;

结果如下：

窗口函数（计算平均值）

select year,country,product,sum,
			sum(sum) over (PARTITION by country) as country_sum,
			avg(sum) over (PARTITION by country) as country_avg
 from sales
order by country,year,product,sum;

结果如下：

窗口函数（排名）

用来计算分类排名的排名窗口函数

- row_number（）函数: 不间断的组内排序,不管值是否重复(不会出现并列情况),结果都是1,2,3,4,5,6
- RANK()函数 : 间断组内排序,也就是说如果值重复,会出现并列的情况,如: 1,1,2,2,2,3,4,5,5,6

SELECT
	YEAR,
	country,
	product,
	sum,
	row_number() over (ORDER BY sum) AS 'rank',
        rank() over (ORDER BY sum) AS 'rank_1'
FROM
	sales;

结果如下:

窗口函数(累加总和)

累加总和,如: 根据A列排序,然后累加总和B列,怎么形容呢?

- B列的第一个值为: 1 ,第二个值为2,第三个值为5,那么在查询出来的时候第一行的B列值为1,第二列的值为1+2=3,第三行为3+5=8,就是指定的列累加

展示sql如下

SELECT
	YEAR,
	country,
	product,
	sum,
	sum(sum) over (PARTITION by country order by sum rows unbounded preceding) as sum_1
FROM
	sales order by country,sum;

结果为:

原子DDL操作

个人理解

- 就像对DDL操作的sql加了个事务，要么全部成功要么全部失败，失败会回滚
- 就是我们在做一些操作表的业务的时候，比如修改表属性，我们可能会修修改多个属性，用MySQL8.0的话，如果遇到异常就会全部失败并回滚，不会出现部分成功部分失败的情况，但是MySQL8.0之前的版本会出现的

在MySQL8.0之前（5.6/5.7）DDL操作都不是原子性的
MySQL8.0开始支持了原子性的DDL操作(要么全部成功,要么全部失败),其中与表相关的DDL只支持InnoDB存储引擎,一个原子DDL操作内容包括了: 更新数据字典,存储引擎层的操作,在binlog日志中记录DDL操作,支持与表相关的DDL: 数据库,表空间,表,索引的CREATE,ALTER,DROP,以及TRUNCATE TABLE。支持的其他的DDL: 存储程序，触发器，视图,UDF的CREATE,DROP以及ALTER语句，支持账户相关的DDL: CREATE,ALTER,DROP，以及使用的RENAME,以及GRANT和REVOKE语句

案例

我们现在操作两张表： T1、 T2，当前t1表是不存在的，t2表存在，如下：

然后我们执行drop语句，同时删除两张表

drop table t1 , t2

MySQL5.7:

我们可以看到，虽然报错，有提示t1表不存在，但是我们再去查询t2表的时候会发现t2表也被删除了
说明MySQL5.7版本是有两个删除动作的，分别是删除t1，在删除t2，虽然执行t1的时候出现异常报错，但是不影响t2的删除
简单来说就是我们MySQL5.7版本的DDL操作是分别删除的两张表（拆分成两条sql），不是原子性操作

MySQL80:

在MySQL8.0版本中我们可以看到，我们的t1表还是报错了，但是我们的t2表没有被删除，
说明MySQL8.0中的DDL操作是原子性操作，就是一个操作里面在创建表，修改表，删除表等时候，我们有多个操作，如果遇到错误，就会全部失败并回滚，不会出现部分成功部分失败的情况

JSON的增强

我们知道在非关系型数据库（NOSQL）中有很大的一个优势就是处理json数据类型，就像mongoDB中的BSON格式的处理
在我们MySQL8.0版本中也支持了JSON数据类型的处理和支持，有很多，详情可以参考官网：

MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type

InnoDB其他改进功能

自增列优化：

MySQL5.7以及更早期版本，InnoDB自增列计数器（AUTO_INCREMENT）的值只存在内存中，MySQL8.0每次变化时将自增计数器的最大值写入redo log中，同时在每次检查点将其写入引擎私有的系统表，解决了长期以来自增字段值可能重复的bug

死锁检查控制（默认开启）

我们知道MySQL中如果出现死锁了，MySQL优化器会检测到死锁，从而自动给我们关闭后面执行的任务，释放掉事务，从而解决死锁
MySQL8.0中我们的死锁检查变成了可配置的，可以根据实际情况去开启或者关闭，进而提升性能
死锁产生的四个条件：

- 互斥条件：一个资源每次只能被一个进程（线程）使用。
- 请求与保持条件：一个进程（线程）因请求资源而阻塞时，对已获得的资源保持不放。
- 不剥夺条件：进程（线程）已获得的资源，在末使用完之前，不能被剥夺。
- 循环等待条件：多个进程（线程）之间形成一种头尾相接的循环等待的资源争用关系。

InnoDB 死锁和问题排查_innodb死锁检测_lamp_yang_3533的博客-CSDN博客

锁定语句选项：

SELECT..... FROM SHARE 和 SELECT....FOR UPDATE中支持NOWAIT,SKIP LOCKED选项，

- NOWAIT：如果请求的行（记录）被其他事务锁定时，语句立即返回
- SKIP LOCKED ：从返回结果集中移除被锁定的行

死锁控制参数

死锁检测功能参数为 innodb_deadlock_detect
死锁检测功能是默认开启的，当出现死锁时，InnoDB就会检测到状态，并及时回滚导致死锁的多个事务中的一个

查看死锁检测

show variables like 'innodb_deadlock_detect'

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)