mysql数据库拉链表指什么
发布时间：2023-06-03 13:52:34 所属栏目：MySql教程 来源：
导读：本篇内容主要讲解“mysql数据库拉链表是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql数据库拉链表是什么”吧!
拉链表产生背景
拉链表产生背景
|
本篇内容主要讲解“mysql数据库拉链表是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql数据库拉链表是什么”吧! 拉链表产生背景 在数据仓库的数据模型设计过程中，经常会遇到这样的需求： 1、数据量比较大； 2、表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等； 3、需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等； 4、变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右； 5、如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费； 对于这种表有几种方案可选： 方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。 方案二：每天保留一份全量的切片数据。 方案三：使用拉链表。 以上方案对比 方案一 这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。 优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。 缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。 方案二 每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。 缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费，这点我感触还是很深的… 当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。 拉链表 拉链表在使用上基本兼顾了我们的需求。 首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。 其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。 所以我们还是很有必要来使用拉链表的。 拉链表概念 拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。 百度百科的解释：拉链表是维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录，通过拉链表可以很方便的还原出拉链时点的客户记录。 拉链表算法 1、采集当日全量数据到ND(NowDay当日)表； 2、可从历史表中取出昨日全量数据存储到OD(OldDay上日)表； 3、两个表进行全字段比较，(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示； 4、两个表进行全字段比较，(OD-ND)为状态到此结束需要封链的数据，需要修改END_DATE，用W_U表示； 5、将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值，可以设为’9999-12-31‘； 6、对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作，历史表（OD）和W_U表比较，START_DATE，END_DATE除外，以W_U表为准，两者交集将其END_DATE改成当日，说明该记录失效。 拉链表示例1 举个简单例子，比如有一张订单表： 6月20号有3条记录： 订单创建日期 订单编号 订单状态 2012-06-20 001 创建订单 2012-06-20 002 创建订单 2012-06-20 003 支付完成 到6月21日，表中有5条记录： 订单创建日期 订单编号 订单状态 2012-06-20 001 创建订单 2012-06-20 002 创建订单 2012-06-20 003 支付完成 2012-06-21 004 创建订单 2012-06-21 005 创建订单 到6月22日，表中有6条记录： 订单创建日期 订单编号 订单状态 2012-06-20 001 创建订单 2012-06-20 002 创建订单 2012-06-20 003 支付完成 2012-06-21 004 创建订单 2012-06-21 005 创建订单 2012-06-22 006 创建订单 数据仓库中对该表的保留方法： 1、只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足； 2、每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费； 如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表： 订单创建日期 订单编号 订单状态 dw_bigin_date dw_end_date 2012-06-20 001 创建订单 2012-06-20 2012-06-20 2012-06-20 001 支付完成 2012-06-21 9999-12-31 2012-06-20 002 创建订单 2012-06-20 9999-12-31 2012-06-20 003 支付完成 2012-06-20 2012-06-21 2012-06-20 003 已发货 2012-06-22 9999-12-31 2012-06-21 004 创建订单 2012-06-21 9999-12-31 2012-06-21 005 创建订单 2012-06-21 2012-06-21 2012-06-21 005 支付完成 2012-06-22 9999-12-31 2012-06-22 006 创建订单 2012-06-22 9999-12-31 说明： 1、dw_begin_date表示该条记录的生命周期开始时间，dw_end_date表示该条记录的生命周期结束时间； 2、dw_end_date = '9999-12-31’表示该条记录目前处于有效状态； 3、如果查询当前所有有效的记录，则select * from order_his where dw_end_date = ‘9999-12-31’； 4、如果查询2012-06-21的历史快照，则select * from order_his where dw_begin_date <= ‘2012-06-21’ and end_date >= ‘2012-06-21’，这条语句会查询到以下记录： 订单创建日期 订单编号 订单状态 dw_bigin_date dw_end_date 2012-06-20 001 支付完成 2012-06-21 9999-12-31 2012-06-20 002 创建订单 2012-06-20 9999-12-31 2012-06-20 003 支付完成 2012-06-20 2012-06-21 2012-06-21 004 创建订单 2012-06-21 9999-12-31 2012-06-21 005 创建订单 2012-06-21 2012-06-21 和源表在6月21日的记录完全一致： 订单创建日期 订单编号 订单状态 2012-06-20 001 创建订单 2012-06-20 002 创建订单 2012-06-20 003 支付完成 2012-06-21 004 创建订单 2012-06-21 005 创建订单 可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源； 拉链表示例2： 在历史表中对人的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题： 人名 开始日期 结束日期 状态 client 19000101 19070901 H在家 client 19070901 19130901 A小学 client 19130901 19160901 B初中 client 19160901 19190901 C高中 client 19190901 19230901 D大学 client 19230901 19601231 E公司 client 19601231 29991231 H退休在家 上面的每一条记录都是不算末尾的，比如到19070901，client已经在A，而不是H了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在结束日期那天，都不在是该条记录结束日期那天的状态。这种现象可以理解为算头不算尾。 拉链表实现方式 1、定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据； CREATE VOLATILE TABLE VT_xxxx_NEW AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;CREATE VOLATILE SET TABLE VT_xxxx_CHG,NO LOG AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS; 2、获取当日全量数据 INSERT INTO VT_xxxx_NEW(xx) SELECT (xx,cur_date, max_date) FROM xxxx_sorce; 3、抽取新增或有变化的数据，从xxxx_NEW临时表到xxxx_CHG临时表； INSERT INTO VT_xxxx_CHG(xx)SELECT xx FROM VT_xxxx_NEWWHERE (xx) NOT IN (select xx from xxxx_HIS where end_date='max_date'); 4、更新历史表的失效记录的end_date为max值 UPDATE A1 FROM xxxx_HIS A1, VT_xxxx_CHG A2SET End_Date='current_date'WHERE A1.xx=A2.xx AND A1.End_Date='max_date'; 5、将新增或者有变化的数据插入目标表 INSERT INTO xxxx_HIS SELECT * FROM VT_xxxx_CHG; 以商品数据为例 存在商品表 t_product，表结构如下： 列名 类型 说明 goods_id varchar(50) 商品编号 goods_status varchar(50) 商品状态（待审核、待售、在售、已删除） createtime varchar(50) 商品创建日期 modifytime varchar(50) 商品修改日期 2019年12月20日的数据如下所示： goods_id goods_status createtime modifytime 001 待审核 2019-12-20 2019-12-20 002 待售 2019-12-20 2019-12-20 003 在售 2019-12-20 2019-12-20 004 已删除 2019-12-20 2019-12-20 商品的状态，会随着时间推移而变化，我们需要将商品的所有变化的历史信息都保存下来。 方案一: 快照每一天的数据到数仓 该方案为：每一天都保存一份全量，将所有数据同步到数仓中，很多记录都是重复保存，没有任何变化。 12月20日（4条数据） goods_id goods_status createtime modifytime 001 待审核 2019-12-18 2019-12-20 002 待售 2019-12-19 2019-12-20 003 在售 2019-12-20 2019-12-20 004 已删除 2019-12-15 2019-12-20 12月21日（10条数据） goods_id goods_status createtime modifytime 以下为12月20日快照数据 001 待审核 2019-12-18 2019-12-20 002 待售 2019-12-19 2019-12-20 003 在售 2019-12-20 2019-12-20 004 已删除 2019-12-15 2019-12-20 以下为12月21日快照数据 001 待售（从待审核到待售） 2019-12-18 2019-12-21 002 待售 2019-12-19 2019-12-20 003 在售 2019-12-20 2019-12-20 004 已删除 2019-12-15 2019-12-20 005（新商品） 待审核 2019-12-21 2019-12-21 006（新商品） 待审核 2019-12-21 2019-12-21 12月22日（18条数据） goods_id goods_status createtime modifytime 以下为12月20日快照数据 001 待审核 2019-12-18 2019-12-20 002 待售 2019-12-19 2019-12-20 003 在售 2019-12-20 2019-12-20 004 已删除 2019-12-15 2019-12-20 以下为12月21日快照数据 001 待售（从待审核到待售） 2019-12-18 2019-12-21 002 待售 2019-12-19 2019-12-20 003 在售 2019-12-20 2019-12-20 004 已删除 2019-12-15 2019-12-20 005 待审核 2019-12-21 2019-12-21 006 待审核 2019-12-21 2019-12-21 以下为12月22日快照数据 001 待售 2019-12-18 2019-12-21 002 待售 2019-12-19 2019-12-20 003 已删除（从在售到已删除） 2019-12-20 2019-12-22 004 待审核 2019-12-21 2019-12-21 005 待审核 2019-12-21 2019-12-21 006 已删除（从待审核到已删除） 2019-12-21 2019-12-22 007 待审核 2019-12-22 2019-12-22 008 待审核 2019-12-22 2019-12-22 MySQL数仓代码实现 MySQL初始化 在MySQL中 lalian 库和商品表用于到原始数据层 -- 创建数据库create database if not exists lalian;-- 创建商品表create table if not exists `lalian`.`t_product`( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态 createtime varchar(50), -- 商品创建时间 modifytime varchar(50) -- 商品修改时间); 在MySQL中创建ods和dw层来模拟数仓 -- ods创建商品表create table if not exists `lalian`.`ods_t_product`( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态 createtime varchar(50), -- 商品创建时间 modifytime varchar(50), -- 商品修改时间 cdat varchar(10) -- 模拟hive分区)default character set = 'utf8';-- dw创建商品表create table if not exists `lalian`.`dw_t_product`( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态 createtime varchar(50), -- 商品创建时间 modifytime varchar(50), -- 商品修改时间 cdat varchar(10) -- 模拟hive分区)default character set = 'utf8'; 增量导入12月20号数据 原始数据导入12月20号数据（4条） insert into `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) values('001', '待审核', '2019-12-18', '2019-12-20'),('002', '待售', '2019-12-19', '2019-12-20'),('003', '在售', '2019-12-20', '2019-12-20'),('004', '已删除', '2019-12-15', '2019-12-20'); 注意：由于这里使用的MySQL来模拟的数仓所以直接使用insert into的方式导入数据，在企业中可能会使用hive来做数仓使用 kettle 或者 sqoop 或 datax 等来同步数据。 # 从原始数据层导入到ods 层insert into lalian.ods_t_productselect *,'20191220' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * from lalian.ods_t_product where cdat='20191220'; 查看dw层的运行结果 select * from lalian.dw_t_product where cdat='20191220'; goods_id goods_status createtime modifytime cdat 1 待审核 2019/12/18 2019/12/20 20191220 2 待售 2019/12/19 2019/12/20 20191220 3 在售 2019/12/20 2019/12/20 20191220 4 已删除 2019/12/15 2019/12/20 20191220 增量导入12月21数据 原始数据层导入12月21日数据（6条数据） UPDATE `lalian`.`t_product` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001'; INSERT INTO `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES ('005', '待审核', '2019-12-21', '2019-12-21'), ('006', '待审核', '2019-12-21', '2019-12-21'); 将数据导入到ods层与dw层 # 从原始数据层导入到ods 层insert into lalian.ods_t_productselect *,'20191221' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * from lalian.ods_t_product where cdat='20191221'; 查看dw层的运行结果 select * from lalian.dw_t_product where cdat='20191221'; goods_id goods_status createtime modifytime cdat 1 待售 2019/12/18 2019/12/21 20191221 2 待售 2019/12/19 2019/12/20 20191221 3 在售 2019/12/20 2019/12/20 20191221 4 已删除 2019/12/15 2019/12/20 20191221 5 待审核 2019/12/21 2019/12/21 20191221 6 待审核 2019/12/21 2019/12/21 20191221 增量导入12月22日数据 原始数据层导入12月22日数据（6条数据） UPDATE `lalian`.`t_product` SET goods_status = '已删除', modifytime = '2019-12-22' WHERE goods_id = '003';UPDATE `lalian`.`t_product` SET goods_status = '已删除', modifytime = '2019-12-22' WHERE goods_id = '006';INSERT INTO `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES('007', '待审核', '2019-12-22', '2019-12-22'),('008', '待审核', '2019-12-22', '2019-12-22'); 将数据导入到ods层与dw层 # 从原始数据层导入到ods 层 insert into lalian.ods_t_product select *,'20191222' from lalian.t_product ; # 从ods同步到dw层 insert into lalian.dw_t_productpeizhiwenjian select * from lalian.ods_t_product where cdat='20191222'; 查看dw层的运行结果 select * from lalian.dw_t_product where cdat='20191222'; goods_id goods_status createtime modifytime cdat 1 待售 2019/12/18 2019/12/21 20191222 2 待售 2019/12/19 2019/12/20 20191222 3 已删除 2019/12/20 2019/12/22 20191222 4 已删除 2019/12/15 2019/12/20 20191222 5 待审核 2019/12/21 2019/12/21 20191222 6 已删除 2019/12/21 2019/12/22 20191222 7 待审核 2019/12/22 2019/12/22 20191222 8 待审核 2019/12/22 2019/12/22 20191222 查看dw层的运行结果 select * from lalian.dw_t_product; goods_id goods_status createtime modifytime cdat 1 待审核 2019/12/18 2019/12/20 20191220 2 待售 2019/12/19 2019/12/20 20191220 3 在售 2019/12/20 2019/12/20 20191220 4 已删除 2019/12/15 2019/12/20 20191220 1 待售 2019/12/18 2019/12/21 20191221 2 待售 2019/12/19 2019/12/20 20191221 3 在售 2019/12/20 2019/12/20 20191221 4 已删除 2019/12/15 2019/12/20 20191221 5 待审核 2019/12/21 2019/12/21 20191221 6 待审核 2019/12/21 2019/12/21 20191221 1 待售 2019/12/18 2019/12/21 20191222 2 待售 2019/12/19 2019/12/20 20191222 3 已删除 2019/12/20 2019/12/22 20191222 4 已删除 2019/12/15 2019/12/20 20191222 5 待审核 2019/12/21 2019/12/21 20191222 6 已删除 2019/12/21 2019/12/22 20191222 7 待审核 2019/12/22 2019/12/22 20191222 8 待审核 2019/12/22 2019/12/22 20191222 （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
