摘 要:隨著開發(fā)的一款手機應(yīng)用用戶的快速增長,開始階段設(shè)計的MySQL數(shù)據(jù)庫和服務(wù)器架構(gòu)無法滿足業(yè)務(wù)增長的實際需求,數(shù)據(jù)量過大導致前端手機應(yīng)用API接口響應(yīng)慢,同時后臺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)化方式。以最小代價完成了適應(yīng)大量數(shù)據(jù)的MySQL優(yōu)化。
關(guān)鍵詞:移動應(yīng)用;MySQL;優(yōu)化;PHP
前言
開發(fā)的一套手機應(yīng)用的后端服務(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死或者低速響應(yīng),造成客戶端無法得到正常響應(yīng)。鑒于這種問題,我們對后臺統(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é)果做出相應(yīng)調(diào)整,考慮增加簡單sql查詢緩存,解決了按天查詢的時候每天固定數(shù)據(jù)緩存的問題,提升效果明顯,但是查當天數(shù)據(jù)的時候由于數(shù)據(jù)量還是很大,所以依然影響速度。進一步考慮的方案是,sql每次都查全表,但是后臺管理界面查詢時直接返回緩存數(shù)據(jù),但是運行一個更新數(shù)據(jù)的服務(wù),每10分鐘在后臺運行一次緩存對應(yīng)的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字段為表中的唯一字段,用來檢索查詢對應(yīng)的sql語句用。同時保障緩存能被迅速檢索到。如果需要就進行數(shù)據(jù)更新??紤]到如此長的字符串進行哈希重復概率很低,因此可以放心存儲。
result_json將查詢結(jié)果做成Json數(shù)據(jù)來存儲。因為結(jié)果數(shù)據(jù)的結(jié)構(gòu)是不定項,未必只是一個值??赡苁歉鞣N數(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)過測試問題解決!