MySQL主從伺服器的一些技巧

問題：主從伺服器表類型的選擇

一般的共識是主伺服器使用innodb，從伺服器使用myisam，以便各盡其能。

問題：主從伺服器欄位類型的選擇

欄位類型對於分頁等操作有很大影響。主伺服器一般是innodb，因為不涉及查詢，所以可以使用varchar等來儲存字串來節省空間的，從伺服器一般是 myisam，因為涉及查詢，所以必須在char和varchar之間仔細權衡，沒有varchar, text, blob欄位的表是靜態表，反之是動態表，靜態表的檢索效率要比動態表好若干倍，一般來說，所有涉及大結果集的查詢都應該儘可能保證在靜態表上完成，這裡說一個例子：比如說常見的articles表有title(varchar), body(text)等欄位，在做文章列表的時候，因為不是靜態表，所以查詢不會很快，下面開始重構解決方案：把原來的articles表拆分成 subjects表和contents表，title欄位設定為一個足夠的char類型放在subjects表裡，body欄位還保持是text類型放到 contents表裡，subjects和contents表之間的關係是一對多，這樣，順帶著也方便的實現了多頁文章的功能，而且更重要的是在查詢文章列表的時候，操作都是在subjects靜態表裡完成，效率肯定會比前一種方案提升很多。

問題：主從伺服器NOW()函數造成資料不一致

假設在主伺服器上執行一條INSERT …. VALUES ( …, NOW())，那麼在從伺服器上也會同樣執行一條的SQL語句，但是一來主從伺服器各自的時間設定可能就不一致，二來主從伺服器間的SQL同步也可能存在時間上的的延遲，這樣，NOW()在兩台伺服器上的結果就可能不一致。解決方案是顯而易見的，就是不要使用NOW()，時間的計算在應用程式裡完成。這裡介紹一個額外的小技巧：在PHP裡如果想獲得目前時間的時間戳記，不要用time()，而應該使用$_SERVER[‘REQUEST_TIME’] （PHP版本大於5.1有效），這樣少做了一次系統調用，更有效率。

問題：主從伺服器讀寫分離時讀操作失敗

先重現一下問題：比如說添加一條新資料，添加成功後根據last_insert_id跳轉到新添加資料的瀏覽頁面。在此過程中添加新資料的操作是在主伺服器上完成的，瀏覽新資料的操作實在從伺服器上完成的，不過由於主從伺服器間SQL同步存在延遲，所以當使用last_insert_id在從伺服器上查詢的時候，從伺服器很可能還沒有還沒來得及同步到此記錄，所以讀操作失敗。解決思路也不複雜，在代碼裡加入一個緩衝層（可以使用memcached），新添加的資料都順手放到緩衝層裡一份，新資料的讀操作也先查詢快取層，這樣就不會再有讀操作失敗的問題了，當然刪除或者更新資料的時候也要順帶著處理好快取資料，可以使用觀察者模式來搞定。不過這樣緩衝方案只限於讀取單一的記錄，對於讀取列表的記錄的情況，則是無效的。

問題：主從伺服器索引是否有必要保持一致

一般都是利用主從伺服器完成讀寫分離，從伺服器上進行讀操作，主伺服器進行寫操作，這樣的話，主伺服器上僅保留主鍵，外鍵，唯一索引等必要的索引即可，以 便保持資料合法性，而對於那些原本用於最佳化SELECT操作的索引，可以全部刪除，如此的話主伺服器的寫操作效率會提升很多。