MySQL Online DDL學習筆記

MySQL online DDL的功能就是在對表就行DDL操作的同時也可以對表進行讀寫操作，

即對表的DDL操作不會影響該表上的事務。

 該功能的優點：

1. 改善繁忙生産環境的響應效率和可用性。
2. 可以使用lock子句在性能和并發性之間進行協調。
3. 相比ALGORITHM=COPY算法使用較少的磁盤空間和IO的開銷。

使用了ALGORITHM，LOCK子句的onlin DDL:

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;      

lock子句用于設定DDL執行期間，該表上的并發程度

ALGORITHM=INPLACE

 and 

ALGORITHM=COPY子句用于設定online DDL的處理算法，進行DDL時一定要盡量避免出現table copy

DDL online支援總覽表：

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata	Notes
CREATE INDEX ,  ADD INDEX	Yes*	No*	Yes	No	Restrictions apply for  FULLTEXT  indexes; see next row.
ADD FULLTEXT INDEX	Yes*	No*	No	No	Adding the first  FULLTEXT  index rebuilds the table if there is no user-defined  FTS_DOC_ID  column. Subsequent  FULLTEXT  indexes may be added on the same table without rebuilding the table.
ADD SPATIAL INDEX	Yes	No	No	No
RENAME INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
DROP INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
OPTIMIZE TABLE	Yes*	Yes	Yes	No	In-place operation is not supported for tables with  FULLTEXT  indexes.
Set column default value	Yes	No	Yes	Yes	Only modifies table metadata.
Change auto-increment value	Yes	No	Yes	No*	Modifies a value stored in memory, not the data file.
Add foreign key constraint	Yes*	No	Yes	Yes	The  INPLACE  algorithm is supported when  foreign_key_checks  is disabled. Otherwise, only the  COPY  algorithm is supported.
Drop foreign key constraint	Yes	No	Yes	Yes	foreign_key_checks  can be enabled or disabled.
Rename column	Yes*	No	Yes*	Yes	To permit concurrent DML, keep the same data type and only change the column name.  ALGORITHM=INPLACE  is not supported for renaming agenerated column.
Add column	Yes*	Yes*	Yes*	No	Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.  ALGORITHM=INPLACE  is supported for adding a virtual generated column but not for adding a stored generated column. Adding a virtual generated column does not require a table rebuild.
Drop column	Yes	Yes*	Yes	No	Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE  is supported for dropping a generated column. Dropping a virtual generated column does not require a table rebuild.
Reorder columns	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change  ROW_FORMAT  property	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change  KEY_BLOCK_SIZE property	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Make column  NULL	Yes	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Make column  NOT NULL	Yes*	Yes	Yes	No	Rebuilds the table in place.  STRICT_ALL_TABLES  or STRICT_TRANS_TABLES SQL_MODE  is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.8, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.
Change column data type	No*	Yes	No	No	VARCHAR  size may be increased using online  ALTER TABLE . See Modifying Column Properties for more information.
Add primary key	Yes*	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE  is not permitted under certain conditions if columns have to be converted to  NOT NULL .
Drop primary key and add another	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Drop primary key	No	Yes	No	No	Only  ALGORITHM=COPY  supports dropping a primary key without adding a new one in the same  ALTER TABLE statement.
Convert character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Specify character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Rebuild with  FORCE  option	Yes*	Yes	Yes	No	Uses  ALGORITHM=INPLACE . ALGORITHM=INPLACE  is not supported for tables with  FULLTEXT  indexes.
“null” rebuild using  ALTER TABLE ... ENGINE=INNODB	Yes*	Yes	Yes	No	Uses  ALGORITHM=INPLACE . ALGORITHM=INPLACE  is not supported for tables with  FULLTEXT  indexes.
Set  STATS_PERSISTENT , STATS_AUTO_RECALC , STATS_SAMPLE_PAGES persistent statistics options	Yes	No	Yes	Yes	Only modifies table metadata.
ALTER TABLE … ENCRYPTION	No	Yes	No	Yes
Drop a  STORED  column	Yes	Yes*	Yes	No	Rebuilds the table in place.
Modify  STORED  column order	Yes	Yes*	Yes	No	Rebuilds the table in place.
Add a  STORED  column	Yes	Yes*	Yes	No	Rebuilds the table in place.
Drop a  VIRTUAL  column	Yes	No	Yes	Yes
Modify  VIRTUAL  column order	Yes	No	Yes	Yes
Add a  VIRTUAL  column	Yes	No	Yes	Yes

 常用的online DDL：

