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

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

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

前言

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

1 當(dāng)前面臨的問題

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

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

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

2 問題的解決方案

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

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

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

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

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

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

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

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

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

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

3 緩存表設(shè)計(jì)

db_cache

字段說明：

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

sql_hash字段為表中的唯一字段，用來檢索查詢對(duì)應(yīng)的sql語(yǔ)句用。同時(shí)保障緩存能被迅速檢索到。如果需要就進(jìn)行數(shù)據(jù)更新?？紤]到如此長(zhǎng)的字符串進(jìn)行哈希重復(fù)概率很低，因此可以放心存儲(chǔ)。

result_json將查詢結(jié)果做成Json數(shù)據(jù)來存儲(chǔ)。因?yàn)榻Y(jié)果數(shù)據(jù)的結(jié)構(gòu)是不定項(xiàng)，未必只是一個(gè)值?？赡苁歉鞣N數(shù)據(jù)對(duì)象。

is_fresh加上是否自動(dòng)刷新，一旦開啟，則后臺(tái)開啟一個(gè)服務(wù)對(duì)數(shù)據(jù)進(jìn)行處理。定時(shí)進(jìn)行數(shù)據(jù)刷新。

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

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

4 后臺(tái)定時(shí)刷新數(shù)據(jù)機(jī)制

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

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

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

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

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

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

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