快捷導(dǎo)航

MySQL中修改表結(jié)構(gòu)時(shí)需要注意的一些地方

更新時(shí)間：2015年06月25日 09:52:01 投稿：goldensun

這篇文章主要介紹了MySQL中修改表結(jié)構(gòu)時(shí)需要注意的一些地方,作者援引Percona的相關(guān)的說(shuō)明來(lái)講述如何避免相關(guān)操作導(dǎo)致表無(wú)法使用的問(wèn)題,一些需要的朋友可以參考下

MySql 在修改表結(jié)構(gòu)的時(shí)候可能會(huì)中斷產(chǎn)品的正常運(yùn)行影響用戶體驗(yàn)，甚至更壞的結(jié)果，丟失數(shù)據(jù)。不是所有的數(shù)據(jù)庫(kù)管理員、程序員、系統(tǒng)管理員都非常了解Mysql能避免這種情況。DBA會(huì)經(jīng)常碰到這種生產(chǎn)中斷的情況，當(dāng)升級(jí)腳本修改了應(yīng)用層和數(shù)據(jù)庫(kù)層，或者缺乏經(jīng)驗(yàn)的管理員、開(kāi)發(fā)在不是很了解Mysql內(nèi)部工作機(jī)制的情況下修改了規(guī)范文件。

真相是：

直接修改表結(jié)構(gòu)的過(guò)程中會(huì)鎖表（在5.6版本之前）
在線的數(shù)據(jù)定義語(yǔ)言在5.6版本不總是在線的而且也會(huì)鎖表
就算使用Percona工具包（在線修改定義文件）也會(huì)有若干個(gè)步驟會(huì)鎖表

Percona MySQL 服務(wù)器開(kāi)發(fā)團(tuán)隊(duì)鼓勵(lì)用戶在計(jì)劃或者執(zhí)行數(shù)據(jù)庫(kù)遷移的時(shí)候先和我們溝通。我們的目標(biāo)是基于用戶給出的各種情況給出最佳的方案。旨在避免鎖表當(dāng)用戶對(duì)非常大的表執(zhí)行DDL，以確保應(yīng)用能像平常一樣正常運(yùn)行，同時(shí)也在努力改善響應(yīng)時(shí)間或增加系統(tǒng)功能。最差的情況是確保那些經(jīng)不起當(dāng)機(jī)的系統(tǒng)在黃金交易時(shí)間正常運(yùn)行。

我們使用的大多數(shù)安裝包仍然小于Mysql5.6，這需要我們不停嘗試新的安裝環(huán)境來(lái)把數(shù)據(jù)庫(kù)遷移造成的損失降到最低。這可能需要一個(gè)能“在線修改規(guī)范定義文件”的工具來(lái)升級(jí)或者修改規(guī)范文件。Mysql5.6解決這一問(wèn)題的做法是通過(guò)減少重建表和鎖表的場(chǎng)景，但這個(gè)方法不能覆蓋所有的可能的操作，例如當(dāng)修改一列的數(shù)據(jù)類型時(shí)必然需要全表重構(gòu)。Przemys?aw和 Malkowski在去年盡可能詳盡的討論了Mysql5.6運(yùn)行中修改定義。

隨著 MySQL 5.7的新功能, 我們尋求不會(huì)鎖表的DDL操作例如; 表優(yōu)化和索引重命名. (More info)

對(duì)于Mysql5.6的用戶，最好的建議是回顧一下數(shù)矩陣來(lái)熟悉在MYSQL之外執(zhí)行定義的更改，好消息是我們很擅長(zhǎng)解決這一問(wèn)題。

說(shuō)實(shí)話，鎖表操作會(huì)經(jīng)常被忽視，在操作30M大小的表時(shí)我們更傾向于直接修改，但是30G，300G的表就要考慮一下了。當(dāng)使用率不高或者對(duì)鎖定時(shí)間要求不是很高的的系統(tǒng)來(lái)說(shuō)直接操作也許更好?？墒牵覀兂３?huì)遇到一個(gè)需要立即執(zhí)行的SQL，或者因?yàn)樾阅軉?wèn)題需要緊急增加一個(gè)索引來(lái)減少加載時(shí)間。

是否需要在系統(tǒng)在線期修改表定義

上面提到，在線修改表定義是工作流中的一個(gè)模塊。通常是不錯(cuò)的解決方案，但也會(huì)遇到不能使用的場(chǎng)合，例如：當(dāng)某個(gè)表使用了觸發(fā)器。了解pt-osc在我們項(xiàng)目中的工作過(guò)程很重要，讓我們來(lái)看一下源代碼：

復(fù)制代碼代碼如下:

[moore@localhost]$ egrep 'Step' pt-online-schema-change

# 步驟 1: 創(chuàng)建一個(gè)新表

# 步驟 2: 修改清空表. 這應(yīng)該比較快,

