MySQL數(shù)據(jù)庫后臺優(yōu)化方案

摘 要：隨著開發(fā)的一款手機應用用戶的快速增長，開始階段設(shè)計的MySQL數(shù)據(jù)庫和服務(wù)器架構(gòu)無法滿足業(yè)務(wù)增長的實際需求，數(shù)據(jù)量過大導致前端手機應用API接口響應慢，同時后臺web運營統(tǒng)計的速度也非常慢，簡單的進行一些SQL語句級別的優(yōu)化無法滿足實際需求，因此對整體架構(gòu)的優(yōu)化勢在必行。文章針對實際業(yè)務(wù)邏輯需要基于現(xiàn)有硬件架構(gòu)的基礎(chǔ)上，闡述分析業(yè)務(wù)瓶頸和最小改動下對后臺統(tǒng)計功能的數(shù)據(jù)庫查詢優(yōu)化方式。以最小代價完成了適應大量數(shù)據(jù)的MySQL優(yōu)化。

關(guān)鍵詞：移動應用；MySQL；優(yōu)化；PHP

前言

開發(fā)的一套手機應用的后端服務(wù)器支持系統(tǒng)，目前的業(yè)務(wù)是前端兩臺Web服務(wù)器做HA，一臺服務(wù)器做圖片服務(wù)器。前端web服務(wù)器承擔的是用戶訪問和手機客戶端的API接口數(shù)據(jù)處理工作，要求處理速度快但是沒有大規(guī)模查詢。每天用戶訪問量大約為5-10萬IP，分別用兩臺web做HA負載。兩臺Web共用一個數(shù)據(jù)庫保持數(shù)據(jù)一致性，另外使用一臺服務(wù)器數(shù)據(jù)庫做數(shù)據(jù)庫從庫。從庫作用是在主庫死機之類的問題出現(xiàn)的時候，切換到從庫來進行服務(wù)。但是實際起到的作用只是一個備份設(shè)備而已。

1 當前面臨的問題

每次后臺進行統(tǒng)計查詢，由于數(shù)據(jù)總量過大，查詢?nèi)砭蜁斐杀礞i死或者低速響應，造成客戶端無法得到正常響應。鑒于這種問題，我們對后臺統(tǒng)計功能進行一些優(yōu)化處理。

問題的原因很容易定位，就是表數(shù)據(jù)量太大，可是業(yè)務(wù)的邏輯又不允許進行簡單分表。嘗試做表分區(qū)效果也很一般。查詢大表的速度實在難以接受：例如lee_userlog表數(shù)據(jù)已經(jīng)有兩千六百多萬條數(shù)據(jù)，每天需要查詢這個表，看數(shù)據(jù)總體統(tǒng)計情況。具體問題分析如下：

直接加memcache的緩存，生存時間10秒-60秒不等。好處是實施方式簡單，但是非常不適合我們的業(yè)務(wù)，因為每次生成緩存的查詢還是非常慢，并且由于每次查詢后間隔至少10分鐘才查第二次，因此這種簡單的緩存方式根本起不到任何作用，這樣的查詢并不十分頻繁，每小時大約要看2-3次，因此直接做緩存是沒有意義的，因為第一次查的時候還是慢，第二查完以后可能十幾分鐘后才查第二次。如果將結(jié)果緩存到半小時以上，將導致兩次查詢結(jié)果完全相同，導致無法對運營狀態(tài)進行有效的數(shù)據(jù)分析，運維人員沒法根據(jù)統(tǒng)計結(jié)果做出相應調(diào)整，考慮增加簡單sql查詢緩存，解決了按天查詢的時候每天固定數(shù)據(jù)緩存的問題，提升效果明顯，但是查當天數(shù)據(jù)的時候由于數(shù)據(jù)量還是很大，所以依然影響速度。進一步考慮的方案是，sql每次都查全表，但是后臺管理界面查詢時直接返回緩存數(shù)據(jù)，但是運行一個更新數(shù)據(jù)的服務(wù)，每10分鐘在后臺運行一次緩存對應的sql語句，將結(jié)果更新到結(jié)果集中，這樣的設(shè)計犧牲了部分實時性，但符合實際使用的情況，至少間隔10分鐘才會進行查詢查看統(tǒng)計結(jié)果。同時大大增加用戶使用的查詢速度。瞬間即可得到結(jié)果，根據(jù)這種思路我們分析實際方案。

2 問題的解決方案

首先可以做的最簡單的事情就是把前端用戶使用數(shù)據(jù)庫和后臺查詢數(shù)據(jù)庫分開，這樣進行讀寫分離后至少進行大規(guī)模查詢不會造成業(yè)務(wù)卡死。我們建立一個只讀的用戶連接到從庫，用戶權(quán)限設(shè)置為只允許進行SELECT操作。

