Summary of MySQL 8.0 Online DDL Quick Column Addition

Problem Description

A few days ago, a colleague asked me a question: What is the reason for Business A to migrate from MySQL to MongoDB?

To be honest, this question is not easy to answer. Why do we need to migrate? We must have encountered some bottleneck, which may be the amount of data or the type of data. So I consulted the business and finally got the answer: some tables in this business need to add fields frequently. The cost of adding fields in MongoDB is almost zero, while the cost of adding fields in lower versions of MySQL is still quite high.

So what are the commonly used methods for adding MySQL fields? Here I will briefly list them:

1. Percona's pt-osc tool

2. GitHub open source project gh-ost tool

3. MySQL native Online DDL

Historical method of adding columns in MySQL Online DDL

01 Copy method

Method for adding columns in MySQL version 5.5 and earlier: Copy

Its execution diagram is as follows:

We have an original table A, which contains only one field, which contains records 1, 2, 4, and 6. When we use the Copy algorithm to add columns:

1. Create a new table tmp-A, which contains 2 fields.

2. Then we copy all the data in table A row by row to the new table tmp-A.

3. Then use tmp-A table and A table to exchange,

In this way, our new table contains 2 fields. Also note that the data records in the new table are more compact than the original table. The original table may have a hole in the middle of the table, or space fragmentation, because records 3 and 5 were deleted.

As you can see, the Copy algorithm needs to copy the data once, and requires additional storage space to store the temporary table tmp-A. In addition, during the data copying process, the write operation of Table A will be lost, that is, Table A cannot have any data updated during the alter table process. This can be a fatal flaw.

02 Inplace Method

MySQL 5.6 introduced Online DDL, which changed the above process to the following:

Its process is somewhat different from the Copy algorithm above:

1. During the Online DDL process, the B+ tree is extracted from table A and stored in an intermediate file tmp-file instead of the intermediate table tmp-A

2. During the execution of step 1, all writes to table A will be recorded in the row log

3. After step 1 is completed, apply all row logs to tmp-file to obtain a data file with the same data as table A.

4. Use the data file tmp-file to replace the data file in table A.

During this process, due to the existence of row log, table A can be added, deleted, modified and checked during the entire process, because these operations will not be lost. This is why this process is called Online DDL.

In addition, it is necessary to explain here that the tmp-A temporary table generated in the Copy algorithm is created at the server level, while the tmp-file in the above Online DDL operation is generated inside the plug-in storage engine Innodb. We call this change operation completed inside Innodb Inplace (which means in place in Chinese), which means that the data does not need to be moved to the "temporary table at the server level".

Instant method introduced in MySQL 8.0.12

MySQL version 8.0.12 introduced the Instant method, which makes adding columns easier. When the instant algorithm adds a column, it is no longer necessary to rebuild the entire table. Instead, it only needs to record the basic information of the newly added column in the table metadata.

Let's look at its advantages. First, we create a table t1 and insert 260,000 records. Then we add columns col_1, col_2, and col_3 respectively, and specify the algorithms for adding columns as copy, inplace, and instant. The results are as follows:

 [test] 23:42:45> select count(1) from t1;
 +----------+
 | count(1) |
 +----------+
 |262144|
 +----------+
 1 row in set (0.06 sec)
 
Solution 1: copy
[test] 23:43:29> alter table t1 add col_1 int,algorithm=copy;  
Query OK, 262144 rows affected (1.48 sec)
Records: 262144 Duplicates: 0 Warnings: 0

Solution 2: inplace
[test] 23:43:46> alter table t1 add col_2 int,algorithm=inplace; 
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0

Solution 3: instant
[test] 23:44:08> alter table t1 add col_3 int,algorithm=instant; 
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

m5480:[email protected] [test] 23:44:14> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  `col_1` int DEFAULT NULL,
  `col_2` int DEFAULT NULL,
  `col_3` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sco` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=458730 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

It is not difficult to see from the results that the execution time is:

copy > inplace > instant

At the same time, the number of rows affected by the copy algorithm is the entire table, while the number of rows affected by the inplace and instant algorithms is 0, indicating that they are online DDL operations.

Finally, we can also view the information of the instant column through the following method:

[test] 23:53:01> SELECT * FROM information_schema.innodb_tables where name like 'test/t1'\G
 *************************** 1. row ***************************
      TABLE_ID: 1079
          NAME: test/t1
          FLAG: 33
        N_COLS: 10
         SPACE: 22
    ROW_FORMAT: Dynamic
 ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 6
1 row in set (0.00 sec)

As you can see, the instant column number of the test.t1 table is 6, which means it is the 7th column of the table (column numbering starts from 0).

Of course, the instant algorithm does not support deleting ordinary columns, cannot set the order of columns, and has some other limitations. For details, please refer to the official document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

But these limitations do not prevent it from being an excellent DDL function. I believe that with the continuous iteration of MySQL versions, in later versions, more change operations will be able to use efficient algorithms such as instant.

The above is the detailed summary of MySQL 8.0 Online DDL fast column addition. For more information about MySQL DDL fast column addition, please pay attention to other related articles on 123WORDPRESS.COM!