向書本學習,還要向?qū)嵺`學習、向生活學習。消化已有知識,
而且要力求有所發(fā)現(xiàn)、有所發(fā)明、有所創(chuàng)造
2019/4/7 11:21:33
mysql數(shù)據(jù)庫里,對一個已創(chuàng)建的表進行DDL操作,比如說添加一個字段。在做測試時,發(fā)現(xiàn)ddl操作的時間特別的長。oracle里,通常情況下只是修改數(shù)據(jù)字典就可以了,操作時間非常的短,阻塞DML的時間也比較短。mysql數(shù)據(jù)庫對表進行ddl操作跟oracle數(shù)據(jù)庫有很大的不同,它先要把原表拷貝一份到臨時表,這期間不阻塞select,阻塞所有的更改操作(update,delete,insert),對臨時表ddl操作完成,刪除原表,重命名臨時表。
如果一張比較大的表進行ddl變更,比如說40G,那拷貝的時間讓人無法忍受,并且阻塞所有的DML操作,讓業(yè)務無法繼續(xù)。
以下是測試過程:
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)
現(xiàn)在對它進行表結(jié)構(gòu)變更,增加一列:
mysql>altertablet1add(telvarchar(20));
QueryOK,2304923rowsaffected(41.03sec)
Records:2304923Duplicates:0Warnings:0
在上述表結(jié)構(gòu)變更過程中,啟動另外一個會話,進行select查詢操作和一個更新操作:
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
通過以上實驗可以看出,對表進行ddl操作時,mysql并不阻塞select查詢,但會嚴重阻塞dml操作。另外,如果你要對表進行ddl操作,由于有一個拷貝操作,你要計算好你的可用空間夠不夠?如果你的系統(tǒng)經(jīng)常要進行表結(jié)構(gòu)變更,那么你將不得不要考慮此問題!
深圳市南山區(qū)南山街道南海大道西桂廟路北陽光華藝大廈1棟4F、4G-04
咨詢電話:136 8237 6272
大客戶咨詢:139 0290 5075
業(yè)務QQ:195006118
技術QQ:179981967