记一次数据同步sql优化

最近给一离职杨姓朋友擦屁股，接手优化他的数据同步job,其中需求为从离线大数据库同步数据到业务库,时间为一天内同步完成

原来的sql为一次删除,一次性查，一次性插入

#伪代码
#预留重新同步场景删除同步数据
delete from tablename WHERE statistics_time = #statisticsTime(curentdate()-1)
#查询需要同步的离线库的数据
datalist=select * from tablename WHERE statistics_time = #statisticsTime(curentdate()-1)
#循环插入业务库数据
datalist.foreach(insert into tablename values(?))

存在问题:

1. 数据量庞大，因为有撑爆内存危险
2. 数据量庞大，异步删除、插入一次性事务过大会导致数据库hang(挂起)

解决方法:

1. 分段删除

#分段删除，直到影响行数为0
do{
	_,row=delete from tablename WHERE statistics_time = #statisticsTime(curentdate()-1) limit 1000
}while(row>0)

2.分段查询插入(需要表id ASC)(limit offset,num在分大页时会有性能问题,所以需要使用索引分段)

id=0
limit=1000
do{
	datalist=select * from tablename WHERE statistics_time = #statisticsTime(curentdate()-1) and id>#id limit #limit
	datalist.foreach(
		insert into tablename values(?)
	)
	if (datalist.size()<limit){#当最后一页时退出
		break;
	}
	id=datalist.get(datalist.size()-1).l("id");
}while(True)

3.异步转同步,减少吞吐量(因为对时间无要求)

测试结果为同步13,537,527数据10分钟左右