殿上欢,有声小说打包下载

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

MySQL8.0.31并行構(gòu)建索引特性管窺

MySQL 8.0.31于2022.10.11發(fā)布了，比我預(yù)計(jì)的日期早了一周，先贊一個(gè)。

創(chuàng)新互聯(lián)專注于庫車網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。熱誠為您提供庫車營銷型網(wǎng)站建設(shè)，庫車網(wǎng)站制作、庫車網(wǎng)頁設(shè)計(jì)、庫車網(wǎng)站官網(wǎng)定制、成都微信小程序服務(wù)，打造庫車網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供庫車網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。

看了下 release notes ，新增的東西不算多，感覺MySQL官方對(duì)8.0版本已經(jīng)進(jìn)入維穩(wěn)的后半段了，英文不好的同學(xué)可以戳此查看徐軼韜老師針對(duì)8.0.31做的快速解讀?。另外，根據(jù) 徐老師的最新推文，也了解到MySQL針對(duì)8.0版本延長了標(biāo)準(zhǔn)支持（Premier Support）時(shí)長，從原來的2023.4延長到了2025.4，不過延伸支持（Extended Support）的期限沒有改變，仍然是2026.4。

本次發(fā)布的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.

只有這么簡單的一句，沒更多擴(kuò)展解釋說明。簡言之，就是支持并行構(gòu)建索引，提升索引構(gòu)建性能。

并行構(gòu)建索引測試

還是直接做個(gè)測試看看吧。

利用sysbench構(gòu)建一個(gè)有400萬行記錄的測試表，只有一個(gè)主鍵索引時(shí)，表空間物理文件大小為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)

接下來，我分別針對(duì)GreatSQL 8.0.25-16、MySQL 8.0.31做重建索引的測試，每個(gè)數(shù)據(jù)庫跑10次，計(jì)算其每次耗時(shí)，去掉最大最小偏差值，取剩下的8次求平均值。都先采用默認(rèn)設(shè)置，最后得到的結(jié)果如下表：

	GreatSQL 8.0.25-16	MySQL 8.0.31
平均耗時(shí)（秒）	42.529	31.202

可以看到，使用MySQL 8.0.31重建索引的效率提升了約36%，還是相當(dāng)可觀的，如果是線上環(huán)境有大表可能提升效果更可觀。

進(jìn)一步提高索引構(gòu)建效率

從MySQL 8.0.27開始，新增選項(xiàng) innodb_ddl_buffer_size?，其作用是作為Online DDL期間的buffer（代替 innodb_sort_buffer_size? 的作用），用于提升輔助索引構(gòu)建的效率。這是Online DDL期間總共可以使用的buffer，如果有多個(gè)DDL并發(fā)線程，則每個(gè)線程最大可用的buffer是 innodb_ddl_buffer_size / innodb_ddl_threads。其默認(rèn)值是1MB，明顯太低了，我在本案中嘗試修改成64M、128MB、256MB、512MB、1GB，再對(duì)比測試其效果。

選項(xiàng) innodb_ddl_threads 也是從8.0.27開始新增的，用于定義Online DDL的并發(fā)線程數(shù)，默認(rèn)值是4，可根據(jù)實(shí)際情況調(diào)整。

模式	平均耗時(shí)（秒）	提升比例
GreatSQL 8.0.25-16默認(rèn)值	42.529
MySQL 8.0.31默認(rèn)值	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，其他以此類推。

可以看到，當(dāng) innodb_ddl_buffer_size? 及 innodb_ddl_threads 值適當(dāng)加大后，重建索引的耗時(shí)明顯降低，最好的情況下，索引重建效率可提升1倍；不過其效率也并不隨著值增加而線性提高?？雌饋?，線上生產(chǎn)環(huán)境需要根據(jù)實(shí)測情況進(jìn)行調(diào)整。

循環(huán)自動(dòng)測試的shell腳本也順便分享出來：

#!/bin/bash
db=MySQL
# 設(shè)置不同的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

# 循環(huán)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

# 調(diào)整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

# 恢復(fù) 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

并行構(gòu)建索引的限制

最后，再來看下并行構(gòu)建索引新特性都有哪些限制：

不支持虛擬列（virtual columns）。

不支持全文索引（full-text index）。

不支持空間索引（spatial index ）。

期待MySQL 8.0未來新版本中繼續(xù)推出更多實(shí)用的新特性吧。

延伸閱讀

Changes in MySQL 8.0.31,https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html
Configuring Parallel Threads for Online DDL Operations,https://dev.mysql.com/doc/refman/8.0/en/online-ddl-parallel-thread-configuration.html
Online DDL Memory Management,https://dev.mysql.com/doc/refman/8.0/en/online-ddl-memory-management.html

網(wǎng)頁名稱：MySQL8.0.31并行構(gòu)建索引特性管窺
本文路徑：http://fisionsoft.com.cn/article/dpodogs.html

新聞中心

并行構(gòu)建索引測試

進(jìn)一步提高索引構(gòu)建效率

并行構(gòu)建索引的限制

其他資訊