一起來聊聊資料庫拉連結串列

推薦學習：

拉連結串列產生背景

在資料倉儲的資料模型設計過程中，經常會遇到這樣的需求：

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條記錄：

到6月21日，表中有5條記錄：

到6月22日，表中有6條記錄：

資料倉儲中對該表的保留方法：

1、只保留一份全量，則資料和6月22日的記錄一樣，如果需要檢視6月21日訂單001的狀態，則無法滿足；

2、每天都保留一份全量，則資料倉儲中的該表共有14條記錄，但好多記錄都是重複儲存，沒有任務變化，如訂單002,004，資料量大了，會造成很大的儲存浪費；

如果在資料倉儲中設計成歷史拉連結串列儲存該表，則會有下面這樣一張表：

說明：

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’，這條語句會查詢到以下記錄：

和源表在6月21日的記錄完全一致：

可以看出，這樣的歷史拉連結串列，既能滿足對歷史資料的需求，又能很大程度的節省儲存資源；

拉連結串列範例2：

在歷史表中對人的一生的記錄可能就這樣幾條記錄，避免了按每一天記錄客戶狀態造成的海量儲存的問題：

上面的每一條記錄都是不算末尾的，比如到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，表結構如下：

2019年12月20日的資料如下所示：

商品的狀態，會隨著時間推移而變化，我們需要將商品的所有變化的歷史資訊都儲存下來。

方案一: 快照每一天的資料到數倉

該方案為：每一天都儲存一份全量，將所有資料同步到數倉中，很多記錄都是重複儲存，沒有任何變化。

12月20日（4條資料）

12月21日（10條資料）

12月22日（18條資料）

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';

增量匯入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';

增量匯入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';

檢視dw層的執行結果

select * from lalian.dw_t_product;

從上述案例，可以看到：表每天保留一份全量，每次全量中會儲存很多不變的資訊，如果資料量很大的話，對儲存是極大的浪費，可以將表設計為拉連結串列，既能滿足反應資料的歷史狀態，又可以最大限度地節省儲存空間。

方案二: 使用拉連結串列儲存歷史快照

拉連結串列不儲存冗餘的資料，只有某行的資料發生變化，才需要儲存下來，相比每次全量同步會節省儲存空間

能夠查詢到歷史快照

額外的增加了兩列（dw_start_date、dw_end_date），為資料行的生命週期。

12月20日商品拉連結串列的資料

12月20日的資料是全新的資料匯入到dw表

• dw_start_date表示某一條資料的生命週期起始時間，即資料從該時間開始有效（即生效日期）
• dw_end_date表示某一條資料的生命週期結束時間，即資料到這一天（不包含）（即失效日期）
• dw_end_date為 9999-12-31，表示當前這條資料是最新的資料，資料到9999-12-31才過期

12月21日商品拉連結串列的資料

拉連結串列中沒有儲存冗餘的資料，即只要資料沒有變化，無需同步

• 001編號的商品資料的狀態發生了變化（從待稽核 → 待售），需要將原有的dw_end_date從9999-12-31變為2019-12-21，表示待稽核狀態，在2019/12/20（包含） - 2019/12/21（不包含）有效；
• 001編號新的狀態重新儲存了一條記錄，dw_start_date為2019/12/21，dw_end_date為9999/12/31；
• 新資料005、006、dw_start_date為2019/12/21，dw_end_date為9999/12/31。

12月22日商品拉連結串列的資料

拉連結串列中沒有儲存冗餘的資料，即只要資料沒有變化，無需同步

• 003編號的商品資料的狀態發生了變化（從在售→已刪除），需要將原有的 dw_end_date從9999-12-31變為2019-12-22，表示在售狀態，在2019/12/20（包含） - 2019/12/22（不包含） 有效
• 003編號新的狀態重新儲存了一條記錄，dw_start_date為2019-12-22，dw_end_date為9999-12-31
• 新資料007、008、dw_start_date為2019-12-22，dw_end_date為9999-12-31

MySQL數倉拉連結串列快照實現

操作流程：

1. 在原有dw層表上，新增額外的兩列
2. 只同步當天修改的資料到ods層
3. 拉連結串列演演算法實現
4. 拉連結串列的資料為：當天最新的資料 UNION ALL 歷史資料

程式碼實現

在MySQL中lalian庫和商品表用於到原始資料層

-- 建立資料庫create database if not exists lalian;-- 建立商品表create table if not exists `lalian`.`t_product2`(
	goods_id varchar(50), -- 商品編號
	goods_status varchar(50), -- 商品狀態
    createtime varchar(50), -- 商品建立時間
    modifytime varchar(50) -- 商品修改時間)default character set = 'utf8';

在MySQL中建立ods和dw層 模擬數倉

-- ods建立商品表create table if not exists `lalian`.`ods_t_product2`(
	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_product2`(
	goods_id varchar(50), -- 商品編號
	goods_status varchar(50), -- 商品狀態
	createtime varchar(50), -- 商品建立時間
	modifytime varchar(50), -- 商品修改時間
	dw_start_date varchar(12), -- 生效日期
	dw_end_date varchar(12), -- 失效時間
	cdat varchar(10)  -- 模擬hive分割區)default character set = 'utf8';

全量匯入2019年12月20日資料

原始資料層匯入12月20日資料（4條資料）

insert into `lalian`.`t_product_2`(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');

將資料匯入到數倉中的ods層

insert into lalian.ods_t_product2select *,'20191220' from lalian.t_product2 where modifytime >='2019-12-20';

將資料從ods層匯入到dw層

insert into lalian.dw_t_product2select goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191220';

增量匯入2019年12月21日資料

原始資料層匯入12月21日資料（6條資料）

UPDATE `lalian`.`t_product2` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';INSERT INTO `lalian`.`t_product2`(goods_id, goods_status, createtime, modifytime) VALUES('005', '待稽核', '2019-12-21', '2019-12-21'),('006', '待稽核', '2019-12-21', '2019-12-21');

原始資料層同步到ods層

insert into lalian.ods_t_product2select *,'20191221' from lalian.t_product2 where modifytime >='2019-12-21';

編寫ods層到dw層重新計算 dw_end_date

select t1.goods_id, t1.goods_status, t1.createtime, t1.modifytime,
       t1.dw_start_date,
       case when (t2.goods_id is not null and t1.dw_end_date>'2019-12-21') then '2019-12-21'else t1.dw_end_date end as dw_end_date ,
       t1.cdatfrom lalian.dw_t_product2 t1left join (select * from lalian.ods_t_product2 where cdat='20191221')t2 on t1.goods_id=t2.goods_idunionselect goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191221';

執行結果如下：

拉鍊歷史表，既能滿足反應資料的歷史狀態，又可以最大程度的節省儲存。我們做拉連結串列的時候要確定拉連結串列的粒度，比如說拉連結串列每天只取一個狀態，也就是說如果一天有3個狀態變更，我們只取最後一個狀態，這種天粒度的表其實已經能解決大部分的問題了。

推薦學習：

以上就是一起來聊聊資料庫拉連結串列的詳細內容，更多請關注TW511.COM其它相關文章！