MySQL DDL詳情揭秘

2024-07-24 12:31:58

字體：大中小

供稿：網(wǎng)友

MySQL中DDL語(yǔ)句，即數(shù)據(jù)定義語(yǔ)言，用于創(chuàng)建、刪除、修改、庫(kù)或表結(jié)構(gòu)，對(duì)數(shù)據(jù)庫(kù)或表的結(jié)構(gòu)操作。常見(jiàn)的有create，alter，drop等。這類語(yǔ)句通常會(huì)耗費(fèi)很大代價(jià)，特別是對(duì)于大表做表結(jié)構(gòu)變更。本篇文章會(huì)揭露各類DDL語(yǔ)句執(zhí)行的詳細(xì)情況。

1.Online DDL簡(jiǎn)介
在MySQL的早期版本中，DDL操作因?yàn)殒i表會(huì)和DML操作發(fā)生鎖沖突，大大降低并發(fā)性。在早期版本中，大部分DDL操作的執(zhí)行原理就是通過(guò)重建表的方式，因?yàn)橐獜?fù)制原表數(shù)據(jù)，所以會(huì)長(zhǎng)時(shí)間鎖表，只能讀不能寫，DDL操作和DML操作有很嚴(yán)重的沖突。從MySQL5.6開(kāi)始，很多DDL操作過(guò)程都進(jìn)行了改進(jìn)，出現(xiàn)了Online DDL，用于支持DDL執(zhí)行期間DML語(yǔ)句的并行操作，提高數(shù)據(jù)庫(kù)的吞吐量。

ALGORITHM=INPLACE，可以避免重建表帶來(lái)的IO和CPU消耗，保證ddl期間依然有良好的性能和并發(fā)。
ALGORITHM=COPY，需要拷貝原始表，所以不允許并發(fā)DML寫操作，可讀。這種copy方式的效率還是不如 inplace ，因?yàn)榍罢咝枰涗泆ndo和redo log，而且因?yàn)榕R時(shí)占用buffer pool引起短時(shí)間內(nèi)性能受影響。
上面只是 Online DDL 內(nèi)部的實(shí)現(xiàn)方式，此外還有 LOCK 選項(xiàng)控制是否鎖表，根據(jù)不同的DDL操作類型有不同的表現(xiàn)：默認(rèn)MySQL盡可能不去鎖表，但是像修改主鍵這樣的昂貴操作不得不選擇鎖表。

2.不同類DDL操作詳情
不同種類DDL語(yǔ)句具體的執(zhí)行情況是不同的，下表列舉出常見(jiàn)DDL語(yǔ)句具體的執(zhí)行詳情，包括是否允許讀寫及是否鎖表。這個(gè)表格希望大家可以詳細(xì)對(duì)比看下，特別要關(guān)注下需要copy table的DDL操作。

操作支持方式 Allow R/W 說(shuō)明
add/create index online 允許讀寫當(dāng)表上有FULLTEXT索引除外，需要鎖表，阻塞寫
drop index online 允許讀寫操作元數(shù)據(jù)，不涉及表數(shù)據(jù)。所以很快，可以放心操作
optimize table online 允許讀寫當(dāng)帶有fulltext index的表用copy table方式并且阻塞寫
alter table...engine=innodb online 允許讀寫當(dāng)帶有fulltext index的表用copy table方式并且阻塞寫
add column online 允許讀寫(增加自增列除外) 1、添加auto_increment列要鎖表，阻塞寫;2、雖采用online方式，但是表數(shù)據(jù)需要重新組織，所以增加列依然是昂貴的操作
drop column online 允許讀寫(增加自增列除外) 同add column，重新組織表數(shù)據(jù)，，昂貴的操作
Rename a column online 允許讀寫操作元數(shù)據(jù);不能改列的類型，否則就鎖表
Reorder columns online 允許讀寫重新組織表數(shù)據(jù)，昂貴的操作
Make column NOT NULL online 允許讀寫重新組織表數(shù)據(jù)，昂貴的操作
Change data type of column copy table 僅支持讀，阻塞寫創(chuàng)建臨時(shí)表，復(fù)制表數(shù)據(jù)，昂貴的操作
Set default value for a column online 允許讀寫操作元數(shù)據(jù)，因?yàn)閐efault value存儲(chǔ)在frm文件中，不涉及表數(shù)據(jù)。所以很快，可以放心操作
alter table xxx auto_increment=xx online 允許讀寫操作元數(shù)據(jù)，不涉及表數(shù)據(jù)。所以很快，可以放心操作
Add primary key online 允許讀寫昂貴的操作
Convert character set copy table 僅支持讀，阻塞寫如果新字符集不同，需要重建表，昂貴的操作
3.DDL最佳實(shí)踐
雖然MySQL 5.6和5.7版本提供了Online DDL操作，但Online DDL仍存在以下問(wèn)題：

執(zhí)行DDL前查看下該表有沒(méi)有被事務(wù)占用，防止出現(xiàn)MDL鎖。
執(zhí)行DDL前確保datadir，tmpdir磁盤空間足夠。
能業(yè)務(wù)低峰期操作的DDL，都盡量安排在業(yè)務(wù)低峰期進(jìn)行。
對(duì)于大表和較大表，如果對(duì)復(fù)制延遲和主庫(kù)性能敏感，建議改為gh-ost或pt-osc工具。
對(duì)于并發(fā)操作較高的表，無(wú)論表數(shù)據(jù)量多少，不能在業(yè)務(wù)高峰期操作。
同個(gè)表的多個(gè)DDL語(yǔ)句可以合并在一起進(jìn)行，避免多次table rebuild帶來(lái)的消耗。但是也要注意分組，比如需要copy table和只需inplace就能完成的，應(yīng)該分兩個(gè)alter語(yǔ)句。

（編輯：武林網(wǎng)）

上一篇：mysql innodb cluster 創(chuàng)建

下一篇：DBeaver5.0.5鏈接mysql