Mysql Online DDL的使用詳解
正文
Online DDL在MySQL 5.6才開始支持的,在5.5及之前版本,使用alter table/create index等命令進(jìn)行表結(jié)構(gòu)修改操作均會鎖表,這在生產(chǎn)環(huán)境上明顯是不可接受的。
在MySQL 5.7,Online DDL在性能和穩(wěn)定性上不斷得到優(yōu)化,性能有顯著優(yōu)勢,且對業(yè)務(wù)負(fù)載影響小,停寫時間可控,相對pt-osc/gh-ost來說,無需安裝第三方依賴包,同時支持Inplace算法的Online DDL,由于無需拷表,所需磁盤空間也更小。
先來看一個常見的DDL語句:
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ù)改動,不涉及Server層;
COPY TABLE流程
- 首先建立臨時表,表結(jié)構(gòu)為ALTAR TABLE更改后的結(jié)構(gòu)
- 將原表中數(shù)據(jù)導(dǎo)入到臨時表(server層創(chuàng)建臨時表,會有顯示的IBD文件)
- 刪除原表
- 將臨時表rename為原來的表名
同時這一過程中,為了保持?jǐn)?shù)據(jù)的一致性,中間復(fù)制數(shù)據(jù)時(Copy Table)全程鎖表只讀,如果有寫請求進(jìn)來將無法提供服務(wù),將導(dǎo)致連接數(shù)爆張。
IN-PLACE流程
- 建立一個臨時文件,掃描原表主鍵的所有數(shù)據(jù)頁
- 用數(shù)據(jù)頁中原表記錄生成B+樹,存儲到臨時文件中(innodb_temp_data_file_path臨時表空間下創(chuàng)建臨時文件)
- 生成臨時文件的過程中,將所有對原表的操作記在一個日志文件(rowlog)中
- 臨時文件生成后,將日志文件中的操作應(yīng)用到臨時文件,得到一個輯數(shù)據(jù)上與原表相同
- 數(shù)據(jù)文件(日志文件記錄和重放操作)
- 用臨時文件替換原表數(shù)據(jù)文件
這一過程中,alter 語句在啟動的時候獲取MDL寫鎖,但是這個寫鎖在真正拷貝數(shù)據(jù)之前就退化成讀鎖,也就是說在最耗時的copy數(shù)據(jù)到臨時文件的過程中,原表是可以進(jìn)行dml操作的,僅僅會在最后的新舊表切換階段加鎖,這個rename的時間就非??炝?。
允許并發(fā)DML的DDL操作
- 創(chuàng)建/新增二級索引
- 重命名二級索引
- 刪除二級索引
- 改變索引類型(USING {BTREE | HASH})
- 添加主鍵(expensive cost)
- 刪除主鍵并增加另一個(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,但本身對服務(wù)器IO,CPU都會造成較高負(fù)擔(dān),同時會導(dǎo)致復(fù)制阻塞,造成另一種形式的從庫復(fù)制延遲,所以如果是大表,建議業(yè)務(wù)低峰期執(zhí)行
不允許并發(fā)DML的DDL操作
- 添加全文索引
- 添加空間索引
- 刪除主鍵
- 改變列數(shù)據(jù)類型
- 添加自增列(新增列->變?yōu)樽栽隽校?/li>
- 變更表字符集
- 修改數(shù)據(jù)類型長度
- 特例:varchar字符長度從10變更到小于255 采用inplace方式不會鎖表;從255變更到10會鎖表;
以上就是Mysql Online DDL的使用詳解的詳細(xì)內(nèi)容,更多關(guān)于Mysql Online DDL的使用的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
如何優(yōu)雅安全的備份MySQL數(shù)據(jù)
這篇文章主要介紹了如何優(yōu)雅安全的備份MySQL數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下2022-10-10Mysql存儲二進(jìn)制對象數(shù)據(jù)問題
這篇文章主要介紹了Mysql存儲二進(jìn)制對象數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-03-03windows下MySQL5.6版本安裝及配置過程附有截圖和詳細(xì)說明
這篇文章主要介紹了windows下MySQL5.6版本安裝及配置過程附有截圖和詳細(xì)說明,需要的朋友可以參考下2013-06-06Mysql報(bào)錯too many connections的原因及解決方案
這篇文章主要給大家介紹了關(guān)于Mysql報(bào)錯too many connections原因及解決方案,文中通過實(shí)例代碼以及圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-09-09詳解MySQL中varchar和int隱式轉(zhuǎn)換的注意事項(xiàng)
這篇文章主要為大家詳細(xì)介紹了MySQL中varchar和int隱式轉(zhuǎn)換的注意事項(xiàng),文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2023-12-12Mysql查詢正在執(zhí)行的事務(wù)以及等待鎖的操作方式
今天小編就為大家分享一篇關(guān)于Mysql查詢正在執(zhí)行的事務(wù)以及等待鎖的操作方式,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2018-12-12