MySQL 8.0 18个管理相关的新特性

时间:2023-01-11 17:10:33

最近在梳理 MySQL 8.0 的新特性,以下是从中选取的 18 个管理相关的新特性。

这 18 个新特性涉及的范围比较广,包括安装、备份、升级、DDL、慢日志、迁移、日常维护等。

掌握这些新特性有助于我们更好地使用 MySQL 8.0。

1. 可持久化全局变量

持久化后的变量会存储在数据目录下的 mysqld-auto.cnf 文件中。

以下是持久化变量相关的命令。

# 持久化变量,同时修改变量的内存值。
SET PERSIST max_connections = 2000;
# 只持久化变量,不修改变量的内存值,适用于只读参数的调整。
SET PERSIST_ONLY back_log = 2000;
# 从 mysqld-auto.cnf 中删除所有持久化变量。
RESET PERSIST;
# 从 mysqld-auto.cnf 中删除指定的变量。如果变量不存在,会报错。
RESET PERSIST system_var_name;
# 从 mysqld-auto.cnf 中删除指定的变量。如果变量不存在,会提示 warning,不报错。
RESET PERSIST IF EXISTS system_var_name;

持久化后的变量即可在 mysqld-auto.cnf 中查看,也可通过 performance_schema.persisted_variables 查看。

2. 可设置管理 IP 和端口

管理 IP 通过 admin_address 参数设置,管理端口通过 admin_port 参数设置。

管理连接的数量没有限制,但仅允许具有 SERVICE_CONNECTION_ADMIN 权限的用户连接。

默认情况下,管理接口没有自己的独立线程,可将 create_admin_listener_thread 设置为 ON 开启。

建议设置管理 IP 和端口,这样即使连接数满了,也不用担心登陆不上实例去调整 max_connections 的大小。

3. 安装包

从 MySQL 8.0.16 开始,MySQL 针对通用二进制包(Linux - Generic)提供了一个最小化版本。

最小化版本移除了 debug 相关的二进制文件。

MySQL 8.0.31 普通版本(mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz)包的大小是 576.8 MB,而最小化版本(mysql-8.0.31-linux-glibc2.17-x86_64-minimal.tar.xz)只有 57.4 MB。后者大小只是前者的 9.95 %。

从 MySQL 8.0.31 开始,通用二进制包还提供了 Linux - Generic (glibc 2.17) (ARM, 64-bit) 版本的下载。

4. 资源组(Resource Groups)

资源组,可用来控制组内线程的优先级及其能使用的资源,目前,能被管理的资源只有 CPU。

# 创建资源组
CREATE RESOURCE GROUP Batch
  TYPE = USER # 资源组的类型,可设置USER(用户资源组),SYSTEM(系统资源组)。
  VCPU = 0-1 # 设置 CPU 亲和性,让线程运行在指定的 CPU 上。不设置,则默认会使用所有的 CPU。     
  THREAD_PRIORITY = 10# 设置线程优先级,有效值是-20(最高优先级)到19(最低优先级)。不设置,则默认为0。对于系统资源组,可设置的优先级范围是-20到0,对于用户资源组,可设置的优先级范围是0到19。

以下是资源组的几种常用方式。

SET RESOURCE GROUP Batch FOR 702,703# 将指定线程分配给资源组。702 是线程ID,对应 performance_schema.threads 中的 THREAD_ID。
SET RESOURCE GROUP Batch; # 将当前会话的线程分配给资源组
SELECT /*+ RESOURCE_GROUP(Batch) */ COUNT(*) FROM sbtest.sbtest1;

5. ALTER DATABASE 支持 READ ONLY 选项

设置为只读模式的库将禁止任何更新操作。适用于数据库迁移场景。

# 将 mydb 设置为只读模式
ALTER DATABASE mydb READ ONLY = 1;
# 关闭只读模式
ALTER DATABASE mydb READ ONLY = 0;

6. 设置 SHOW PROCESSLIST 的实现方式

SHOW PROCESSLIST 默认是从线程管理器中获取线程信息。这种实现方式会持有全局互斥锁,对数据库的性能会有一定的影响。

所以一般都推荐使用 performance_schema.processlist,这种方式不会持有全局锁。

在 MySQL 8.0.22 中,引入了 performance_schema_show_processlist 参数,用来设置 SHOW PROCESSLIST 的实现方式。设置为 ON,则会使用 performance_schema.processlist 这种实现方式,默认为 OFF。

7. DDL

在 MySQL 8.0.27 中,引入了 innodb_ddl_threads 和 innodb_ddl_buffer_size 提升索引的创建速度。

8. 秒级加列

从 MySQL 8.0.12 开始,Online DDL 开始支持 INSTANT 算法。

