MySQL 8.0 Online DDL操作的并行线程

在MySQL 8.0.27中引入了新的参数：innodb_ddl_threads。表示在索引创建期间，在sort和build阶段可以使用的并发线程数。可以应用于在线ddl操作时创建或rebuild二级辅助索引。

参数innodb_ddl_threads要和innodb_ddl_buffer_size结合使用。innodb_ddl_buffer_size设置的buffer是所有线程公用的，每个线程能使用的buffer大小是：innodb_ddl_buffer_size/innodb_ddl_threads。

如果你增加了innodb_ddl_threads的大小，建议也增加innodb_ddl_buffer_size的大小。

如果你有快的存储、或多个CPU核，使用这些参数可以加快二级辅助索引的创建。

比如，这里有一张大表bookings。最初是默认设置：

?

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

在MySQL8.0.27中，默认的设置是：

?

1 2 3

innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4

为了确定如何设置innodb_ddl_threads最佳，先来看看有多少个cpu：

?

1 2 3 4 5 6

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

这个环境有16个内核。因为我的机器内存比较富裕，我将做如下的设置：

?

1 2 3 4 5 6

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)

时间提高了很多。

以下是测试结果：

?

1 2 3 4 5 6 7 8 9 10 11

ddl buffer size     ddl threads     parallel read theads    　　　　　　　　　　　　　　　　　　　　　　　　execution time 1048576             4               4                       9 min 0.6838 sec 104857600           8               8                       4 min 8.3601 sec 1048576000          8               8                       3 min 9.1862 sec 1048576000          16              16                      3 min 7.4079 sec 1048576000          16              8                       3 min 4.1161 sec 1048576000          12              4                       3 min 8.7854 sec 1048576000          4               12                      3 min 5.9497 sec 1048576000          4               4                       3 min 12.2435 sec 2097152000          4               4                       2 min 43.6280 sec 2097152000          8               8                       2 min 44.6516 sec

从测试结果可以看到，增加buffer size的大小，可以达到最佳性能。
但是还有别的影响因素么？当然并非如此，还取决于你的数据量。而且持续加大buffer size之后，时间也不会继续减少。

这个参数也有助于在单个alter操作中构建多个索引：

?

1 2	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 min 46.9949 sec