关于mysql:MySQL-8031并行构建索引特性管窥

• GreatSQL社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
• GreatSQL是MySQL的国产分支版本，应用上与MySQL统一。
• 本文起源：原创投稿；作者：YeJinrong/叶金荣

测试效率晋升36% ~ 100%，相当可观

本文目录

• 并行构建索引测试
• 进一步提高索引构建效率
• 并行构建索引的限度

MySQL 8.0.31于2022.10.11公布了，比我预计的日期早了一周，先赞一个。

看了下 release notes ，新增的货色不算多，感觉MySQL官网对8.0版本曾经进入维稳的后半段了，英文不好的同学能够戳此查看 徐轶韬老师针对8.0.31做的疾速解读。另外，依据 徐老师的最新推文，也理解到MySQL针对8.0版本缩短了规范反对（Premier Support）时长，从原来的2023.4缩短到了2025.4，不过延长反对（Extended Support）的期限没有扭转，依然是2026.4。

本次公布的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.

只有这么简略的一句，没更多扩大解释阐明。简言之，就是反对并行构建索引，晋升索引构建性能。

并行构建索引测试

还是间接做个测试看看吧。

利用sysbench构建一个有400万行记录的测试表，只有一个主键索引时，表空间物理文件大小为1044381696 Bytes，增加完测试索引后，表空间物理文件大小涨到1434451968 Bytes，减少了37.35%。

mysql> CREATE TABLE `t1` (  `id` int NOT NULL AUTO_INCREMENT,  `k` int NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;mysql> select count(*) from t1;+----------+| count(*) |+----------+|  4000000 |+----------+1 row in set (0.35 sec)

接下来，我别离针对GreatSQL 8.0.25-16、MySQL 8.0.31做重建索引的测试，每个数据库跑10次，计算其每次耗时，去掉最大最小偏差值，取剩下的8次求平均值。都先采纳默认设置，最初失去的后果如下表：

能够看到，应用MySQL 8.0.31重建索引的效率晋升了约36%，还是相当可观的，如果是线上环境有大表可能晋升成果更可观。

进一步提高索引构建效率

从MySQL 8.0.27开始，新增选项 innodb_ddl_buffer_size，其作用是作为Online DDL期间的buffer（代替 innodb_sort_buffer_size 的作用），用于晋升辅助索引构建的效率。这是Online DDL期间总共能够应用的buffer，如果有多个DDL并发线程，则每个线程最大可用的buffer是 innodb_ddl_buffer_size / innodb_ddl_threads 。其默认值是1MB，显著太低了，我在本案中尝试批改成64M、128MB、256MB、512MB、1GB，再比照测试其成果。

选项 innodb_ddl_threads 也是从8.0.27开始新增的，用于定义Online DDL的并发线程数，默认值是4，可依据理论状况调整。

下面表格中，64M示意 innodb_ddl_buffer_size=64M，8thds示意innodb_ddl_threads=8 ，其余以此类推。

能够看到，当 innodb_ddl_buffer_size 及 innodb_ddl_threads 值适当加大后，重建索引的耗时明显降低，最好的状况下，索引重建效率可晋升1倍；不过其效率也并不随着值减少而线性进步。看起来，线上生产环境须要依据实测状况进行调整。

循环自动测试的shell脚本也顺便分享进去：

#!/bin/bashdb=MySQL# 设置不同的ddl buffer sizefor dbp in 64 128 256 512 1024doexec 3>&1 4>&2 1>> parallel-index-build-${dbp}m.log 2>&1# 循环10次for i in $(seq 1 10)do echo "$db cycle $i" mysql -N -s -q -f -S./$db/mysql.sock -e "set global innodb_ddl_buffer_size=$dbp*1024*1024" test > /dev/null 2>&1 mysql -f -S./$db/mysql.sock -e "select @@global.innodb_ddl_buffer_size" test mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 drop index i1, drop index i2;" test > /dev/null 2>&1 sleep 5 time mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 add index i1(k), add index i2(pad);" test echo "" echo "" sleep 5done# 调整ddl threads = 8exec 3>&1 4>&2 1>> parallel-index-build-${dbp}m-8th.log 2>&1for i in $(seq 1 10)do echo "$db cycle $i" mysql -N -s -q -f -S./$db/mysql.sock -e "set global innodb_ddl_threads=8" test > /dev/null 2>&1 mysql -f -S./$db/mysql.sock -e "select @@global.innodb_ddl_threads" test mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 drop index i1, drop index i2;" test > /dev/null 2>&1 sleep 5 time mysql -N -s -q -f -S./$db/mysql.sock -e "alter table t1 add index i1(k), add index i2(pad);" test echo "" echo "" sleep 5done# 复原 ddl threads=4 mysql -N -s -q -f -S./$db/mysql.sock -e "set global innodb_ddl_threads=4" test > /dev/null 2>&1 mysql -f -S./$db/mysql.sock -e "select @@global.innodb_ddl_threads" testdone

并行构建索引的限度

最初，再来看下并行构建索引新个性都有哪些限度：

不反对虚构列（virtual columns）。
不反对全文索引（full-text index）。
不反对空间索引（spatial index ）。
期待MySQL 8.0将来新版本中持续推出更多实用的新个性吧。

延长浏览

[Changes in MySQL 8.0.31] (https://dev.mysql.com/doc/rel...
Configuring Parallel Threads for Online DDL Operations, https://dev.mysql.com/doc/ref...
Online DDL Memory Management, https://dev.mysql.com/doc/ref...

Changes in MySQL 8.0.31
Configuring Parallel Threads for Online DDL Operations
[Online DDL Memory Management]() https://dev.mysql.com/doc/ref...)

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。

相干链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

欢送来GreatSQL社区发帖发问
https://greatsql.cn/

技术交换群：

微信：扫码增加GreatSQL社区助手微信好友，发送验证信息加群。