1. Problem background
MySQL version: 8.0.30
Test table data volume: 2 million
In MySQL, the R&D initially executed the following SQL statement, adding a column id3 that is allowed to be NULL to the table t_email, and setting the default value to 0:
ALTER TABLE t_email ADD COLUMN id3 INT NULL DEFAULT 0;
Then I found that I needed to modify the column to NOT NULL, so I tried to execute the following statement:
ALTER TABLE t_email MODIFY COLUMN id3 INT NOT NULL DEFAULT 0;
However, when directly modifying, it was found that ALGORITHM=INSTANT could not be used, and only ALGORITHM=INPLACE or ALGORITHM=COPY could be used, resulting in a long operation (14.67 seconds).
In subsequent tests, the method of deleting the column first and then adding the column again was successfully used. ALGORITHM=INSTANT, which significantly improved the speed.
2. Problem analysis
- Limitations of ALGORITHM=INSTANT
8.0.12 Newly added INSTANT algorithm.
MySQL's ALGORITHM=INSTANT is an algorithm that quickly modifys table structures, but it only supports specific DDL operations.
Adding and deleting columns is supported, modifying columns NULL or NOT NULL does not.
ALGORITHM=INSTANT does not support this operation when trying to modify a column from NULL to NOT NULL, so MySQL can only use ALGORITHM=INPLACE or ALGORITHM=COPY, resulting in a longer operation.
Table 17.18 Online DDL Support for Column Operations - Overhead of ALGORITHM=INPLACE and ALGORITHM=COPY
ALGORITHM=INPLACE: Modify metadata directly on the table, but it may still be necessary to reconstruct the table data.
ALGORITHM=COPY: Create a new table and copy the data, and delete the old table after completion. This method is usually slower, especially when the table data volume is large. - Optimization for deleting columns and re-adding columns
By deleting and re-adding the column, you can bypass the limitation of MODIFY COLUMN and use ALGORITHM=INSTANT directly to significantly improve performance.
3. Solution
- Original plan
Added column id3 that is allowed as NULL and set the default value to 0
mysql> alter table t_email add column id3 int null default 0;
Query OK, 0 rows affected (0.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
Modify the column's attribute to not allow NULL, others remain unchanged
mysql> alter table t_email modify column id3 int not null default 0;
Query OK, 0 rows affected (14.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
- Optimized solution
Added column id3 that is allowed as NULL and set the default value to 0.
mysql> alter table t_email add column id3 int null default 0, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.85 sec)
Records: 0 Duplicates: 0 Warnings: 0
Deleting the id3 column takes 0.98 seconds.
mysql> alter table t_email drop column id3, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.98 sec)
Records: 0 Duplicates: 0 Warnings: 0
Re-add the id3 column, change the attribute to NOT NULL, and set the default value to 0, which takes 0.68 seconds.
mysql> alter table t_email add column id3 int not null default 0, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
4. Summary
Limitation of ALGORITHM=INSTANT: No support for modifying the NULL attribute of the column. Only specific DDL operations (such as adding columns, deleting columns) are supported. Although it is not operational and does not block DML, the MDL must be obtained at the beginning and end of DDL. If there are transactions on the table in these two stages, the DDL will be blocked because it cannot obtain MDL.
Optimization idea: For operations that do not support ALGORITHM=INSTANT, you can try to bypass the restrictions by deleting columns and re-adding columns. This method has a greater performance improvement as the larger the table data.