mysql8新特性一

一、账户与安全

1.用户的创建与授权

  在MySQL5.7的版本:

  > grant all privileges on *.* to '用户名'@'主机' identified by '密码';

  在MySQL8.0需要分开执行:

  >create user '用户名'@'主机' identified by '密码';

  >grant all privileges on *.* to '用户名'@'主机';

  用以前的一条命令在8.0里面创建用户,会出现sql语法错误
 

2.认证插件更新

  MySQL5.7默认身份插件是mysql_native_password

  MySQL8.0默认的身份插件是caching_sha2_password
  查看身份认证插件命令:show variables like 'default_authentication_plugin%';或
select user,host,plugin from mysql.user;


  身份认证插件可以通过以下2中方式改变:    

       1)系统变量default_authentication_plugin去改变,在my.ini文件的[mysqld]下面设置default_authentication_plugin=mysql_native_password即可
  2)如果希望只是某一个用户通过mysql_native_password的方式认证,可以修改数据库mysql下面的user表的字段,

             执行以下命令: alter user '用户名'@'主机' identified width mysql_native_password by '密码';

3.密码管理  

MySQL8.0的密码管理策略有3个变量  

        password_history 修改密码不允许与最近几次使用或的密码重复,默认是0,即不限制    

        password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复,默认是0,即不限制
  password_require_current 修改密码是否需要提供当前的登录密码,默认是OFF,即不需要;如果需要,则设置成ON  

查询当前MySQL密码管理策略相关变量,使用以下命令:    

>show variables like 'password%';  

1)设置全局的密码管理策略,在my.ini配置文件中,设置以上3个变量的值这种设置方式,需要重启mysql服务器;某些生产环境不允许重启,MySQL8.0提供了关键字persist 持久化,

执行以下命令:  

>set persist password_history=6;  

这条命令会在数据目录下生成新的配置文件(/var/lib/mysql/mysqld-auto.cnf),下次服务器重启的时候除了读取全局配置文件,还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的
 

2)针对某一个用户单独设置密码管理策略  

>alter user '用户名'@'主机' password history 5;  这样,这个用户的password_history 就被设置成了5;

查看一下:  

>show user,host,Password_reuse_history from user; 

4.角色管理

角色:一组权限的集合 一组权限赋予某个角色,再把某个角色赋予某个用户,那用户就拥有角色对应的权限

1)创建一个角色

>create role '角色1'; (角色也是在用户表中mysql.user)

2)为这个角色赋予相应权限

>grant insert,update on *.* to '角色1';

3)创建一个用户

>create user '用户1' identified by '用户1的密码';

4)为这个用户赋予角色的权限

>grant '角色1' on *.* to '用户1';

执行完上面4步,用户1就拥有了插入与更新的权限

5)再创建1个用户

>create user '用户2' identified by '用户2的密码';

6)为这个用户赋予同样的角色

>grant '角色1' on *.* to '用户2'; 执行完上面2步,

用户2也用了角色1的权限,即插入与更新 查看用户权限,执行以下命令:

>show grants for '用户名' [using '角色名'];

7)启用角色,设置了角色,如果不启用,用户登录的时候,依旧没有该角色的权限

> select current_role();查看启用的角色

> set role '角色名';启用角色

>set default role '角色名' to '用户名';设置默认角色

8)如果一个用户有多个角色,使用以下命令

>set default role all to '用户名';

MySQL中与用户角色相关的表:

mysql.default_roles、mysql.role_edges

9)撤销权限

>revoke insert,update on *.* from '角色名';  

 

二、优化器索引

1.隐藏索引(invisible index)

  隐藏索引不会被优化器使用,但仍需要维护

  应用场景:

  1)软删除

    删除索引,在线上,如果删除错了索引,只能通过创建索引的方式将其添加回来,对于一些大的数据库而言,是比较耗性能的;为了避免删错,可以先将其设置为不可见,优化器这时候就不会使用它,但是后台仍然在维护,确定后,再删除。

  2)灰度发布

    与软删除差不多,如果想要测试一些索引的功能或者随后可能会使用到这个索引,可以先将其设置为隐藏索引,对于现有的查询不会产生影响,测试后,确定需要该索引,可以将其设置成可见索引。

  创建隐藏索引,执行如下命令(如果是不隐藏,则不需要后面的invisible关键字):

  >create index 索引名称 on 表名(字段名) invisible;

  查询某一张表的索引,执行如下命令:

  >show index from 表名;

  使用explain语句查看查询优化器对索引的使用情况

  >explain select * from 表名 where 条件;

  查询优化器有很多开关,有一个是use_invisible_indexes(是否使用隐藏索引),默认是off(不适用),将其设置成on,即可使用隐藏索引。查看当前查询优化器的所有开关,执行如下命令:

  >select @@optimizer_switch;

       打开开关

       >set session optimizer_switch="use_invisible_indexes=on";

  设置已经存在的索引为可见或者隐藏,执行如下命令:

  >alter table 表名 alter index 索引名 visible;

  >alter table 表名 alter index 索引名 invisible;
  主键不可以设置为隐藏所以。


