Mysql8.0新特性之详细版本

1. 账户与安全

• 用户创建与授权

之前：创建用户并授权

1 grant all privileges on *.* to 'myuser'@'%' identified by '3edc
#EDC';2 select user, host form mysql.user;

之后：创建用户和授权必须分开

1 create user 'myuser'@'%' identified by '3edc#EDC';
2 grant all privileges on *.* to 'myuser'@'%';3 select user, host form mysql.user;

• 认证插件更新

1 show variables like 'default_authentication%';2 select user, host, plugin from mysql.user;

之前：mysql_native_password

之后：caching_sha2_password

1 #修改为之前的认证插件
2 
#方法一 修改配置文件
3 default-authentication-plugin=mysql_native_password
4 
#方法二 修改用户密码指定认证插件
5 alter user 'myuser'@'%' identified with mysql_native_password by '3edc
#EDC';

• 密码管理

【新增】允许限制使用之前的密码。

password_history=3 #不能和最近3天的密码相同

password_reuse_interval=90 #不能同90天内使用过的密码相同

password_require_current=on #修改密码时需要输入当前密码

show variables like 'password%';
#修改全局密码策略-按天设置
#password_history 与 password_reuse_interval设置方法相同
#方法一 添加配置文件password_history=3
#方法二 持久化参数设置set persist password_history=3;
#方法三 通过用户设置alter user 'myuser'@'%' password history 5;
select user, hostm password_reuse_histtory from mysql.user;
#修改全局密码策略-输入密码设置
#只针对普通用户有效，针对root等具有修改mysql.user表权限的用户无效set persist password_require_current=on;
alert user user() identified by 'newpassword' replace 'oldpassword';

• 角色管理

【新增】根据角色设置用户权限

#创建角色creaye role 'role_1_wirte';
#角色即用户select user, host, authentication_string from mysql.user;
#给角色授权grant insert, update, delete on test_db.* to 'role_1_wirte';
#给用户赋予角色grant 'role_1_wirte' on 'myuser';
#查询用户权限show grant for 'myuser';show grant for 'myuser' using 'role_1_wirte';
#用户启用角色(普通用户登录)set role 'role_1_wirte';
#设置默认角色（root用户）set default role all to 'myuser';
select * from mysql.default_roles;select * from mysql.role_edges;
#撤销角色revoke insert, update, delete, select on test_db from 'role_1_wirte';
show grant for 'role_1_wirte';show grant for 'myuser' using 'role_1_wirte';

2. 优化器索引

• 隐藏索引（invisible index）

【新增】不会被优化器使用，但仍然需要维护。

应用场景：软删除，灰度发布。

#创建数据库、表create database test_db;
use test_db;
create table test1 (id int(11), parent_id int(11));
#创建普通索引和隐藏索引create index id_idx on test1(id);
create index parent_id_idx on test1(parent_id) invisible;
#查看索引show index from test1\G
#查询优化器explain select * from test1 where id = 1;
explain select * from test1 where parent_id = 1;
#当前会话测试隐藏索引set session optimizer_switch="use_incisible_indexes=on";
select @@optimizer_switch\Gexplain select * from test1 where parent_id = 1;
#设置隐藏索引可见于隐藏alter table test1 alter index parent_id visible;
alter table test1 alter index parent_id invisible;
#注意：主键不能设置隐藏索引

• 降序索引（descending index）

之前：虽然可指定降序索引，实为升序索引。

之后：支持降序索引。

仅InnoDB存储引擎支持降序索引，并且只支持BTree降序索引。

group by不再对结果隐式排序，需要使用order by进行排序。

#Mysql5.7创建降序索引create table test2 (read_num int(5), wirte_num int(5), index read_wirte_idx(read_num asc, wirte_num desc));
show create table test2\G
#Mysql8.0创建降序索引create table test2 (read_num int(5), wirte_num int(5), index read_wirte_idx(read_num asc, wirte_num desc));
show create table test2\Ginsert into test2 values(100, 2),(200, 4),(300, 6);
#Mysql5.7和Mysql8.0上分别测试explain select * from test2 order by read_num, wirte_num desc;
explain select * from test2 order by read_num desc, wirte_num;
#Mysql5.7和Mysql8.0上分别测试group byselect count(*) as cnt, wirte_num from test2 group by wirte_num;
select count(*) as cnt, wirte_num from test2 group by wirte_num order by wirte_num;

• 函数索引

之前：虚拟列创建索引。

之后：支持索引中使用函数的值；支持降序索引；支持JSON数据索引；

原理：基于虚拟列功能实现。

#创建表和索引create table test3 ( id varchar(10), login_code varchar(20));
create index login_code_func_idx on test3((upper(login_code)));
show create table test3\G
#测试函数索引explain select * from test3 where upper(id);
explain select * from test3 where upper(login_code);
#JSON索引create table test4 ( result_data json, index((cast(result_data->>'$.code' as char(3)))));
show create table test4\Gexplain select * from test4 where cast(result_data->>'$.code' as char(3)) = '200';
#Mysql5.7虚拟列实现create table test5 ( id varchar(10), login_code varchar(20));
alter table test5 add column username varchar(20) generated always as (upper(login_code));
insert into test5(id, login_code) values ('A001', 'alan');
create index login_code_idx on test3((upper(login_code)));
create index username_idx on test5(username);
explain sel