其次根據(jù)實際業(yè)務(wù)邏輯將問題拆解為三種情況：

（1）按日存儲統(tǒng)計結(jié)果類型數(shù)據(jù)，每天會產(chǎn)生數(shù)據(jù)，但是過了當天數(shù)據(jù)就變?yōu)殪o止數(shù)據(jù)。統(tǒng)計結(jié)果不會改變。

（2）數(shù)據(jù)總體情況統(tǒng)計，整個表的數(shù)據(jù)記錄，求和等操作。隨時跟著時間在增加或減少。

（3）更復雜的統(tǒng)計情況，數(shù)據(jù)按日統(tǒng)計，但統(tǒng)計出來的數(shù)據(jù)也隨時會變化。

第一種情況：按日統(tǒng)計的，在第一次查詢的時候保存為一條數(shù)據(jù)記錄通過查詢時間來分析，如果含有當天記錄則不緩存：

if （date（'Y-m-d'，$puttime）！=date（'Y-m-d'） date（'Y-m-d'，$endtime）！=date（'Y-m-d'））

如果是按日統(tǒng)計數(shù)據(jù)，不含有當天數(shù)據(jù)的，直接緩存統(tǒng)計結(jié)果到另一個緩存表中。

第二種情況：我們將數(shù)據(jù)統(tǒng)計的SQL語句整體緩存到緩存表中，后臺做一個服務(wù)程序定時更新這個表，維持總體統(tǒng)計數(shù)據(jù)為準實時，這一過程大約每10分鐘會更新一次，稍有滯后但是依然能滿足實際需求。

第三種情況：我們記錄每次更新數(shù)據(jù)的時間點和結(jié)果。新一次查詢的時候，由于數(shù)據(jù)永遠處于增量情況，因此我們只需要查出上次時間點到當前的增量數(shù)據(jù)統(tǒng)計情況合并如上次緩存結(jié)果中，同時更新緩存結(jié)果到當前時間點即可。這樣查詢量減少了幾個數(shù)量級，速度也就有了保障。

3 緩存表設(shè)計

db_cache

字段說明：

sql_name插入值的之前要將插入sql語句做處理，防止sql嵌套出現(xiàn)問題。

sql_hash字段為表中的唯一字段，用來檢索查詢對應的sql語句用。同時保障緩存能被迅速檢索到。如果需要就進行數(shù)據(jù)更新。考慮到如此長的字符串進行哈希重復概率很低，因此可以放心存儲。

result_json將查詢結(jié)果做成Json數(shù)據(jù)來存儲。因為結(jié)果數(shù)據(jù)的結(jié)構(gòu)是不定項，未必只是一個值。可能是各種數(shù)據(jù)對象。

is_fresh加上是否自動刷新，一旦開啟，則后臺開啟一個服務(wù)對數(shù)據(jù)進行處理。定時進行數(shù)據(jù)刷新。

alive_time增加緩存數(shù)據(jù)生存時間，超過時間，則會被清除。

time數(shù)據(jù)表字段time被定義為記錄數(shù)據(jù)最后修改時間，這樣可以方便檢驗數(shù)據(jù)的更新情況。

4 后臺定時刷新數(shù)據(jù)機制

（1）定時查詢緩存表，清除超過生存時間的緩存數(shù)據(jù)。

（2）定時查緩存表，把需要刷新的數(shù)據(jù)sql掉出來，跑一次結(jié)果。并更新到緩存數(shù)據(jù)中。

（3）由于一些特殊的函數(shù)中查詢出來的結(jié)果要求特別處理一

下，例如有些數(shù)據(jù)要查出來后再統(tǒng)計一次或者只需要一個最后結(jié)果。這樣既節(jié)省了存儲空間，也節(jié)省了多次重復計算的資源。但是因為這個需要非常個性化，所以我們引入func_name字段進行存儲。

（4）大多數(shù)需求都不需要個性化處理，所以我們使用php面向?qū)ο蟮哪g(shù)方法__call（）來簡化代碼。

這樣節(jié)省傳入?yún)?shù)和人為輸入可能出現(xiàn)的寫錯函數(shù)名等問題的發(fā)生。

后臺使用駐留服務(wù)的形式，這樣的方式在實際使用中出現(xiàn)問題。原因是長期連接從庫造成從庫卡死，主從無法同步。為了解決這樣的問題，繼續(xù)修改代碼，每次長時間查詢完成都自動斷開庫，然后重連。經(jīng)過測試問題解決！