MySQL Online DDL詳解

標籤：block   HERE   clu   cert   required   incr   spec   generated   first   

  

MySQL online DDL的功能就是在對錶就行DDL操作的同時也可以對錶進行讀寫操作，即對錶的DDL操作不會影響該表上的事務。

 該功能的優點：

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

 

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_SIZEproperty	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 orSTRICT_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 TABLEstatement.
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_PAGESpersistent 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);

 

刪除二級索引：

 

 

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

當索引正在被建立或刪除時，是可以對錶進行讀寫操作的， 在對錶執行create index或drop index語句時，只有在訪問該表的事務完成之後，create index和drop index語句才可以完成，所以索引的初始狀態反映了表中最近使用的資料，在以前，對正在執行create index或drop index的表進行操作時，會導致將insert，update，delete操作撤銷掉的死結。

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

  

 

修改欄位的屬性值：

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 changecolumn 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等待排他訪問逾時。

 

 

未完待續

 

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

 

MySQL Online DDL詳解