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

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

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

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

真相是：

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

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

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

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

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

說實話，鎖表操作會經(jīng)常被忽視，在操作30M大小的表時我們更傾向于直接修改，但是30G，300G的表就要考慮一下了。當(dāng)使用率不高或者對鎖定時間要求不是很高的的系統(tǒng)來說直接操作也許更好?？墒牵覀兂３龅揭粋€需要立即執(zhí)行的SQL，或者因為性能問題需要緊急增加一個索引來減少加載時間。

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

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

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

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

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

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

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

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

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

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

# Step 7: 刪除舊表.

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

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

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

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

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

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

 wait_timeout=10000

    innodb_lock_wait_timeout=1

    lock_wait_timeout=60

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

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

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

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

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

201562594352787.png (300×296)

選擇合適的DDL操作

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