联机修改表对象结构： 在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 hub;
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/hub/t_idb_big.ibd
alter table t_idb_big add index ind_data_type (data_type),algorithm=inplace;
du –k /data/mysqldata/3306/data/hub/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/hub/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 hub;
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表空间的配置
本文永久更新链接地址：
下载本文