Modify the table structure of MySQL _ MySQL

Modify the table structure of MySQL in the bitsCN.com mysql database to perform DDL operations on a created table, such as adding a field. During the test, it is found that the ddl operation takes a very long time. In oracle, you can only modify the data dictionary. the operation time is very short, and the blocking time of DML is relatively short. The ddl operation on a mysql database table is very different from that on an oracle database. it first needs to copy the original table to a temporary table, which does not block the select operation during this period, all changes (update, delete, insert) are blocked. ddl operations on the temporary table are completed. The original table is deleted and the temporary table is renamed.
If a large table is changed to ddl, for example, 40 GB, the copy time is intolerable and all DML operations are blocked, making the business unable to continue.
The following is the test process:
Mysql> desc t1;
+ -------------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------- + ------------- + ------ + ----- + --------- + ------- +
| Id | int (11) | YES | MUL | NULL |
| Nick | varchar (32) | YES | NULL |
| Email | varchar (32) | YES | NULL |
| Gmt_create | datetime | YES | NULL |
| Gmt_modified | datetime | YES | NULL |
+ -------------- + ------------- + ------ + ----- + --------- + ------- +
Mysql> select count (*) from t1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 2228017 |
+ ---------- +
1 row in set (1.78 sec)
Add a column to change the table structure:
Mysql> alter table t1 add (tel varchar (20 ));
Query OK, 2304923 rows affected (41.03 sec)
Records: 2304923 Duplicates: 0 Warnings: 0
During the above table structure change process, start another session to perform select query and update operations:
Mysql> select count (*) from t1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 2304923 |
+ ---------- +
1 row in set (2.10 sec)
Mysql> select * from t1 limit 10;
+ ------ + ------- + ---------------- + ----------------------- + --------------------- +
| Id | nick | email | gmt_create | gmt_modified |
+ ------ + ------- + ---------------- + ----------------------- + --------------------- +
| 0 | nick0 | nick0@taobao.com | 00:00:00 | 00:00:00 |
| 1 | nick1 | nick1@taobao.com | 00:00:00 | 00:00:00 |
| 2 | nick2 | nick2@taobao.com | 00:00:00 | 00:00:00 |
| 3 | nick3 | nick3@taobao.com | 00:00:00 | 00:00:00 |
| 4 | nick4 | nick4@taobao.com | 00:00:00 | 00:00:00 |
| 5 | nick5 | nick5@taobao.com | 00:00:00 | 00:00:00 |
| 6 | nick6 | nick6@taobao.com | 00:00:00 | 00:00:00 |
| 7 | nick7 | nick7@taobao.com | 00:00:00 | 00:00:00 |
| 8 | nick8 | nick8@taobao.com | 00:00:00 | 00:00:00 |
| 9 | nick9 | nick9@taobao.com | 00:00:00 | 00:00:00 |
+ ------ + ------- + ---------------- + ----------------------- + --------------------- +
10 rows in set (0.00 sec)
Mysql> update t1 set nick = 'Test _ nick 'where id = 1;
Query OK, 4 rows affected (43.89 sec) -- Here is the blocking time
Rows matched: 4 Changed: 4 Warnings: 0bitsCN.com