关于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/bash
db=MySQL
# 设置不同的 ddl buffer size
for dbp in 64 128 256 512 1024
do
exec 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 5
done

# 调整 ddl threads = 8
exec 3>&1 4>&2 1>> parallel-index-build-${dbp}m-8th.log 2>&1
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_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 5
done

# 复原 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" test
done

并行构建索引的限度

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

不反对虚构列（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 社区助手 微信好友，发送验证信息 加群。