MySQL 8.0 新特性汇总

文章目录

• • 前言
  • 1. 运维管理
  • • 1.1 可持久化变量
    • 1.2 管理员端口
    • 1.3 资源组
    • 1.4 数据库粒度只读
    • 1.5 show processlist 实现方式
    • 1.6 加速索引创建速度
    • 1.7 控制连接的内存使用量
    • 1.8 克隆插件
    • 1.9 mysqldump 新增参数
    • 1.10 慢日志增强
    • 1.11 快速加列
    • 1.12 InnoDB 隐藏主键
    • 1.13 Redo 配置
    • 1.14 undo 配置
    • 1.15 事务调度 CATS
    • 1.16 自增主键持久化
    • 1.17 在线回收临时表空间
    • 1.18 自适应参数
  • 2. 开发相关
  • • 2.1 默认字符集
    • 2.2 GROUP BY ASC/DESC 语法
    • 2.3 公用表达式
    • 2.4 窗口函数
    • 2.5 check 约束
    • 2.6 隐藏列
    • 2.7 不可见索引
    • 2.8 降序索引
    • 2.9 函数索引
    • 2.10 VALUES 语法
    • 2.11 NOWAIT 和 SKIP LOCKED
    • 2.12 分区表支持
  • 3. 复制相关
  • • 3.1 Binlog 中包含元数据
    • 3.2 Binlog 记录事务提交时间戳
    • 3.3 Binlog 过期时间
    • 3.4 ReplicaSet 复制方案
  • 4. 安全相关
  • • 4.1 角色管理
    • 4.2 caching_sha2_password
    • 4.3 print_identified_with_as_hex
    • 4.4 更细粒度的权限控制
    • 4.5 生成随机密码
    • 4.6 双重密码支持
    • 4.7 密码错误锁定用户
    • 4.8 账号备注信息
  • 5. 优化器相关
  • • 5.1 hash join
    • 5.2 直方图
  • 6. 推荐升级流程
  • 总结

前言

MySQL 5.7 在 2023 年 10 月 31 日起，已经终止软件生命周期了，意味着 MySQL 官方将不再提供对 MySQL 5.7 版本的技术支持和更新。8.0 版本成为官方长期支持版本，提供了很多新特性，本文将详细解读 MySQL 8.0 版本的新特性，为 MySQL 版本升级作参考和指导。

1. 运维管理

1.1 可持久化变量

MySQL 5.7 版本，使用 SET 命令修改参数后，需要再将参数写入到配置文件中，否则重启后又恢复默认配置。MySQL 8.0 提供SET PERSIST 语法可以将参数持久化到配置文件中，用户无需再对配置文件进行编辑，是一个对云厂商比较友好的特性。

# 持久化变量，同时修改变量内存值
SET PERSIST max_connections = 1000;# 只持久化修改，不修改内存中变量值，适用于修改只读参数
SET PERSIST_ONLY back_log = 100;# 清空 mysqld-auto.cnf 中的变量，持久化修改参数的原理是 MySQL 维护了一个配置文件，即 mysqld-auto.cnf
RESET PERSIST;# 删除 mysqld-auto.cnf 中的特定变量，如果变量不存在会报错
RESET PERSIST system_var_name;# 删除 mysqld-auto.cnf 中的特定变量，不存在则不会报错。
RESET PERSIST IF EXISTS system_var_name;

当使用 SET PERSIST 命令时，变量更改将更新到数据目录中的 mysqld-auto.cnf 选项文件。mysqld-auto.cnf 是仅在第一次执行 PERSIST 或 PERSIST_ONLY 语句时创建的 JSON 格式文件。内容如下：

需要的授权：

GRANT SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, SHUTDOWN on *.* to 'admin'@'localhost';

参考资料：Persisted System Variables

1.2 管理员端口

MySQL 8.0 提供了管理员的连接地址和端口，默认的管理员端口是 33062，相当于是为管理员连接提供了专用通道，且没有连接数限制，这样即使 MySQL 连接数满了，也可以连接上去修改 max_connections 的大小。

[(none)]>show variables like '%admin%';
+---------------------------------+-----------------+
| Variable_name                   | Value           |
+---------------------------------+-----------------+
| admin_address                   |                 |
| admin_port                      | 33062           |
+---------------------------------+-----------------+

使用管理员端口的用户需要拥有 SERVICE_CONNECTION_ADMIN 权限的用户连接。

