Mysql 除了并行查询以外也可以并行索引,升级吧少年

POSTGRESQL 在 DDL DML DQL 都可以并行，之前MYSQL 在并行方面一直是软肋，MYSQL 8 已经提供了DQL的并行， DDL 的并行也支持了，从MYSQL5.X 升级到8 是必然了.

注意版本必须是8.027 ，对写这篇文字的MYSQL 最新的可以工作的版本。

首先我们先要弄清楚到底那些操作会使用DDL 并行的操作， 

1 索引操作

2 主键操作

3 column 字段的操作

4 分区表，表空间，表的维护操作

针对索引的操作本身只有全文索引和空间索引是不能使用并行的能力

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Creating or adding a secondary index	No	Yes	No	Yes	No
Dropping an index	No	Yes	No	Yes	Yes
Renaming an index	No	Yes	No	Yes	Yes
Adding a FULLTEXT index	No	Yes*	No*	No	No
Adding a SPATIAL index	No	Yes	No	No	No
Changing the index type	Yes	Yes	No	Yes	Yes

下面以添加索引为例，这里需要引入 8.027 的新参数 innodb-ddl-threads,

那么为什么索引的并行操作能提高添加索引的效率，原理也很简单

在添加二级索引的时候，我们需要

1 扫描clustered index  并将数据存储都临时表中

2 针对这些数据进行排序

3 加载排序的数据从临时文件，写入到二级索引中

与并行索引工作有关的参数

1  扫描聚集索引（主键）的并行数由 innodb_parallel_read_threads 来决定，默认设置为4， 具体以cpu的核心数 - 2 到 8 来设置。 但需要注意的是即使设置了，如果无法使用并行，系统还会使用单核心的方式运行。

2  在创建并行索引是，需要注意对于并行的线程分配内存 在8.027上新添加了 innodb_ddl_buffer_size  参数，内存具体在每个线程上的使用是 innodb_ddl_buffer_size / innodb_ddl_threads 

参数默认大小 1MB

3  innodb_online_alter_log_max_size 这个设置主要针对进行DDL操作时的临时空间的设置，默认128MB ，在索引操作时，会将读取的数据写入临时文件，临时文件的尺寸由innodb_online_alter_log_max  决定。

4   innodb_sort_buffer_size 在操作二级索引建立时需要对临时的文件的内容进行排序，就需要innodb_sort_buffer_size 来控制缓存。

5  innodb_tmpdir 临时对alter table 的语句中存储的临时数据目录的磁盘空间不足也会导致操作失败

我们来对并行添加索引的参数和速度进行一个比较。

CREATE TABLE `t_user` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  `age` tinyint(4) DEFAULT NULL,

  `create_time` datetime DEFAULT NULL,

  `update_time` datetime DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

delimiter $$

DROP PROCEDURE IF EXISTS proc_batch_insert;

CREATE PROCEDURE proc_batch_insert()

BEGIN

DECLARE pre_name BIGINT;

DECLARE ageVal INT;

DECLARE i INT;

SET pre_name=187635267;

SET ageVal=100;

SET i=1;

WHILE i < 1000000 DO

INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+i)%30,NOW(),NOW());

SET pre_name=pre_name+100;

SET i=i+1;

END WHILE;

END $$

delimiter ;

我们建立一个t_user表，并在表里添加100万的数据

然后我们我们先添加索引，

1  不调节任何参数，时间为3.68秒

2  调整DDL 操作的buffer 加大到目前的默认的配置的8倍

     在添加索引时间变为 2.93秒

3  将并行开到 8 在建立索引， 时间为2.78秒

4  将并行关闭，在建立所以，建立时间为3.41秒

通过上面的参数调节我们可以看到，不同的参数的变化对于添加索引的性能影响，加大 innodb_ddl_buffer_size 和 innodb_parallel_read_threads  对索引的建立的速度提升有很大的帮助。

另外 MYSQL 8.027 引入了 innodb_ddl_threads 参数这个参数主要针对索引建立时的排序和建立阶段，同时也会应用到rebuild secondrary indexes 的操作中，默认值 4 ， 可以设置的值从1 -64 ，对于MYSQL 使用多核心CPU越来越有意义。