数据同步还是要分不同的场景。
一、同库
如果表在同一个数据库里，可以直接用下面这个sql语句实现。
INSERT INTO ... SELECT
存在的问题
这种写法虽然方便，但是对于数据量比较大的情况不推荐，因为它会占用很长时间的事务，再加上如果select里的语句没有用到索引，那将会导致select的表锁表很长时间，使得其它更新、删除、查询等业务出现等待事务超时、插入更新失败等情况。
这边要说明的是，mysql innodb的事务锁表是，插入不锁表，更新、删除根据索引来区分，如果用不到索引，就锁全表，如果是范围索引，那就锁部分数据，如果索引能定位到某一行，那就是行级锁。
优化方向是尽量使得select语句能用到索引，缩小锁表范围，另外可以分批insert，减小事务时间。
INSERT INTO t_person_copy (
aaaaaaaa,
bbbbbbbb,
cccccccc
) SELECT
'aaaaaa',
a.bbbbbb,
nei.cccccc
FROM
t_person_info a
left join `t_org_info` nei on nei.org_code = a.org_code and nei.status = 1
WHERE
a.STATUS = 1
二、不同数据库
通过多数据源的代码实现，因为涉及到的数据量很大，所以用到了多线程进行同步处理。
application.yml 配置多数据源
spring:
datasource:
dynamic:
primary: businessDB
datasource:
preDB:
url: jdbc:mysql://111.22.33.444:3306/pre_db?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
username: xiaweiyi8080
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
druid:
max-active: 20
initial-size: 5
min-idle: 5
max-wait: 200000
test-while-idle: true
businessDB:
url: jdbc:mysql://111.22.33.445:3306/bus_db?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
username: xiaweiyi8080
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
druid:
max-active: 20
initial-size: 5
min-idle: 5
max-wait: 200000
test-while-idle: true
servlet:
multipart:
# 开启 multipart 上传功能
enabled: true
# 文件写入磁盘的阈值
file-size-threshold: 2KB
# 最大文件大小
max-file-size: 200MB
# 最大请求大小
max-request-size: 215MB
mybatis:
config-location: classpath:/mybatis-config.xml
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
auto-mapping-behavior: full
# 如果查询结果中包含空值的列，则 MyBatis 在映射的时候，不会映射这个字段
call-setters-on-nulls: true
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath*:mapper/**/*Mapper.xml
logging:
level:
com.nat.sync: debug
service示例
利用@DS这个注解来区分调用哪个数据源，如果想用上面配置的默认的primary数据源，就无需配置，如果想用其它数据源可以加上该配置@DS("preDB")
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
/**
* <p>
* 服务实现类
* </p>
*
* @since 2022-05-02
*/
@Service
@DS("preDB")
public class PreUserServiceImpl extends ServiceImpl<PreUserMapper, PreUserDO>
implements PreUserService {
// ... 省略
同步任务处理类
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.google.common.base.Throwables;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;
/**
* 线程池多线程同步人员数据（多数据源）
* @Author: 夏威夷8080
* @Date: 2022/5/3 16:23
*/
@Component
@Slf4j
public class PersonTaskHandler {
/**
* 对查到的人员总数据进行分割，每段的数量
*/
public static final Integer AVERAGE_NUMBER = 50000;
/**
* 分页查询的每页大小
*/
public static final Integer PAGE_SIZE = 100000;
/**
* 每次批量插入或更新的最大数量
*/
public static final Integer BATCH_SIZE = 1000;
@Autowired
private PersonService personService;
@Autowired
private PreUserService preUserService;
@Autowired
private TaskLogService taskLogService;
public void doSync(Date maxUpdateTime) {
for (int k = 0; k < 20; k++) {
int page = k;
int size = 500000;
String lastSql = "limit " + page*size + "," +size;
log.info("----------lastSql----------{}", lastSql);
// 因为数据很多，所以分页查询，每次查出50W条data进行处理
QueryWrapper<PreUserDO> queryWrapper = new QueryWrapper<>();
queryWrapper.lambda().orderByAsc(PreUserDO::getId).last(lastSql);
List<PreUserDO> preUserDOS = preUserService.list(queryWrapper);
if (!CollectionUtils.isEmpty(preUserDOS)) {
Integer dataCount = preUserDOS.size();
log.info("----------共查到人员数据：{}", dataCount);
// 每5W个一组进行分割，分多个线程进行处理
List<List<PreUserDO>> parts0 = Lists.partition(preUserDOS, AVERAGE_NUMBER);
int forNumber = parts0.size();
log.info("----------将会进行{}次处理", forNumber);
// 创建线程池
ExecutorService executor = Executors.newFixedThreadPool(forNumber);
for (int n = 0; n < parts0.size(); n++) {
int finalM = n;
executor.submit(() -> {
List<PreUserDO> preUsers = parts0.get(finalM);
int dataSize = preUsers.size();
log.info("----------{}查到{}条待处理的人员数据", finalM, dataSize);
// 将源库里查到的人员数据实体转成目标库里的人员实体
List<PersonDO> personInfoDOS = preUsers.stream()
.map(preUserDO -> buildPerson(preUserDO))
.collect(Collectors.toList());
log.info("----------{}整理人员数据{}", finalM, dataSize);
// 因为mybatis plus 的batch批量操作最多就是1000条，所以对处理好的批量数据进行分割
List<List<PersonDO>> parts = Lists.partition(personInfoDOS, BATCH_SIZE);
log.info("----------{}处理好的批量数据分割为{}段", finalM, parts.size());
for (int j = 0; j < parts.size(); j++) {
List<PersonDO> infoDOS = parts.get(j);
try {
// 根据id批量更新或插入，批量操作的效率会比单条操作快很多
boolean result = personService.saveOrUpdateBatch(infoDOS);
if (result) {
total.updateAndGet(v -> v + infoDOS.size());
log.info("----------{}批量入库或更新成功{}条人员", finalM, infoDOS.size());
} else {
try {
fail.updateAndGet(v -> v + infoDOS.size());
log.info("----------{}批量入库或更新失败{}条人员", finalM, infoDOS.size());
List<Long> ids = infoDOS.stream().map(infoDO -> infoDO.getId()).collect(Collectors.toList());
log.info("----------{}记录失败ids:{}", finalM, JSONUtil.toJsonStr(ids));
} catch (Exception e) {
log.error("----------{}人员入库出错2：{}", finalM, Throwables.getStackTraceAsString(e));
}
}
} catch (Exception e) {
log.error("----------{}批量入库或更新出错{}条人员:{}", finalM, infoDOS.size(), Throwables.getStackTraceAsString(e));
fail.updateAndGet(v -> v + infoDOS.size());
List<Long> ids = infoDOS.stream().map(infoDO -> infoDO.getId()).collect(Collectors.toList());
log.info("----------{}记录失败ids2:{}", finalM, JSONUtil.toJsonStr(ids));
}
}
});
}
int activeCount = ((ThreadPoolExecutor) executor).getActiveCount();
log.info("----------PersonTaskHandler正在活跃的线程数量：{}", activeCount);
// 该方法不是立马停止关闭线程池，而是会拒绝接收新的任务提交，
// 并且会等待已提交未执行和已提交已执行的所有线程都走完
executor.shutdown();
// 判断是否所有的线程已经运行完
while (!executor.isTerminated()) {
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
log.info("----------本次分页处理完{}----------", page);
} else {
log.info("----------没有查到数据了{}----------", page);
break;
}
}
log.info("----------本次待同步人员数据全部处理完----------");
try {
taskLogService.complete(taskLogDO, total.get(), fail.get());
} catch (Exception e) {
log.error("人员日志完善出错：{}，{},{}", taskLogDO.getId(), taskLogDO.getTaskNo(), Throwables.getStackTraceAsString(e));
}
log.info("----------本次同步人员日志更新完----------");
}
private PersonDO buildPerson(PreUserDO preUserDO) {
PersonDO p = new PersonDO();
p.setId(preUserDO.getId());
// ...
return p;
}
}
三、kettle
kettle是一个ETL（Extract, Transform and Load抽取、转换、载入）工具，它的使用场景有，在不同应用或数据库之间整合数据、数据清洗、大批量数据装载入数据库。总之功能非常强大，而且该工具本身也是用Java写的。
具体怎么使用，大家自行百度吧，这边就不介绍了。
