1. MySQL8新特性

关于 MySQL 5.7 版别，其将于 2023年 10月31日 停止支撑。后续官方将不再进行后续的代码保护。

MySQL 8.0 全内存访问能够轻易跑到 200W QPS，I/O 极点高负载场景跑到 16W QPS，除此之外MySQL 8还新增了许多功用，那么咱们来一起看一下。

1.1. 账户与安全

1.1.1. 用户创立和授权

到了MySQL8中，用户创立与授权句子必须是分隔履行，之前版别是能够一起履行。

MySQL-MySQL8新特性

MySQL8的版别

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

MySQL-MySQL8新特性

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

MySQL-MySQL8新特性

MySQL-MySQL8新特性

MySQL5.7的版别

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

MySQL-MySQL8新特性

1.1.2. 认证插件更新

MySQL 8.0中默认的身份认证插件是caching_sha2_password，替代了之前的mysql_native_password。

show variables like 'default_authentication%';

5.7版别

MySQL-MySQL8新特性

8版别

MySQL-MySQL8新特性

select user, host,plugin from mysql.user;

这个带来的问题便是假如客户端没有更新，就衔接不上！！

MySQL-MySQL8新特性

当然能够经过在MySQL的服务端找到my.cnf的文件，把相关参数进行修正（不过要MySQL重启后才干收效）

MySQL-MySQL8新特性

假如没办法重启服务，还有一种动态的方法：

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

MySQL-MySQL8新特性

MySQL-MySQL8新特性

运用老的Navicat for MySQL也能访问

MySQL-MySQL8新特性

1.1.3. 暗码管理

MySQL 8.0开端答应限制重复运用曾经的暗码（修正暗码时）。

而且还加入了暗码的修正管理功用

show variables like 'password%';

MySQL-MySQL8新特性

修正战略（全局级）

set persist password_history=3;        --修正暗码不能和最近3次共同

MySQL-MySQL8新特性

修正战略（用户级）

alter user 'lijin'@'%' password history 3;

select user, host,Password_reuse_history from mysql.user;

MySQL-MySQL8新特性

MySQL-MySQL8新特性
运用重复暗码修正用户暗码(指定lijin用户)

alter user 'lijin'@'%' identified by 'Lijin@2022';

MySQL-MySQL8新特性
假如咱们把全局的参数改为0，则关于root用户能够重复的修正暗码

alter user 'root'@'localhost' identified by '789456';

MySQL-MySQL8新特性

password_reuse_interval 则是依照天数来限定（不答应重复的）

password_require_current 是否需求校验旧暗码（off 不校验、 on校验）(针对非root用户)

set persist password_require_current=on;

1.2. 索引增强

1.2.1. 躲藏索引

MySQL 8.0开端支撑躲藏索引 (invisible index)，不可见索引.

躲藏索引不会被优化器运用，但仍然需求进行保护。 运用场景: 软删去、灰度发布。

软删去：便是咱们在线上会常常删去和创立索引，假如是曾经的版别，咱们假如删去了索引，后面发现删错了，我又需求创立一个索引，这样做的话就非常影响功用。在MySQL8中咱们能够这么操作，把一个索引变成躲藏索引（索引就不可用了，查询优化器也用不上），最终确定要进行删去这个索引咱们才会进行删去索引操作。

灰度发布：也是相似的，咱们想在线上进行一些测验，能够先创立一个躲藏索引，不会影响当前的生产环境，然后咱们经过一些附加的测验，发现这个索引没问题，那么就直接把这个索引改成正式的索引，让线上环境收效。

运用事例（灰度发布）：

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

MySQL-MySQL8新特性

MySQL-MySQL8新特性

MySQL-MySQL8新特性

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

MySQL-MySQL8新特性

运用查询优化器看下：

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

MySQL-MySQL8新特性

这儿能够看到躲藏索引不会用上。

这儿能够经过优化器的开关，翻开一个设置，便利咱们对躲藏索引进行设置。

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

MySQL-MySQL8新特性

红色的部分便是默认查询优化器对躲藏索引不可见，咱们能够经过参数进行修正。保证咱们能够用躲藏索引进行测验。

set session optimizer_switch="use_invisible_indexes=on';   --在会话等级设置查询优化器能够看到躲藏索引

MySQL-MySQL8新特性

再运用查询优化器看下：

explain select * from t1 where j=1;

MySQL-MySQL8新特性

把躲藏索引变成可见索引（正常索引）

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

MySQL-MySQL8新特性

最终一点，不能把主键设置成不可见的索引（躲藏索引）（MySQL做了限制）

1.2.2. 降序索引

MySQL 8.0开端真正支撑降序索引 (descendingindex) 。只有InnoDB存储引擎支撑降序索引，只支撑BTREE降序索引。别的MySQL8.0不再对GROUP BY操作进行隐式排序。

在MySQL中创立一个t2表

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

MySQL-MySQL8新特性

假如是5.7中，则没有显示升序仍是降序信息

MySQL-MySQL8新特性

咱们插入一些数据，给大家演示下降序索引的运用

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

MySQL-MySQL8新特性

看下索引运用情况

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

MySQL-MySQL8新特性

咱们在5.7对比一下

MySQL-MySQL8新特性

这儿说明，这儿需求一个额定的排序操作，才干把刚才的索引利用上。

咱们把查询句子换一下

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

MySQL8中运用了

MySQL-MySQL8新特性

别的还有一点，便是group by句子在 8之后不再默认排序

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

MySQL-MySQL8新特性