参考资料：Administrative Connection Management

1.3 资源组

MySQL 是单进程多线程的程序，在 8.0 之前所有的线程优先级都是相同的，并且所有的线程资源都是共享的。8.0 之后推出 RESOURCE GROUP 特性，DBA 可以通过资源组的方式修改线程优先级以及所使用的资源，目前仅支持 CPU 资源。

业务上经常会有一些跑批操作，这些跑批 SQL 往往较复杂且涉及数据量大，执行起来非常消耗资源，往往是业务低峰夜间执行，避免影响其它线程运行避免 CPU 跑满，堵塞其它线程，导致请求进不去。有了 RESOURCE GROUP 特性，我们可以创建一个资源组，让跑批任务限制在固定的一个或多个 CPU 核上，避免影响其它线程。

由于文章篇幅，如果想使用或者详细了解该特性的朋友请参考下方文档。

详细解读：8.0 新特性 - RESOURCE GROUP

1.4 数据库粒度只读

MySQL 8.0 可设置数据库粒度的只读模式，禁止所有更新操作，适用于数据迁移场景。

# 开启只读模式
ALTER DATABASE db_name READ ONLY = 1;
# 关闭只读模式
ALTER DATABASE db_name READ ONLY = 0;

1.5 show processlist 实现方式

show processlist 默认是从全局的线程管理器中获取线程信息，这种实现方式会持有全局互斥锁，对数据库的性能有一定的影响，官方推荐使用 performance_schema.processlist 的方式查询，这种方式不会持有全局锁。

MySQL 8.0.22 中，引入 performance_schema_show_processlist 参数，设置为 ON 则表示使用 performance_schema.processlist 的方式实现 show processlist 默认为 OFF。

参考资料：The processlist Table

1.6 加速索引创建速度

MySQL 8.0.27 中，引入 innodb_ddl_threads 参数。该参数用于 MySQL 创建二级索引时，在排序和构建阶段，使用线程的个数，一定程度上可以加快索引的创建速度，默认为 4 个。

大表创建索引时，可以给流量较低的备库开启该参数，从而减小主备延迟。

详细解读：8.0 新特性 - innodb_ddl_threads

1.7 控制连接的内存使用量

MySQL 8.0.28 开始，引入 connection_memory_limit 变量，可以限制单个连接内存的使用量，如果超过最大值连接会被断开。

1.8 克隆插件

克隆插件（Clone Plugin）是 MySQL 8.0.17 引入的一个重大特性，可以从本地或者远程克隆数据。

如果在 8.0.17 之前想要给 MySQL 复制拓扑中添加一个新节点，只支持 Binlog 一种恢复方式，如果新节点所需要的 Binlog 在集群中不存在，就只能先借助备份工具进行全量备份恢复，再配置增量同步。这种方式虽然能达到添加新节点的目的，但总归是需要借助外部工具，相对来说是有一定的使用门槛和工作量。

Clone 插件支持本地克隆和远程克隆，可以很方便的帮助我们添加一个新的节点，也可以作为 Innodb 引擎的物理备份工具。

克隆插件之前有文章详细介绍过，感兴趣的可以阅读。

推荐阅读：MySQL 8.0 Clone Plugin 详解

1.9 mysqldump 新增参数

在 MySQL 8.0.30 中，mysqldump 新增了 mysqld-long-query-time 选项，允许自定义 long_query_time 的会话值。这样可以避免将逻辑备份相关的语句记录在慢日志中。

参考资料：mysqldump_mysqld-long-query-time

1.10 慢日志增强

在 MySQL 8.0.14 中，引入 log_slow_extra 参数，开启后在慢日志中会记录更加详细的信息，例如语句异常终止的信号、语句返回数据的字节大小、排序的行数等等。

该参数默认是关闭的，开启后记录的信息更多，意味着慢日志将膨胀的更快，额外的字段信息可参考下方资料。

参考资料：log_slow_extra

1.11 快速加列

从 MySQL 8.0.12 开始，OnlineDDL 开始支持 INSTANT 算法，可以使用该算法进行加字段操作，只需修改表的元数据信息，操作瞬间就可以完成。不过在 8.0.29 之前，列只能加在表的最后的位置。从 8.0.29 开始移除了该限制，快速加列支持加到表的任何位置，并且删除列也支持 INSTANT 算法。

