MySQL 8.0 新特性汇总

前言

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 语法可以将参数持久化到配置文件中，用户无需再对配置文件进行编辑，是一个对云厂商比较友好的特性。

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

复制代码

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

必要的授权：

1. 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 的大小。

1. [(none)]>show variables like '%admin%';
2. +---------------------------------+-----------------+
3. | Variable_name                   | Value           |
4. +---------------------------------+-----------------+
5. | admin_address                   |                 |
6. | admin_port                      | 33062           |
7. +---------------------------------+-----------------+

复制代码

使用管理员端口的用户必要拥有 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 可设置数据库粒度的只读模式，禁止所有更新操作，实用于数据迁移场景。

1. # 开启只读模式
2. ALTER DATABASE db_name READ ONLY = 1;
3. # 关闭只读模式
4. 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 在磁盘上，单个文件的大小。
  

1. > du -sh ib_logfile*
2. 128M        ib_logfile0
3. 128M        ib_logfile1
5. > cat /etc/my.cnf | grep innodb_log_file_size
6. innodb_log_file_size = 128M

复制代码

这两个参数不支持在线修改，必须重启生效。
MySQL 8.0.30 版本发布后提供新参数 innodb_redo_log_capacity 代替之前两个参数，不过原来两个参数并没有取消，会主动计算转换为 innodb_redo_log_capacity 抛出警告：

