数据库数据同步方案总结

数据同步还是要分不同的场景。

一、同库

如果表在同一个数据库里，可以直接用下面这个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写的。

具体怎么使用，大家自行百度吧，这边就不介绍了。