MySQL8新特性

账户与安全

用户创建和授权

相关sql

-- 创建用户并授权
grant all privileges on *.* to 'tgl'@'%' identified by '123456';


-- 创建用户
create user 'tgl'@'%' identified by '123456';
-- 为用户授权
grant all privileges on *.* to 'tgl'@'%';

mysql8中，创建用户和授权必须分开执行，之前版本是可以一起执行

mysql5.7

认证插件更新

相关命令

show variables like 'default_authentication%';

MySQL8中，默认认证插件为：caching_sha2_password

MySQL5.7中，默认的认证插件为：mysql_native_password

mysql_native_password 插件使用经典密码验证方式，通过比较用户在连接 MySQL 时发送的密码与服务器上保存的密码来进行身份验证。它是 MySQL 默认的身份验证插件，该插件存在一些安全隐患，包括密码被客户端截取、中间人攻击等风险。

caching_sha2_password 插件是 MySQL 5.7.5 版本及更高版本中引入的新的身份验证插件，改进了 mysql_native_password 插件存在的安全隐患。它使用 SHA-256 算法对密码进行加密，并使用随机生成的盐值进行加盐，从而更加安全。除此之外，caching_sha2_password 插件还支持认证缓存，可以缓存连接的客户端的身份验证信息，以提高认证的性能。

密码管理

MySQL8在修改密码时，允许重复使用之前的密码，并且还加入了密码管理功能。

相关sql

show variables like 'password%';

password_history

修改密码不能和最近几次一致。

全局修改

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

show variables like 'password%';

用户维度修改

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

select user,host,Password_reuse_history from mysql.user;

password_require_current

代表是否需要校验旧密码，off不校验，on校验，针对非root用户。

password_reuse_interval

按照天数来限制修改密码不能重复。

索引

隐藏索引（不支持主键）

MySQL8中支持隐藏索引（invisible index）

创建隐藏索引的sql

create index user_name_index on user (name) invisible

查看索引

查看查询优化器使用开启了使用隐藏索引的开关

select @@optimizer_switch;

会话维度开启查询优化器使用隐藏索引开关

set session optimizer_switch="use_invisible_indexes=on";

修改隐藏索引的显隐

-- 使索引显示
alter table user alter index  user_name_index visible;

-- 使索引隐藏
alter table user alter index  user_name_index invisible;

可以在创建sql时指定索引为隐藏索引，并且可以后续通过sql将sql改为“显示”或者“隐藏”，“显示”时索引可以正常使用，“隐藏”时索引不可用（查询优化器也无法使用）。

适用场景：在一些灰度发布或者是临时线上添加索引的场景中使用，与不支持invisible index的版本相比，隐藏索引更加灵活。传统方式添加索引和删除索引会会数据库的性能造成影响，而设置隐藏索引的“显示”或“隐藏”对数据库的性能影响很小。

降序索引

MySQL8支持索引中字段指定升序、降序。

MySQL8创建id和score复合索引，id升序，score降序

create index user_index on user (id asc, score desc);

MySQL5.7中不可指定索引中字段的排序。

create index user_index on user (id, score);

同样的查询语句

explain select id,score from user order by id ,score desc;

MySQL8

MySQL5.7

MySQL8中group by 不在支持默认排序，需要手动添加order by 进行排序。

函数索引

在MySQL8.0之前，如果在索引列上使用函数，会导致索引失效，但是MySQL8.0已经支持创建函数索引。

在name字段上创建大写函数索引，可以看到和非函数索引相比，函数索引的主要体现在“表达式”。其实函数索引基于虚拟列功能实现，相当于在MySQL中新增了一个列，这个列会根据你的函数计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。

create index name_index on user ((upper(name)));

转大写函数索引

普通索引

通用表表达式（CTE）

MySQL8.0开始支持通用表表达式（common table expression） ，即WITH子句。

简单入门：

一下SQL就是一个简单的CTE表达式，类似于递归调用。首先执行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;

如下是一张员工表，id、name、l_i，其中l_id是直属上级的id。

查询公司员工并输出员工id层级，其中职级最高的员工id排在最前面，中间用“-”拼接。

with recursive emp_view(id, name, l_id_path) as(
            select id, name, cast(id as char(200))
            from employee
            where l_id = 0
            union all
            select t2.id, t2.name, concat(t1.l_id_path, '-', t2.id) as l_id_path
            from emp_view as t1
                     join employee as t2
                          on t1.id = t2.l_id)
select *
from emp_view
order by id;

查询结果

通用表达式与派生表类似，就像语句级别的临时表或者视图。CTE可以在查询多次引用，可以引用其他CTE，可以递归。CTE支持select、insert、update、delete等语句。

窗口函数

MySQL8.0支持创库函数（Window Function），也称分析函数。窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果。聚合窗口函数：SUM、AVG、COUNT、MAX、MIN等等。

seals表，反映的是一些每个国家从2020-2023年一些产品的销售情况：

普通的分组聚合，以国家维度统计。

select country, sum(sum)
from sales
group by country
order by country desc;

汇总窗口函数

select year, country, product, sum, 
sum(sum) over (PARTITION BY country) as countru_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;

排名窗口函数

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

累计求和窗口函数

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;

专用窗口函数：

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

原子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语句。

drop table t1,t2;

上面的语句，如果只存在t1，不存在t2。

MySQL5.7中会删除t1。而在MySQL8.0中会报错，因为此语句是原子操作，t1不会被删除。

InnoDB其他功能

自增列持久化

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

死锁检查控制

MySQL5.7.15（MySQL8.0）增加了一个新的动态变量，用于控制系统是否执行InnoDB死锁检查。对于高并发系统，禁用死锁检查可能带来性能问题。

show variables like  'innodb_deadlock_detect';

SELECT ... FRO SHARE 和 SELECT...FRO UPDATE支持NOWAIT、SKIP LOCKED选项。对于NOWAIT，如果请求的行被其他锁定时，语句立即返回。对于SKIP LOCKED，从返回的结果集中移动被锁定的行。

SELECT * FROM t WHERE id = 2 FOR UPDATE NOWAIT;


SELECT * FROM t FOR UPDATE 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 TABLESAPCE ... RENAME TO 重命名通用表空间。