方案缺点:需要数据库产品支持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内容来自木庄网络博客
标签:Mysql
相关阅读 >>
mysql8.0连接协议及3306、33060、33062端口的作用解析
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。