InnoDB联机修改表对象结构

php中文网

发布时间：2016-06-07 16:08:25

1162人浏览过

来源于php中文网

原创

联机修改表对象结构：在MySQL5.6之前 1.创建一个结构与原表对象完全相同的临时表(隐式操作，该对象用户不可见)，并将该表的结构

联机修改表对象结构：

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

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操作

增加表中数据

GNCMS可视化拖拽公司网站模板1.0.0

高端网络建设公司设计类网站模板 IT互联网移动建站类网站源码(带手机版数据同步)本套模板采用现在非常流行的全屏自适应布局设计，且栏目列表以简洁，非常时尚大气。后台支持可视拖拽编辑。模板特点：1、手工书写DIV+CSS、代码精简无冗余。2、自适应结构，全球先进技术，高端视觉体验。3、SEO框架布局，栏目及文章页均可独立设置标题/关键词/描述。4、后台直接修改联系方式、传真、邮箱、地址等，修改更加方

下载

alter table t_idb_big drop id;

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