MySQL 8.0并行构建索引

Posted on 2022-10-14 11:12 高&玉阅读(727) 评论(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;