【MYSQL8的新特性】

MySQL8的新特性

1.用户管理

1.用户创建与授权

   MySQL5.7之前必须用户的创建和授权是一起进行，在MySQL8呢，必须要分开进行授权操作

-- MySQL8执行 结果如下图 1
grant all privileges on *.* to 'lijin'@'%' identified by 'Lijin@2022';
-- MySQL8执行 成功 图2 
create user 'lijin'@'%' identified by 'Lijin@2022';
grant all privileges on *.* to 'lijin'@'%'；

图1 ：

图2：

2.密码插件加强

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

show variables like 'default_authentication%';

5.7版本

8版本

select user, host,plugin from mysql.user;

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

当然可以通过在MySQL的服务端找到my.cnf的文件，把相关参数进行修改（不过要MySQL重启后才能生效）

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

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

使用老的Navicat for MySQL也能访问

3. 密码管理

MySQL 8.0开始允许限制重复使用以前的密码（修改密码时）。

并且还加入了密码的修改管理功能

show variables like 'password%';

修改策略（全局级）

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

修改策略（用户级）

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

select user, host,Password_reuse_history from mysql.user;

使用重复密码修改用户密码(指定lijin用户)

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

如果我们把全局的参数改为0，则对于root用户可以反复的修改密码

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

password_reuse_interval 则是按照天数来限定（不允许重复的）

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

set persist password_require_current=on;

1.7.2. 索引增强

2.索引加强

1.隐藏索引(灰度发布、MySQL不可见)

-- SQL 内部有隐藏索引字段 通过设置 INVISIBLE属性来设置来达到灰度发布、MySQL不可见 不会影响线上业务
-- 具体SQL如下：
	CREATE INDEX [indexName] FROM [tableName(filedName)] [INVISIBLE]；

-- 查看隐藏索引
SHOW INDEX FROM  [tableName] \G

-- 查看表的创建信息
SHOW CREATE TABLE [tableName]\G

-- 可以通过设置会话的来进行设置我们的隐藏索引的有效,达到我们想要的不会影响我们的业务要求
-- 具体设置SQL如下：
-- 当前会话打开隐藏字段可见 设置查询优化器可以看到隐藏索引
SET SESSION optimizer_switch="use_invisible_indexes=on';   

--  测试完毕之后可以 打开我们的隐藏索引的可见性
ALTER TABLE [tableName] ALTER INDEX [indexName] INVISIBLE

ALTER TABLE [tableName] ALTER INDEX [indexName] VISIBLE
	

• 图1：查看单表的索引创建情况 (默认会有visable属性，因为是5.6版本的MySQL数据库所以没有)
  

2.降序索引

在创建表的时候8以前的版本默认索引是支持升序索引的，在MySQL8提高了降序索引 主要的作用就是用来提高的我们的查询效率

CREATE TBALE tableName(
	I INT,
	J INT,
	INDEX(I,J DESC) -- 组合索引 I是升序 J是降序
)

CREATE INDEX 【indexName】 FROM  【tableName(columnName [DESC|ASC])】

3.函数索引(MySQL8)

  函数索引，在我们MySQL5.7版本，我们的索引字段若是携带了函数或者在索引列进行了计算操作就会导致我们的索引失效，在MySQL8中我们的函数式索引解决这个问题

-- 函数式索引 原理:就是在我们的表中增加一个不可见的列 UAPPER 函数向上转型 有一列用户不可见的列
CREATE INDEX indexName FROM [tableName((函数(colunname)))]

4.MySQL表达式(CET)

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;

5.MySQL窗口函数支持

-- 聚合操作
SELECT COUNTRY,SUM(COUNT) FROM T1 GROUP BY COUNTRY ORDER BY COUNTRY 
-- 窗口函数的主要作用就是在原来的基础之上加一列 来实现我们的聚合操作
SELECT *,SUM(COUNT) over (PARTITION by country) as COUNTSUM FROM T1

6.对JSON的数据类型支持

官网地址详细可以查看官网(不懂英文可以使用插件来查看)： https://dev.mysql.com/doc/refman/8.0/en/json.html

-- 在我们创建数据类型的时候可以使用JSON来当作数据类型了
CREATE TABLE T1(
	jsonArray JSON
);
-- 插入数据
INSERT INTO T1(jsonArray) VALUES('{"name":"张三","age":14}');

-- 插入错误数据 会报错 跟Java类似会报错解析异常
INSERT INTO T1(jsonArray) VALUES('{"name":"张三","age":14');

7.DLL原子性操作

  普通来说就是我们的DLL语句如果发生了报错就会回滚 (若不懂回滚建议查看一下MySQL事务)

-- 由于某些原因导致我们的DLL语句报错MySQL8就会不执行一部分操作
DROP TABLE T1,T2

8. 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 重命名通用表空间。