怎樣避免 MySQL建表結構時導致表無法使用的問題

本文主要講述了怎樣避免 MySQL建表結構時導致表無法使用的問題，我們一起去看看吧！

MySql 在修改表結構的時候可能會中斷產品的正常運行影響用戶體驗，甚至更壞的結果，丟失數據。不是所有的數據庫管理員、程序員、系統管理員都非常了解Mysql能避免這種情況。DBA會經常碰到這種生產中斷的情況，當升級腳本修改了應用層和數據庫層，或者缺乏經驗的管理員、開發在不是很了解Mysql內部工作機制的情況下修改了規范文件。

真相是：

直接修改表結構的過程中會鎖表(在5.6版本之前)

在線的數據定義語言在5.6版本不總是在線的而且也會鎖表

就算使用Percona工具包(在線修改定義文件)也會有若干個步驟會鎖表

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

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

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

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

說實話，鎖表操作會經常被忽視，在操作30M大小的表時我們更傾向於直接修改，但是30G，300G的表就要考慮一下了。當使用率不高或者對鎖定時間要求不是很高的的系統來說直接操作也許更好。可是，我們常常會遇到一個需要立即執行的SQL，或者因為性能問題需要緊急增加一個索引來減少加載時間。

是否需要在系統在線期修改表定義

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

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

 步驟 1: 創建一個新表

 步驟 2: 修改清空表. 這應該比較快,

 步驟3: 創建觸發器來捕獲原始表的改變 <--(鎖定元數據)

 步驟 4: 復制數據.

 步驟5: 重命名表: <--(鎖定元數據

 步驟6: 更新外鍵 如果是子表.

 步驟7: 刪除舊表.

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

最近，我們通知了一個擁有高並發高事務量系統的用戶運行pt-osc在大型數據表上。這件事對於他們來說很平常，幾小時後我們的客服被告知該客戶遇到了最大連接數超過的問題。這個問題是如何產生的呢?當pt-osc運行到步驟五的時候會嘗試去鎖定數據並重命名原表和隱藏表，然而這不會在開啟事務的時候立即執行，因此這條線程會被排在重命名後面。這表現在用戶應用上就是系統停機。數據庫無法開啟新的連接並且所有的線程都被阻塞在重命名命令之後。

數據被鎖

5.5.3版本的說明，當開啟一個事務時會鎖定它會用到的所有表的數據(不依賴於存儲引擎)，並在事務提交的時候釋放鎖。這樣做確保了在開啟事務期間不能修改表的定義。

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

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

wait_timeout=10000

innodb_lock_wait_timeout=1

lock_wait_timeout=60

當使用 &ndash;tries我們可以顆粒化地鑒別操作，嘗試次數、在嘗試的間隔等待。這種組合可以確保pt-osc在合適的時機殺掉自己的等待會話進程，確保線程堆棧的空閒，並提供給我們循環操作來獲取管理因觸發器、重命名、修改外鍵而造成的鎖。

&ndash;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&ndash;tries

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

選擇合適的DDL操作

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

-大多數情況下pt-osc正是我們所需要的

-在很多案例中pt-osc是需要的，但是用法需要稍作調整

-在少數情況下pt-osc不是很合適，我們需要考慮本地阻塞修改，或者采用轉移的操作改成在副本集中復制。

以上就是精品為大家整理的怎樣避免 MySQL建表結構時導致表無法使用的問題的文章，希望對大家有所幫助。