Copying a table structure in MySQL

MySQL may encounter problems when modifying the table structure. The copy time of MySQL when modifying the table structure seriously affects the operation of MySQL when modifying the table structure. The problem is analyzed below.

In the mysql database, 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:

 
 

  
  
[Coolcode]
  
  
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)
  
  
[/Coolcode]
  
  
Add a column to change the table structure:
  
  
[Coolcode]
  
  
Mysql> alter table t1 add (tel varchar (20 ));
  
  
Query OK, 2304923 rows affected (41.03 sec)
  
  
Records: 2304923 Duplicates: 0 Warnings: 0
  
  
[/Coolcode]
  
  
During the above table structure change process, start another session to perform select query and update operations:
  
  
[Coolcode]
  
  
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)-blocking time here
  
  
Rows matched: 4 Changed: 4 Warnings: 0
  
  
[/Coolcode]
  
  
 
 
 

From the above experiment, we can see that mysql does not block select queries when performing ddl operations on tables, but seriously blocks dml operations. In addition, if you want to perform ddl operations on the table, because of a copy operation, do you have to calculate whether your available space is sufficient? If your system often needs to modify the table structure in MySQL, you have to consider this issue!
 

10 common MySQL command lines

Add new users through MySQL Command Line

Optimize MySQL statement instances through Indexes

MySQL permission table Introduction

Instances that grant MySQL user permissions