ORACLE11.2.0.3和MYSQL5.6 DDL比較

1、CREATE INDEX,DROP INDEX
2、ADD COLUMN,DROP COLUMN


MYSQL 5.6後大大增強了ONLINE DDL功能，典型就是上面的2個方面，
首先ADD COLUMN和DROP COLUMN不再會堵塞DML操作，同時建立索引
方面預設使用LOCK=NONE的模式而不會堵塞DML，
LOCK有4個模式 預設NONE，可選SHARED和exclusive以及DEFAULT，
在LOCK=NONE模式下這種情況和ORACLE的CREATE INDEX ONLINE極為相識
在5.6.19上測試就連如果CREATE INDEX LOCK=NONE的時候有一個事物沒有
提交或者正在進行，也是不能建立索引的，ORACLE也是一樣的，換句話說
ORACLE和MYSQL都會在建立索引初期試圖或者一個X獨佔的鎖，一旦獲取後
立即降級，但是MYSQL的這個等待過程會堵塞SELECT，我們知道ORACLE裡面
任何情況下是不會堵塞SELECT的。下面分別說明；


1、CREATE INDEX （線上） 在有事物沒有提交的情況下


ORACLE 11.2.0.3測試CREATE INDEX ONLINE
 首先在表中插入一條資料，不要提交
 insert into testti select * from testti where rownum<=1;
 然後另外開啟一個會話進行
 create index test_in on testti(username) online;
 此時CREATE INDEX 被堵塞，檢視V$LOCK
        SID TYPE      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ----------
        48 TX            0          4          0
        48 TM            2          0          0
        48 TM            4          0          0
        48 TX            6          0          0
        53 TM            3          0          0
        53 TX            6          0          1
可以看到SID 53堵塞了SID 48，SID 48試圖獲取MODE 4的鎖的時候被一個MODE 6的TX鎖堵塞
但是其他會話SELECT 語句是不會堵塞的


MYSQL 5.6.19進行同樣的測試CREATE INDEX LOCK=NONE
 首先在表中刪除一條資料，不要提交
 begin;
 mysql> delete from testno where i=122;
Query OK, 1 row affected (0.24 sec)
 然後另外開啟一個會話
mysql> create index test_ind on testno(j) lock=none;
 此時堵塞
 然後在開啟一個會話
  select * from testno limit 1;
  此時SELECT 被堵塞
  最後檢視INNODB STATUS來判斷
  TRANSACTIONS
------------
Trx id counter 462509
Purge done for trx's n:o < 462509 undo n:o < 0 state: running but idle
History list length 434
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 4, OS thread handle 0x40b4c940, query id 275 localhost root System lock
show engine innodb status
---TRANSACTION 462459, not started
MySQL thread id 3, OS thread handle 0x40b0b940, query id 274 localhost root Waiting for table metadata lock
select * from testno limit 1
---TRANSACTION 462471, not started
MySQL thread id 2, OS thread handle 0x40671940, query id 273 localhost root Waiting for table metadata lock
create index test_ind on testno(j) lock=none
---TRANSACTION 462492, ACTIVE 100 sec inserting
mysql tables in use 2, locked 2
7016 lock struct(s), heap size 800296, 836672 row lock(s), undo log entries 322558
MySQL thread id 1, OS thread handle 0x40430940, query id 272 localhost root Sending data
insert into testno select * from testno


可以清楚看到locked 2


由此我們看出在CREATE INDEX上ORACLE和MYSQL如果在有本表未提交的事物的時候都會出現堵塞


 index (re)build online cleanup  


2、CREATE INDEX （線上）在沒有事物的情況下


ORACLE：
會話1 create index test_in on testti(username) online;
會話2 可以進行任何DML 沒有問題


但是ORACLE 會受到CREATE INDEX ONLINE期間事物的影響，雖然不影響DML，但是建立期間的
事物必須提交後，整個建立過程才會完成。


MYSQL：
會話1 create index test_ind on testno(j) lock=none;
會話2 可以進行任何DML 沒有問題




3、DROP INDEX


關於DROP INDEX 如果，有事物正在訪問本表ORACLE和MYSQL基本採用同樣方式就是不讓你刪除
ORACLE 報錯
drop index test_in
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
MYSQL則是等待METADATA


如果沒有事物正在訪問，進行刪除索引2種資料庫都是非常快的原因基本一致就是他只是簡單的刪除
資料字典資訊，然後標記空間為可用，並非真正的刪除資料。
mysql> drop index test_ind on testno;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


SQL> drop index test_in;
Index dropped
0.17秒






4、ADD COLUMN




 


在當前有事物的情況下，
首先
 insert into testti select * from testti where rownum<=1;
另外開啟會話
 alter table testti add test varchar2(20);


ORACLE 11G等待事物事物結束，等待事件為：
 enq: TX - row lock contention
 注意11G中有引數ddl_lock_timeout，但是此操作並不是受此引數影響
但這個操作在10G是報
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


但是如果
alter table testti add test varchar2(20) default 'test1'
10G 11G都是報錯
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




MYSQL同樣的機制
在有事物的情況ADD COL也不能完成 INNODB STATUS 如下：
Trx id counter 462664
Purge done for trx's n:o < 462661 undo n:o < 0 state: running but idle
History list length 474
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 13, OS thread handle 0x40b4c940, query id 446 localhost root System lock
show engine innodb status
---TRANSACTION 462658, not started
MySQL thread id 2, OS thread handle 0x40671940, query id 445 localhost root Waiting for table metadata lock
alter table testno add m int(10)
---TRANSACTION 462663, ACTIVE 45 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x40430940, query id 443 localhost root cleaning up




在沒有事物的情況下：


ORACLE如果不指定預設值，增加欄位非常快，機會瞬間完成，所以不存在堵塞的情況。
如果是
alter table testti add test varchar2(20) default 'test1';
然後另外開啟一個會話
insert into testti select * from testti where rownum<=1;


ORACLE等待時間為：
library cache lock




MYSQL在這方面貌似做得更好，ADD COLUMN不會堵塞任何DDL，他會把期間的操作放入
innodb_online_alter_log_max_size ，等待完成後同步回來，實際上ORACLE和MYSQL
在CREATE INDEX ONLINE的情況下都是使用了這個機制，都是將修改放入一個臨時的
區域完成後進行同步來完成一致性，ORACLE是將臨時記錄記錄到SYS_JOURNAL_****表裡面   
只是ORACLE在ADD COL的情況下應該是沒有使用的，因為如果沒有預設值ORACLE的
增加COL是非常快的，但是MYSQL有無預設值是一樣的。
會話1：
mysql> alter table testno add m int(10) default 0;
Query OK, 0 rows affected (43.97 sec)
Records: 0  Duplicates: 0  Warnings: 0


會話2：
mysql> insert into testno values(122,'test1');
Query OK, 1 row affected (0.00 sec)
插入不受影響。




5、drop col


drop col ORACLE和MYSQL都和ADD COL帶預設值的方式沒有太多的區別，ORACLE在沒有事物的情況
下任然會堵塞會話等待為
enq: TM - contention
MYSQL則不會，但是MYSQL可能的需要重組表類似ORACLE的MOVE TABLE，還需考證