建立二級索引：

CREATE INDEX name ON table (col_list);
ALTER TABLE table ADD INDEX name (col_list);      

删除二級索引：

DROP INDEX name ON table;

 ALTER TABLE tbl_name DROP INDEX name;      

在innodb表上建立和删除二級索引不會産生table-copying

當索引正在被建立或删除時，是可以對表進行讀寫操作的， 在對表執行create index或drop index語句時，隻有在通路該表的事務完成之後，create index和drop index語句才可以完成，是以索引的初始狀态反映了表中最新的資料，在以前，對正在執行create index或drop index的表進行操作時，會導緻将insert，update，delete操作撤銷掉的死鎖。

online ddl的另外一個用法是，在進行資料遷移時，可以先建立表結構，然後導入資料，最後建立相關索引，這種方式通常會提高資料遷移的效率。

新建立的二級索引隻包含在create index和alter table語句完成時，已經在該表上commit的資料，不包含未送出的值，old version值，已經标記删除的值。

如果在建立二級索引時，MySQL崩潰或是異常當機，在恢複時，MySQL會drop掉建立了一半的索引，是以此時需要重新手動建立索引。

重命名索引也是online DDL

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;      

建立全文索引：

CREATE FULLTEXT INDEX name ON table(column);

如果在建立第一個全文索引時，如果

FTS_DOC_ID列之前沒有建立過，那麼建立全文索引需要rebuild table，在之後建立全文索引時是online狀态的，不需要rebuild table。

 添加和删除主鍵索引：

重建聚集索引總是要進行copy table，是以在建表時一定要設計好主鍵，盡量在之後使用alter table來修改

隻有當sql_mode的設定包含了 

strict_trans_tables

 或

strict_all_tables

 标記時， 

ALGORITHM=INPLACE是允許的

但是建立主鍵的列包含null值的話，會導緻語句執行失敗。

 如果表中沒有主鍵，innodb會自動選擇第一個被定義為not null unique的字段作為主鍵，或是在内部自動生成一個自動作為主鍵，但這會有隐藏的空間開銷。

MySQL建立新的聚集索引是通過将原表中已存在的資料copy到按照目标索引結構産生的臨時表中，在資料copy完成後，将該臨時表rename為原來的表的名稱，

并将原表從資料庫中drop掉。

因為MySQL是索引組織表，表的結構與主鍵密切相關，是以重定義主鍵一定會導緻copy table，

此時使用

ALGORITHM=INPLACE雖然會出現copy table，但仍比ALGORITHM=COPY的效率要高，因為：

1. 不産生undo和redo日志。
2. 二級索引的記錄是預先排好序的，是以可以按照順序導入。
3. 沒有使用到change buffer，因為沒有随機通路insert到二級索引。

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

隻有ALGORITHM=COPY模式支援drop primary key.

ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

資料會大量重組，該操作是一個開銷很大操作。

修改字段的屬性值：

ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal;

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT;      

 字段的預設值存儲在表的.frm檔案中，而不是innodb的資料字典中。

修改字段的自增值：

ALTER TABLE table AUTO_INCREMENT=next_value;      

在使用複制的分布式系統，或者是分片中，需要重新設定自增值到某一個指定的值，在資料倉庫中，有時需要清空表中的資料，然後重置自增值，再導入新的資料。

重命名字段名稱：

ALTER TABLE tbl CHANGE old_col_name new_col_name datatype;      

