MySQL修改表結構時的拷貝問題

MySQL修改表結構時難免會出現問題，而MySQL修改表結構時拷貝的時間嚴重影響了MySQL修改表結構的操作，下面就對該問題進行分析。

mysql數據庫裡，對一個已創建的表進行DDL操作，比如說添加一個字段。在做測試時，發現ddl操作的時間特別的長。oracle裡，通常情況下只是修改數據字典就可以了，操作時間非常的短，阻塞DML的時間也比較短。mysql數據庫對表進行ddl操作跟oracle數據庫有很大的不同，它先要把原表拷貝一份到臨時表，這期間不阻塞select,阻塞所有的更改操作(update,delete,insert),對臨時表ddl操作完成，刪除原表，重命名臨時表。
如果一張比較大的表進行ddl變更，比如說40G，那拷貝的時間讓人無法忍受，並且阻塞所有的DML操作,讓業務無法繼續。

以下是測試過程：

[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(*) |  
+———-+  
| 2228017 |  
+———-+  
1 row in set (1.78 sec)  
[/coolcode]  
現在對它進行表結構變更，增加一列：  
[coolcode]  
mysql> alter table t1 add(tel varchar(20));  
Query OK, 2304923 rows affected (41.03 sec)  
Records: 2304923 Duplicates: 0 Warnings: 0  
[/coolcode]  
在上述表結構變更過程中，啟動另外一個會話，進行select查詢操作和一個更新操作：  
[coolcode]  
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 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 1 | nick1 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 2 | nick2 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 3 | nick3 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 4 | nick4 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 5 | nick5 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 6 | nick6 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 7 | nick7 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 8 | nick8 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |  
| 9 | nick9 | [email protected] | 2008-03-14 00:00:00 | 2008-03-14 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) –這裡是阻塞的時間  
Rows matched: 4 Changed: 4 Warnings: 0  
[/coolcode]  
 

通過以上實驗可以看出，對表進行ddl操作時，mysql並不阻塞select查詢，但會嚴重阻塞dml操作。另外，如果你要對表進行ddl操作，由於有一個拷貝操作，你要計算好你的可用空間夠不夠？如果你的系統經常要進行MySQL修改表結構，那麼你將不得不要考慮此問題！
 

10大常用MySQL命令行

通過MySQL命令行增加新用戶

通過索引優化MySQL語句的實例

MySQL權限表的介紹

授予MySQL用戶權限的實例