MySQL 8.0新特性

一. 账户与安全

MySQL 8中，用户创建与授权语句必须是分开执行。老版本是一个sql。

1. 查看用户信息

2. 用户权限管理范围

1. 可以限制用户访问哪些库、哪些表
2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
3. 可以限制用户登录的IP或域名
4. 可以限制用户自己的权限是否可以授权给别的用户

3. 用户创建和授权

• all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。

  可通过官网看权限类型

• on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写*表示所有数据库，所有表。如果我要指定将权限应用到test库的a表中，可以这么写：test.a。

• to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”zhp”@”192.0.0.%”，表示yangxin这个用户只能在192.0.0.* IP段登录。

• identified by：指定用户登录密码。

• with grant option：表示允许用户将自己权限授权给他人

-- 用户创建
create user 'zhp'@'%' identified by 'zhp.1221';-- 用户授权
grant all privileges on *.* to 'zhp'@'%' with grant option;-- 用户权限变更时，重新加载权限，将权限信息从内存写入数据库
flush privileges;

1） 创建并授权用户

创建zhp账户。密码zhp.1221。

授权test数据库中的a表的所有权限。

允许zhp账户登录任何机器。

2）登录zhp，密码zhp.1221。验证数据库权限

没有指定information_schema数据库，为什么还会显示这个库的权限呢？

MySQL 允许所有用户看到系统数据库的名称和某些表的内容，以便他们能够查询关于自己数据库对象的元数据。

3）查看用户权限

show grants for 'zhp'@'%';

4）撤销用户权限

撤销用户test.a表的查看权限

登录用户zhp查看a表

权限拒绝

5）用户重命名&修改密码

⭐️用户重命名

rename user 'zhp'@'%' to 'zhang'@'%';

⭐️修改密码

set password for 'zhang'@'%' = '123456';alter user 'zhang'@'%' identified by '123456';

⭐️账号登录

6）删除用户

-- 切库
use mysql;
-- 删用户
drop user 'zhang'@'%';

4. 认证插件更新

1）认证插件

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

show variables like 'default_authentication_plugin';

⭐️如果更新相关插件参数，则需对my.cnf文件进行修改，修改重启MySQL重启后才生效。

2）my.cnf文件&配置修改

⭐️如何知道my.cnf文件位置在哪？

mysql --help | grep my.cnf;

# mac安装的mysql软件不自带my.cnf# 创建my.cnf
sudo touch /etc/my.cnf;
# 增加权限
sudo chmod 664 /etc/my.cnf;
# 修改配置
sudo vim /etc/my.cnf;[mysqld]
# 端口修改
port=3307
# 修改验证身份插件
# default-authentication-plugin=mysql_native_password# 重启mysql
sudo /usr/local/mysql/support-files/mysql.server restart

配置信息

重启mysql

验证更改的配置，连接端口是否生效

3. 密码管理

MySQL 8.0 开始允许限制重复使用以前密码（修改密码时）。并且还加入密码的修改管理功能。

show variables like 'password%';

通过mysql.user查看历史用户的限制密码次数

use mysql;
--查看限制重复使用以前密码
select user, Password_reuse_history from user;

⭐️修改限制重复使用以前密码，修改密码不能和最近3次一致

-- 全局策略
set persist password_history = 3;-- 用户级别
alter user 'zhang'@'%' password history 3;

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

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

set persist password_require_current=on;

二. 索引增强

1. 隐藏索引

1）概念&应用场景

MySQL 8.0 开始支持隐藏索引（invisible index），不可见索引。

隐藏索引不会被优化器使用，但仍需要进行维护。

应用场景

1. 软删除：线上表，进行删除索引，会消耗性能，在MySQL8中可以把指定的索引变成隐藏索引（索引为不可用，查询优化器也不可用），最后确定要删除索引，进行alter table 表名 drop index 索引名删除。
2. 灰度发布：进行线上表的索引发布，索引创建，会对表的写操作阻塞住，极大消耗性能，这时可先创建一个隐藏索引，不影响当前生产环境。或者在线上进行一些索引测试，可以通过设置让mysql优化器可用这个隐藏索引，方便我们测试，后续会讲这个配置。

