MySQL8MySQL5。7都即将停只维护了，是时候学习一波MySQL8了

　　MySQL 8新特性选择MySQL 8的背景：MySQL 5.6已经停止版本更新了，对于 MySQL 5.7 版本，其将于 2023年 10月31日 停止支持。后续官方将不再进行后续的代码维护。
　　另外，MySQL 8.0 全内存访问可以轻易跑到 200W QPS，I/O 极端高负载场景跑到 16W QPS，如下图：
　　上面三个图来自于MySQL官网：www.mysql.com/why-mysql/b…
　　除了高性能之外，MySQL 8还新增了很多功能，我找了几个比较有特点的新特性，在这里总结一下。
　　本文使用的MySQL版本为 8.0.29账户与安全用户的创建和授权
　　在MySQL之前的版本，创建用户和给创建的用户授权可以一条语句执行完成：grant all privileges on *.* to ＂zhangsan＂@＂%＂ identified by ＂Fawai@kuangtu6＂;
　　在MySQL 8中，创建用户和授权需要分开执行，否则会报错，执行不成功：
　　在 MySQL 8 中，需要分2不完成创建用户和授权的操作：-- 创建用户 create user ＂zhangsan＂@＂%＂ identified by ＂Fawai@kuangtu6＂; -- 授权 grant all privileges on *.* to ＂zhangsan＂@＂%＂;
　　再执行创建用户时，出现了如下错误：
　　这是因为我的 MySQL 8 安装完成后，进入命令行用的还是临时密码，并未修改root的初始密码，需要修改密码才允许操作。
　　修改密码操作：-- 修改root密码 alter user user() identified by ＂Root@001＂;
　　再创建用户即可：mysql> create user ＂zhangsan＂@＂%＂ identified by ＂Fawai@kuangtu6＂; Query OK, 0 rows affected (0.01 sec)  mysql> grant all privileges on *.* to ＂zhangsan＂@＂%＂; Query OK, 0 rows affected (0.00 sec)认证插件
　　在MySQL中，可以用 show variables 命令查看一些设置的MySQL变量，其中密码认证插件的变量名称是 default_authentication_plugin 。
　　MySQL 5.7版本 ：mysql> show variables like ＂%default_authentication%＂; +-------------------------------+-----------------------+ | Variable_name                 | Value                 | +-------------------------------+-----------------------+ | default_authentication_plugin | mysql_native_password | +-------------------------------+-----------------------+ 1 row in set (0.02 sec)
　　MySQL 8版本 ：mysql> show variables like ＂%default_authentication%＂; +-------------------------------+-----------------------+ | Variable_name                 | Value                 | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+ 1 row in set (0.07 sec)
　　可以看出，5.7 版本的默认认证插件是 mysql_native_password ， 而 8.0 版本的默认认证插件是 caching_sha2_password 。
　　caching_sha2_password 这个认证插件带来的问题是，我们直接在客户端连接MySQL会连不上，比如用Navicat ：
　　我们可以临时修改一下认证插件为 mysql_native_password ，再看一下是否能连接上，修改命令为：mysql> alter user ＂zhangsan＂@＂%＂ identified with mysql_native_password by ＂Fawai@kuangtu6＂;
　　此时，我们来看一下 user 表中的插件信息：
　　zhangsan用户的认证插件改为了mysql_native_password ，而其他的认证插件仍为默认的 caching_sha2_password 。
　　当然，alter user 修改插件的方式只能作为临时修改，而要永久修改，则需要修改MySQL配置文件 /etc/my.cnf 中的配置：
　　然后重启MySQL服务即可。密码管理
　　MySQL 8增加了密码管理功能，开始允许限制重复使用以前的密码：
　　这里有几个属性，其中：password_history ：此变量定义全局策略，表示在修改密码时，密码可以重复使用之前密码的更改次数。如果值为 0（默认值），则没有基于密码更改次数的重用限制。eg：值为2，表示修改密码不能和最近2次一致。password_require_current ：此变量定义全局策略，用于控制尝试更改帐户密码是否必须指定要替换的当前密码。意思就是是否需要校验旧密码（off 不校验、 on校验）(针对非root用户)。password_reuse_interval ：对于以前使用的帐户密码，此变量表示密码可以重复使用之前必须经过的天数。如果值为 0（默认值），则没有基于已用时间的重用限制。
　　修改 password_history 全局策略：-- 修改密码不能和最近2次一致 set persist password_history=2;
　　而如果要修改用户级别的 password_history ，命令为：alter user ＂zhangsan＂@＂%＂ password history 2;
　　下面来修改一下密码试试。-- zhangsan的原密码是Fawai@kuangtu6，执行修改密码操作，仍修改密码为Fawai@kuangtu6，根据密码策略不允许与最近2次的密码相同，应该修改不成功 alter user ＂zhangsan＂@＂%＂ identified by ＂Fawai@kuangtu6＂;
　　如果把全局参数 password_history 改为0，则对于root用户就没有此限制了：
　　索引增强
　　MySQL 8 对索引也有相应的增强，增加了方便测试的 隐藏索引 ，真正的 降序索引 ，还增加了 函数索引。隐藏索引
　　MySQL 8开始支持隐藏索引 (invisible index)，也叫不可见索引。隐藏索引不会被优化器使用，但仍然需要进行维护-创建、删除等。 其常见应用场景有：软删除、灰度发布。软删除：就是我们在线上会经常删除和创建索引，以前的版本，我们如果删除了索引，后面发现删错了，我又需要创建一个索引，这样做的话就非常影响性能。在MySQL 8中我们可以这么操作，把一个索引变成隐藏索引（索引就不可用了，查询优化器也用不上），最后确定要进行删除这个索引我们才会进行删除索引操作。灰度发布：也是类似的，我们想在线上进行一些测试，可以先创建一个隐藏索引，不会影响当前的生产环境，然后我们通过一些附加的测试，发现这个索引没问题，那么就直接把这个索引改成正式的索引，让线上环境生效。
　　有了 隐藏索引 ，大大方便了我们做测试，可以说是非常的体贴了！
　　下面举个例子看看隐藏索引怎么用法。
　　创建一个表 t_test ，并创建一个正常的索引 idx_name ，一个隐藏索引 idx_age ：create table t_test(id int, name varchar(20), age int); create index idx_name on t_test(name); create index idx_age on t_test(age) invisible;
　　此时，看一下索引信息：mysql> show index from t_testG *************************** 1. row ***************************         Table: t_test    Non_unique: 1      Key_name: idx_name  Seq_in_index: 1   Column_name: name     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment:  Index_comment:        Visible: YES    Expression: NULL *************************** 2. row ***************************         Table: t_test    Non_unique: 1      Key_name: idx_age  Seq_in_index: 1   Column_name: age     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment:  Index_comment:        Visible: NO    Expression: NULL 2 rows in set (0.01 sec)
　　普通索引的 Visible 属性值为OFF，隐藏索引为ON。
　　再来看一下MySQL优化器怎么处理这两种索引的：
　　可以看到，隐藏索引在查询的时候并不会用到，就跟没有这个索引一样，那么 隐藏索引 的用处到底是个什么玩意呢？
　　这里可以通过优化器的开关--optimizer_switch ，mysql> select @@optimizer_switchG *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
　　看到 use_invisible_indexes 配置默认是 OFF 的，将其打开看看效果：-- 在会话级别设置查询优化器可以看到隐藏索引 set session optimizer_switch=＂use_invisible_indexes=on＂;
　　再来看一下隐藏索引 idx_age 是否生效：
　　666！！！
　　这样的话就方便我们项目做灰度发布了，项目上线前，我想测试一下添加的新索引是否有用，可以现将其设置为隐藏索引，这样不会影响线上业务，在会话级别将隐藏索引打开进行测试，发现没有问题后转为可见索引。
　　可见索引与隐藏索引转换的SQL语句：-- 转换成可见索引 alter table t_test alter index idx_age visible; -- 转换成隐藏索引 alter table t_test alter index idx_age invisible;降序索引
　　MySQL 8支持 降序索引 ：DESC在索引中定义不再被忽略，而是导致键值以降序存储。
　　以前，可以以相反的顺序扫描索引，但会降低性能。降序索引可以按正序扫描，效率更高。
　　当最有效的扫描顺序混合了某些列的升序和其他列的降序时，降序索引还使优化器可以使用多列索引。
　　举个例子，在 MySQL 8 和 MySQL 5.7 中均执行如下建表语句：CREATE TABLE t (   c1 INT, c2 INT,   INDEX idx1 (c1 ASC, c2 ASC),   INDEX idx2 (c1 ASC, c2 DESC),   INDEX idx3 (c1 DESC, c2 ASC),   INDEX idx4 (c1 DESC, c2 DESC) );
　　然后看一下表的索引信息：
　　具体的用处在哪里呢？插入一些数据看一下。insert into t(c1, c2) values (1, 10),(2, 20),(3, 30),(4, 40),(5, 50);
　　函数索引
　　在之前的MySQL版本中，查询时对索引进行函数操作，则该索引不生效，基于此，MySQL 8中引入了 函数索引 。
　　还是举个简单的例子看一下：创建一个表t2，字段c1上建普通索引，字段c2上建upper函数（将字母转成大写的函数）索引。create table t2(c1 varchar(10), c2 varchar(10)); create index idx_c1 on t2(c1); create index idx_c2 on t2((upper(c2)));
　　通过show index from t2G 看一下：
　　下面来分别查询一下，看看索引的使用情况：
　　由于c1字段上是普通索引，使用upper(c1)查询时并没有用到索引优化，而c2字段上有函数索引upper(c2)，可以把整个upper(c2)看成是一个索引字段，查询时索引生效了！
　　函数索引的实现原理：
　　函数索引在MySQL中相当于新增了一个列，这个列会根据函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引，其实就是增加了一个虚拟的列，然后根据虚拟的列进行查询，从而达到利用索引的目的。原子DDL操作
　　MySQL 8.0 支持原子数据定义语言 (DDL) 语句。此功能称为原子 DDL。原子 DDL 语句将与 DDL 操作关联的数据字典更新、存储引擎操作和二进制日志写入组合到单个原子操作中。
　　操作要么被提交，适用的更改被持久化到数据字典、存储引擎和二进制日志中，要么被回滚，即使服务器在操作期间停止。
　　举个简单的例子：数据库中有表t1，没有表t2，执行语句删除t1和t2。mysql> create table t1(c1 int); Query OK, 0 rows affected (0.04 sec)  mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1             | +----------------+ 1 row in set (0.00 sec)  mysql> drop table t1,t2; ERROR 1051 (42S02): Unknown table ＂test.t2＂ mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1             | +----------------+ 1 row in set (0.00 sec)
　　上面是在 MySQL 8 中的操作，可以看到该操作并没有删除掉表t1，那么在之前的版本呢，下面在 MySQL 5.7 版本中进行同样的操作：mysql> create table t1(c1 int); Query OK, 0 rows affected (0.06 sec)  mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1             | +----------------+ 1 row in set (0.00 sec)  mysql> drop table t1,t2; ERROR 1051 (42S02): Unknown table ＂test.t2＂ mysql> show tables; Empty set (0.00 sec)
　　虽然也有报错提示说t2表不存在，但是t1表是真实的被删除掉了！
　　TIPS：如果确需要执行drop表操作，请使用 if exists 来防止删除不存在的表时出现的错误。
　　一个原子 DDL 操作内容包括：更新数据字典存储引擎层的操作在 binlog 中记录 DDL 操作
　　支持与表相关的 DDL：数据库表空间表索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE支持的其他 DDL ：存储程序、触发器、视图、UDF 的 CREATE、DROP 以及ALTER 语句。支持账户管理相关的 DDL：用户和角色的 CREATE、ALTER、DROP 以及适用的 RENAME，以及 GRANT 和 REVOKE 语句。通用表达式（CTE）
　　Common Table Expressions（CTE）通用表达式，也就是MySQL 8中的 with 语句。
　　通过一个简单的例子了解一下。
　　idx展示1~10行，可以直接select 1 union select 2 ...select 10这样：select 1 as idx UNION select 2 as idx UNION select 3 as idx UNION select 4 as idx UNION select 5 as idx UNION select 6 as idx UNION select 7 as idx UNION select 8 as idx UNION select 9 as idx UNION select 10 as idx;
　　通过CTE表达式，可以用递归的方式简化为如下写法：with recursive cte(idx) as  ( select 1 UNION select idx+1 from cte where idx<10 ) select * from cte;
　　再比如，有这样一个场景，查看某个员工的上下级关系，就可以通过CTE递归查出来。
　　这里 dev.mysql.com/doc/refman/… 有更多比较好的例子，大家可以看一下。其他
　　MySQL 8 还有很多比较实用的新特性，比如 ：
　　Window Function，对于查询中的每一行，使用与该行相关的行执行计算。
　　JSON增强
　　InnoDB 其他改进功能 ，比如死锁检查控制 innodb_deadlock_detect，对于高并发的系统，禁用死锁检查可能带来性能的提高。
　　这里不多做举例了（有没有一种可能是作者太懒？），官方文档上面那是相当的详细！
　　作者：行百里er
　　链接：https://juejin.cn/post/7111255789876019208
　　＂做程序员，圈子和学习最重要＂因为有有了圈子可以让你少走弯路，扩宽人脉，扩展思路，学习他人的一些经验及学习方法！同时在这分享一下是一直以来整理的Java后端进阶笔记文档和学习资料免费分享给大家！需要资料的朋友私信我扣【1】