六、MySQL8新特性

一、账户与安全

1、MySQL 8 性能好

可达到 200W QPS；

2、用户创建和授权：

5.7 一句话实现用户的 创建和授权 语句；

grant all privileges on *.* to 'allwe'@'%' identified by 'password';

8 分开执行；

create user 'allwe'@'%' identified by 'password';  //创建用户语句

 grant all privileges on *.* to 'allwe'@'%' ;   //授权语句

3、认证插件更新：

查询认证方式：select user,host,plugin from mysql.user;

5.7  mysql_native_password;   //安全级别低

8 caching_sha2_password;   //安全级别高

8 修改登录密码认证插件 

查询插件 show variables like 'default_authentication';

        1、修改插件 在my.cnf文件中修改配置（需要重启MySQL）；

                配置：default-authentication-plugin=mysql_native_password

        2、直接修改用户密码的插件

                命令：alter user 'allwe'@'%' identified with mysql_native_password by 'password';

4、密码管理：

5.7 不允许重复修改密码：123456 -> 123456（×）；

8 允许重复修改密码；

开启限制后记录历史密码，限制最近使用的N次密码；

        1、检查密码配置：show variables like 'password%'；

                结果：password_history = 3；//不允许使用最近的N次历史密码；

                命令：修改【限制历史密码次数】：set persist password_history = 3；

        2、直接修改用户密码的限制

                命令：alter user 'allwe'@'%' password history 3；

                查看配置：select user,host,password_reuse_history from mysql.user；

                //也是不让使用最近的N次；

        3、修改密码时是否需要输入旧密码：

                结果：password_require_current = OFF；//默认不需要

        4、不让使用几天内用过的密码：

                结果：password_reuse_interval = 3；

二、索引增强（隐藏、降序、函数索引）

1、隐藏索引

特点：不可见，不会被优化器使用；

支持软删除：

        删除、创建索引，都会消耗性能；

        在mysql 8 中可以将索引进行软删除，暂时不使用索引；

         隐藏索引可以删除和恢复；

支持灰度发布：

        线上测试；

        加索引会影响业务，加隐藏索引不会影响；

        在测试环境，可以通过一些设置，查询优化器会使用这个索引，如果这个索引没问题，那就改成正式索引；

相关操作命令：      

        创建隐藏索引： create index j_idx on table(j) invisible;

        检查索引：show index from table\G;

        visible = NO；//不可见索引

        检查是否使用隐藏索引：select @@optimizer_switch\G;

        use_invisible_indexes = off; //是否使用隐藏索引

        当前会话启用隐藏索引：set session optimizer_switch="use_invisible_indexes=on";

        修改索引：alter table t1 alter index j_idx visible; //可见

2、降序索引

MySQL 8 -> innoDB支持降序索引；

创建方式：create table t2 (c1 int,c2 int ,index idx_1(c1 asc,c2 desc));

检查索引：show create table t2\G；

支持索引字段按倒序排序，联合索引支持每个字段都可以有自己的排序方式；

3、函数索引

老版本：使用group by 【字段】；会默认对字段做升序排序；

 MySQL 8 不会自动排序；

        UPPER函数：create index idx_c1 on table((UPPER(c1))); //对UPPER函数做个函数索引

        检查 show index from from table\G；

                Expressions = upper(c1)；

        查询语句中，可以对索引列使用对应的函数，不会让索引失效；

        底层实现：新增了一个不可见列（函数 + 索引列）,查询时用的索引列是这个不可见列；

三、通用表表达式

CTE：common table expression ；

相当于一个with 子句；

1、示例：查询上级层级关系（id，name，m_id(上级id)）

with recursive staff_view(id,name,m_id) as 
-- 查询到的数据都存到staff_view视图里面
(
  -- 第一次进递归运行的SQL
  select id,name,cast(id as char(200))
  from staff where m_id = 0
  
  union all
   
  -- 第一次递归之后运行的SQL
  select s2.id,s2.name,concat(s1.m_id,'-',s2.id)
  from staff_view as s1 join staff as s2
  on s2.id = s1.m_id
)
select * from staff_view order by id;

2、支持select、insert、update、delete；

四、窗口函数

特点：窗口函数与【分析、聚合、分组】类似

1、普通函数示例

-- sum，avg，count，max，min；

select 
    year,
    country,
    product,
    sum,
    -- 创建一个新的列，求每个国家商品数量总和
    sum(sum) over (partition by country) as country_sum,
    -- 加一列平均值
    avg(sum) over (partition by country,product) as country_product_avg
from sales
    order by XXX;

2、专用函数示例

-- 没写完

五、原子DDL操作、JSON增强及其他

1、DDL操作：drop、create 语句

老版本不支持原子操作，有的操作成功，有的失败；

MySQL 8 支持原子操作，要么都成功，要么都失败；

2、JSON增强

mongoDB noSQL数据 --> JSON

3、自增列持久化

解决长期使用自增列重复的风险；

4、死锁检查控制

增加了一个新的动态变量，用来控制是否启用死锁检查；

在高并发环境下，关闭死锁检查可能会提高性能；

innodb_deadlock_detect

5、锁定语句选项

在select ... for share 和 select ... for update 中支持两个参数：

        nowait ：如果请求的行被其他事务占用，那么语句立即返回；

        skip locked：舍弃掉被占用的行数据，直接不返回；

6、innodb 其他功能改进

支持部分快速DDL，alter table algorithm=instant；

innob 临时表使用共享的临时表空间：ibtmp1；

默认创建2个UNDO表空间，不再使用系统表空间；

支持重命名表空间；