InnoDB联机修改表对象结构

联机修改表对象结构：

在MySQL5.6之前

1.创建一个结构与原表对象完全相同的临时表(隐式操作，该对象用户不可见)，并将该表的结构修改为期望的结构

2.锁定原表，只许查询，不许修改

3.将原表数据复制到新创建的临时表，类似insert into new_tb select * from old_tb;

4.将原表重命名，新创建的临时表名称修改为正式表名，之后释放锁定，删除原表

在MySQL5.6以后，联机DDL修改InnoDB表提供有限支持

就地进行In-Place，表示修改操作可以直接在该表对象上执行

复制表Copies Tables，表示需要复制整个表才能执行修改操作

用户可以通过ALTER TABLE语句中的LOCK和ALGORITHM两个子句，来明确控制联机DDL时的操作行为。LOCK子句对于表并行读控制的微调比较有效，而ALGORITHM子句则对于操作时的性能和操作策略有较大影响

LOCK有4个选项值：

DEFAULT：默认处理策略，等同于不指定LOCK子句

NONE：不使用锁定策略，其他会话既能读也能写

SHARED：采取共享策略，其他会话可读但不可写

EXCLUSIVE：采取排他锁定，其他会话既不能读也不能写

ALGORITHM有3个选项值：

DEFAULT：相当于不指定ALGORITHM子句

INPLACE：如果支持就直接修改，不支持就报错

COPY：不管是否支持就地修改，都采取将表对象中数据新复制一份的方式修改

如果希望并发粒度最高，那么就要指定LOCK=NONE(可读可写)，若希望操作成本最低，最好指定ALGORITHM=INPLACE(直接对对象进行操作，涉及读写的数据量最小)

 

联机DDL测试：

登录到mysql，执行对象创建脚本

use hugcdb;

set autocommit=0;

create table t_idb_big as select * from information_schema.columns;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

alter table t_idb_big add id int unsigned not null primary key auto_increment;

select count(*) from t_idb_big;

1.测试增/删索引

使用INPLACE方式效率非常高

du –k /data/mysqldata/3306/data/hugcdb/t_idb_big.ibd

alter table t_idb_big add index ind_data_type (data_type),algorithm=inplace;

du –k /data/mysqldata/3306/data/hugcdb/t_idb_big.ibd

alter table t_idb_big drop index ind_data_type,algorithm=inplace;

使用COPY方式效率较低

create index ind_data_type on t_idb_big(data_type) alogorithm=copy;

du –k /data/mysqldata/3306/data/hugcdb/t_idb_big.ibd

drop index ind_data_type on t_idb_big alogorithm=copy;

2.测试增/删索引过程中DML操作

增加表中数据

alter table t_idb_big drop id;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

insert into t_idb_big select * from t_idb_big;

alter table t_idb_big add id int unsigned not null primary key auto_increment;

首先测试传统方式修改表结构，在第一个会话中执行DDL语句

set old_alter_table=1;

create index ind_tablename on t_idb_big(table_name);

在另一个会话执行下列操作

set autocommit=0;

use hugcdb;

select count(*) from t_idb_big where table_name=’FILES’;

delete from t_idb_big where table_name=’FILES’;

rollback;

语句被阻塞

引入联机DDL方式，在第一个会话中执行

set old_alter_table=0;

create index ind_tablename on t_idb_big(table_name) algorithm=inplace;

在另一个会话执行下列操作

select count(*) from t_idb_big where table_name=’FILES’;

delete from t_idb_big where table_name=’FILES’;

rollback;

3.测试修改列

通过COPY机制修改列

alter table t_idb_big change nullable is_unllable varchar(3),algorithm=copy;

联机DDL方式修改列

alter table t_idb_big change nullable is_unllable varchar(3),algorithm=inplace;

4.测试修改自增列

传统方式修改

alter table t_idb_big auto_increment=1000000,algorithm=copy;很慢

连接DDL方式修改

alter table t_idb_big auto_increment=1000000,algorithm=inplace;很快

不仅不需要重建对象，而且只需要修改.frm文件中的标记和内存中的自增值，完全不需要动表中的数据

5.测试LOCK子句控制并行DML

show processlist;

 

ID列用于标识会话，Command列用于标识该会话指定的命令类型(比如说查询、空闲等)，State列标识该会话当前的状态，Info列标识该会话当前执行的操作，如果为NULL，则说明该会话当前是空闲状态，重点关注State列和Info列