mysql8.0特性管理相关

转自：https://mp.weixin.qq.com/mp/appmsgalbum?__biz=Mzg5OTY2MjU5MQ==&action=getalbum&album_id=2435603183193505795&scene=173&from_msgid=2247491832&from_itemidx=1&count=3&nolastread=1#wechat_redirect

 

 

最近在梳理 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. minimal 安装包

从 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. 秒级加列 INSTANT 

从 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.7> SELECT 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).

19. 降序索引

20.CTE

21.窗口函数/分析函数