In MySQL5.5 and previous versions, performing an alter operation on a large table (over millions of records) in a running production environment is a difficult task. Because the table and lock table will be rebuilt, affecting the user's use. It is therefore important to know when the alter operation will end. Even when the CREATE index is executed. If Fast_index_creation is enabled, the table is not rebuilt, but the table is still locked. Fast_index_creation features are introduced in the MySQL5.5 and later versions. This feature can also be used in MySQL5.1 if InnoDB plugin is used. The Online DDL feature has been introduced since MySQL5.6 began. He has enhanced many kinds of ALTER TABLE operations to avoid copying tables and lock tables, allowing the Select,insert,update,delete statement to run while running the alter operation. So in the latest version, we can suppress file copies and locking by using the algorithm and lock options. But even in MySQL5.6, there are still some alter operations that require rebuilding tables, such as adding/removing columns, adding/Removing primary keys, changing data types, and so on.The MySQL5.6 online DDL status profile is as follows (yes* and no* indicate that the results depend on some additional conditions):
| Perform actions |
Allow Algorithm=inplace |
Whether to copy tables |
Allow concurrent DML |
Allow concurrent queries |
Notes and Precautions |
| Create Indexadd Index |
Yes* |
no* |
Yes |
Yes |
For full-text indexing, there are some limitations, specifically the next line. Currently, this operation is not performed in situ and requires copying tables. |
| Add Fulltext Index |
Yes |
no* |
No |
Yes |
Creating the first full-text index involves copying tables unless you have used the fts_doc_id column. Subsequent full-text indexes are executed in situ. |
| Drop Index |
Yes |
No |
Yes |
Yes |
|
| Optimize table |
Yes |
Yes |
Yes |
Yes |
Use Algorithm=inplace in MySQL5.6.17. If you set old_alter_table=1 or use the mysqld--skip-new option, use Algorithm=copy. Algorithm=inplace does not apply if the table uses full-text indexing |
| Set default value for column |
Yes |
No |
Yes |
Yes |
Modifying a. frm file, not involving data files |
| Change auto-increment value |
Yes |
No |
Yes |
Yes |
Modify a value stored in memory without modifying the data file |
| Add FOREIGN KEY constraint |
Yes* |
no* |
Yes |
Yes |
Disable foreign_key_checks, you can avoid copying tables |
| Drop Forgien Key constraing |
Yes |
No |
Yes |
Yes |
Foreign_key_checks can be disabled or turned on |
| Rename Column |
Yes* |
no* |
Yes* |
Yes |
Allow concurrent DML, keep the same data type, change only field names |
| Add Column |
Yes |
Yes |
Yes* |
Yes |
DML operations are not allowed when the Auto-increment field is added. Although Algorithm=inplace can be allowed, the data will be restructured at a more expensive cost. |
| Drop Column |
Yes |
Yes |
Yes |
Yes |
Although Algorithm=inplace can be allowed, the data will be restructured at a more expensive cost. |
| Reorder columns |
Yes |
Yes |
Yes |
Yes |
Although Algorithm=inplace can be allowed, the data will be restructured at a more expensive cost. |
| Change Row_formatproperty |
Yes |
Yes |
Yes |
Yes |
Although Algorithm=inplace can be allowed, the data will be restructured at a more expensive cost. |
| Change Key_block_size Property |
Yes |
Yes |
Yes |
Yes |
Although Algorithm=inplace can be allowed, the data will be restructured at a more expensive cost. |
| Make column null |
Yes |
Yes |
Yes |
Yes |
Although Algorithm=inplace can be allowed, the data will be restructured at a more expensive cost. |
| Make Cplumn NOT NULL |
Yes* |
Yes |
Yes |
Yes |
When Sql_mode is strict_all_tables, execution fails if the column being executed contains NULL. Although Algorithm=inplace can be allowed, the data will be restructured at a more expensive cost. |
| Change data type of column |
No |
Yes |
Yes |
Yes |
|
| Add primary Key |
Yes* |
Yes |
Yes |
Yes |
Although the algorithm=inplace can be allowed, but the data to be restructured, the cost is more expensive. Algorithm=inplace is not allowed if the column must be converted to a non-empty condition. |
| Drop primary key and add other |
Yes |
Yes |
Yes |
Yes |
Algorithm=inplace is allowed when a new primary key is added to the same ALTER table. The data is to be restructured, so the cost is more expensive. |
| Drop PRIMARY Key |
No |
Yes |
No |
Yes |
Deleting a primary key but not adding a new primary key is limited |
| Convert Character Set |
No |
Yes |
No |
Yes |
If the new character encoding is different, the table will be rebuilt |
| Specify character Set |
No |
Yes |
No |
Yes |
If the new character encoding is different, the table will be rebuilt |
| Rebulid with FORCE option |
Yes |
Yes |
Yes |
Yes |
Use Algorithm=inplace in MySQL5.6.17. If you set old_alter_table=1 or use the mysqld--skip-new option, use Algorithm=copy. Algorithm=inplace does not apply if the table uses full-text indexing |
| Rebulid with "null" ALTER TABLE ... Engine=innodb |
Yes |
Yes |
Yes |
Yes |
Use Algorithm=inplace in MySQL5.6.17. If you set old_alter_table=1 or use the mysqld--skip-new option, use Algorithm=copy. Algorithm=inplace does not apply if the table uses full-text indexing |
below to do the test, by Algorithm=inplace and algorithm=copy two different algorithms to delete and add a field, respectively, Algorithm=inplace (online DDL) You can perform DML operations when you delete and add fields, but the alter time is longer because the data needs to be reorganized. In the case of Algorithm=copy, the DML operation waits for metadata lock during execution, but the alter time is much shorter than algorithm=inplace because the table and the lock table are to be copied. 1.algorithm=inplace mode Delete field test: session 1:13:55:11 pe> ALTER TABLE product_2 algorithm=inplace,drop column ext_1; Query OK, 0 rows affected (42.18 sec) records:0 duplicates:0 warnings:0 13:56:20 pe> session 2:13:55: pe> Update product_2 set category_id=5312 where Id=621; query OK, 1 row affected (2.16 sec) Rows matched:1 Chan Ged:1 warnings:0 13:55:45 pe> INSERT INTO product_2 (id,product_code,product_cname,product_ename,category_id VALUES (2000001, ' 000222222 ', ' cname ', ' ename ', 100); Query OK, 1 row affected (0.19 sec) 13:55:45 pe> Delete from product_2 where id=2000001; Query OK, 1 row affected (2.57 sec) 2.algorithm=inplace mode add field test: session 1:13:59:05 pe> ALTER TABLE PR Oduct_2 ALGORITHM=INPLace,add column Ext_1 char (10); Query OK, 0 rows affected (42.98 sec) records:0 duplicates:0 warnings:0 13:59:51 pe> session 2:13:59: pe> Update product_2 set category_id=5312 where Id=621; query OK, 0 rows affected (2.37 sec) rows Matched:1 Cha nged:0 warnings:0 13:59:30 pe> INSERT INTO product_2 (id,product_code,product_cname,product_ename,category_ ID) VALUES (2000001, ' 000222222 ', ' cname ', ' ename ', 100); Query OK, 1 row Affected (0.00 sec) 13:59:30 pe> Delete from product_2 where id=2000001; Query OK, 1 row affected (2.50 sec) 13:59:32 pe> 3.algorithm=copy mode Delete field test: Session1 : 13:59:32 pe> ALTER TABLE product_2 algorithm=copy,drop column ext_1; Query OK, 1999999 rows Affected (20.91 sec) records:1999999 duplicates:0 warnings:0 session2:14:03:15 pe> Update Product_2 set category_id=5312 where Id=621; query OK, 0 rows affected (16.80 sec) rows matched:1 changed:0 Warning S:0 14:03:32 pe> Insert Into Product_2 (id,product_code,product_cname,product_ename,category_id) VALUES (2000001, ' 000222222 ', ' cname ', ' Ename ', 100); Query OK, 1 row Affected (0.00 sec) 14:03:32 pe> Delete from product_2 where id=2000001; Query OK, 1 row affected (1.61 sec) 14:03:33 pe> 4.algorithm=copy mode add field test: session1:14:06:40 pe> ALTER TABLE product_2 algorithm=copy,add column ext_1 char (10); Query OK, 1999999 rows Affected (21.77 sec) records:1999999 duplicates:0 warnings:0 session2: 14:03:33 pe> Update product_2 set category_id=5312 where Id=621; query OK, 0 rows affected (11.90 sec) rows matched:1 changed:0 W arnings:0 14:07:28 pe> INSERT INTO product_2 (id,product_code,product_cname,product_ename,category_id) VALUES (2000001, ' 000222222 ', ' cname ', ' ename ', 100); Query OK, 1 row Affected (0.00 sec) 14:07:28 pe> Delete from product_2 where id=2000001; Query OK, 1 row affected (1.71 sec) 14:07:29 pe> processlist:987835 root localhost pE Query 7 waiting for table metadata lock update product_2 set category_id=5312 where Id=621 mysql 5.6 Alter Ta Ble common Scenario test: mysql 5.6 's ALTER TABLE has two different algorithms for Algorithm=inplace and Algorithm=copy. Using Algorithm=inplace allows you to execute DML statements concurrently while executing alterde. But the cost is also relatively large, in this mode, the alter time is about twice times the algorithm=copy algorithm. Test as follows: In 5.6, Alter table prefers to use the algorithm=inplace algorithm. With fields, indexes, and primary keys, the default value of the field setting allows DML operations to be performed at the same time as the alter, while changing the field type locks the table. 1. Add field session1:17:03:40 pe> ALTER TABLE product_2 add column ext_1 char (10); Query OK, 0 rows affected (42.52 sec) records:0 duplicates:0 warnings:0 17:04:31 Pe> session2:17:03:5 0 pe> Update product_2 set category_id=5312 where Id=621; query OK, 0 rows affected (2.09 sec) rows Matched:1 Chan ged:0 warnings:0 17:03:54 pe> INSERT INTO product_2 (id,product_code,product_cname,product_ename,category_id VALUES (2000001, ' 000222222 ', ' cname ', ' ename ', 100); Query OK, 1 row affected (0.15 sec) 17:03:54 pe> Delete from product_2 where id=2000001; Query OK,1 Row affected (1.69 sec) 17:03:56 pe> 2. Add index session1:17:12:34 pe> ALTER TABLE product Add index Idx_1 (category_id); Query OK, 0 rows affected (49.38 sec) records:0 duplicates:0 warnings:0 17:13:38 Pe> session2:17:12:5 1 pe> Update product set category_id=5312 where id=621; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0 17:12:52 pe> insert INTO product (Id,prod UCT_CODE,PRODUCT_CNAME,PRODUCT_ENAME,CATEGORY_ID) VALUES (20000001, ' 000222222 ', ' cname ', ' ename ', 100); Query OK, 1 row affected (0.01 sec) 17:12:52 pe> Delete from product where id=2000001; Query OK, 1 row affected (0.05 sec) 17:12:53 pe> 3. Add primary key session1:17:38:15 pe> ALTER TABLE Produ Ct_2 Add primary key (ID); Query OK, 0 rows affected (47.42 sec) records:0 duplicates:0 warnings:0 17:39:48 pe> session2:17:39:03 PE&G T Update product_2 set category_id=5312 where Id=621; query OK, 0 rows affected (2. sec) Rows matched:1 changed:0 warnings:0 17:39:07 pe> insert INTO product_2 (id,product_code,product_cname,p RODUCT_ENAME,CATEGORY_ID) VALUES (20000001, ' 000222222 ', ' cname ', ' ename ', 100); Query OK, 1 row Affected (0.00 sec) 17:39:07 pe> Delete from product_2 where id=2000001; Query OK, 0 rows affected (1.66 sec) 17:39:09 pe> 4. Set the default value, change only the. frm file, and soon 17:20:48 pe> alter Table Product_2 Modify Ext_1 char (TEN) default ' Test '; Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:0 5. Change the field type, lock table session1:17:22:22 pe> A Lter table product_2 Modify ext_1 datetime; Query OK, 1999999 rows Affected (24.90 sec) records:1999999 duplicates:0 warnings:0 17:28:34 pe> sess Ion2:17:28:12 pe> Update product_2 set category_id=5312 where id=621; Query OK, 0 rows affected (22.97 sec) rows matched:1 changed:0 warnings:0 17:28:37 pe>
MySQL5.6 ALTER TABLE analysis and testing
