MySQL 8 新特性总结_「MySQL 8」MySQL 5.7都即将停只维护了，是时候学习一波MySQL 8了

MySQL 8具备卓越的处理能力，同时引入了众多新颖的功能，我挑选了部分特色鲜明的改进，进行归纳梳理。

本文使用的MySQL版本为 8.0.29

账户与安全用户的创建和授权

早先的MySQL版本里，新增账号以及授予账号权限这两步操作能够合并成一个指令来实施。

授予张三所有权限于所有数据库，用户名为张三，密码为Fawai@kuangtu6，主机名为任意地址；

在MySQL 8的环境下，添加账号和赋予权限这两个操作必须分开实施，否则系统会显示错误，导致任务无法顺利完成。

在 MySQL 8，创建账号和赋予权限，必须分两步来执行，

-- 创建用户
建立用户 zhangsan@%，密码为 Fawai@kuangtu6。
-- 授权
授予张三所有权限于所有数据库，所有表，所有用户，所有操作，无限制，无例外。

再执行创建用户时，出现了如下错误：

这个情况发生在我安装的 MySQL 8 系统里,进入命令行时使用的是临时密码,没有更改 root 的原始密码,所以必须修改密码后才能进行操作。

修改密码操作：

-- 修改root密码
修改账户名，设置为用户，密码为根001号

再创建用户即可：

mysql中新建一个用户名为zhangsan的账户，该账户可以从任何主机登录，登录密码设置为Fawai@kuangtu6。
查询成功，没有数据被改动，耗时零点零一秒。
mysql分配所有权限给张三，在所有数据库和所有主机上，账号为zhangsan，主机名为百分号
查询成功，没有数据被改动，耗时零秒。

认证插件

在MySQL数据库里，能够运用 show 语句来检视部分参数的MySQL设定项，这些设定项里，与密码验证机制相关的参数名称是

。

MySQL 5.7版本 ：

mysql查询默认认证方式设置,显示相关参数值,使用特定模式匹配关键词
长长的一条线段，下面连接着一个小方块，两者之间有明显的界限。
变量名, 代表具体内容, 值, 表明具体数值
+-------------------------------+-----------------------+
默认身份验证插件，采用mysql原生密码验证方式
+-------------------------------+-----------------------+
1 row in set (0.02 sec)

MySQL 8版本 ：

mysql> show variables like '%default_authentication%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
默认的认证插件是缓存型哈希密码验证方式
+-------------------------------+-----------------------+
1 row in set (0.07 sec)

可以明确，5.7 版本预设的身份验证组件是 d ，与此同时，8.0 版本预设的身份验证组件也是 d 。

这个认证插件造成的一个后果是，当我们从客户端直接链接到MySQL时会失败，举例来说

可以暂时调整一下验证模块为 d ，接着检查能否建立连接，变更指令如下：

mysql更改用户zhangsan的密码,使用通配符匹配主机,密码设置为Fawai@kuangtu6,采用mysql原生密码验证方式

此时，我们来看一下 user 表中的插件信息：

认证用户的插件已经更换为d，其余用于认证的插件则维持原本的d设置。

当然，暂时调整插件设置仅能作为权宜之计，若需彻底更改，就必须编辑MySQL的配置文档 /etc/f 里的相关设定

然后重启MySQL服务即可。

密码管理

MySQL 8增添了密码维护措施，能够对先前密码的重复应用加以约束，具体体现在这一项新规定上。

这里有几个属性，其中：

修改 全局策略：

-- 修改密码不能和最近2次一致
设置密码历史记录长度为两,将持久保存该配置,以便在重启后依然生效。

而如果要修改用户级别的 ，命令为：

更改用户名为张三的密码历史记录为2条,对任意主机登录生效

下面来修改一下密码试试。

zhangsan原先设置的密码是Fawai@kuangtu6，后来尝试修改密码，输入的仍然是Fawai@kuangtu6，但是系统根据密码规则判定，新密码不能和最近两次的密码重复，因此这次修改没有被接受
更改用户名为张三的账户密码为福爱酷图六号，该账户允许从任何主机登录

当全局参数置为0时，root用户便不受此限制条件约束：

索引增强

