一點關於MySQL參數delay_key_write、myisam_recover_options的使用經驗
最近在做數據庫實例遷移的時候遇到了幾個比較詭異的問題：
MyISAM實例正常shutdown後rsync數據文件到另外一台機器上起實例後，訪問表時提示表自動修復失敗需要repair table。提示信息：Table './test/record_03' is marked as crashed and last (automatic?) repair failed
表損壞後，利用repair table命令將表修復時提示表裡存在很多的duplicate key.
warning : Duplicate key for record at 129584986 against record at 62008769
warning : Duplicate key for record at 104678355 against record at 61426294
warning : Duplicate key for record at 297493788 against record at 61697778
warning : Duplicate key for record at 209950328 against record at 61548867
warning : Duplicate key for record at 105894968 against record at 61866949
...
...
補充一下當時的環境信息：
1.MySQL版本為官方版的5.5.12，表引擎是MyISAM
2.遷移的步驟：stop slave->flush tables->正常shutdown實例->rsync表文件->在新機器上起實例。rsync前後表文件MD5值一樣。
3.表的結構類似這樣：
[sql]
create table test (
id int auto_increment primary key,
a varchar(100) not null,
b int not null,
c int,
unique key (a,b)
)
首先說第一個問題，這裡分兩點：1.為什麼會需要修復？2.為什麼自動修復失敗？
我是正常關閉實例，不明白表需要修復的原因於是請淘寶丁奇幫忙分析了數據文件，顯示文件頭裡記錄顯示表被關閉仍有6個線程在訪問。但我可以確定當時rsync數據文件前是正常shutdown實例的，這個可能是MyISAM本身的問題或者是本身數據文件就已經存在問題都不得而知，總而言之當我遷移過來後訪問表時需要修復這點是合理的，因為表被關閉時還有線程在訪問，這會被MySQL認為表非正常關閉，需要修復。
為什麼會自動修復失敗，這是由於我配置了參數myisam_recover_options=default，這個配置表示每次訪問MyISAM表之前都會先檢測表是否需要修復，如果需要則自動進行，這也就是前面看到信息last (automatic?) repair failed。而修復失敗是因為這個參數帶來的修復行為默認是從key cache裡面找需要修復的數據，而我當時是shutdown實例，rsync到新環境中起實例，此時已沒有當時的現場(key cache環境)，加上default不會強制進行修復（強制修復表如果索引文件和數據文件數據不一致則自動進行刪除或者增加行），（如果是myisam_recover_options=force，那麼即使此時key cache不存在了也會進行強制修復，此時做的就是對比數據文件和索引文件，然後刪除數據文件中多余的行，因此這樣可能會丟數據）。在自動修復時可以再error log中看到如下信息：
130410 9:31:23 [ERROR] /usr/local/mysql55/bin/mysqld: Table './image_0/record_00' is marked as crashed and should be repaired
130410 9:31:23 [Warning] Checking table: './image_0/record_00'
130410 9:31:24 [ERROR] Got an error from unknown thread, /export/home/pb2/build/sb_0-3198286-1302530066.93/mysql-5.5.12/storage/myisam/ha_myisam.cc:868
130410 9:31:24 [Warning] Recovering table: './image_0/record_00'
130410 9:31:28 [Note] Retrying repair of: './image_0/record_00' with keycache
130410 9:31:37 [ERROR] Couldn't repair table: image_0.record_00
接著說第二個問題，在第一個問題中提示表損壞，需要手工repair table，而我執行了這條命令後提示表裡存在很多重復鍵數據:
warning : Duplicate key for record at 129584986 against record at 62008769
warning : Duplicate key for record at 104678355 against record at 61426294
warning : Duplicate key for record at 297493788 against record at 61697778
warning : Duplicate key for record at 209950328 against record at 61548867
warning : Duplicate key for record at 105894968 against record at 61866949
這個重復鍵指的是唯一鍵(a,b)，然後進行了一個小測試驗證是數據文件裡面有重復的行（在a,b字段上）。邏輯圖大概是這樣
[plain]
id a b c
1 1 2 3
2 1 2 4
3 2 3 55
4 2 3 54
上面這個列表是我自己模擬的，在(a,b)上有唯一鍵約束，但是在數據文件中卻存在(a,b)重復的行。驗證方法較簡單：
/*強制走全表掃描*/select * from test where a=val1 and b=val2;
結果集有2行
/*強制走索引*/select * from test where a=val1 and b=val2;
結果集只有1行
當時遇到問題第一反應是開發人員那邊是不是曾經導過數據且導數據的時候禁止了唯一約束檢查，但是事實上開發說自己沒這麼干過，於是開發說這是MySQL的bug，我怎麼也覺得MySQL不會存在這樣大的bug，一直覺得是對MySQL使用方法不對。於是對遷移前後的環境做了對比檢查終於發現了疑點：老的環境中配置了delay_key_write=ALL，myisam_recover_options=OFF（默認是OFF）。而我的新環境中則是delay_key_write=ON，myisam_recover_options=default 。
簡單解釋下delay_key_write,每次更新MyISAM表索引後不立即將更新flush到物理磁盤上，這些修改依然保留在內存中，直到這個表被關閉時再flush，ALL表示對所有的表都使用這一行為，ON表示對創建表時使用了delay_key_write的表使用這個功能。OFF表示不進行delay key write。而官方建議在使用delay_key_write時同時用myisam_recover_options這樣才能保證數據一致性，否則，舉個最簡單的例子，索引數據一致都沒flush到磁盤上，突然表被非正常關閉，等下次表被訪問如果不進行相應的檢測修復，那麼就會出現數據文件與索引不一致的情況，這樣就會導致數據不一致。
再簡單解釋下myisam_recover_options，default表示每次訪問表時會先判斷是否需要修復，但是不會強制修復（僅僅嘗試從key cache中修復），backup表示修復時會先將老的數據文件先做個備份，force表示強制修復，具體用法見下圖。
此時就大致可以確定是由於MySQL參數使用不合理導致的數據不一致問題，所以在使用MyISAM時如果配置了delay_key_write，就千萬記得同時配置一個myisam_recover_options這樣才可能防止數據不一致性的情況出現。那麼我是怎麼解決這個問題的呢？目前來說還是通過遷移後的MySQL配置參數環境保持與老環境一致，否則如果我現在強制加上參數myisam_recover_options，那麼就會導致所有的表損壞，損壞之後恢復的唯一途徑就是repair table，這會丟失數據（或者配置myisam_recover_options=force,backup，這個效果是跟repair table一樣，且同時對將要修改的MYD文件做一個備份），因為對於重復鍵的行，如果是利用repair table去做的話，我無法干預MySQL留下其中具體的哪行，所以如果強行repair table雖然可以解決重復鍵錯誤，但是最終留下的哪行不一定是用戶想要的那行數據。當然還有另外兩種可行的方法，第一，咨詢業務負責人後手工刪除重復鍵中不需要的行。第二，強制repair table 然後將老的數據留一份做備份，然後用戶端發現異常了再進行數據恢復，不過這個做法不可取，否則都會被用戶投訴致死。
總之，都21世紀了，都5.6了，不要再用MyISAM了吧，我這是歷史系統，沒辦法，後期進行升級改造也是必須進行的。
