关于mysql:技术分享-MySQL-并行-DDL

作者：李鹏博

爱可生 DBA 团队成员，会变身，次要负责 MySQL 故障解决和 SQL 审核优化。对技术执着，为客户负责。

本文起源：原创投稿

*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

随着 MySQL 版本的不断更新，对 DDL 操作的反对也在一直的欠缺和更新：比方从 MySQL 5.6 引入 Online DDL ，在 MySQL 5.7 对 Online DDL 进一步欠缺，到当初的 8.0 版本，则对 DDL 的实现从新进行了设计，比方 DDL 操作反对原子个性，在 MySQL 8.0.27 引入并行 DDL 。本篇就来探索一下 MySQL 8.0.27 的并行 DDL 对于 DDL 操作速度的晋升。

MySQL 8.0.14 引入了 innodb_parallel_read_threads 变量来管制扫描聚簇索引的并行线程。MySQL 8.0.27 引入了 innodb_ddl_threads 变量来管制用于创立二级索引时的并行线程数量，此参数个别和一并引入的 innodb_ddl_buffer_size 一起应用，innodb_ddl_buffer_size 用于指定进行并行 DDL 操作时可能应用的 buffer 大小，buffer 是在所有的 DDL 并行线程中平均分配的，所以个别如果调大 innodb_ddl_threads 变量时，也须要调大 innodb_ddl_buffer_size 的大小。

innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 的默认大小别离为：

mysql> select @@global.innodb_ddl_threads;+-----------------------------+| @@global.innodb_ddl_threads |+-----------------------------+|                           4 |+-----------------------------+1 row in set (0.00 sec)mysql> select @@global.innodb_ddl_buffer_size;+---------------------------------+| @@global.innodb_ddl_buffer_size |+---------------------------------+|                         1048576 |+---------------------------------+1 row in set (0.00 sec)mysql> select @@global.innodb_parallel_read_threads;+---------------------------------------+| @@global.innodb_parallel_read_threads |+---------------------------------------+|                                     4 |+---------------------------------------+1 row in set (0.00 sec)

接下来测试一下调大 innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 参数值对 DDL 操作的性能晋升。

首先创立一张 5000 万的表：

-- 数据库版本为8.0.28mysql> select @@version;+----------+| @@version|+----------+| 8.0.28   |+----------+1 row in set (0.00 sec)-- buffer pool大小为24Gmysql> select @@global.innodb_buffer_pool_size;+----------------------------------+| @@global.innodb_buffer_pool_size |+----------------------------------+|                      25769803776 |+----------------------------------+1 row in set (0.001 sec)mysql> create database action;Query OK, 1 row affected (0.01 sec)# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/data/mysql/data/3306/mysqld.sock  --mysql-user=root --mysql-password='123' --mysql-db=action --tables=1 --table-size=50000000 --report-interval=1 --threads=8 preparemysql> select count(*) from action.sbtest1;+----------+| count(*) |+----------+| 50000000 |+----------+1 row in set (21.64 sec)-- 表空间大小为12G# ll -htotal 12G-rw-r-----. 1 mysql mysql 12G Jan 20 17:38 sbtest1.ibd

别离测试不同的线程数量和缓冲区大小的 DDL 操作工夫，例如：

-- 设置并发DDL线程为1mysql> set innodb_ddl_threads = 1;Query OK, 0 rows affected (0.01 sec)-- 设置buffer大小为512Mmysql> set innodb_ddl_buffer_size = 536870912;Query OK, 0 rows affected (0.00 sec)-- 设置并行索引扫描线程为1mysql> set innodb_parallel_read_threads = 1;Query OK, 0 rows affected (0.01 sec)-- 执行DDL操作mysql> alter table action.sbtest1 add index idx_c(c);Query OK, 0 rows affected (6 min 54.21 sec)Records: 0  Duplicates: 0  Warnings: 0-- 查看DDL的内存最大占用mysql> select event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024 from performance_schema.memory_summary_global_by_event_name where event_name='memory/innodb/ddl';+-------------------+----------------------------------------+| event_name        | CURRENT_NUMBER_OF_BYTES_USED/1024/1024 |+-------------------+----------------------------------------+| memory/innodb/ddl |                           513.08750916 |+-------------------+----------------------------------------+1 row in set (0.00 sec)

通过一直调整相干参数失去以下后果：

能够看到，随着并发线程的增多和 buffer 的减少，DDL 操作所占用的资源也越多，而 DDL 操作所破费的工夫则越少。不过通过比照资源的耗费和 DDL 速度的晋升比例，最正当的并行线程数量为4-8个，而 buffer 大小能够依据状况进行调整。

参考链接：https://dev.mysql.com/doc/ref...