通过只修改数据字典的方法来实现大表快速加列，避免之前加列操作必须做的数据拷贝，从而大幅缩小大表加列所需的时间，减少对系统的影响。

推荐阅读：8.0 新特性 - Instant Add Column

1.12 InnoDB 隐藏主键

MySQL Innodb 引擎采用的是 IOT（索引组织表）存储方式，主键的重要性就不言而喻。在早期版本用户如果没有显式指定主键，会自动生成隐藏主键 row_id 来组织 B+ 树，隐藏主键 row_id 只会作用于 MVCC、Redo 和 Undo 等内部机制，无法在复制模块中使用。一些大数据组件生成的表结构，往往都没有主键设计，会出现全表扫描回放问题，带来非常大的主备延迟。

而且 MySQL 一些周边软件 gh-ost、DTS 服务等，都有依赖主键设计，没有主键会降低数据库的可维护性。

MySQL 8.0.30 引入 sql_generate_invisible_primary_key 参数，可为没有显式设置主键的表创建一个隐式的主键。该特性之前的文章中有详细解读。

推荐阅读：8.0 新特性 - Generated Invisible Primary Key

1.13 Redo 配置

在 MySQL 8.0.30（也包含 5.7 版本） 版本 Redo 的配置相关的参数主要有下面两个：

• innodb_log_files_in_group：Redo 文件的个数，默认为 2。

• innodb_log_file_size：Redo 在磁盘上，单个文件的大小。

  du -sh ib_logfile*
  128M ib_logfile0
  128M ib_logfile1

  cat /etc/my.cnf | grep innodb_log_file_size
  innodb_log_file_size = 128M

这两个参数不支持在线修改，必须重启生效。

MySQL 8.0.30 版本发布后提供新参数 innodb_redo_log_capacity 代替之前两个参数，不过原来两个参数并没有取消，会自动计算转换为 innodb_redo_log_capacity 抛出警告：

[Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=1073741824. Please use innodb_redo_log_capacity instead.

支持在线修改：

set global innodb_redo_log_capacity=1073741824;

推荐阅读：8.0 新特性 - innodb_redo_log_capacity

1.14 undo 配置

MySQL 8.0 版本默认开启 innodb_undo_tablespaces 参数，将 undo 日志从系统表空间 ibdata 文件中独立出来，这样就可以动态调整 undo 大小，便于收缩 undo 空间，否则必须重新初始化实例。

除了默认会独立出 undo 空间外，还提供了 SQL 语句可用于管理 undo 表空间。

参考资料：Undo Tablespaces

1.15 事务调度 CATS

MySQL 8.0 中的 InnoDB 引擎使用了 Contention-Aware Transaction Scheduling (CATS) 竞争感知事务调度算法，可以提升高并发场景下数据库的吞吐，提升数据库整体性能。

当多个事务同时获取一把锁，那么哪个事务先获得这把锁？在 MySQL 8.0 版本之前，使用的是 FIFO 先进先出算法，该算法会将锁优先分配给最先进入等待队列中的事务。在 MySQL 8.0 中，则会计算每个事务堵塞的事务数，最后将锁分配给堵塞事务最多的事务。

推荐阅读：Transaction Scheduling

1.16 自增主键持久化

MySQL 5.7 版本 auto_increment 是存储在内存中的，这就导致每次重启 MySQL 都会重新计算该值，计算逻辑是取该字段的 MAX VALUE 请看下方 case：

-- 创建一张测试表，id 为自增主键
create table t1(id bigint auto_increment primary key,c1 varchar(10) not null,c2 varchar(10) not null
);

插入 8 行记录：

insert into t1(c1, c2) value ('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b');

此时 AUTO_INCREMENT 的值为 9，下一条写入会被分配自增 ID 为 9：

select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME = 't1';

TABLE_NAME

AUTO_INCREMENT

t1

9

然后删除后 4 条记录：

delete from t1 where id > 4;

重启数据库：

>$ service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL... SUCCESS! 

查询 t1 表的自增 ID：

select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME = 't1';

TABLE_NAME

AUTO_INCREMENT

t1

5

可以看到 MySQL 在重启之后自增列的值被重置了。这个现象被称之为 BUG 主要是在现在的互联网业务中，支撑业务数据的不仅仅只有 MySQL，还可能会有 Redis，RabbitMQ 等缓存和消息队列或者是单独的 MySQL 日志归档库，自增列可能会被用来作为关联各个存储之间的“逻辑外键”，当 MySQL 重启之后，新写入的数据可能会用到已经被删除的值，导致数据库中的数据和外部系统之间的数据出现错误的关联。另外一种问题场景就是 MySQL 自身各个表之间有外键关系，但是没有建立外键约束，也会遇到类似的问题。

该 BUG 已在 MySQL 8.0 版本修复，推荐阅读。
Auto-Increment Counter Persistence in MySQL 8: Comparing the Evolution From MySQL 5.7

1.17 在线回收临时表空间

在 MySQL 5.7 版本中，用户创建的临时表和磁盘临时表会存储在全局的临时表空间 ibtmp1 中，会话结束后并不会释放临时空间，只会打上删除的标记可复用，如果要释放临时表空间需要重启实例。

MySQL 8.0 新增 innodb_temp_tablespaces_dir 参数，默认在数据目录中 #innodb_tmp 命名的一个临时表空间池，一个会话最多会分配两个临时表空间，分别用来存储用户创建的临时表和优化器内部创建的临时表。当会话连接断开时，会截断这两个临时表空间。

参考资料： Temporary Tablespaces

1.18 自适应参数

MySQL 8.0.14 推出了 innodb_dedicated_server 参数，开启该参数后 MySQL 会根据服务器的配置自适应一些关键参数。比如 buffer pool 的大小和 redo log 的大小，是一个对云厂商比较友好的特性。如果你的 MySQL 是部署在一个专属的数据库服务器中，可以开启该参数。

参考资料：innodb_dedicated_server

2. 开发相关

2.1 默认字符集

MySQL 8.0 默认字符由 latin1 调整为 utf8mb4，值得注意的是，在 MySQL 8.0 中，utf8mb4 的默认排序规则是 utf8mb4_0900_ai_ci，在 MySQL 5.7 中则是 utf8mb4_general_ci，也就是如下两个参数的默认值。

[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_0900_ai_ci

从 MySQL 5.7 升级到 8.0 的话，可以将参数设置为 5.7 相同的值，数据迁移完成后可以改回，或者一直沿用 5.7 的参数。

参考资料：MySQL 8.0 Configuration Changes

2.2 GROUP BY ASC/DESC 语法

MySQL 8.0 版本，不支持 GROUP BY ASC/DESC 语法，如果需要对分组的列进行排序，需要显式指定排序列。

-- MySQL 5.7
select `name`,count(*) from table_name GROUP BY `name` desc;-- MySQL 8.0 
select `name`,count(*) from table_name GROUP BY `name` ORDER BY `name` desc;

推荐阅读：MySQL 8.0 SQL Change

2.3 公用表达式

公用表达式 简称：WITH (Common Table Expressions)，是 MySQL 8.0 新增的语法，可以定义一个临时的结果集合，全局可用。某些特殊场景下使用公用表达式可以让 SQL 更加简洁，同时也提升了 SQL 的可读性。

WITH cte (col1, col2) AS
(SELECT 1, 2UNION ALLSELECT 3, 4
)
SELECT col1, col2 FROM cte;-- 输出：
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+

参考资料：Common Table Expressions

2.4 窗口函数

窗口函数（Window Function）是一个提升 MySQL 数分能力的特性，可针对一组进行计算，并为每行返回一个结果，这一点和聚合函数不同。聚合函数只能为每个分组返回一个结果，窗口函数中的 over 子句定义了要计算行的行窗口。

sales 为销售表，有每个城市和商品的利润：

mysql> SELECT * FROM sales ORDER BY country, year, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2001 | Finland | Phone      |     10 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2001 | USA     | Computer   |   1200 |
| 2001 | USA     | TV         |    150 |
| 2001 | USA     | TV         |    100 |
+------+---------+------------+--------+

如果想计算总利润，可以使用 sum 函数，如果想要知道每个国家的利润，可以使用 group by 分组再使用 sum 函数。

mysql> SELECT SUM(profit) AS total_profitFROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profitFROM salesGROUP BY countryORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

聚合函数则可以使用聚会函数为每一行返回一个结果，如下面的 case 可以把总利润和国家利润计算出来放在每条记录的后面，便于对比。

mysql> SELECTyear, country, product, profit,SUM(profit) OVER() AS total_profit,SUM(profit) OVER(PARTITION BY country) AS country_profitFROM salesORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

参考资料：Window Function Concepts and Syntax

2.5 check 约束

MySQL 8.0 支持 check 约束，请看下方示例，在 age 字段可以创建一个约束，年龄必须小于 150 岁。

CREATE TABLE user_test(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键',name VARCHAR(10) not null COMMENT '姓名',age int UNSIGNED CHECK(age<150) COMMENT '年龄'
);

插入 200 岁的张三会返回异常。

insert into user_test values (1, '张三', 200);
-- 异常
ERROR 3819 (HY000): Check constraint 'user_test_chk_1' is violated.

参考资料：CHECK Constraints

2.6 隐藏列

隐藏列是 MySQL 8.0.23 引入的新特性，可以设置列的可见性，除非显式指定隐藏属性的字段，否则 select * from 不会显示。

CREATE TABLE t1 (i INT,j DATE default '2024-01-01' INVISIBLE
) ENGINE = InnoDB;

写入三条数据：

insert into t1 values (1),(2),(3);>select * from t1;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+>select i, j from t1;
+------+------------+
| i    | j          |
+------+------------+
|    1 | 2024-01-01 |
|    2 | 2024-01-01 |
|    3 | 2024-01-01 |
+------+------------+

参考资料：Invisible Columns

2.7 不可见索引

可以修改索引的可见性，如果调整索引不可见，那么优化器会默认忽略该索引。该特性在索引维护中非常有用，大表中维护一个索引的代价还是比较大的。

讲一个笔者亲身经历过的故障吧，研发人员要删除一张 20G 大表中的一个索引，执行完后数据库 CPU 立马飙升，原来还有 SQL 依赖刚才删除的索引，那索引被删掉后这业务的 SQL 就全部全表扫描了，数据库 CPU、IO 立马打满了。此时加索引也很难加上，必须得停掉相关业务的 SQL 重新添加索引，影响范围很大。

如果有不可见索引，那么删除一个索引的流程就会安全很多，可以先调整索引的可见性，观测一段时间，期间如果有任何问题则打开索引即可，毕竟大表加一个索引也需要时间，修改索引可见性几乎瞬间完成。

-- 查询元数据，哪些索引不可见
select table_schema, table_name, index_name, column_name, is_visible
from information_schema.statistics
where is_visible = 'no';-- 修改索引可见
alter table xx alter index idx_xx visible;
-- 修改索引不可见
alter table xx alter index idx_xx invisible;
-- 创建一个不可见索引
alter table xx add index idx_xx(column_name) invisible;

推荐阅读：Invisible Indexes

2.8 降序索引

MySQL 8.0 之前的索引排序规则之前只允许 ASC 存储，创建时指定 DESC 也会被忽略，现在创建索引时可以指定索引的排序方向，便于应对一些排序场景，避免文件排序。

推荐阅读：Descending Indexes

2.9 函数索引

MySQL 8.0 提供了函数索引，可应对使用函数导致索引失效的场景，详细可参考下方案例。

推荐阅读：MySQL 函数导致索引失效应对策略

2.10 VALUES 语法

values 是 MySQL 8.0.19 引入的一个 DML 语法，属于一个种表值构造语法。

mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8) ORDER BY column_1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |       -2 |        3 |
|        4 |        6 |        8 |
|        5 |        7 |        9 |
+----------+----------+----------+
3 rows in set (0.00 sec)

推荐阅读：VALUES Statement

2.11 NOWAIT 和 SKIP LOCKED

MySQL 8.0 版本在 SELECT…FOR SHARE 和 SELECT…FOR UPDATE 语句中引入了 NOWAIT 和 SKIP LOCKED 选项，用来解决电商场景中的热点行问题。

CREATE TABLE `test_semi` (`a` int NOT NULL,`b` int DEFAULT NULL,`c` int DEFAULT NULL,PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;insert into test_semi values(10, 1, 0),(11, 2, 0),(12,1,0),(13,2,0),(14,1,0);

使用 Session 1 开启一个事务：

begin;
select * from test_semi where b = 1 for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |    1 |    0 |
| 12 |    1 |    0 |
| 14 |    1 |    0 |
+----+------+------+

Session 2 尝试查询全表：

-- 堵塞：
select * from test_semi for update;-- 跳过了锁定的行
select * from test_semi for update  SKIP LOCKED;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 11 |    2 |    0 |
| 13 |    2 |    0 |
+----+------+------+-- 需要加的锁，被其他事务持有，则直接返回异常，不会等待锁超时
select * from test_semi for update NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

推荐阅读：Locking Reads

2.12 分区表支持

通用的分区接口已从代码中移除，在 MySQL 8.0 中，如果要使用分区表，只能使用 InnoDB 存储引擎。

3. 复制相关

3.1 Binlog 中包含元数据

MySQL 5.7 中 Binlog 是不记录元数据的，使用 mysqlbinlog 解析出来的 binlog 文本字段是用 @1、@2 按照顺序排列的，没有字段名称。MysQL 8.0.1 版本提供了 binlog_row_metadata 参数，设置为 full 的时候会在 Binlog Table_map event 中记录字段信息。

使用 mysqlbinlog 解析时需要指定 --print-table-metadat 选项。

mysqlbinlog -vv --base64-output=decode-rows --print-table-metadata mysql-bin.000003 #240729 11:12:54 server id 553306  end_log_pos 431 CRC32 0x5478e143 	Table_map: `test`.`test_semi` mapped to number 146
# has_generated_invisible_primary_key=0
# Columns(`a` INT NOT NULL,
#         `b` INT,
#         `c` INT)
# Primary Key(a)
# at 431
#240729 11:12:54 server id 553306  end_log_pos 597 CRC32 0x05b1fd6a 	Update_rows: table id 146 flags: STMT_END_F
### UPDATE `test`.`test_semi`
### WHERE
###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3=0 /* INT meta=0 nullable=1 is_null=0 */

3.2 Binlog 记录事务提交时间戳

如果想从 Binlog 查一些问题，比如业务 bug 造成的数据错乱，需要秒级以内的粒度来分析问题，MySQL 8.0 的 Binlog 中会记录事务提交的时间戳。

-- 源库事务提交的时间戳
# original_commit_timestamp=1722222774134690 (2024-07-29 11:12:54.134690 CST)
-- 当前节点事务提交的时间戳
# immediate_commit_timestamp=1722222774134690 (2024-07-29 11:12:54.134690 CST)

还可以提升 MySQL 复制关系的可观测性，详细参考下文。

推荐阅读：MySQL 8 and Replication Observability

3.3 Binlog 过期时间

在 MySQL 8.0 之前，Binlog 过期时间由 exprice_logs_days 参数控制，单位是天。在 MySQL 8.0 中，引入 binlog_expire_logs_seconds 参数，可设置秒级别的过期时间，默认是 2592000 秒，既 30 天。

如果 expire_logs_days 和 binlog_expire_logs_seconds 参数都设置了非 0 的值，那么 binlog_expire_logs_seconds 的优先级更高。

另外，MySQL 8.0.29 新增 binlog_expire_logs_auto_purge 参数，用来禁用 Binlog 自动清理。在此之前只能通过将 binlog_expire_logs_seconds 和 exprice_logs_days 设置为 0 来实现。

参考资料：binlog_expire_logs_seconds

3.4 ReplicaSet 复制方案

MySQL Innodb ReplicaSet 是 MySQL 团队在 8.0 版本上添加的功能，用来帮助用户快速部署和管理主从复制，在数据库层仍然使用的是主从复制技术。

• ReplicaSet 主要包含三个组件：MySQL Router、MySQL Server 以及 MySQL Shell 高级客户端。
• MySQL Shell 负责管理 ReplicaSet 包括部署、切换、节点加入等，都可以通过内置 AdminAPI 自动化完成。
• MySQL Router 是一款轻量级中间件，可在应用程序和 ReplicaSet 之间提供透明路由和读写分离功能。
  

推荐阅读：MySQL Innodb ReplicaSet

4. 安全相关

4.1 角色管理

MySQL Role 就是 8.0 新增加关于权限的功能 Role 角色 可以理解为是一个权限的集合，比如在 SQLserver 中也有相关 Role 的功能，下图是 SQLserver 的角色管理界面 与 SQLserver 不同的是 MySQL 目前没有系统默认系统的角色，需要我们自行创建。

如何使用 role 本文就不展开介绍了，如果一个实例账号比较多的情况下，可通过 role 简化 DBA 的日常管理操作。另外一个账号可以属于多个角色，并且支持在 session 中切换，我觉得这是一个非常好的特性，有什么用呢？比如 DBA 日常排查问题的时候只需要只读权限即可，在变更的时候才需要高权限，那么 DBA 可以在两个角色中切换，可以有效避免误操作。

推荐阅读：Using Roles

4.2 caching_sha2_password

MySQL 8.0 版本默认的密码插件由 mysql_native_password 更改为 caching_sha2_password 插件。这项改动有什么影响？遇到问题如何修改呢？可参考下方文档。

推荐阅读：Caching SHA-2 Pluggable Authentication

4.3 print_identified_with_as_hex

该特性是与 caching_sha2_password 配套的特性，用于账号迁移场景。

set session print_identified_with_as_hex = on;show create user t1@'%';
CREATE USER `t1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x2441243030352416564A3E2B11497D1D225702020B6A635920160378523571616436544E5A594B7273357142392E434D79796D424162485A365536317A63425349372E446A42 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;

参考资料：SHOW CREATE USER Statement

4.4 更细粒度的权限控制

MySQL 8.0 拥有更细粒度的权限控制，如果授予 super 权限会提示 warning。

通过 show privileges 命令查询权限选项 5.7 有 31 个，而 8.0 有 69 个权限选项，另外授权相关的表引擎调整为了 InnoDB 引擎。

show privileges;

参考资料：Privileges Provided by MySQL

4.5 生成随机密码

MySQL 8.0 不能通过 grant 语法创建用户，必须先 create user 然后再授权。在创建用户过程中，可以让 MySQL 生成随机密码。

create user user1@'%' identified by random password;-- 输出：
+-------+------+----------------------+-------------+
| user  | host | generated password   | auth_factor |
+-------+------+----------------------+-------------+
| user1 | %    | K[BnRT0qxSWd8zG]J7A0 |           1 |
+-------+------+----------------------+-------------+

修改密码的时候也可以指定随机密码：

alter user user1@'%' identified by random password;-- 输出：
+-------+------+----------------------+-------------+
| user  | host | generated password   | auth_factor |
+-------+------+----------------------+-------------+
| user1 | %    | TCiEI%8<}cN8&_eOY.m( |           1 |
+-------+------+----------------------+-------------+set password for user1@'%' to random;-- 输出：
+-------+------+----------------------+-------------+
| user  | host | generated password   | auth_factor |
+-------+------+----------------------+-------------+
| user1 | %    | YpS/oLgUY_@G>jO5/lE, |           1 |
+-------+------+----------------------+-------------+

随机密码的长度由参数 generated_random_password_length 来决定，默认为 20 个字符。

参考资料：Random Password Generation

4.6 双重密码支持

MySQL 8.0 支持一个用户同时设置两个密码，新密码和旧密码都可以用于登陆数据库。

-- 给 user1 添加一个新的随机密码
alter user user1@'%' identified by random password retain current password;-- 输出：
+-------+------+----------------------+-------------+
| user  | host | generated password   | auth_factor |
+-------+------+----------------------+-------------+
| user1 | %    | E8wLo{>0]so],XImeeEH |           1 |
+-------+------+----------------------+-------------+-- 删除旧密码
alter user user1@'%' discard old password;

可用于业务连接账号密码修改过渡阶段。

参考资料：Dual Password Support

4.7 密码错误锁定用户

MySQL 8.0.19 开始，可设置用户密码错误次数，失败后锁定用户。

-- FAILED_LOGIN_ATTEMPTS：密码失败次数
-- PASSWORD_LOCK_TIME：锁定时间，单位是天 设置为 UNBOUNDED 表示永久锁定alter user user1@'%' FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 3;

密码输入失败 4 次，锁定 3 天，抛出如下异常。

ERROR 3955 (HY000): Access denied for user 'user1'@'localhost'. Account is blocked for 3 day(s) (3 day(s) remaining) due to 4 consecutive failed logins.

对于锁定的账号，在以下情况下会解锁：

• 数据库重启。
• 执行 FLUSH PRIVILEGES。
• 锁定时间结束。
• 通过 alter user 重置 FAILED_LOGIN_ATTEMPTS 或 PASSWORD_LOCK_TIME。
• 通过 alter user user1@‘%’ account unlock; 解锁。

参考资料：Failed-Login Tracking and Temporary Account Locking

4.8 账号备注信息

创建用户时，可以通过 COMMENT/ATTRIBUTE 子句对账号添加备注信息。设置的备注信息可以使用下面的方式查看。

-- 添加备注信息
alter user 'user1'@'%' ATTRIBUTE '{"username":"张三", "empno":"10001", "job":"DBA"}';-- 查询
select * from user_attributes where user = 'user1';-- 输出：
+-------+------+-----------------------------------------------------------------------------------+
| USER  | HOST | ATTRIBUTE                                                                         |
+-------+------+-----------------------------------------------------------------------------------+
| user1 | %    | {"job": "DBA", "empno": "10001", "comment": "测试用户", "username": "张三"}       |
+-------+------+-----------------------------------------------------------------------------------+

5. 优化器相关

5.1 hash join

MySQL 8.0 引入了 Hash join，对于 BNL Join，会基于驱动表的数据在 join buffer 中构建一个Hashmap。如果驱动表的数据可以一次性全部加载到 join buffer 中，则只需要对被驱动表进行一次全表扫描，就能完成 join。如果驱动表的数据无法一次性全部加载到 join buffer 中，MySQL 会根据关联条件对驱动表和被驱动表进行分片，一次 join 一对分片，这种情况下，一般只需要扫描两次数据，就能完成 join 操作。

MySQL 8.0.20 之前 hash join 只适用于等值连接和笛卡尔积。

select * from a join b on a.id = b.id;
select * from a join b;

MySQL 8.0.20 开始，hash join 支持非等值连接、半连接、反连接、左外连接、右外连接。

-- 非等值连接
select * from a join b where a.a1 > b.b1;
-- 半连接
select * from a where a.a1 in (select b1 from b);
-- 反连接
select * from a where not exists (select * from b where a.a1 = b.b1)
-- 左外连接
select * from a left join b on a.id = b.id;
-- 右外连接
select * from a right join b on a.id = b.id;

推荐阅读：MySQL优化器特性（四）表关联之BNL（Block Nested Loop）和Hash Join

5.2 直方图

直方图（Histogram）是数据库提供的一种（索引之外的）基础统计信息，用于描述列上的数据分布情况。它最典型的场景是估算查询谓词的选择性，以便选择合适的执行计划。

例如下方的 SQL 需要先通过 where 条件过滤数据后选择结果集小的表作为驱动表，这时，优化器需要知道两表的总行数，也需要知道每个表符合条件的行数，也就是谓词的选取率。如果该字段没有二级索引和直方图的话，MySQL 很难准确的选到小表，因为无法知道表中的数据分布，在多表关联的场景执行计划表关联的顺序对 SQL 执行效率影响很大。

select * 
from customer join orders on customer.cust_id = orders.customer_id 
where customer.balance < 1000 and orders.total > 10000

如果为两张表创建直方图，表中的数据会被排序分为 100 个 bucket，并记录每个桶中数据的最大值、最小值、出现频次占比等信息。这样的话就可以影响优化器，在关联过程中，选择合适的小表作为驱动表。

该特性适合多表关联由于数据分布不均匀选错索引的场景，可以创建直方图引导优化器选择高效的访问路径。毕竟索引的维护是有代价的，直方图只在创建和更新时才会有开销。

参考资料：Optimizer Statistics

6. 推荐升级流程

1. 基于当前业务的数据备份恢复一台实例，将这台实例升级为 8.0。
2. 在新实例上进行充分的测试（回归测试/性能测试）通过业务测试识别出潜在问题。
3. 将测试发现的问题进行改造。
4. MySQL 5.7 和 MySQL 8.0 配置双向同步（用 read_only 保持只有一边写入）便于割接后遇到问题回滚。
5. 割接切换到 MySQL 8.0。
6. 观测 7 天，无异常后释放双向同步和 5.7 数据库资源。

总结

MySQL 8.0 相比 5.7 版本更新很多新特性，很多改变没有直接删除，便于用户过渡，兼容性还是比较高的。不过到 8.1、8.2 的创新版本，老的 5.7 的参数和语法就已经删除。

由于 5.7 已经结束软件生命周期，意味着 Oracle 官方将不再为该版本提供更新和漏洞修复，虽然数据库厂商保证原则性上 MySQL 是安全的，保不齐有时会发现极其罕见的重大安全漏洞。如果用户坚持使用 5.7 版本将会面临安全风险增大、生态系统衰退、运维成本增高等问题。建议还在使用 5.7 的用户开始筹备规划未来的升级计划，从而为业务提供持续可靠的数据库服务。