How to quickly add columns in MySQL 8.0

Preface:

I heard a long time ago that MySQL 8.0 supports fast column addition, which can add fields to large tables in seconds. The author also has an 8.0 environment locally, but has not tested it. In this article, let’s take a look at how to quickly add columns in MySQL 8.0.

1. Understand the background information

Changing the table structure is one of the more common requirements in the business operation process. In the MySQL environment, you can use the Alter statement to complete these operations. The operations corresponding to these Alter statements are usually also called DDL operations. Generally, DDL operations on large tables will have a significant impact on the business and need to be performed when the business is idle or during maintenance. MySQL 5.7 supports Online DDL. Most DDLs do not affect the reading and writing of tables, but they still consume a lot of time, occupy additional disk space, and cause master-slave delays. Therefore, large table DDL is still a headache for DBAs.

I heard that MySQL 8.0 solves this headache for DBAs, so let’s take a closer look. The easiest way to learn about new features is to consult the official documentation. According to the official documentation, the Instant Add Column feature was introduced in MySQL 8.0.12 and was contributed by the Tencent Games DBA team. Note that this feature is only available for InnoDB tables.

2. Quick column addition test

Quick column addition uses an instant algorithm, so that when adding a column, there is no need to rebuild the entire table. You only need to record the basic information of the new column in the table metadata. Adding ALGORITHM=INSTANT after the alter statement means using the instant algorithm. If it is not explicitly specified, operations that support the instant algorithm will be used by default. If ALGORITHM=INSTANT is specified but is not supported, the operation fails immediately with an error.

Regarding the DDL operation of columns, whether algorithms such as instant are supported, the official document provides a table, which is now organized as follows. Asterisks indicate that not all are supported and there are dependencies.

The most widely used instant algorithm is to add columns. As you can see, there are some limitations when using this algorithm. Some of the limitations are as follows:

• If the alter statement contains add column and other operations, and one of the operations does not support the instant algorithm, the alter statement will report an error and all operations will not be executed.
• Columns can only be added sequentially. Adding columns only supports adding columns at the end, but not adding columns in the middle of existing columns.
• Compressed tables are not supported, that is, the table row format cannot be COMPRESSED.
• Tables containing full-text indexes are not supported.
• Temporary tables are not supported.
• Tables created in data dictionary tablespaces are not supported.
  

It is better to test it in practice than to say more. Let's take version 8.0.19 as an example to verify it in practice:

# Use sysbench to generate a 10 million watt tablemysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+

# Add a column without a default valuemysql> alter table sbtest1 add column col1 varchar(20), algorithm=instant;
Query OK, 0 rows affected (0.63 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Add a column with a default value mysql> alter table sbtest1 add column create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', algorithm=instant;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Do not explicitly specify the instant algorithm mysql> alter table sbtest1 add column col2 varchar(20);
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Set the default value of the column mysql> alter table sbtest1 alter column col1 set default 'sql',algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Specify the In Place algorithm to add columns (this algorithm is used in version 5.7)
mysql> alter table sbtest1 add column col_inplace varchar(20),algorithm=inplace;
Query OK, 0 rows affected (1 min 23.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

Through the above tests, we can find that adding columns using the instant algorithm is basically completed within 1 second. For large tables, this speed is very fast and the business is basically unaware of it. When using the inplace algorithm in version 5.7, the time to add a column rises to several minutes. By comparison, the quick column adding function of version 8.0 is indeed very practical!

Summarize:

Although there are some limitations for adding columns quickly, and the instant algorithm is only applicable to some DDL operations, this new feature of 8.0 is exciting enough and largely solves the problem of adding fields to large tables. Through this article, I hope you can understand this new feature. If you want to upgrade to 8.0, you can start to do it accurately.

The above is the details of how to quickly add columns in MySQL 8.0. For more information about how to quickly add columns in MySQL 8.0, please pay attention to other related articles on 123WORDPRESS.COM!