快捷導(dǎo)航

Mysql Online DDL的使用詳解

更新時(shí)間：2021年05月20日 11:32:44 作者：xmebcc

在日常DBA運(yùn)維過程中，對(duì)表結(jié)構(gòu)進(jìn)行變更算是個(gè)普遍的需求了。如果操作的對(duì)象是個(gè)熱表、大表，難免心里一怵，這些DDL操作是否可以直接執(zhí)行，哪些會(huì)影響線上讀寫，哪些會(huì)影響主從，甚至導(dǎo)致服務(wù)器壓力驟升，本文做了梳理，希望對(duì)大家有所幫助。

正文

Online DDL在MySQL 5.6才開始支持的，在5.5及之前版本，使用alter table/create index等命令進(jìn)行表結(jié)構(gòu)修改操作均會(huì)鎖表，這在生產(chǎn)環(huán)境上明顯是不可接受的。

在MySQL 5.7，Online DDL在性能和穩(wěn)定性上不斷得到優(yōu)化，性能有顯著優(yōu)勢(shì)，且對(duì)業(yè)務(wù)負(fù)載影響小，停寫時(shí)間可控，相對(duì)pt-osc/gh-ost來(lái)說，無(wú)需安裝第三方依賴包，同時(shí)支持Inplace算法的Online DDL，由于無(wú)需拷表，所需磁盤空間也更小。

先來(lái)看一個(gè)常見的DDL語(yǔ)句：

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

其中，LOCK描述了DDL期間運(yùn)行的并發(fā)程度，ALGORITHM描述了DDL的實(shí)現(xiàn)方式

LOCK參數(shù)

LOCK=NONE:允許并發(fā)的查詢和DML操作
LOCK=SHARED:允許并發(fā)的查詢，但阻塞DML操作
LOCK=DEFAULT: 由系統(tǒng)決定，允許盡可能多的并發(fā)性(并發(fā)查詢、DML或兩者)。如果省略LOCK子句相當(dāng)于指定LOCK=DEFAULT
LOCK=EXCLUSIVE:阻塞并發(fā)查詢和DML操作。

ALGORITHM參數(shù)

ALGORITHM=COPY：采用拷表方式進(jìn)行表變更，與pt-osc/gh-ost類似；
ALGORITHM=INPLACE：僅需要進(jìn)行引擎層數(shù)據(jù)改動(dòng)，不涉及Server層；

COPY TABLE流程

首先建立臨時(shí)表，表結(jié)構(gòu)為ALTAR TABLE更改后的結(jié)構(gòu)
將原表中數(shù)據(jù)導(dǎo)入到臨時(shí)表（server層創(chuàng)建臨時(shí)表，會(huì)有顯示的IBD文件）
刪除原表
將臨時(shí)表rename為原來(lái)的表名

同時(shí)這一過程中，為了保持?jǐn)?shù)據(jù)的一致性，中間復(fù)制數(shù)據(jù)時(shí)（Copy Table）全程鎖表只讀，如果有寫請(qǐng)求進(jìn)來(lái)將無(wú)法提供服務(wù)，將導(dǎo)致連接數(shù)爆張。

IN-PLACE流程

建立一個(gè)臨時(shí)文件，掃描原表主鍵的所有數(shù)據(jù)頁(yè)
用數(shù)據(jù)頁(yè)中原表記錄生成B+樹，存儲(chǔ)到臨時(shí)文件中（innodb_temp_data_file_path臨時(shí)表空間下創(chuàng)建臨時(shí)文件）
生成臨時(shí)文件的過程中，將所有對(duì)原表的操作記在一個(gè)日志文件(rowlog)中
臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)輯數(shù)據(jù)上與原表相同
數(shù)據(jù)文件（日志文件記錄和重放操作）
用臨時(shí)文件替換原表數(shù)據(jù)文件

這一過程中，alter 語(yǔ)句在啟動(dòng)的時(shí)候獲取MDL寫鎖，但是這個(gè)寫鎖在真正拷貝數(shù)據(jù)之前就退化成讀鎖，也就是說在最耗時(shí)的copy數(shù)據(jù)到臨時(shí)文件的過程中，原表是可以進(jìn)行dml操作的，僅僅會(huì)在最后的新舊表切換階段加鎖，這個(gè)rename的時(shí)間就非?？炝?。

允許并發(fā)DML的DDL操作

創(chuàng)建/新增二級(jí)索引
重命名二級(jí)索引
刪除二級(jí)索引
改變索引類型(USING {BTREE | HASH})
添加主鍵（expensive cost）
刪除主鍵并增加另一個(gè)（expensive cost）(ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;)
新增列（expensive cost）
刪除列（expensive cost）
重命名列
列重新排序（expensive cost）
改變列默認(rèn)值
刪除列默認(rèn)值
改變列自增值
設(shè)置列屬性null/not null （expensive cost）
修改枚舉或集合列的定義
Change ROW_FORMAT
Change key block size

標(biāo)記為expensive cost的操作雖然允許OnlineDDL,但本身對(duì)服務(wù)器IO,CPU都會(huì)造成較高負(fù)擔(dān)，同時(shí)會(huì)導(dǎo)致復(fù)制阻塞，造成另一種形式的從庫(kù)復(fù)制延遲，所以如果是大表，建議業(yè)務(wù)低峰期執(zhí)行

不允許并發(fā)DML的DDL操作

添加全文索引
添加空間索引
刪除主鍵
改變列數(shù)據(jù)類型
添加自增列（新增列->變?yōu)樽栽隽校?/li>
變更表字符集
修改數(shù)據(jù)類型長(zhǎng)度
- 特例：varchar字符長(zhǎng)度從10變更到小于255 采用inplace方式不會(huì)鎖表；從255變更到10會(huì)鎖表；

以上就是Mysql Online DDL的使用詳解的詳細(xì)內(nèi)容，更多關(guān)于Mysql Online DDL的使用的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: