mysql線上修改表結構大資料表的風險與解決辦法歸納

網際網路應用會頻繁加功能，修改需求。那麼表結構也會經常修改，加欄位，加索引。線上直接在生產環境的表中修改表結構,對使用者使用網站是有影響。

以前我一直為這個問題頭痛。當然那個時候不需要我來考慮,雖然我們沒專門的dba，他們資料量比我們更大，那這種問題也會存在。所以我很想看看業界是怎麼做的,我想尋找有沒有更高階的方案，呵呵，讓我覺得每次開發一個新功能，我線上加欄位都比較糾結。後來只知道，不清楚在什麼時候，無意中看到一個資料介紹online-schema-change這個工具，於是順便搜出了不少東西。後來逐漸發現騰訊，淘寶他們都會存在這種問題，我發現解決思路都差不多。具體看完我這篇歸納的文章

 

由於mysql線上ddl(加欄位、加索引等修改表結構之類的操作）過程如下：

 A.對錶加鎖(表此時只讀)
B.複製原表物理結構
C.修改表的物理結構
D.把原表資料匯入中間表中，資料同步完後，鎖定中間表，並刪除原表
E.rename中間表為原表
F.重新整理資料字典，並釋放鎖

 

在這個過程中會鎖表。造成當前操作的表無法寫入資料，影響使用者使用。由於需要複製原表的資料到中間表，所以表的資料量越大，等待的時候越長，卡死在那裡(使用者被拒絕執行update和insert操作,表現就是延遲了一直在等待)。

其實就是對錶加了個排它鎖，這個時候其他使用者只能讀表的資料,不能寫。想具體體驗一下是什麼效果，我以前測驗對mysql的表加鎖，操作的時候是如何的：http://www.cnblogs.com/wangtao_20/p/3463435.html

 

平時進行修改表的結構，更改欄位，新增欄位，更改欄位名稱一般都是透過ALTER TABLE  TABLENAE 語法進行修改的。對於測試庫，線上小表或者併發訪問不是很大的情況是OK。但是如果是線上大表。那就很麻煩。由於表資料量大，複製表需要比較長的時間，在這個時間段裡面，表是被加了鎖的(寫鎖)，加寫鎖時其他使用者只能select表不能update、insert表。表資料量越大，耗時越長。

 

所以，對於資料量大的表，數量很大。線上修改表結構一直是一個頭痛的問題，因為網際網路應用的一大特點不能影響使用者正常使用，否則使用者會慢慢流失掉。

 

 

有些公司碰到的表資料很小，幾萬到幾十萬行資料一張表，可能還不會遇到應用卡死的問題。所以我們網站在跑，開發個新功能，需要加個新欄位,經常是直接操作不會影響什麼(何況只是延遲寫入操作而已,呵呵)

看這幾篇文章就知道了：

1、http://wiki.hexnova.com/pages/viewpage.action?pageId=2031684 mysql線上修改表欄位造成的鎖表

2、

3、比如就有人專門在加欄位之前進行測驗mysql是否複製表，以減低應用卡死的風險：http://www.cnblogs.com/zuoxingyu/archive/2013/03/28/2986715.html

複製表結構，然後插入少量的資料。去修改表結構。看影響的行。如果為0，則表示不會複製中間表的方式

 

 

 

目前業界實踐出了一些成熟的解決辦法：

1、很多公司以前的做法是：停掉mysql伺服器來修改表結構。然後進行滾動式更新。比如很多臺mysql伺服器。先修改主伺服器的表結構，把這臺伺服器停掉來更新(一般多臺主伺服器，讓其他主伺服器提供服務)。等到更新完，就滾動到從伺服器(在此之前是其他從伺服器提供服務的)。其實想想發現有個弊病：修改表結構要等到很長時間才能生效。mysql伺服器越多，就需要的時間越長。那我可以理解：假設需要幾天，那只有等到更新完畢。才能把程式碼丟上去，因為表結構沒有更新完畢，新的程式操作新的欄位會出錯的。

 

從馮大輝那篇文章那裡聽說，Facebook數千臺MySQL伺服器在過去增加個索引需要幾個月的滾動升級(後來他們自己開發了後面提到的工具，只需要幾天)

 

能夠停掉mysql伺服器來修改欄位，這就好辦,時間長也無所謂,呵呵，至少使用者不會使用你網站的時候卡死吧。但是網際網路應用往往不能影響使用者使用，所以很多公司儘量是在凌晨的時候進行操作(這個時候訪問使用者少，對使用者影響就小)

比如像這個例子：

表的資料量上億。要把表的儲存引擎從myisam改為innodb(我覺得儲存結構都不同了，轉換需要時間更長)，但是他是停掉mysql伺服器操作的。

阿里巴巴的馮大輝分享中也提到，業務應用大，需求就會頻繁變化。所以就經常涉及到修改資料庫欄位，線上的調整欄位是要考慮很多的問題的。作者認為，目前沒有特別的方法來解決這個問題(技術是適應需求變化，支撐運營的)。他說豆瓣對此也很頭痛，只能把伺服器短暫的停一下。

 

2、測驗法。加欄位，加索引，先在測試環境模擬測試一下需要多長時間。免得伺服器生產環境正式加的時候,應用卡死了，好有個預期準備。

我記得以前在a公司,表資料量也上千萬,壓根就沒這種測驗吧，大白天，就直接加欄位和索引,反正我也不知道前臺影響如何,當時我也沒這個經驗,何況我也不是技術負責人,呵呵，技術負責人都沒不清楚這個，我那就更加沒了，那個技術經理是做企業級開發的，跟web開發環境和思維方式是不同的,他不清楚會存在這些影響吧，再說，當時在加的時候沒法湊巧使用者投訴說，網站無法下單了啊,沒這麼巧的情況。其實從我現在理解角度來看，我絕對會更加嚴格點。

 

前面也提到了，國外有人研究修改表結構會不會複製一張臨時表，就看"rows affected “的值。如果為0，則表示不會複製中間表的方式,這樣子就很快的。我沒試過

 

3、使用專門的輔助工具。一些公司開發了自己的內部工具來輔助進行。比如facebook。

另外騰訊的技術也介紹了他們自己定製的tmysql進行線上加欄位的實現原理：

 

facebook自己開發的工具，官網：

 

 

----------------------------------------------------------------------

我記得好像最先是facebook進行了方案創新(呵呵，當你的遇到的是複雜問題,沒有人解決過就只能先創造新的技術方案了)，當時馮大輝專門寫了一篇，其他文章介紹他們的創新。其他工具都是跟這個思路差不多的。不過我下載了facebook官網的，是用php實現的。沒具體看。因為下載的包裡面都是php檔案。

 

 

總的來說，這些工具大致的理大同小異：表結構的修改在建立的一張新表中執行(這樣不需要鎖定原表了，也就不會影響mysql提供服務)，更為關鍵的是解決了一個問題，當這個間隔時間內，使用者在使用mysql，對錶資料進行了更新怎麼辦？

 

工具的解決辦法思路是，在原表中建立幾個觸發器針對uptate、delete 、insert操作都記錄下來，這樣子把對原表的操作記錄下來，方便更新到新建立的臨時表中中去。

 

 

聽過豆瓣網的架構變遷分享會中提到，他們以前在這方面也吃過苦頭的，一張很大的表(比如上千萬)，線上加個索引，由於資料量大，整個應用就卡死了。

其實有時候卡幾個小時可能都很正常。死鎖了嘛。另外對臨時表要進行復制資料，建立這個臨時表也需要時間嘛。

 

他們現在用的辦法是：先複製一張一模一樣的表，數量也是差不多，先在這張表上面測試，看看需要多長時間。如果幾分鐘，是在可以接受的時間範圍內，就可以。如果幾個小時就不行了。這樣子提早預先知道。

另外，也使用了online-schema-change這個工具。

 

關於online-schema-change

是percona推出的一個針對mysql線上ddl的工具

percona是一個mysql分支維護公司，專門提供mysql技術服務的。我的理解，類似於linux的分支redhat公司

官網下載地址為：

 

騰訊，淘寶，百度這些公司多少都有自己開發的工具來解決這個頭痛的問題。

 

 

另外，mysql5.5企業版是支援線上ddl了，不過企業版要收費嘛

 

MySQL 5.6改進了安全功能，例如對關鍵配置檔案和使用者密碼的加密方式，但是對InnoDB的提升才是大新聞。使用該儲存引擎的最後一個主要障礙便是缺少全文索引，但是現在這個問題已經不復存在了。

該版本還為InnoDB引入了線上DDL，DBA一定會非常喜歡這個功能。增加、重新命名和刪除列等常用的操作可以和併發查詢同時執行。儘管可能涉及到一些資料的複製或重組，但是大多數線上DDL操作都能夠就地執行。