MySQL 8.0 – 用于在线 DDL 操作的 InnoDB 并行线程

作者：Frederic Descamps

译者：徐轶韬

MySQL 8.0.27引入了一个新变量“innodb_ddl_threads”，用来控制 InnoDB 创建（排序和构建）二级索引的最大并行线程数。

这个新变量与另一个新变量“innodb_ddl_buffer_size"配合使用
。

如果用户使用快速存储并且具有多个 CPU 内核，可以调整这些变量以加快二级索引的创建。

在这个例子中，我使用了 airportdb 数据库，并为最大的表booking添加了一个二级索引。

首先从默认设置开始：

SQL  alter table booking 
       add index idx_2(flight_id, seat, passenger_id);
Query OK, 0 rows affected (9 min 0.6838 sec)

MySQL 8.0.27 中的默认设置是：

innodb_ddl_threads = 4
innodb_ddl_buffer_size = 1048576
innodb_parallel_read_threads = 4

“innodb_ddl_buffer_size”在所有定义的“innodb_ddl_threads”之间共享。如果用户增加线程数量，同时建议增加缓冲区大小。

为了找到这些变量的最佳值，让我们看看CPU 内核的数量：

SQL  select count from information_schema.INNODB_METRICS 
       where name = 'cpu_n';
+-------+
| count |
+-------+
|    16 |
+-------+

显示有16 个内核可以共享。由于我的机器内存充足，我将为InnoDB DDL 缓冲区分配1GB。

SQL  SET innodb_ddl_threads = 8;
SQL  SET innodb_parallel_read_threads = 8;
SQL  SET innodb_ddl_buffer_size = 1048576000;


SQL  alter table booking add index idx_2(flight_id, seat, passenger_id);
Query OK, 0 rows affected (3 min 9.1862 sec)

与之前相比，性能大幅提升！

下表记录了摘要，用于查看这些变量的影响：

读者可以看到，对于我们的系统和数据，通过增加用于 InnoDB DDL 线程的缓冲区大小来实现最佳性能。

这是否意味着只有缓冲区大小重要？并不是。这取决于用户的数据，如果只有一个线程但有2G的缓冲区，执行时间是3分22.9617秒。持续增加缓冲区大小并不一定会提高性能。

请注意，此功能还提供了在同一个 alter 语句中为一个表并行构建多个索引的可能性：

SQL  alter table booking add index idx_2(flight_id, seat, passenger_id), 
      add index idx_3(price, passenger_id), add index idx_4(seat,price);
Query OK, 0 rows affected (10 min 17.8435 sec)

使用默认设置需要 28 分 46.9949 秒。

但是在 32 核 OCI 上的 MySQL 数据库服务实例上执行相同的查询在2 分 11.6700 秒内执行，缓冲区大小为 2G！

这种在 InnoDB 中处理在线 DDL 的新方法是一个非常好的改进，欢迎读者试用。

感谢您关注“MySQL解决方案工程师”！