mysql中資料庫覆蓋匯入的幾種方式總結

2022-03-25 13:02:30

部分覆蓋

業務場景

某業務每天給業務表中匯入巨量資料進行分析，業務表中某列存在主鍵，當插入資料和已有資料存在主鍵衝突時，希望能夠對該行資料使用新資料覆蓋或者說更新，而當新老資料userid不衝突的情況下，直接將新資料插入到資料庫中。以將表src中的資料覆蓋式匯入業務表des中為例：

應用方案

方案一：使用DELETE+INSERT組合實現（UPDATE也可以，請讀者思考）

--開啟事務
START TRANSACTION;
--去除主鍵衝突資料
DELETE FROM des
USING src
WHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);
--匯入新資料
INSERT INTO des
SELECT *
FROM src
WHERE NOT EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);
--事務提交
COMMIT;

方案優點：使用最常見的使用DELETE和INSERT即可實現。

方案缺點：1、分了DELETE和INSERT兩個步驟，易用性欠缺；2、藉助子查詢識重，DELETE/INSERT效能受查詢效能制約。

方案二：使用MERGE INTO功能實現

MERGE INTO des USING src ON (des.userid = src.userid)
WHEN MATCHED THEN UPDATE SET des.b = src.b
WHEN NOT MATCHED THEN INSERT VALUES (src.userid,src.b);

方案優點：MERGE INTO單SQL搞定，使用便捷，內部去重效率高。

方案缺點：需要資料庫產品支援MERGE INTO功能，當前Oracle、GaussDB(DWS)等資料庫已支援此功能，mysql的insert into on duplicate key也類似此功能。

完全覆蓋

業務場景

某業務每天給業務表中匯入一定時間區間的資料進行分析，分析只需要匯入時間區間的去除，不需要以往歷史資料，這種情況就需要使用到覆蓋式匯入。

應用方案

方案一：使用TRUNCATE+INSERT組合實現

--開啟事務
START TRANSACTION;
--清除業務表資料
TRUNCATE des;
--插入1月份資料
INSERT INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';
--提交事務
COMMIT;

方案優點：簡單暴力，先清理在插入直接實現類似覆蓋寫功能。

方案缺點：TRUNCATE清理業務表des資料時對錶加8級鎖直到事務結束，在因資料量巨大而INSERT時間很長的情況下，des表在很長時間內是不可存取的狀態，業務表des相關的業務處於中斷狀態。

方案二：使用建立臨時表過渡的方式實現

--開啟事務
START TRANSACTION;
--建立臨時表
CREATE TABLE temp(LIKE desc INCLUDING ALL);
--資料先匯入到臨時表中
INSERT INTO temp SELECT * FROM src WHERE TIME > '2020-01-01 00:00:00' AND TIME < '2020-02-01 00:00:00';
--匯入完成後刪除業務表des
DROP TABLE des;
--修改臨時表名temp->des
ALTER TABLE temp RENAME TO des;
--提交事務
COMMIT;

方案優點：相比方案一，在INSERT期間，業務表des可以繼續被存取（老資料），即事務提交前分析業務可繼續存取老資料，事務提交後分析業務可以存取新匯入的資料。

方案缺點：1、組合步驟較多，不易用；2、DROP TABLE操作會刪除表的依賴物件，例如檢視等，後面依賴物件的還原可能會比較複雜。

方案三：使用INSERT OVERWRITE功能

INSERT OVERWRITE INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';

方案優點：單條SQL搞定，執行便捷，能夠支援一鍵式切換業務查詢的新老資料，業務不中斷。

方案缺點：需要產品支援INSERT OVERWRITE功能，當前impala、GaussDB(DWS)等資料庫均已支援此功能。

總結

隨著巨量資料的場景越來越多，資料匯入的場景也越來越豐富，除了本文介紹的覆蓋式資料匯入，還有其他諸如忽略衝突的INSERT IGNORE匯入等等其他的匯入方式，這些匯入場景可以以使用基礎的INSERT、UPDATE、DELETE、TRUNCATE來組合實現，但是也同樣會對高階的一鍵SQL功能有直接訴求，後面有機會再敘述。

mysql中資料庫覆蓋匯入的幾種方式總結

目錄

部分覆蓋

業務場景

應用方案

完全覆蓋

業務場景

應用方案

總結

熱門文章