使用这个算法进行加列操作,只需修改表的元数据信息,操作瞬间就能完成。不过在 MySQL 8.0.29 之前,列只能添加到表的最后位置。

从 MySQL 8.0.29 开始,则移除了这一限制,新增列可以添加到表的任何位置。

不仅如此,从 MySQL 8.0.29 开始,删列操作也可以使用 INSTANT 算法。

9. 提升了 DROP TABLE,TRUNCATE TABLE,DROP TABLESPACE 操作的性能

这个优化是 MySQL 8.0.23 引入的。在之前的版本中,这些操作会遍历整个 Buffer Pool,删除对应表(或表空间)的数据页。在遍历的过程中,会加锁(latch)。加锁期间,会阻塞所有的 DML 操作。

注意,阻塞时间与 Buffer Pool 的大小有关,与表的大小无关。Buffer Pool 越大,遍历时间会越长,相应的,阻塞时间也会越久。

优化后,待删除的数据页会做异步处理。

10. 操作系统查看 MySQL 的线程名

从 MySQL 8.0.27 开始,通过 ps 命令可以直接查看 MySQL 的线程名。

# ps -p 22307 H -o "pid tid cmd comm"
  PID   TID CMD                         COMMAND
22307 22307 /usr/local/mysql/bin/mysqld mysqld
22307 22316 /usr/local/mysql/bin/mysqld ib_io_ibuf
22307 22318 /usr/local/mysql/bin/mysqld ib_io_log
22307 22319 /usr/local/mysql/bin/mysqld ib_io_rd-1
22307 22331 /usr/local/mysql/bin/mysqld ib_io_rd-2
...

11. 控制连接的内存使用量

从 MySQL 8.0.28 开始,引入了 connection_memory_limit 参数限制单个用户连接可以使用的最大内存量,global_connection_memory_limit 参数限制所有用户连接可以使用的内存总量。

mysql> SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1;
ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 2456976 bytes.

注意,这里说的内存不包括 InnoDB Buffer Pool。

12. 慢日志

在 MySQL 8.0.14 中,引入了 log_slow_extra 参数,可以将更详细的信息记录到慢日志中。

看下面这个示例,对比下参数开启前后的输出。

# Time: 2022-12-11T08:19:52.135515Z
# User@Host: root[root] @ localhost []  Id:   660
# Query_time: 10.000188  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1670746782;
select sleep(10);

# Time: 2022-12-11T08:20:54.397597Z
# User@Host: root[root] @ localhost []  Id:   662
# Query_time: 10.000194  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1 Thread_id: 662 Errno: 0 Killed: 0 Bytes_received: 23 Bytes_sent: 57 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-12-11T08:20:44.397403Z End: 2022-12-11T08:20:54.397597Z
SET timestamp=1670746844;
select sleep(10);

除此之外,SET timestamp 现在记录的是语句的开始时间,不再是语句的结束时间。

13. 备份

在 MySQL 8.0.30 中,mysqldump 新增了 --mysqld-long-query-time 选项,允许自定义 long_query_time 的会话值。

这样可避免将备份相关的查询语句记录在慢日志中。

14. 克隆插件

克隆插件(Clone Plugin)是 MySQL 8.0.17 引入的一个重大特性。

有了克隆插件,只需一条命令就能很方便地添加一个新的节点,无论是在组复制还是普通的主从环境中。

克隆插件的具体用法及实现细节可参考:MySQL 8.0 新特性之 Clone Plugin

15. 备份锁

注意,引入备份锁是为了阻塞备份过程中的 DDL,不是为了替代全局读锁。

之所以 XtraBackup 8.0 及 MySQL Enterprise Backup 在备份的过程中不再加全局读锁,主要是因为 performance_schema.log_status 的引入。

16. 数据库升级

数据库升级无需再执行 mysql_upgrade 脚本。升级逻辑已内置到 mysqld 的启动流程中。

升级之前,可通过 MySQL Shell 中的 util.checkForServerUpgrade() 检查实例是否满足升级条件。

17. MySQL 客户端

mysql 客户端默认会开启 --binary-as-hex。

开启后,mysql 客户端会使用十六进制表示法显示二进制数据。例如,

mysql8.0> SELECT UNHEX(41);
+----------------------+
| UNHEX(41)            |
+----------------------+
| 0x41                 |
+----------------------+
1 row in set (0.00 sec)

mysql5.7SELECT UNHEX(41);
+-----------+
| UNHEX(41) |
+-----------+
| A         |
+-----------+
1 row in set (0.00 sec)

如果要禁用十六进制表示法,需设置 --skip-binary-as-hex。

18. 可通过 RESTART 命令重启 MySQL 实例

能使用 RESTART 命令的前提是 mysqld 是通过 mysqld_safe 或 systemctl 等守护进程启动的。

mysql> restart;
ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).