需求與背景
需求
需求比較明確，有個表相對來說比較大，有800多萬行的數據，現在需要按天對其做count/sum操作，需要能實時得到結果，需要支持門店、大區、全國等範圍的查詢，需要按月統計，門店不多，1000以內（門店不會增加太多，基本上可以認為是1000以內）
資源情況
資源是來了創業公司後覺得落差最大的一點，現階段的資源以及數據庫的使用方式：
· 沒有分庫分表，800萬數據在同一個表內
· 數據庫資源非常差，4C8G的機器，支撐的tps/qps都非常小，所說百級別都可能達不到
現狀總是難以讓人滿意，咱們就不吐槽了
難點
使用目前的mysql資源做count/sum，根本就跑不出結果來
常用方案
1． 離線計算：公司有hive的部署及相關負責人在運維，通過hive做離線計算可得到結果，但是無法滿足實時性的需求
2． 流式計算：可做到毫秒級延遲，公司無部署，不可能為這個需求部署一套，成本太高了
3． 使用olap：非常適合，apache drill可支持cube，完全能滿足需求，且開發成本低，但是公司同樣無任何olap產品的部署，成本太高
因此，常用方案在現階段行不通，只能想其它的方案
可行方案
有沒有方法可以解決這樣的問題呢？當然是有的，可以使用預計算的方式，不僅成本低，不需要額外部署任何引擎直接使用原來的mysql，而且可以做到無延遲，下面詳細描述具體方案
基本思路
· 基於時間對count/sum提前做累加
· 每個門店每天一個累加的count和sum，當原始表中有數據插入後，更新累加值，count+1，sum則加上業務值
· 1000個門店，一天只有1000個count和1000個sum，一個月最多31天，那麼一個月最多31000個count和31000個sum
· 統計數據，可分為兩類需求：
· 查當月/當天的，要實時查，需要直接從db裡查詢
· 查歷史月份的數據，則可以通過hive計算出彙總後的結果
· 只需要保留一個月的數據，歷史數據不需要保留，歷史數據在經過hive做計算得到最終的結果後就可以刪除了，所以最多隻有3萬級別的count和sum
· 查詢時，根據索引直接查出當天的count和sum
· 想要查詢大區或者全國的數據時，也只需要從這3萬個count或3萬個sum中通過in查詢或者or查詢做統計，已有的mysql資源完全沒問題
· 在設計數據庫時，將count和sum作為兩個不同的字段，因此一行數據可以同時表示出一個門店一天的count和sum，總體數據量3萬級別
將數據量控制在3w級別後，即使對全表做統計，也不會有什麼問題，通過這種思路將問題簡化了，但是這樣做有一個缺點就是歷史數據如何處理，代碼剛上線的時候，肯定是沒有歷史結果的，可以通過hive對歷史數據做一次彙總，並導入到結果表中
建表
在原有的mysql中建以下表：
create table aggregation_value(
\tid int unsigned auto_increment,primary key,
\tentity varchar(128) not null comment ‘表示主體，門店id’,
\ttime_value bigint not null comment ‘時間，需要精確到天，即timestamp去掉時分秒以及毫秒’,
\tcount_value bigint default '0' not null comment ‘需要的count值’,
\tsum_value decimal default '0' not null comment ‘需要的sum值’,
\tcreate_time datetime default CURRENT_TIMESTAMP not null,
\tmodify_time datetime default CURRENT_TIMESTAMP not null,
flag varchar(32) default ‘’ not null comment ‘作為業務意義的標識字段，對兼容性的考慮，假如count或者sum的業務意義有變化，可以通過變更flag這個字段將新的數據與老的數據做區分’,
\tkey uk unique (entity, time_value)
) comment '記錄聚合函數的值 engine=InnoDB;
數據的累加
對於數據的累加，我們需要處理表中沒有數據的情況以及有數據的情況，當表中沒有數據時需要插入，有數據時需要更新，而插入時，會有併發導致插入出現索引衝突，所以當出現索引衝突後，說明這行數據已經存在，需要更新數據，偽代碼如下：
beginTransaction();//開啟事務
doBiz();//業務處理
try {
\tlong daytime = getCurrentDay();//獲取當前時間點的精確到天的timestamp
\tint updateRowCount = updateCountAndSum(dayTime, storeId);//通過當前時間天與門店id更新aggregation_value表
\tif (updateRowCount == 0) {
\t\t//數據不存在，需要插入
\t\tinsertAggregationValueWithCountAndSum(dayTime, storeId);
\t}
} catch (DataIntegrityViolationException e) {
\t//插入失敗，有索引衝突
updateCountAndSum(dayTime, storeId);//通過當前時間天與門店id更新aggregation_value表
}
commitTransaction();//提交事務
查詢
查詢比較簡單，直接查aggregation_value表：
· 查詢門店一天數據
select count_value, sum_value from aggregation_value where time_value = #{time} and entity = #{storeId}
· 查詢門店一月的數據，在建表時，索引字段為（entity, time_value），因此可通過time_value做範圍查詢
select count_value, sum_value from aggregation_value where entity = #{storeId} and time_value >= #{start} and time_value <= #{end}
· 查詢全國
select count_value, sum_value from aggregation_value where time_value = #{time} and entity in (#{storeId}, …)
閱讀更多 無醉 的文章
