Oracle 11.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，还需考证