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列

MySQL InnoDB存储引擎锁机制实验

InnoDB存储引擎的启动、关闭与恢复

MySQL InnoDB独立表空间的配置

MySQL Server 层和 InnoDB 引擎层 体系结构图

InnoDB 死锁案例解析

MySQL Innodb独立表空间的配置

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/4540aedefae4bb53b6e28636fd739330.html