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。最初是默认设置：

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中，默认的设置是：

innodb_ddl_threads = 4
innodb_ddl_buffer_size = 1048576
innodb_parallel_read_threads = 4

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

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

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

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)

时间提高了很多。

以下是测试结果：

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操作中构建多个索引：

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