MySQL 8.0并行构建索引

前言

MySQL 8.0.27之前排序或加载操作都是单线程，8.0.27开始为了提升创建或重建二级索引速度引入了innodb_ddl_buffer_size、innodb_ddl_threads这两个参数。

• innodb_ddl_buffer_size：在线定义DDL的缓冲区大小，默认大小是1048576bytes（1MB）。
• innodb_ddl_threads：在线定义DDL的线程数，默认是4。

在MySQL 8.0.27之前为了提升在线创建或重建二级索引速度可以增加DDL在线定义缓冲区innodb_sort_buffer_size的值。

参考：https://dev.mysql.com/doc/refman/8.0/en/online-ddl-memory-management.html

 

MySQL 8.0.31版本支持多线程在线创建或重建二级索引：

InnoDB: InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded. Previously, this action was performed by a single thread.

参考：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html

 

配置在线DDL并发线程

在线创建或重建二级索引的流程：

• 扫描聚集索引并将数据写入临时排序文件（innodb_parallel_read_threads定义扫描聚集索引的线程数）；
• 排序数据；
• 从临时排序文件中加载已经排序好的数据到二级索引中。

在线DDL的限制：

• 创建包含虚拟列的索引不支持并行线程；
• 创建full-text索引不支持并行线程；
• 创建spatital索引不支持并行线程
• 创建表上虚拟列索引不支持并行扫描；
• 创建full-text索引不支持并行扫描；
• 创建spatital索引不支持并行扫描。

参考：https://dev.mysql.com/doc/refman/8.0/en/online-ddl-parallel-thread-configuration.html

重建二级索引压测

测试目的：测试innodb_parallel_read_threads、innodb_ddl_buffer_size、innodb_ddl_threads这三个参数分别对创建或重建二级索引的时间影响。

测试环境：

• 台式机：64G 7200转机械盘 Win10
• VMware Workstation
• CentOS 7.6
• 4C
• 4G MEM

 

使用sysbench生成测试表sbtest1（500W行）

mysql> create database sysbench;
[mysql]# sysbench /home/mysql/sysbench/sysbench-master/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-socket=/tmp/mysql80.sock --mysql-user=root --mysql-password=Gaoyu@029 --mysql-db=sysbench --tables=1 --table-size=5000000 prepare
mysql> alter table sbtest1 drop index c;
mysql> optimize table sbtest1;

MySQL 8.0.31

innodb_ddl_threads：4 和8

innodb_ddl_buffer_size：默认值是4M，压力测试每次增加4M。

MySQL 8.0.28

innodb_ddl_threads：4 和8

innodb_ddl_buffer_size：默认值是4M，压力测试每次增加4M。

MySQL  8.0.26

innodb_parallel_read_threads值分别为4和8（缺省等于4）

innodb_sort_buffer_size从4M开始每次增加4M一直加到80M（缺省是4M）

MySQL 8.0.27之前是单线程创建或重建二级索引，所以这两个值增加对创建或重建二级索引的速度影响不大。

8.0.31与8.0.28对比

innodb_ddl_threads值均为4

从我的环境压测结果来看，8.0.28版本的执行时间略短于8.0.31，innodb_ddl_buffer_size的值到100M后执行时间变化不大。

8.0.26与8.0.31对比

MySQL 8.0.31相较于8.0.26版本在我的压测环境中重建二级所有执行时间有将近36.8%的提升。