2）看索引可见性信息

-- create index 
create index idx_city on a (city) invisible;
-- 查看a表的索引信息
show index from a\G;

3）mysql查询优化器，看是否走idx_city索引

select city from a where city = '四川';

4）对隐藏索引进行测试

可通过优化开关，打开设置，方便对隐藏索引进行设置

-- 查看优化器开关各种参数配置
select @@optimizer_switch\G

红色部分默认，隐藏索引不可用，参数配置修改

• 会话级别的设置只对当前连接有效。
• 当客户端断开连接时，这些设置会被重置为默认值或全局设置的值

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

5）隐藏索引变成可见索引

-- 可见
alter table a alter index idx_city visible;
-- 不可见
alter table a alter index idx_city invisible;

6）键不能设置为隐藏索引（MySQL做了限制）

2. 降序索引

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

https://dev.mysql.com/blog-archive/mysql-8-0-labs-descending-indexes-in-mysql/

3 函数索引

1）概念

查询如果加入函数，索引不生效，MySQL 8引入了函数索引。

MySQL8.0.13开始支持在索引使用函数（表达式）的值。支持降序索引，支持JSON数据的索引。

2）使用函数索引（表达式）

create table c (c1 varchar(10), c2 varchar(10));-- 普通索引
create index idx_c1 on c (c1);
-- 大写函数索引
create index idx_func on c ((UPPER(c2)));

3）查看索引信息

show index from c\G

4）执行计划验证函数索引

-- 函数索引
explain select * from c where upper(c2) = 'TREE';
-- 普通索引
explain select * from c where upper(c1) = 'TREE';

5）使用函数索引（JSON）

-- 具体来说，您的语句尝试从 data 列中的 JSON 对象提取名为 "name" 的键的值，并将其转换为不超过25个字符的字符串，然后为此表达式创建索引。
create table d (data json, index((CAST(data->>'$.name' as char(25)))));explain select * from d where CAST(data ->> '%$.name' as char(25)) = 'zhp';

6）函数索引基于虚拟列功能实现

函数索引在MySQL中相当于新增了一个列，这个列会根据指定的函数进行计算结果，然后使用函数索引时，用这个新增的计算列作为索引。

三. 通用表达式（CTE）

资料

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

CTE（Common Table Exressions），是一个可以在单个语句范围内被创建的临时结果集，可在该语句中被多次引用。

1. 入门

-- 入门1
WITH cte (col1, col2) AS
(SELECT 1, 2UNION ALLSELECT 3, 4
)
SELECT col1, col2 FROM cte;-- 入门2
-- cte临时结果集，被select n+1 from cte where n<10调用
WITH recursive cte(n) as
( select 1union ALLselect n+1 from cte where n<10
)
select * from cte;

2. 案例

有个cte_test表，有id，name，parent_id，查询每个员工的上下级关系

-- 创建表
create table cte_test
(id        int(10) primary key auto_increment,name      varchar(20),parent_id int(10)
);-- 数据插入
insert into cte_test (name, parent_id)
values ('张三', 0),('李四', 1),('王五', 1),('奥特曼', 2),('喜羊羊', 2),('懒羊羊', 4);-- 查询with cte_view(id, name, parent_id) as(select id,name,parent_id from cte_test where parent_id = 0union allselect c2.id, c2.name, concat(c1.parent_id, '-', c2.id)from cte_test as c1 inner join cte_test as c2on c1.id = c2.parent_id
)
select * from cte_view order by id;

使用通用表达式的好处就是上下级层级就算有3～n层，也可以帮我们遍历出来（parent_id默认父级id-子集id），而老的方式的写法SQL语句就需要调整。

总结：

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

四. 窗口函数

1. 概念