1. [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.

复制代码

支持在线修改：

1. 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：

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

复制代码

插入 8 行记载：

1. 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：

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

复制代码

TABLE_NAMEAUTO_INCREMENTt19 然后删除后 4 条记载：

1. delete from t1 where id > 4;

复制代码

重启数据库：

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

复制代码

查询 t1 表的自增 ID：

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

复制代码

TABLE_NAMEAUTO_INCREMENTt15 可以看到 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，也就是如下两个参数的默认值。

1. [mysqld]
2. character_set_server=utf8mb4
3. 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 语法，假如必要对分组的列进行排序，必要显式指定排序列。

1. -- MySQL 5.7
2. select `name`,count(*) from table_name GROUP BY `name` desc;
4. -- MySQL 8.0
5. 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 的可读性。

1. WITH cte (col1, col2) AS
2. (
3.   SELECT 1, 2
4.   UNION ALL
5.   SELECT 3, 4
6. )
7. SELECT col1, col2 FROM cte;
9. -- 输出：
10. +------+------+
11. | col1 | col2 |
12. +------+------+
13. |    1 |    2 |
14. |    3 |    4 |
15. +------+------+

复制代码

  参考资料：Common Table Expressions
  2.4 窗口函数

窗口函数（Window Function）是一个提升 MySQL 数分本领的特性，可针对一组进行计算，并为每行返回一个效果，这一点和聚合函数不同。聚合函数只能为每个分组返回一个效果，窗口函数中的 over 子句界说了要计算行的行窗口。
sales 为销售表，有每个都会和商品的利润：

1. mysql> SELECT * FROM sales ORDER BY country, year, product;
2. +------+---------+------------+--------+
3. | year | country | product    | profit |
4. +------+---------+------------+--------+
5. | 2000 | Finland | Computer   |   1500 |
6. | 2000 | Finland | Phone      |    100 |
7. | 2001 | Finland | Phone      |     10 |
8. | 2000 | India   | Calculator |     75 |
9. | 2000 | India   | Calculator |     75 |
10. | 2000 | India   | Computer   |   1200 |
11. | 2000 | USA     | Calculator |     75 |
12. | 2000 | USA     | Computer   |   1500 |
13. | 2001 | USA     | Calculator |     50 |
14. | 2001 | USA     | Computer   |   1500 |
15. | 2001 | USA     | Computer   |   1200 |
16. | 2001 | USA     | TV         |    150 |
17. | 2001 | USA     | TV         |    100 |
18. +------+---------+------------+--------+

复制代码

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

1. mysql> SELECT SUM(profit) AS total_profit
2.        FROM sales;
3. +--------------+
4. | total_profit |
5. +--------------+
6. |         7535 |
7. +--------------+
8. mysql> SELECT country, SUM(profit) AS country_profit
9.        FROM sales
10.        GROUP BY country
11.        ORDER BY country;
12. +---------+----------------+
13. | country | country_profit |
14. +---------+----------------+
15. | Finland |           1610 |
16. | India   |           1350 |
17. | USA     |           4575 |
18. +---------+----------------+

复制代码

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

1. mysql> SELECT
2.          year, country, product, profit,
3.          SUM(profit) OVER() AS total_profit,
4.          SUM(profit) OVER(PARTITION BY country) AS country_profit
5.        FROM sales
6.        ORDER BY country, year, product, profit;
7. +------+---------+------------+--------+--------------+----------------+
8. | year | country | product    | profit | total_profit | country_profit |
9. +------+---------+------------+--------+--------------+----------------+
10. | 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
11. | 2000 | Finland | Phone      |    100 |         7535 |           1610 |
12. | 2001 | Finland | Phone      |     10 |         7535 |           1610 |
13. | 2000 | India   | Calculator |     75 |         7535 |           1350 |
14. | 2000 | India   | Calculator |     75 |         7535 |           1350 |
15. | 2000 | India   | Computer   |   1200 |         7535 |           1350 |
16. | 2000 | USA     | Calculator |     75 |         7535 |           4575 |
17. | 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
18. | 2001 | USA     | Calculator |     50 |         7535 |           4575 |
19. | 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
20. | 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
21. | 2001 | USA     | TV         |    100 |         7535 |           4575 |
22. | 2001 | USA     | TV         |    150 |         7535 |           4575 |
23. +------+---------+------------+--------+--------------+----------------+

复制代码

  参考资料：Window Function Concepts and Syntax
  2.5 check 约束

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

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

复制代码

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

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

复制代码

  参考资料：CHECK Constraints
  2.6 隐藏列

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

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

复制代码

写入三条数据：

1. insert into t1 values (1),(2),(3);

复制代码

1. >select * from t1;
2. +------+
3. | i    |
4. +------+
5. |    1 |
6. |    2 |
7. |    3 |
8. +------+
10. >select i, j from t1;
11. +------+------------+
12. | i    | j          |
13. +------+------------+
14. |    1 | 2024-01-01 |
15. |    2 | 2024-01-01 |
16. |    3 | 2024-01-01 |
17. +------+------------+

复制代码

  参考资料：Invisible Columns
  2.7 不可见索引

可以修改索引的可见性，假如调整索引不可见，那么优化器会默认忽略该索引。该特性在索引维护中非常有用，大表中维护一个索引的代价还是比较大的。
讲一个笔者亲身经历过的故障吧，研发职员要删除一张 20G 大表中的一个索引，执行完后数据库 CPU 立马飙升，原来另有 SQL 依赖刚才删除的索引，那索引被删掉后这业务的 SQL 就全部全表扫描了，数据库 CPU、IO 立马打满了。此时加索引也很难加上，必须得停掉相干业务的 SQL 重新添加索引，影响范围很大。
假如有不可见索引，那么删除一个索引的流程就会安全许多，可以先调整索引的可见性，观测一段时间，期间假如有任何标题则打开索引即可，毕竟大表加一个索引也必要时间，修改索引可见性几乎瞬间完成。

1. -- 查询元数据，哪些索引不可见
2. select table_schema, table_name, index_name, column_name, is_visible
3. from information_schema.statistics
4. where is_visible = 'no';

复制代码

1. -- 修改索引可见
2. alter table xx alter index idx_xx visible;
3. -- 修改索引不可见
4. alter table xx alter index idx_xx invisible;
5. -- 创建一个不可见索引
6. 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 语法，属于一个种表值构造语法。

1. mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8) ORDER BY column_1;
2. +----------+----------+----------+
3. | column_0 | column_1 | column_2 |
4. +----------+----------+----------+
5. |        1 |       -2 |        3 |
6. |        4 |        6 |        8 |
7. |        5 |        7 |        9 |
8. +----------+----------+----------+
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 选项，用来解决电阛阓景中的热门行标题。

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

复制代码

使用 Session 1 开启一个变乱：

1. begin;
2. select * from test_semi where b = 1 for update;
3. +----+------+------+
4. | a  | b    | c    |
5. +----+------+------+
6. | 10 |    1 |    0 |
7. | 12 |    1 |    0 |
8. | 14 |    1 |    0 |
9. +----+------+------+

复制代码

Session 2 实验查询全表：

1. -- 堵塞：
2. select * from test_semi for update;
4. -- 跳过了锁定的行
5. select * from test_semi for update  SKIP LOCKED;
6. +----+------+------+
7. | a  | b    | c    |
8. +----+------+------+
9. | 11 |    2 |    0 |
10. | 13 |    2 |    0 |
11. +----+------+------+
13. -- 需要加的锁，被其他事务持有，则直接返回异常，不会等待锁超时
14. select * from test_semi for update NOWAIT;
15. 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 选项。

1. mysqlbinlog -vv --base64-output=decode-rows --print-table-metadata mysql-bin.000003

复制代码

