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.ibdalter table t_idb_big add index ind_data_type （data_type）,algorithm=inplace;du –k /data/mysqldata/3306/data/hugcdb/t_idb_big.ibdalter 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.ibddrop 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子句控制并行DMLshow processlist;ID列用于标识会话，Command列用于标识该会话指定的命令类型（比如说查询、空闲等），State列标识该会话当前的状态，Info列标识该会话当前执行的操作，如果为NULL，则说明该会话当前是空闲状态，重点关注State列和Info列MySQL InnoDB存储引擎锁机制实验 http://www.linuxidc.com/Linux/2013-04/82240.htmInnoDB存储引擎的启动、关闭与恢复 http://www.linuxidc.com/Linux/2013-06/86415.htmMySQL InnoDB独立表空间的配置 http://www.linuxidc.com/Linux/2013-06/85760.htmMySQL Server 层和 InnoDB 引擎层体系结构图 http://www.linuxidc.com/Linux/2013-05/84406.htmInnoDB 死锁案例解析 http://www.linuxidc.com/Linux/2013-10/91713.htmMySQL Innodb独立表空间的配置 http://www.linuxidc.com/Linux/2013-06/85760.htm本文永久更新链接地址