数据库数据同步方案总结
数据同步还是要分不同的场景。
一、同库
如果表在同一个数据库里，可以直接用下面这个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> * * @author https://www.cnblogs.com/shamo89 * @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写的。
具体怎么使用，大家自行百度吧，这边就不介绍了。