MySQL 8.0支持窗口函数（Window Function），也称分析函数。窗口函数与分组聚合函数类似。但是每一行数据都生成一个结果。聚合窗口函数：sum，avg，count，max，min等等。

2. 建表&数据

create table func_test
(year    int(10),country varchar(10),product varchar(10),sum     int(10)
);insert into func_test
values (2021, 'USA', 'photo', 1300),(2022, 'USA', 'photo', 1800),(2020, 'USA', 'photo', 2400),(2021, 'CHA', 'photo', 5300),(2022, 'CHA', 'photo', 7800),(2020, 'CHA', 'photo', 3400),(2021, 'JPA', 'photo', 3300),(2022, 'JPA', 'photo', 5800),(2020, 'JPA', 'photo', 6400),(2021, 'USA', 'TV', 3300),(2022, 'USA', 'TV', 800),(2020, 'USA', 'TV', 400),(2021, 'CHA', 'TV', 300),(2022, 'CHA', 'TV', 300),(2021, 'USA', 'TV', 800),(2020, 'CHA', 'TV', 400),(2021, 'JPA', 'TV', 300),(2022, 'JPA', 'TV', 800),(2020, 'JPA', 'TV', 400)

3. 普通的分组，聚合（以国家单位统计）

select country, sum(sum) from func_test group by country

4. 窗口函数（以国家汇总）

OVER (PARTITION BY country): 这指定了一个窗口函数，它将数据按照 country 列进行分组，并在每个分组内执行 SUM() 操作。

把国家进行sum汇总。并展示

select year, country, product, sum, sum(sum) over (partition by country) as country_sum
from func_test
order by country, year, product, sum

5. 窗口函数（计算平均值）

select year, country, product, sum, sum(sum) over (partition by country) as country_sum,avg(sum) over (partition by country) as country_sum
from func_test
order by country, year, product, sum

6. 窗口函数（排名）

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

SELECTYEAR,country,product,sum,row_number() over (ORDER BY sum) AS 'rank',rank() over (ORDER BY sum) AS 'rank_1'
FROMfunc_test;

7. 窗口函数（累积总和）

SELECTYEAR,country,product,sum,sum(sum) over (PARTITION by country order by sum rows unbounded preceding) as sum_1
FROMsales order by country,sum;

8. 更多窗口函数

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

五. 原子DDL操作

MySQL8.0开始支持原子DDL操作，其中与表相关的原子DDL只支持InnoDB存储引擎。

一个原子DDL操作内容包括：更新数据字段，存储引擎层操作，在binlog中记录DDL操作。

支持与表相关的DDL：数据库，表空间，表，索引的create，alter，drop，truncate table。

支持的其他DDL：存储程序，触发器，视图，UPD的create，drop，alter.

支持账户管理相关的DDL：用户和角色create，alter，drop，rename，grant，revoke。

-- 如果只有t1,没有表t2表。在MySQL5.7与8表现是不同的。
-- 5.7中会删除t1表。而在8中会报错，因为是原子操作。
drop table t1, t2;

六. JSON增强

MySQL - The JSON Data Type

七. InnoDB其他改进功能

1. 自增列持久化

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

2. 死锁检查控制

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

show variables like 'innodb_deadlock_detect';

3. 锁定语句选项

select ... for share 和 select ... for update 支持nowait，skip，locked选项。对于nowait，如果请求的行被其他事务锁定时，语句立即返回。对于skip locked，从返回的结果集中移除被锁定的行。

4. InnoDB其他改进功能

1. 支持部分快速DDL，alter table algorithm=instant。
2. InnoDB临时表使用共享的临时表空间ibtmp1。
3. 新增静态变量 innodb_dedicated_server，自动配置 InnoDB 内存参数：innodb_buffer_pool_size/innodb_log_file_size 等。
4. 默认创建 2 个 UNDO 表空间，不再使用系统表空间。
5. 支持 ALTER TABLESPACE … RENAME TO 重命名通用表空间。