- 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倍；不過其效率也并不隨著值增加而線性提高，看起來，線上生產環境需要根據實測情況進行調整，
回圈自動測驗的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/relnotes/mysql/8.0/en/news-8-0-31.htm
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
Changes in MySQL 8.0.31
Configuring Parallel Threads for Online DDL Operations
Online DDL Memory Management https://dev.mysql.com/doc/refman/8.0/en/online-ddl-memory-management.html)
Enjoy GreatSQL ??
關于 GreatSQL
GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，
相關鏈接： GreatSQL社區 Gitee GitHub Bilibili
GreatSQL社區：
歡迎來GreatSQL社區發帖提問
https://greatsql.cn/
技術交流群：
微信：掃碼添加
GreatSQL社區助手微信好友，發送驗證資訊加群，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/517702.html
標籤：其他
下一篇：動態Xml決議TSQL