MySQL 8.0.31并行构建索引特性管窥

关注

原创 2022-10-25 12:54:02 180 阅读 AI 写同款· GEO 优化 ›

MySQL8.0新特性 MySQL 8.0：支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中。函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。MySQL 8.0：InnoDB会根据服务器上检测到的内存大小自动调整 innodb_buffer_pool_size，innodb_log_file_size 等参数，尽可能的冲分利用系统资源。阅读详情

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次求平均值。都先采用默认设置，最后得到的结果如下表：

	GreatSQL 8.0.25-16	MySQL 8.0.31
平均耗时（秒）	42.529	31.202

可以看到，使用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，可根据实际情况调整。

模式	平均耗时（秒）	提升比例
GreatSQL 8.0.25-16默认值	42.529
MySQL 8.0.31默认值	31.202	36.30%
64M	23.448	81.38%
64M + 8thds	21.202	100.59%
128M	22.856	86.07%
128M + 8thds	21.456	98.21%
256M	22.047	92.90%
256M + 8thds	21.266	99.99%
512M	22.885	85.84%
512M + 8thds	23.227	83.10%
1G	25.239	68.51%
1G + 8thds	24.486	73.69%

上面表格中，64M表示 innodb_ddl_buffer_size=64M，8thds表示innodb_ddl_threads=8 ，其他以此类推。

可以看到，当 innodb_ddl_buffer_size 及 innodb_ddl_threads 值适当加大后，重建索引的耗时明显降低，最好的情况下，索引重建效率可提升1倍；不过其效率也并不随着值增加而线性提高。看起来，线上生产环境需要根据实测情况进行调整。

file

循环自动测试的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