# Step 3: 創(chuàng)建觸發(fā)器來(lái)捕獲原始表的改變 <--(鎖定元數(shù)據(jù))

# Step 4: 復(fù)制數(shù)據(jù).

# Step 5: 重命名表: <--(鎖定元數(shù)據(jù)

# Step 6: 更新外鍵 如果是子表.

# Step 7: 刪除舊表.

我把上面第三步到第五步高亮出來(lái)，這是鎖表可能引起系統(tǒng)停機(jī)的時(shí)間。但步驟六設(shè)計(jì)外鍵更新是一個(gè)循環(huán)的操作，是避免在更新關(guān)系的時(shí)候隱含地重建表。有很多方法可以確保表的完整性約束，在pt-osc的說(shuō)明文檔中詳細(xì)說(shuō)明了，在開(kāi)始之前預(yù)覽你的表結(jié)構(gòu)包括約束，并知道怎樣把修改表定義所造成的影響降到最低。

最近，我們通知了一個(gè)擁有高并發(fā)高事務(wù)量系統(tǒng)的用戶運(yùn)行pt-osc在大型數(shù)據(jù)表上。這件事對(duì)于他們來(lái)說(shuō)很平常，幾小時(shí)后我們的客服被告知該客戶遇到了最大連接數(shù)超過(guò)的問(wèn)題。這個(gè)問(wèn)題是如何產(chǎn)生的呢？當(dāng)pt-osc運(yùn)行到步驟五的時(shí)候會(huì)嘗試去鎖定數(shù)據(jù)并重命名原表和隱藏表，然而這不會(huì)在開(kāi)啟事務(wù)的時(shí)候立即執(zhí)行，因此這條線程會(huì)被排在重命名后面。這表現(xiàn)在用戶應(yīng)用上就是系統(tǒng)停機(jī)。數(shù)據(jù)庫(kù)無(wú)法開(kāi)啟新的連接并且所有的線程都被阻塞在重命名命令之后。

201562594352787.png (300×296) 5.5.3版本的說(shuō)明，當(dāng)開(kāi)啟一個(gè)事務(wù)時(shí)會(huì)鎖定它會(huì)用到的所有表的數(shù)據(jù)（不依賴于存儲(chǔ)引擎），并在事務(wù)提交的時(shí)候釋放鎖。這樣做確保了在開(kāi)啟事務(wù)期間不能修改表的定義。

長(zhǎng)遠(yuǎn)來(lái)看我們可以采用一些新的技術(shù)來(lái)避免這種情況，例如non-default pt-osc的選項(xiàng)，換言之就是不會(huì)刪除原表把數(shù)據(jù)換到新表。這種聯(lián)合脫離了隱藏表和觸發(fā)器，我們應(yīng)該鼓勵(lì)將重命名操作變得原子化。

校訂：2.2版本的percona工具新增了一個(gè)變量–tries 和變量–set-vars 共同被部署，解決了各種pt-osc操作可能會(huì)鎖表的情況。pt-osc (–set-vars)默認(rèn)會(huì)設(shè)置如下的會(huì)話變量當(dāng)連接到數(shù)據(jù)庫(kù)服務(wù)器的時(shí)候。

復(fù)制代碼代碼如下:

 wait_timeout=10000

    innodb_lock_wait_timeout=1

    lock_wait_timeout=60

當(dāng)使用 –tries 我們可以顆粒化地鑒別操作，嘗試次數(shù)、在嘗試的間隔等待。這種組合可以確保pt-osc在合適的時(shí)機(jī)殺掉自己的等待會(huì)話進(jìn)程，確保線程堆棧的空閑，并提供給我們循環(huán)操作來(lái)獲取管理因觸發(fā)器、重命名、修改外鍵而造成的鎖。

復(fù)制代碼代碼如下:

–tries swap_tables:5:0.5,drop_triggers:5:0.5

說(shuō)明文檔在這里http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change–tries

它闡述了即便使用了諸如pt-osc之類的工具，充分了解你想解決的問(wèn)題是很重要。下面的流程圖會(huì)幫助你當(dāng)你了解修改了MYSQL數(shù)據(jù)庫(kù)的結(jié)構(gòu)的注意事項(xiàng)。請(qǐng)仔細(xì)閱讀建議盡管有些圖上未標(biāo)出，例如磁盤空間，IO加載等。

201562594352787.png (300×296)

選擇合適的DDL操作

確保能清楚了解在修改表結(jié)構(gòu)對(duì)你的系統(tǒng)會(huì)產(chǎn)生何種影響，并選擇合適的方法來(lái)使這種影響降到最低。有時(shí)這意味著需要將改動(dòng)延期直到系統(tǒng)到了不常使用的時(shí)候或者使用能在操作期間不鎖表的工具。當(dāng)你表中有觸發(fā)器的時(shí)候一般直接修改表結(jié)構(gòu)。