MySQL 8.0 新特性：直方图

查询优化器负责将SQL查询转换为尽可能高效的执行计划，但随着数据环境不断变化，查询优化器可能无法找到最佳的执行计划，导致SQL效率低下。造成这种情况的原因是优化器对查询的数据了解的不够充足，例如：每个表有多少行数据，每列中有多少不同的值，每列的数据分布情况。

因此MySQL8.0.3推出了直方图(histogram)功能，直方图是列的数据分布的近似值，其向优化器提供更多的统计信息。比如字段的个数，每个不同值的百分比，最大/最小值等。MySQL的直方图分为：等宽直方图和等高直方图，MySQL会自动分配使用哪种类型的直方图，无法干预

直方图同时也存在一定的限制条件：

创建和删除直方图

创建语法

创建直方图时能够同时为多个列创建直方图，但必须指定bucket数量，范围在1-1024之间，默认100。对于bucket数量应该综合考虑其有多少不同值、数据的倾斜度、精度等，建议从较低的值开始，不符合再依次增加。

删除语法

直方图信息

MySQL通过字典表column_statistics来保存直方图的定义，每行记录对应一个字段的直方图，已JSON格式保存。

MySQL为employees的first_name字段分配了等高直方图，默认为100个bucket。

当生成直方图时，MySQL会将所有数据都加载到内存中，并在内存中执行所有工作。如果在大表上生成直方图，可能会将几百M的数据读取到内存中的风险，因此我们可以通过参数 hitogram_generation_max_mem_size 来控制生成直方图最大允许的内存量，当指定内存满足不了所有数据集时就会采用采样的方式。

从MySQL8.0.19开始，存储引擎自身提供了存储在表中数据的采样实现，存储引擎不支持时，MySQL使用默认采样需要全表扫描，这样对于大表来说成本太高，采样实现避免了全表扫描提高采样性能。

通过INNODB_METRICS计数器可以监视数据页的采样情况，这需要提前开启计数器

采样率的计算公式为： sampled_page_read/(sampled_pages_read + sampled_pages_skipped)

优化案例

复制一张表出来，源表不添加直方图，新表添加直方图

分别在两张表上查看SQL的执行计划

可以看出Cost值从30214.45降到了18744.56，扫描行数从299822降到了41654，性能有所提升

MySQL 8.0.27 增加了多因素身份认证(MFA)功能，可以为一个用户指定多重的身份校验。为此还引入了新的系统变量 authentication_policy ，用于管理多因素身份认证功能。

我们知道在 MySQL 8.0.27 之前，create user 的时候可以指定一种认证插件，在未明确指定的情况下会取系统变量 default_authentication_plugin的值。default_authentication_plugin 的有效值有3个，分别是 mysql_native_password ，sha256_password ，caching_sha2_password ，这个3个认证插件是内置的、不需要注册步骤的插件。

在 MySQL 8.0.27 中由 authentication_policy 来管理用户的身份认证，先启个 mysql

同时查看下 authentication_policy 和 default_authentication_plugin 的值

我们看到 authentication_policy 的默认值是*,,

第1个元素值是星号（ ），表示可以是任意插件，默认值取 default_authentication_plugin 的值。如果该元素值不是星号（ ），则必须设置为 mysql_native_password ，sha256_password ，caching_sha2_password 中的一个。

第2，3个元素值为空，这两个位置不能设置成内部存储的插件。如果元素值为空，代表插件是可选的。

建个用户看一下，不指定插件名称时，自动使用默认插件 caching_sha2_password

指定插件名称时，会使用到对应的插件

尝试变更一下 authentication_policy 第一个元素的值，设置为 sha256_password

再次创建一个用户，不指定插件的名称

可以看到默认使用的插件是 sha256_password ，说明当 authentication_policy 第一个元素指定插件名称时，default_authentication_plugin 被弃用了。

首先我们恢复 authentication_policy 至默认值

创建一个双重认证的用户。如下创建失败了，因为不可以同时用2种内部存储插件。

那我们来装一个可插拔插件 Socket Peer-Credential

再创建一个双重认证的用户

创建成功，之后用户'wei4'@'localhost'必须提供正确的密码，且同时本地主机的登录用户为 root 时，才会验证通过。

来试一下，以主机 root 用户身份，提供正确的密码 123 ，登录成功。

修改一下，将'wei4'@'localhost'要求的主机登录用户修改为wei4

再次以主机 root 用户身份，提供正确的密码 123 ，登录失败

因此可以认定双重身份认证机制是生效的。MySQL 8.0.27 最多可以对一个用户设置三重的身份认证，这里不再做展示说明。

简单总结下，已有的密码口令身份验证很适合网站或者应用程序的访问，但是在特定的情况下 如网络在线金融交易方面可能还是不够安全。多因素身份认证(MFA)功能的引入，可以在一定程度上提升数据库系统的安全性。

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_authentication_policy

在 MySQL 8.0.23 版本中新添加了一个功能：可以给字段附加不可见属性。对于非指定字段的查询语句默认隐藏不可见字段的内容。该功能可适用于需要给表添加字段并需要对已有的业务系统隐藏时使用以及给表添加主键字段或索引字段时使用。

MySQL 从 8.0.23 版本之前，所有表的字段均为可见字段，在 8.0.23 版本之后，可以给字段添加不可见属性。默认对 select * 等 *** 作隐藏，只有当 sql 语句中指定该字段值时才会显示

官网连接： https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

1、新建一张表，给当中字段赋予不可见属性（INVISIBLE）

2、建表语句以及表结构中均可以查看到不可见字段的字段信息

3、在系统表 INFORMATION_SCHEMA.COLUMNS 中的 EXTRA 字段值中也可以查看到表字段值的不可见属性。

1、每张表必须要有至少一个可见字段。

2、不可见字段允许被定义为主键或创建2级索引，也可以定义自增属性。适合给已有的表添加主键或者索引。

3、使用 DML 语句时，如果涉及到不可见字段的值，需要在sql语句中显示指定该不可见字段。否则会按照默认方式，忽略不可见字段进行 DML 语句的解析和处理。同理 create table as select，insert into select 等，不可见字段需要明文指定，否则按默认不做处理。

4、使用 select...outfile 以及 load data 方式导入导出含不可见字段表时，默认对不可见列不做处理。如果需要导出不可见列的数据或者将数据导入至不可见列中，也需要明文指定字段名。

5、允许给已有的字段附件不可见属性

6、使用 mysqldump 备份时，逻辑备份文件自带不可见字段的建表语句，并明文指定了插入数据的字段值。

7、binlog 会记录 DDL 的不可见列字段属性。当 binlog 为 STATEMENT 模式时，原始 DML 语句会被记录，当 binlog 为 ROW 模式时，不可见列如果有值，也会被记录。

不可见字段这项新功能是对 MySQL 表结构体系的一种补充。不仅允许用户对已使用的表做结构变更并且兼顾业务侧的需要。弥补了业务初期创建错误表结构这样的问题点。一定程度上提升了 MySQL 在使用上的容错率。

---