MySQL 8 对索引功能进行了改进，提供了易于检测的 隐藏索引 ，实现了真正的 降序索引 ，并且引入了 函数索引。

隐藏索引

MySQL 8新增了隐藏索引功能，这种索引也称作不可见索引。此类索引不会被查询优化器所采纳，不过依然需要执行维护操作，包括建立和移除等。它们主要适用于两种情况：实现数据的软删除，以及进行灰度发布部署。

借助 隐藏索引 ，测试工作变得十分便捷，这真是个周到的设计！

下面举个例子看看隐藏索引怎么用法。

建立一张表格，然后建立常规索引，再建立非公开索引，

建立名为t_test的表格，包含字段id，字段类型为整数，字段name，字段类型为可变长度字符串，最大长度为20个字符，字段age，字段类型为整数。
为t_test表的name列建立索引，索引名称为idx_name。
建立索引 idx_age, 针对表 t_test, 依据列 age, 设置为隐藏状态

此时，看一下索引信息：

mysql查询t_test表的索引信息
这个队列的第一行内容如下，包含了多个信息项，每个项之间用逗号分隔开来，整体结构清晰明了。
        Table: t_test
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
该行位于第二排位置，它非常醒目，很容易被注意到，在众多行当中显得格外突出，是视觉焦点所在之处。
        Table: t_test
   Non_unique: 1
     Key_name: idx_age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: NO
   Expression: NULL
2 rows in set (0.01 sec)

普通索引的 属性值为OFF，隐藏索引为ON。

再来看一下MySQL优化器怎么处理这两种索引的：

能够发现，隐形索引在进行检索时并不会发挥作用，仿佛不存在这个索引一般，那么 隐形索引 的实际作用究竟是什么？

这里可以通过优化器的开关-- ，

mysql查询系统变量设置情况，具体查看优化器开关参数，使用格式如下
*************************** 1. row ***************************
@@优化器设置：索引合并开启,索引合并联合开启,索引合并排序联合开启,索引合并交集开启,引擎条件下推开启,索引条件下推开启,多路重复读取开启,多路重复读取成本基于开启,嵌套循环块开启,批量键访问关闭,物化开启,半连接开启,宽松扫描开启,首匹配开启,重复项剔除开启,子查询物化成本基于开启,使用索引扩展开启,条件发散过滤开启,派生表合并开启,使用不可见索引关闭,跳过扫描开启,哈希连接开启,子查询到派生表关闭,优先排序索引开启,超图优化器关闭,派生表条件下推开启

看到 s 配置默认是 OFF 的，将其打开看看效果：

-- 在会话级别设置查询优化器可以看到隐藏索引
开启会话中的优化器设置，让其在查询时考虑隐藏索引，这样索引的可见性被启用，查询计划会包含这些索引的选项。

再来看一下隐藏索引 是否生效：

666！！！

如此一来，便于我们项目实施分阶段发布，项目正式推出之前，我想验证新增索引的实际效用，能够暂时将其设定为非公开索引，从而不会干扰线上业务，在会话层面将非公开索引启用进行检测，确认无误后改回公开索引。

可见索引与隐藏索引转换的SQL语句：

-- 转换成可见索引
修改表t_test, 使索引idx_age变为可见, 完成操作
-- 转换成隐藏索引
修改t_test表的idx_age索引，将其设置为不可见状态

降序索引

MySQL 8引入了可用的逆序排列功能，DESC在索引构建时不再被忽视，而是会使得数据按照从高到低的顺序排列。

早些时候，索引能够逆向检索，不过这样会牺牲速度，采用倒序索引则能正向遍历，速度更快。

当最优的查询路径同时涉及部分字段按值递增排列，以及另一些字段按值递减排列时，采用降序排列的索引能够帮助查询优化器利用到多字段索引。

以一个实例来说明，在 MySQL 8 版本和 MySQL 5.7 版本都可以运行下面这个创建数据表的指令：

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

然后看一下表的索引信息：

具体的用处在哪里呢？插入一些数据看一下。

在表t中插入若干行数据，第一列的值分别为1, 2, 3, 4, 5，第二列的值对应为10, 20, 30, 40, 50。

函数索引

