聯(lián)機修改表對象結(jié)構(gòu)： 在MySQL5.6之前 1.創(chuàng)建一個結(jié)構(gòu)與原表對象完全相同的臨時表(隱式操作，該對象用戶不可見)，并將該表的結(jié)構(gòu)

聯(lián)機修改表對象結(jié)構(gòu)：

在MySQL5.6之前

1.創(chuàng)建一個結(jié)構(gòu)與原表對象完全相同的臨時表(隱式操作，該對象用戶不可見)，并將該表的結(jié)構(gòu)修改為期望的結(jié)構(gòu)

2.鎖定原表，只許查詢，不許修改

3.將原表數(shù)據(jù)復制到新創(chuàng)建的臨時表，類似insert into new_tb select * from old_tb;

4.將原表重命名，新創(chuàng)建的臨時表名稱修改為正式表名，之后釋放鎖定，刪除原表

在MySQL5.6以后，聯(lián)機DDL修改InnoDB表提供有限支持

就地進行In-Place，表示修改操作可以直接在該表對象上執(zhí)行

復制表Copies Tables，表示需要復制整個表才能執(zhí)行修改操作

用戶可以通過ALTER TABLE語句中的LOCK和ALGORITHM兩個子句，來明確控制聯(lián)機DDL時的操作行為。LOCK子句對于表并行讀控制的微調(diào)比較有效，而ALGORITHM子句則對于操作時的性能和操作策略有較大影響

LOCK有4個選項值：

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

NONE：不使用鎖定策略，其他會話既能讀也能寫

SHARED：采取共享策略，其他會話可讀但不可寫

EXCLUSIVE：采取排他鎖定，其他會話既不能讀也不能寫

ALGORITHM有3個選項值：

DEFAULT：相當于不指定ALGORITHM子句

INPLACE：如果支持就直接修改，不支持就報錯

COPY：不管是否支持就地修改，都采取將表對象中數(shù)據(jù)新復制一份的方式修改

如果希望并發(fā)粒度最高，那么就要指定LOCK=NONE(可讀可寫)，若希望操作成本最低，最好指定ALGORITHM=INPLACE(直接對對象進行操作，涉及讀寫的數(shù)據(jù)量最小)

聯(lián)機DDL測試：

登錄到mysql，執(zhí)行對象創(chuàng)建腳本

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

增加表中數(shù)據(jù)

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;

首先測試傳統(tǒng)方式修改表結(jié)構(gòu)，在第一個會話中執(zhí)行DDL語句

set old_alter_table=1;

create index ind_tablename on t_idb_big(table_name);

在另一個會話執(zhí)行下列操作

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;

語句被阻塞

引入聯(lián)機DDL方式，在第一個會話中執(zhí)行

set old_alter_table=0;

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

在另一個會話執(zhí)行下列操作

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;

聯(lián)機DDL方式修改列

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

4.測試修改自增列

傳統(tǒng)方式修改

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

連接DDL方式修改

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

不僅不需要重建對象，而且只需要修改.frm文件中的標記和內(nèi)存中的自增值，，完全不需要動表中的數(shù)據(jù)

5.測試LOCK子句控制并行DML

show processlist;

ID列用于標識會話，Command列用于標識該會話指定的命令類型(比如說查詢、空閑等)，State列標識該會話當前的狀態(tài)，Info列標識該會話當前執(zhí)行的操作，如果為NULL，則說明該會話當前是空閑狀態(tài)，重點關(guān)注State列和Info列

MySQL InnoDB存儲引擎鎖機制實驗

InnoDB存儲引擎的啟動、關(guān)閉與恢復

MySQL InnoDB獨立表空間的配置

MySQL Server 層和 InnoDB 引擎層 體系結(jié)構(gòu)圖

InnoDB 死鎖案例解析

MySQL Innodb獨立表空間的配置

本文永久更新鏈接地址：

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com