Oracle11.2.0.3和MySQL5.6DDL比较

MySQL 5.6后大大增强了ONLINE DDL功能，典型就是上面的2个方面， 首先ADD COLUMN和DROP COLUMN不再会堵塞DML操作，同时建立索引

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);