轻量级低成本高性能的大表count/sum方案

需求与背景

需求

需求比较明确，有个表相对来说比较大，有800多万行的数据，现在需要按天对其做count/sum操作，需要能实时得到结果，需要支持门店、大区、全国等范围的查询，需要按月统计，门店不多，1000以内（门店不会增加太多，基本上可以认为是1000以内）

资源情况

资源是来了创业公司后觉得落差最大的一点，现阶段的资源以及数据库的使用方式：

• 没有分库分表，800万数据在同一个表内
• 数据库资源非常差，4C8G的机器，支撑的tps/qps都非常小，所说百级别都可能达不到

现状总是难以让人满意，咱们就不吐槽了

难点

使用目前的mysql资源做count/sum，根本就跑不出结果来

常用方案

• 离线计算：公司有hive的部署及相关负责人在运维，通过hive做离线计算可得到结果，但是无法满足实时性的需求
• 流式计算：可做到毫秒级延迟，公司无部署，不可能为这个需求部署一套，成本太高了
• 使用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(
    id int unsigned auto_increment,primary key,
    entity varchar(128) not null comment ‘表示主体，门店id’,
    time_value bigint not null comment ‘时间，需要精确到天，即timestamp去掉时分秒以及毫秒’,
    count_value bigint default '0' not null comment ‘需要的count值’,
    sum_value decimal default '0' not null comment ‘需要的sum值’,
    create_time datetime default CURRENT_TIMESTAMP not null,
    modify_time datetime default CURRENT_TIMESTAMP not null,
        flag varchar(32) default ‘’ not null comment ‘作为业务意义的标识字段，对兼容性的考虑，假如count或者sum的业务意义有变化，可以通过变更flag这个字段将新的数据与老的数据做区分’,
    key uk  unique (entity, time_value)
) comment '记录聚合函数的值 engine=InnoDB;

数据的累加

对于数据的累加，我们需要处理表中没有数据的情况以及有数据的情况，当表中没有数据时需要插入，有数据时需要更新，而插入时，会有并发导致插入出现索引冲突，所以当出现索引冲突后，说明这行数据已经存在，需要更新数据，伪代码如下：

beginTransaction();//开启事务
doBiz();//业务处理
try {
    long daytime = getCurrentDay();//获取当前时间点的精确到天的timestamp
    int updateRowCount = updateCountAndSum(dayTime, storeId);//通过当前时间天与门店id更新aggregation_value表
    if (updateRowCount == 0) {
        //数据不存在，需要插入
        insertAggregationValueWithCountAndSum(dayTime, storeId);
    }
} catch (DataIntegrityViolationException e) {
    //插入失败，有索引冲突
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}, …)