历史拉链表是一种数据模型,主要是针对数据仓库设计中表存储数据的方式而定义的。所谓历史拉链表,就是指记录一个事物从开始一直到当前状态的所有变化信息。拉所有记录链表可以避免按每一天存储造成的海量存储问题,同时也是处理缓慢变化数据的一种常见方式。
应用场景
现假设有如下场景:一个企业拥有5000万会员信息,每天有20万会员资料变更,需要在数仓中记录会员表的历史变化以备分析使用,即每天都要保留一个快照供查询,反映历史数据的情况。在此场景中,需要反映5000万会员的历史变化,如果保留快照,存储两年就需要2X365X5000W条数据存储空间,数据量为365亿,如果存储更长时间,则无法估计需要的存储空间。而利用拉链算法存储,每日只向历史表中添加新增和变化的数据,每日不过20万条,存储4年也只需要3亿存储空间。
实现步骤
在拉链表中,每一条数据都有一个生效日期(effective_date)和失效日期(expire_date)。假设在一个用户表中,在2019年11月8日新增了两个用户,如下表所示,则这两条记录的生效时间为当天,由于到2019年11月8日为止,这两条就还没有被修改过,所以失效时间为一个给定的比较大的值,比如:3000-12-31
member_id | phoneno | create_time | update_time |
10001 | 13300000001 | 2019-11-08 | 3000-12-31 |
10002 | 13500000002 | 2019-11-08 | 3000-12-31 |
第二天(2019-11-09),用户10001被删除了,用户10002的电话号码被修改成13600000002.为了保留历史状态,用户10001的失效时间被修改为2019-11-09,用户10002则变成了两条记录,如下表所示:
member_id | phoneno | create_time | update_time |
10001 | 13300000001 | 2019-11-08 | 2019-11-09 |
10002 | 13500000002 | 2019-11-08 | 2019-11-09 |
10002 | 13600000002 | 2019-11-09 | 3000-12-31 |
第三天(2019-11-10),又新增了用户10003,则用户表数据如小表所示:
member_id | phoneno | create_time | update_time |
10001 | 13300000001 | 2019-11-08 | 2019-11-09 |
10002 | 13500000002 | 2019-11-08 | 2019-11-09 |
10002 | 13600000002 | 2019-11-09 | 3000-12-31 |
10003 | 13300000006 | 2019-11-10 | 3000-12-31 |
如果要查询最新的数据,那么只要查询失效时间为3000-12-31的数据即可,如果要查11月8号的历史数据,则筛选生效时间<= 2019-11-08并且失效时间>2019-11-08的数据即可。如果查询11月9号的数据,那么筛选条件则是生效时间<=2019-11-09并且失效时间>2019-11-09
表结构
- MySQL源member表
CREATE TABLE member(
member_id VARCHAR ( 64 ),
phoneno VARCHAR ( 20 ),
create_time datetime,
update_time datetime );