MySQL-MySQL8新特性

在8要排序的话，就需求手动把排序句子加上

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

MySQL-MySQL8新特性

1.2.3. 函数索引

之前咱们知道，假如在查询中加入了函数，索引不收效，所以MySQL8引入了函数索引。

MySQL 8.0.13开端支撑在索引中运用函数（表达式)的值。支撑降序索引，支撑JSON 数据的索引 函数索引基于虚拟列功用完成。

运用函数索引（表达式）

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

MySQL-MySQL8新特性

show index from t3\G

MySQL-MySQL8新特性

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

MySQL-MySQL8新特性

运用函数索引（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 ';

MySQL-MySQL8新特性

函数索引基于虚拟列功用完成

函数索引在MySQL中相当于新增了一个列，这个列会根据你的函数来进行核算成果，然后运用函数索引的时分就会用这个核算后的列作为索引。

1.3. 通用表表达式（CTE）

MySQL8.0开端支撑通用表表达式(CTE）(common table expression)，即WITH子句。

简略入门：

以下SQL便是一个简略的CTE表达式，相似于递归调用，这段SQL中，首先履行select 1 然后得到查询成果后把这个值n送入 union all下面的 select n+1 from cte where n <10,然后一向这样递归调用union all下面sql句子。

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

MySQL-MySQL8新特性

事例介绍：

一个staff表，里面有id，有name还有一个 m_id，这个是对应的上级id。数据如下：

MySQL-MySQL8新特性

假如咱们想查询出每一个员工的上下级关系，能够运用以下方法

递归CTE：

with recursive staff_view(id,name,m_id) as
(select id ,name ,cast(id as char(200)) 
 from staff where m_id =0
 union ALL 
 select s2.id ,s2.name,concat(s1.m_id,'-',s2.id)
 from staff_view as s1 join  staff as s2
 on s1.id = s2.m_id
)
select * from staff_view order by id

MySQL-MySQL8新特性

运用通用表表达式的优点便是上下级层级就算有4，5，6甚至更多层，都能够帮助咱们遍历出来，而老的方法的写法SQL句子就要调整。

总结：

通用表表达式与派生表相似，就像句子等级的暂时表或视图。CTE能够在查询中屡次引证，能够引证其他CTE，能够递归。CTE支撑SELECT/INSERT/UPDATE/DELETE等句子。

1.4. 窗口函数

MySQL 8.0支撑窗口函数(Window Function)，也称剖析函数。窗口函数与分组聚合函数相似，但是每一行数据都生成一个成果。聚合窗口函数: SUM /AVG / COUNT /MAX/MIN等等。

事例如下：sales表结构与数据如下：

MySQL-MySQL8新特性

一般的分组、聚合（以国家核算）

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

MySQL-MySQL8新特性

窗口函数（以国家汇总）

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

MySQL-MySQL8新特性

窗口函数（核算平局值）

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;

MySQL-MySQL8新特性

专用窗口函数：

  • 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
  • 分布函数：PERCENT_RANK()、CUME_DIST()
  • 前后函数：LAG()、LEAD()
  • 头尾函数：FIRST_VALUE()、LAST_VALUE()
  • 其它函数：NTH_VALUE()、NTILE()

MySQL-MySQL8新特性

窗口函数（排名）

用于核算分类排名的排名窗口函数，以及获取指定方位数据的取值窗口函数

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

MySQL-MySQL8新特性

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;

MySQL-MySQL8新特性

MySQL-MySQL8新特性

当然能够做的操作许多，详细见官网：

dev.mysql.com/doc/refman/…

1.5. 原子DDL操作

MySQL 8.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 句子。

drop table t1,t2;

上面这个句子，假如只有t1表，没有t2表。在MySQL5.7与8 的表现是不同的。

5.7会删去t1表。而在8中由于报错了，整个是一个原子操作，所以不会删去t1表。

1.6. JSON增强

详细看官网信息，英文好的直接看，英文欠好的找个翻译东西即可看懂

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

MySQL-MySQL8新特性

1.7. InnoDB其他改进功用

自增列耐久化

MySQL 5.7 以及早期版别，InnoDB 自增列计数器（AUTO_INCREMENT）的值只存储在内存中。MySQL 8.0 每次变化时将自增计数器的最大值写入 redo log，一起在每次检查点将其写入引擎私有的体系表。处理了长期以来的自增字段值或许重复的 bug。

死锁检查控制

MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量，用于控制体系是否履行 InnoDB 死锁检查。关于高并发的体系，禁用死锁检查或许带来功用的提高。

innodb_deadlock_detect

锁定句子选项

SELECT … FOR SHARE 和 SELECT … FOR UPDATE 中支撑 NOWAIT、SKIP LOCKED 选项。关于 NOWAIT，假如恳求的行被其他业务锁定时，句子当即回来。关于 SKIP LOCKED，从回来的成果集中移除被锁定的行。

InnoDB 其他改进功用。

  • 支撑部分快速 DDL，ALTER TABLE ALGORITHM=INSTANT;
  • InnoDB 暂时表运用共享的暂时表空间 ibtmp1。
  • 新增静态变量 innodb_dedicated_server，自动配置 InnoDB 内存参数：innodb_buffer_pool_size/innodb_log_file_size 等。
  • 默认创立 2 个 UNDO 表空间，不再运用体系表空间。
  • 支撑 ALTER TABLESPACE … RENAME TO 重命名通用表空间。

继续创造，加快生长！这是我参与「日新计划 10 月更文挑战」的第21天，点击检查活动概况