MySQL Table structure modification

In the MySQL database, DDL operations on a table that has been created, such as adding a field. During the test, the DDL operation was found to be exceptionally long. Oracle, usually only modify the data dictionary can be, the operation time is very short, blocking DML time is relatively short. MySQL database on the table for DDL operations and Oracle database is very different, it first to copy the original table to the temporary table, this period does not block select, blocking all change operations (Update,delete,insert), the temporary table DDL operations completed, delete the original table , rename the temporary table.

If a larger table makes DDL changes, such as 40G, the time of the copy is intolerable, and all DML operations are blocked, leaving the business unable to continue.

Here 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(*) |
+----------+
|　2228017 |
+----------+
1 row in set (1.78 sec)

Now make a table structure change and add a column:

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, select query operations, and an update operation:

Mysql> Select COUNT (*) from T1;
+----------+
| COUNT (*) |
+----------+
| 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 | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 1 | Nick1 | nick1@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 2 | Nick2 | nick2@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 3 | Nick3 | nick3@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 4 | Nick4 | nick4@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 5 | Nick5 | nick5@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 6 | Nick6 | nick6@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 7 | Nick7 | nick7@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 8 | Nick8 | nick8@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 9 | Nick9 | nick9@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
+------+-------+------------------+---------------------+---------------------+
Rows in Set (0.00 sec)
mysql> Update T1 set nick= ' Test_nick ' where id=1;
Query OK, 4 rows affected (43.89 sec)--This is the blocked time
Rows Matched:4 Changed:4 warnings:0

It can be seen from the above experiments that MySQL does not block a select query when DDL operations are performed on a table, but it can severely block DML operations. In addition, if you want to perform DDL operations on a table, do you want to calculate your free space enough if you have a copy operation? If your system is constantly changing the table structure, then you will have to consider this issue!