innodb_ddl_buffer_size的问题
1、首先看下这个参数的意思
官网解释：
Online DDL operations that create or rebuild secondary indexes allocate temporary buffers during different phases of index creation. The innodb_ddl_buffer_size variable, introduced in MySQL 8.0.27, defines the maximum buffer size for online DDL operations. The default setting is 1048576 bytes (1 MB). The setting applies to buffers created by threads executing online DDL operations. Defining an appropriate buffer size limit avoids potential out of memory errors for online DDL operations that create or rebuild secondary indexes. The maximum buffer size per DDL thread is the maximum buffer size divided by the number of DDL threads (innodb_ddl_buffer_size/innodb_ddl_threads).
Prior to MySQL 8.0.27, innodb_sort_buffer_size variable defines the buffer size for online DDL operations that create or rebuild secondary indexes.
人话翻译：
innodb_ddl_buffer_size影响DDL的操作。
2、实操
2.1、准备一张表
[root@mydb01 ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=1 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare表信息
mysql> use test_db
mysql> desc sbtest1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| k | int | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+-----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
mysql> show create table sbtest1;
| sbtest1 | CREATE TABLE `sbtest1` (
`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`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
1 row in set (0.00 sec)2.2、创建索引的效果对比
mysql> flush tables;
mysql> create index idx_c on sbtest1(c);
Query OK, 0 rows affected (10.82 sec)
mysql> set persist innodb_ddl_buffer_size=1*1024*1024*1024;
mysql> drop index idx_c on sbtest1;
mysql> flush tables;
mysql> create index idx_c on sbtest1(c);
Query OK, 0 rows affected (6.79 sec)快了4秒
3、结论
生产环境可以适当调整这个参数的值，当然也需要配合innodb_ddl_threads一起使用
原文始发于微信公众号（库海无涯）：MySQL如何加速DDL操作
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/241316.html