1. #240729 11:12:54 server id 553306  end_log_pos 431 CRC32 0x5478e143         Table_map: `test`.`test_semi` mapped to number 146
2. # has_generated_invisible_primary_key=0
3. # Columns(`a` INT NOT NULL,
4. #         `b` INT,
5. #         `c` INT)
6. # Primary Key(a)
7. # at 431
8. #240729 11:12:54 server id 553306  end_log_pos 597 CRC32 0x05b1fd6a         Update_rows: table id 146 flags: STMT_END_F
9. ### UPDATE `test`.`test_semi`
10. ### WHERE
11. ###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
12. ###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
13. ###   @3=0 /* INT meta=0 nullable=1 is_null=0 */

复制代码

3.2 Binlog 记载变乱提交时间戳

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

1. -- 源库事务提交的时间戳
2. # original_commit_timestamp=1722222774134690 (2024-07-29 11:12:54.134690 CST)
3. -- 当前节点事务提交的时间戳
4. # 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 配套的特性，用于账号迁移场景。

1. set session print_identified_with_as_hex = on;
3. show create user t1@'%';
4. 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 引擎。

1. show privileges;

复制代码

  参考资料：Privileges Provided by MySQL
  4.5 天生随机密码

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

1. create user user1@'%' identified by random password;
3. -- 输出：
4. +-------+------+----------------------+-------------+
5. | user  | host | generated password   | auth_factor |
6. +-------+------+----------------------+-------------+
7. | user1 | %    | K[BnRT0qxSWd8zG]J7A0 |           1 |
8. +-------+------+----------------------+-------------+

复制代码

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

1. alter user user1@'%' identified by random password;
3. -- 输出：
4. +-------+------+----------------------+-------------+
5. | user  | host | generated password   | auth_factor |
6. +-------+------+----------------------+-------------+
7. | user1 | %    | TCiEI%8<}cN8&_eOY.m( |           1 |
8. +-------+------+----------------------+-------------+
10. set password for user1@'%' to random;
12. -- 输出：
13. +-------+------+----------------------+-------------+
14. | user  | host | generated password   | auth_factor |
15. +-------+------+----------------------+-------------+
16. | user1 | %    | YpS/oLgUY_@G>jO5/lE, |           1 |
17. +-------+------+----------------------+-------------+

复制代码

随机密码的长度由参数 generated_random_password_length 来决定，默认为 20 个字符。
   参考资料：Random Password Generation
  4.6 双重密码支持

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

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

复制代码

可用于业务毗连账号密码修改过渡阶段。
   参考资料：Dual Password Support

  4.7 密码错误锁定用户

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

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

复制代码

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

1. 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 子句对账号添加备注信息。设置的备注信息可以使用下面的方式查看。

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

复制代码

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 只实用于等值毗连和笛卡尔积。

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

复制代码

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

1. -- 非等值连接
2. select * from a join b where a.a1 > b.b1;
3. -- 半连接
4. select * from a where a.a1 in (select b1 from b);
5. -- 反连接
6. select * from a where not exists (select * from b where a.a1 = b.b1)
7. -- 左外连接
8. select * from a left join b on a.id = b.id;
9. -- 右外连接
10. 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 执行效率影响很大。

1. select
2.   *
3. from
4.   customer
5.   join orders on customer.cust_id = orders.customer_id
6. where
7.   customer.balance < 1000
8.   and orders.total > 10000

复制代码

假如为两张表创建直方图，表中的数据会被排序分为 100 个 bucket，并记载每个桶中数据的最大值、最小值、出现频次占比等信息。这样的话就可以影响优化器，在关联过程中，选择符合的小表作为驱动表。
该特性适合多表关联由于数据分布不均匀选错索引的场景，可以创建直方图引导优化器选择高效的访问路径。毕竟索引的维护是有代价的，直方图只在创建和更新时才会有开销。
   参考资料：Optimizer Statistics
  6. 推荐升级流程

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

总结

MySQL 8.0 相比 5.7 版本更新许多新特性，许多改变没有直接删除，便于用户过渡，兼容性还是比较高的。不过到 8.1、8.2 的创新版本，老的 5.7 的参数和语法就已经删除。
由于 5.7 已经竣事软件生命周期，意味着 Oracle 官方将不再为该版本提供更新和漏洞修复，虽然数据库厂商包管原则性上 MySQL 是安全的，保不齐有时会发现极其罕见的庞大安全漏洞。假如用户坚持使用 5.7 版本将碰面对安全风险增大、生态系统阑珊、运维本钱增高等标题。建议还在使用 5.7 的用户开始准备规划未来的升级计划，从而为业务提供连续可靠的数据库服务。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。