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獨立資料表空間的配置
本文永久更新連結地址: