报表增量更新UT模式

28 Feb 2018

解决问题

  1. “报表”这里理解为缓存表, 可以随时从其他”事实表”重新计算得到
  2. B报表依赖A数据源时候, 数据一致性问题
  3. 以及在订正数据的时候, 所有关联报表的自动更新机制

对于汇总数据, 最简单的方式是创建视图, 但是数据量大时, 对于视图的查询过滤会非常慢, 所以需要将常见聚合查询结果缓存下来.

可以使用类似物化视图. 不过物化视图的实现方式一般是全量的, 而且是每次更新触发的, 会导致性能非常差. 另外也不灵活.

所以业务上一般的做法, 还是根据不同数据需求, 做不同的报表.

UT模式简介

一般而言, 日志会有一个发生时间(如点击时间, 回调时间戳), 和实际更新到数据库时间区别, 为了可重跑性, 一般按照日志发生时间来统计报表.

每个数据源/报表每条字段有个最后更新时间戳(UT). 依赖于A数据源的下游表B, 定期执行汇总查询操作同步数据, 在每次统计的时候记录上次最后同步的UT. 每次执行时:

用过Kafka/Kinesis等队列的朋友会发现, 这个UT实际上就是消费的游标位置 (Sequence Number), 只不过我们吧时间戳当作单增游标. 但是这里不需要保证每一条数据的游标唯一性, 因为一批次更新的数据使用同一个UT木有问题.

说明

MySQL例子

-- 订单表
create table orders (
    tid int,
    uid int comment '账号',
    gid int comment '产品',
    amount decimal(8,2) comment '金额',
    status tinyint default 0 comment '状态: 0未付款/1到账/2退款/...',
    ct timestamp not null,
    ut timestamp not null default current_timestamp on update current_timestamp,

    dt char(10) generated always as (date(ct)) virtual,
    primary key (tid),
    key (ut, dt, gid)
);

-- 报表(缓存表)
create table income (
    dt date,
    gid int,
    income decimal(8,2),
    ut timestamp not null default current_timestamp on update current_timestamp,
    key (ut),
    primary key (dt, gid)
);

-- 定时统计任务, 即便历史上一条订单状态变更了也会统计到
-- 注意不能跟据status做where筛选, 也不能做having过滤
insert into income (dt, gid, income)
select dt, gid, sum(case status when 1 then amount else 0 end) as income from orders
where (dt, gid) in (select distinct dt, gid from orders where ut between @last_ut and now())
-- and status=1
group by dt, gid
-- having income > 0
on duplicate key update income = values(income)
;

HOME