背景:随着时间的推移,交易系统中的订单表越来越大,目前达到500w+数据。为了防止数据量过大导致的查询性能问题,现将订单表进行拆分,分为实时库和历史库。实时库保留近6个月的数据,用于退款业务需求,其余订单数据全部迁移到历史库中。
方式一:复制表(结构与数据)
可通过navicat右键选择复制表(结构与数据)进行全量同步数据。但是该操作会锁表,导致其他事务的新增、修改、删除操作都被挂起,慎用!
方式二:dbf文件方式导入导出数据
可通过navicat菜单进行dbf格式导出。此操作数据完整性最高;导出文件大(500w数据可达到30G);不锁表(导出过程中可新增、修改、删除)。
测试:500w数据
导出时间:30min+
导入时间:字段映射存在问题(导入失败)
方式三:txt文件方式
可通过navicat菜单进行txt格式导出。数据完整性中等;导出文件不大(500w数据不到1G);不锁表。
测试:500w数据
导出时间:8min
导入时间:约2h
方式四:导入导出命令(推荐)
注意:当前用于需要有该导入导出命令权限,。
导出时不锁T_UNION_ORDER 表
select * from T_UNION_ORDER into outfile 'b.txt';
导入时锁T_UNION_ORDER_copy1表
load data infile 'b.txt' into table T_UNION_ORDER_copy1;
测试:500w数据
导出时间:1min
导入时间:8min
方式五:程序迁移(推荐)
先插入数据到新表中,再删除原表数据,两组操作作为一个事务来处理。可参考以下步骤执行:
步骤一:定时任务开启时间:2点~3点 1小时内每10s触发一次同步任务。
步骤二:一个批次的数据量为300条,1h同步10.8w条数据。
insert_time 条件值取第前180天。insert_time没有创建索引走的全表扫描,sql语句耗时时间和符合条件的记录条数占全量数据的百分比相关,占比越大耗时越短,占比越小耗时越长。因此程序上线初期一次同步任务的执行时间较短,后期随着需要同步的数据越来越少,sql执行的时间也越来越长。
select * from T_UNION_ORDER where insert_time < '2023-01-01 00:00:00' limit 300;
批量进行数据插入,一个批次的数据量要适中,太大会导致字符串长度超长报错,太小频繁访问数据库导致可能的性能问题。
insert into T_UNION_ORDER_HISTORY ('') values (''),(''),('');
批量删除,删除操作会加锁。虽然是行锁,如果in的数据量太大可能会造成索引失效,行锁升级为表锁。
delete from T_UNION_ORDER where order_no in ('');
步骤三:增加手工触发订单数据同步机制。