2.降序索引(descending index)

  MySQL8.0开始真正支持降序索引,只有InnoDB引擎支持降序索引,且必须是BTREE降序索引,MySQL8.0不在对group by操作进行隐式排序

mysql5.7

> create table t1(c1 int, c2 int, index idx(c1 asc, c2 desc));

> show create table t1;

> insert into t1(c1,c2) values(1,100),(2,300),(3,110),(4,30);

> explain select * from t1 order by c1,c2 desc\G;

mysql8

> create table t1(c1 int, c2 int, index idx(c1 asc, c2 desc));

> show create table t1;

> insert into t1(c1,c2) values(1,100),(2,300),(3,110),(4,30);

> explain select * from t1 order by c1,c2 desc\G;

> explain select * from t1 order by c1 desc,c2 \G;

3.函数索引

  索引中使用函数表达式
  
  支持降序索引,支持JSON数据节点的索引

  函数索引是基于虚拟列的功能实现的

假设用户表(tb_user)的用户登录账号(username)不需要区分大小写,则可以创建一个函数索引

>create index username_upper_index on tb_user((upper(username)));
这样在查询的时候 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就会使用索引。

上面的函数索引,也可以通过MySQL5.7已有的虚拟计算列来模拟,为用户表(tb_user)创建新的一列(new_column),这一列是计算列,不需要赋值,它的值就是username的大写。

>alter tbale tb_user add column new_column varchar(10) generated always as (upper(username));

然后给new_column创建一个索引,可以达到模拟MySQL8.0中的函数索引的效果。这样在查询的时候 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就会使用索引。

 

json数据节点的索引

 ->> (内联路径)运算符,该运算符等效于调用 JSON_UNQUOTE()的结果JSON_EXTRACT()

> create table emp(data json, index( (CAST(data->>'$.name' as char(30)) )));

> explain select * from emp where CAST(data->>'$.name' as char(30)) = 'abc'\G;

 

三、通用表表达式

1、非递归CTE

> with cte1(id) as (select 1),

cte2(id) as (select id+1 from cte1)

select * from cte1 join cte2;

 

> With cte1(country_id,country_name) as (select id,name from sch_city where level = 0),
 cte2(province_id,province_name) as (select id,name from sch_city where level = 1),
cte3(city_id,city_name) as (select id,name from sch_city where level = 2)
Select a.country_id, country_name,a.province_id, province_name,a.city_id,city_name,a.address 
from sch_address a 
Left join cte1 on a.country_id = cte1.country_id
Left join cte2 on a.province_id = cte2.province_id
Left join cte3 on a.city_id = cte3.city_id;

2、递归CTE

With recursive cte(n) as 
(
Select 1
Union all
Select n+1 from cte where n < 5

Select * from cte;

> Create table employees(id int, name varchar(32), manager_id int);

> Insert into employees values(29,'Pedro',198),(72,'Pierre',29),(123,'Adil',692),(198,'John',333), (692,'Tarek',333),(4610,'Sarah',29);

> Insert into employees(id,name) values(333,'Yasmina');

With recursive employee_paths(id,name,path) as
(
Select id,name,cast(id as char(200))
From employees
Where manager_id is null
Union all
Select e.id,e.name,concat(ep.path,',',e.id)
From employee_paths as ep join employees as e
On ep.id = e.manager_id
)
Select * from employee_paths;

 

With recursive sch_city_paths(id, name, path) as
(
Select id,name,cast(id as char(200))
From sch_city
Where level = 0
Union all
Select c.id,c.name,concat(cp.path,',',c.id)
From sch_city_paths as cp join sch_city as c
On cp.id = c.pid
)
Select * from sch_city_paths;

3、递归限制

递归表达式的查询中需要包含一个终止递归的条件

cte_max_recursive_depath 最大递归层数
max_execution_time

4、CTE小结

通用表表达式与派生表类似,就像语句级别的临时表或视图。

CTE可以在查询中多次引用,可以引用其他CTE,可以递归

CTE支持SELECT/INSERT/UPDATE/DELETE等语句

 

 

 

 

 

 

 

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值