當使用該語句隻是修改字段的名稱，而沒有修改字段類型時，該語句的執行總是online的。

在對外鍵限制中的字段進行重命名後，在外鍵的定義中會自動将該字段的名稱更新為重命名後的名稱，對外鍵中的字段進行重命名操作時，該操作僅運作在 in-place模式，

當在修改語句中指定 

ALGORITHM=COPY或者其他因素導緻修改語句使用了 

ALGORITHM=COPY，那麼重命名字段的語句将會執行失敗。

使用in-place模式修改varchar字段的長度：

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);      

每次修改varchar字段的長度時，varchar内部編碼需要的位元組長度值一定要保持相同，因為varchar值從0-255時，需要1個位元組長度來編碼值，當varchar從256位元組開始需要2個位元組長度來編碼值，是以當修改varchar字段的長度時，從0位元組增加到255位元組，或者從大于等于256位元組開始增加長度都是支援in-place模式的，當從一個小于256位元組的長度增加到大于256位元組的長度，因為此時會導緻varchar内部編碼值所需要的位元組長度從1個位元組長度變為2個位元組長度，是以此時不能使用in-place模式，隻能使用ALGORITHM=COPY模式，例如下述修改varchar長度的語句将會執行失敗：

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.      

縮小varchar字段的長度時不支援in-place模式的，此時需要進行table copy(

ALGORITHM=COPY

).

添加删除外鍵：

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) REFERENCES tbl2(col2) referential_actions;

ALTER TABLE tbl DROP FOREIGN KEY fk_name;      

當

foreign_key_checks參數為disable時，建立外鍵是online的。删除外鍵時，與該參數的取值無關，都是online狀态的。

删除外鍵和索引：

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;      

當外鍵已經作用于正在被修改的表時，進行online DDL會存在一些額外的限制：

如果在子表上進行alter table操作，當存在

ON UPDATE或者使用了CASCADE和SET NULL參數的

ON DELETE子句時，導緻子表的資料随着父表進行變化，

那麼子表上的alter table語句要等待父表上的事務commit，同理，在父表上進行alter table操作，也要等待子表上的相關事務commit。

因為在進行DDL操作時，對表的讀寫操作不會受到影響，是以提高了應用的響應效率。

由于in-place操作不需要rebuild table，是以節省了磁盤IO和cpu的開銷，最小化了資料庫的負載，在執行DDL期間提供了較好的性能。

相對于copy table，in-place操作隻是将較少的資料讀入到buffer pool中，避免了将頻繁使用的資料從buffer pool中沖走，在以前版本，進行DDL操作會導緻系統性能降低。

Online DDL的locking選項

通過lock子句強制使用更嚴格的鎖模式，如果lock子句指定鎖的嚴格程度低于某些DDL的最低鎖嚴格程度，那麼多将會報錯，

LOCK=NONE

:

允許并發查詢和DML

LOCK=SHARED

:

允許并發查詢，但是阻塞DML

LOCK=DEFAULT

:

 盡量滿足最大并發，如果沒有指定lock子句，預設為此模式

LOCK=EXCLUSIVE

:

阻塞并發查詢和DML

當主要關心的是在盡可能短的時間内完成DDL，而并發查詢和DML的執行不重要的情況下，使用此模式

 在大多數情況，在表上的online DDL操作會等待目前正在通路該表的事務commit或rollback，因為當DLL語句正在準備的時候，需要對表進行短暫的排他通路。

同理online DDL在完成之前需要對表進行一個短暫的排他通路。是以，如果運作在該表上的事務執行時間過長，那麼會導緻online DDL等待排他通路逾時。

未完待續

本部落格剛剛開通，旨在将本人的工作和學習經驗在此分享，因為之前的資料雜亂無章，過于零碎，是以整理的時間會長一些，很多地方也沒有描述清楚，在後續過程中會進一步整理。

轉載于:https://www.cnblogs.com/dba-wubing/p/9103833.html