乐趣区

关于数据库:数据库覆盖式数据导入方法部分和完全

jiezi

前言

家喻户晓，数据库中 INSERT INTO 语法是 append 形式的插入，而最近在解决一些客户数据导入场景时，常常遇到须要笼罩式导入的状况，常见的笼罩式导入次要有上面两种：

1、局部笼罩：新老数据依据要害列值匹配，能匹配上则应用新数据笼罩，匹配不上则直接插入。

2、齐全笼罩：间接删除所有老数据，插入新数据。

本文次要介绍如何在数据库中实现笼罩式数据导入的办法。

局部笼罩

业务场景

某业务每天给业务表中导入大数据进行剖析，业务表中某列存在主键，当插入数据和已有数据存在主键抵触时，心愿可能对该行数据应用新数据笼罩或者说更新，而当新老数据 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 性能有间接诉求，前面有机会再持续介绍。

本文分享自华为云社区《数据库笼罩式数据导入办法介绍》，原文作者：along_2020。

点击关注，第一工夫理解华为云陈腐技术~

退出移动版