过去，若对索引执行函数运算，MySQL查询时该索引会失效，为此，MySQL 8推出了 函数索引。

不妨以一个具体情形为例，设想建立一张名为t2的表格，其中字段c1设置常规索引，字段c2则应用upper函数索引，此函数能够将字符内容转换为大写形式。

建立名为t2的表格，包含字段c1和c2，字段c1的数据类型为varchar，最大长度为10，字段c2的数据类型也为varchar，最大长度同样为10。
为表列c1建立索引idx_c1，应用于表t2，通过该索引可以提升查询效率，方便后续数据检索，同时优化数据访问性能，有助于加快数据读取速度。
为表列t2中的字段c2建立索引，索引名称为idx_c2，并将字段值转换为大写形式进行索引

通过show index from t2\G 看一下：

下面来分别查询一下，看看索引的使用情况：

c1字段仅配置了常规索引，当采用upper(c1)进行检索时，索引并未发挥作用，而c2字段则建立了函数索引upper(c2)，这个索引可以视为一个特殊字段，在查询过程中能够被有效利用。

函数索引的实现原理：

函数索引在MySQL里等于加了个字段，这个字段会依据函数来得出值，当用到函数索引时，就会以这个计算出的字段作为索引，本质上就是多出一个假字段，然后按假字段来检索，这样就能借助索引了。

原子DDL操作

MySQL 8.0 提供了原子化的数据定义语言指令。这项特性叫做原子 DDL。使用原子 DDL 的话，DDL 命令执行时会将数据字典的变更、存储引擎的动作以及二进制日志的记录整合在一起处理。

操作只有两种结果，要么成功执行，相关的变更会保存到数据字典、存储引擎和二进制日志中，要么被撤销执行，即便服务器在处理过程中中断。

设立一个具体情形：资料库里有个表格叫t1，但找不到表格t2，然后实施指令移除t1跟t2。

mysql创建表t1, 字段c1是整数类型, 结束操作
查询成功，没有数据被改动，耗时零点零四秒
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)
mysql> drop table t1,t2;
数据库查询发生异常,目标表名为test.t2,系统无法识别该表,提示名称不存在
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

先前的演示是在 MySQL 8 环境下完成的，由此能够确认这一步骤并未导致表 t1 被移除，那么在更早的版本中，情况又会是怎样呢，接下来将切换到 MySQL 5.7 版本，并执行相同的指令。

mysql> create table t1(c1 int);
查询成功，没有数据被改动，耗时零点零六秒
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)
mysql> drop table t1,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
Empty set (0.00 sec)

尽管系统提示存在关于t2表缺失的错误信息，但事实是t1表这个原本存在的数据表已经被彻底移除了。

在必须实施删除表操作的情况下，应当运用 if 条件判断，以避免因表不存在而引发的错误。

一个原子 DDL 操作内容包括：

支持与表相关的 DDL：

通用表达式（CTE）

Table作为CTE，在MySQL 8里表现为with子句。

通过一个简单的例子了解一下。

索引呈现前十条记录，能够直接 1 并集 2 ，如此类推至 10

select 1 as idx
UNION
select 2 as idx
UNION
select 3 as idx
UNION
select 4 as idx
UNION
select 5 as idx
UNION
select 6 as idx
UNION
select 7 as idx
UNION
select 8 as idx
UNION
select 9 as idx
UNION
select 10 as idx;

通过CTE表达式，可以用递归的方式简化为如下写法：

with recursive cte(idx) as 
(
select 1
UNION
查询结果集中元素的序号，该序号由原始索引值加一得出，数据来源于公共表表达式cte，筛选条件为元素在公共表表达式中的位置索引值<10
)
select * from cte;

比如，设想这样一个情形，要弄清某个职员的上下级关联，能够借助CTE递归来探寻。

这里 /doc//… 有更多比较好的例子，大家可以看一下。

其他

MySQL 8 还有很多比较实用的新特性，比如 ：

，对于查询中的每一行，使用与该行相关的行执行计算。

JSON增强

其余优化措施，诸如死锁检测的开关 ct，在高并发环境下，关闭死锁检测或许能提升运行效率。

此处无需赘述范例，或许作者过于疏懒？官方资料里记载极为完备！