数据库热迁移（sqlserver pgsql）

在进行数据迁移前 我们要先清楚如何进行迁移对业务影响最小？ 如何性能最高的进行迁移数据库？ 迁移过程中会出现哪些问题。

如何进行迁移对业务影响最小

同步方式1

数据迁移的过程中，最简单暴力的方式直接导出数据库中所有的sqlserver语句，编写脚本，将千万数据量转换成pgsql语法,这种方式会产生很多的问题

缺点

1. 数据迁移效率：将千万级别的数据量通过脚本转换和导入可能会非常耗时和耗资源。数据导出时一个漫长的过程，且可能会让线上服务宕机，同时在千万级别数据量同时插入一直占用连接，也会导致数据库宕机，最终导致同步数据丢失 失败。
2. 数据一致性：直接导出和转换数据可能导致数据一致性问题。在数据库迁移过程中，数据可能会发生变化，例如新增、删除或修改。简单地导出和转换数据可能无法捕捉到这些变化，导致目标数据库中的数据不一致。

同步方式2

编写业务进行同步

优点

1. 减少数据丢失风险：热同步可以最大程度地减少数据丢失的风险。 可以通过编写日志，具体查看同步到的位置，具体到几百行的位置，同步时如有一条数据同步失败，通过日志记录及时的发现。
2. 高可用性：编写合理的业务，几乎不会对正在运行的业务产生影响 每次只查询一批数据，插入一批数据，不会对数据库造成很大的压力。如果意外宕机的情况下，也能及时找到数据同步的位置，继续进行数据的同步
3. 实时性：业务可以实现实时的数据同步时，能根据sqlserver日志进行 进行同步，当有新的语句插入时，及时检测到日志同步到另一个数据库。

如何性能最高的进行迁移数据库？

如上所示，我们首先采用第二种同步方式。编写合理的sql语句进行数据的同步，在批量请求数据的时候我们可能会遇到问题就是内存问题，如果数据量请求一旦过多就会导致数据库崩溃，cpu爆满，所以我们编写sql语句时，选择合理的索引。（比如深度分页问题 当你查询的数据靠后时，没有合适的索引就会造成全表扫描，导致数据库宕机）

当插入语句过多数据量庞大时， 造成B+树的深度过高，需要重复的扫描磁盘。所以再插入语句前为数据表建立合适的索引。

迁移过程中同步问题

在sqlserver中，有很种类型的日志，选择适合自己业务场景的日志进行使用，这里我们使用CT日志结合业务进行增量同步。（sql server 中还有cdc日志 记录了详细更改的数据），CT里面记录了原表中修改新增或删除的id 我们集体通过id找到原表中的数据，进行删除或者是修改新增。

开启CT

表开启CT

t_log 表名称

ALTER TABLE t_log
ENABLE CHANGE_TRACKING

查看某张表CT追踪

SELECT * FROM CHANGETABLE(CHANGES t_log, 0) as tbver

查询出来的字段有如下

//表示更改的版本号。每次更改都会递增版本号。
SYS_CHANGE_VERSION

//表示创建更改的版本号。对于插入操作，该字段的值等于 SYS_CHANGE_VERSION 的值；
//对于更新和删除操作，该字段的值表示最初创建更改的版本号
SYS_CHANGE_CREATION_VERSION

//表示更改的操作类型。可能的值包括 'I'（插入）、'U'（更新）和 'D'（删除）。
SYS_CHANGE_OPERATION

//表示发生更改的列的位图。每个位表示对应列是否发生了更改。
//如果对应位为 1，则表示该列发生了更改；如果对应位为 0，则表示该列没有发生更改
SYS_CHANGE_COLUMNS

//更改的上下文信息。该字段的值通常为空.
SYS_CHANGE_CONTEXT

//当前表主键
id

如下编写具体同步业务（数据层涉及内部安全不在此展示）

package biz.yuanbei.father;

import biz.yuanbei.dao.sqlserver.SqlSyncVersionDao;
import biz.yuanbei.framework.common.utils.SyncDataDto;
import biz.yuanbei.redis.dao.RedisDao;
import biz.yuanbei.redis.dao.RedisLockDao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
public abstract class DataSyncHelper<T> {

    @Autowired
    protected SqlSyncVersionDao sqlSyncVersionDao;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;



    protected String  KEYVALUE;

    public abstract void setVariable(String value);

    //清除所有的缓存
    public abstract void claneCache();



    protected abstract T fetchById(int Id);

    protected abstract int update(T elem);

    protected abstract int removeById(T ele);


    protected abstract int getCount(SyncDataDto syncData);

    protected abstract List<T> getList(SyncDataDto syncData);

    protected abstract List<?> convertTo(List<T> dataList);

    protected abstract void   StopSyncThenSync();

    protected abstract int[] insertBatch(List<?> elems);
    protected abstract int[] insertBatchs(List<?> elems);

    public void syncStart(SyncDataDto syncData, int pageSize) {
        // 获取需要同步的数据总数
        int count = getCount(syncData);
        if (count <= 0) {
            // 如果数据总数小于等于0，则没有需要同步的数据，记录日志并返回
            log.info("sync data not args {}", syncData);
            return;
        }
        //同步方式
        //1.0 计算总页说
        //2.0 将页进行拆分，获取页中的数据List
        //3.0 将查询到的数据转换未目标对象
        //4.0 进行数据的插入。

        // 计算总页数
        int totalPage = (count + pageSize - 1) / pageSize;
        // 循环处理每一页的数据v
        for (int i = 0;i < totalPage;i++) {
            int pageIndex = i + 1;
            syncData.setPageIndex(pageIndex);
            syncData.setPageSize(pageSize);
            try {
                // 获取当前页的数据列表
                List<T> dataList = getList(syncData);
                if (CollectionUtils.isEmpty(dataList)) {
                    // 如果数据列表为空，则跳过当前页的处理
                    continue;
                }
                // 将数据列表转换为目标类型的列表
                List<?> elems = convertTo(dataList);


                // 批量插入数据
                int[] ints = insertBatchs(elems);
            } catch (Exception e) {
                log.error("sync data error: {},{}", e.getMessage(), e);
            }
        }
    }

    public abstract void SyncThenSync();


    public void  clearCache(String LOCK_KEY   ,String DATA_KEY,String INCREMENT) {
        RedisLockDao.unlock(LOCK_KEY);
        RedisDao.delete(DATA_KEY);
        RedisLockDao.unlock(INCREMENT);

    }



    //构建条件
    public static StringBuilder builderWheres(SyncDataDto args, MapSqlParameterSource params) {
        StringBuilder wheres = new StringBuilder();

        if (args.getAccId() > 0) {
            wheres.append(" and accId = :accId ");
            params.addValue("accId", args.getAccId());
        }

        if (args.getStartTime() != null) {
            wheres.append(" and LogTime >= :startTime ");
            params.addValue("startTime", args.getStartTime());
        }

        if (args.getEndTime() != null) {
            wheres.append(" and LogTime <= :endTime ");
            params.addValue("endTime", args.getEndTime());
        }

        if (args.getStartId() > 0) {
            wheres.append(" and userLogId >= :startId ");
            params.addValue("startId", args.getStartId());
        }

        if (args.getEndId() > 0) {
            wheres.append(" and userLogId <= :endId ");
            params.addValue("endId", args.getEndId());
        }
        return wheres;
    }

}

package biz.yuanbei.service;

import biz.yuanbei.dao.pgsql.PayClassSyncDao;
import biz.yuanbei.dao.sqlserver.PayClassDaos;
import biz.yuanbei.entity.lod.PayClassSqlServiceEntity;
import biz.yuanbei.entity.lod.SqlSyncVersionEntity;
import biz.yuanbei.entity.news.PayClassEntity;
import biz.yuanbei.father.DataSyncHelper;
import biz.yuanbei.framework.common.common.goodsstock.Constants;
import biz.yuanbei.framework.common.utils.JsonUtils;
import biz.yuanbei.framework.common.utils.SyncDataDto;
import biz.yuanbei.redis.dao.RedisDao;
import biz.yuanbei.redis.dao.RedisLockDao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.time.LocalDateTime;
import java.util.List;
import java.util.stream.Collectors;

@Service
@Slf4j
public class SyncPayClassSerivce extends DataSyncHelper<PayClassSqlServiceEntity> {

    protected Boolean isLock = true;
    @Autowired
    private PayClassDaos payRecordDaos;
    @Autowired
    private PayClassSyncDao payClassDao;



    //1.0 计算总页说
    //2.0 将页进行拆分，获取页中的数据List
    //3.0 将查询到的数据转换未目标对象
    //4.0 先创建数据源
    //4.1 再对插入数据源的数据进行编写sql
    public final String LOCK_KEY = "payclass-task-sync-lock";
    private final String DATA_KEY = "payclass-data-task-sync";

    private final String INCREMENT = "PAYCLASS-INCREMENT";


    //清除所有缓存
    @Override
    public void claneCache(){
        clearCache(LOCK_KEY,DATA_KEY,INCREMENT);
    }
    public void unSync() {

        RedisLockDao.unlock(LOCK_KEY);
    }

    private boolean acquireLock(String lockKey) {
        if (RedisLockDao.customLock(lockKey, 10)) {
            return true;
        }
        log.info("正在同步中.........");
        return false;
    }

    public void taskSync() {
        log.info("准备同步");
        if (!acquireLock(LOCK_KEY)) {
            log.info("同步失败,请清理缓存");
            return;
        }
        try {

            SyncDataDto syncDataDto = new SyncDataDto();
            long startId = syncDataDto.getStartId();
            long endId = syncDataDto.getEndId();
            // 进行同步操作，使用 startId 和 endId 进行同步
            // 更新同步状态
            String jsonStr = RedisDao.get(DATA_KEY);
            startId = JsonUtils.toObject(jsonStr == null ? "{}" : jsonStr, SyncDataDto.class).getStartId();
            endId = startId + Constants.MAX_PAGE_SIZE;

            long pClassMaxId = payRecordDaos.getMaxId();

            while (startId < pClassMaxId) {

                // 存储同步状态到 Redis
                syncDataDto.setStartId(startId);
                syncDataDto.setEndId(endId);

                sync(syncDataDto);

                startId += Constants.MAX_PAGE_SIZE;
                endId = startId + Constants.MAX_PAGE_SIZE;

                syncDataDto.setStartId(startId);
                syncDataDto.setEndId(endId);

                RedisDao.set(DATA_KEY, JsonUtils.toJsonStr(syncDataDto));

                log.info("同步进度 start {}  end {}", startId, endId);
            }
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
        } finally {
            RedisLockDao.unlock(LOCK_KEY);
        }
    }


    public void sync(SyncDataDto args) {
        syncStart(args, Constants.MAX_PAGE_SIZE);
    }

    @Override
    public void setVariable(String value) {
        this.KEYVALUE = this.LOCK_KEY;
    }

    @Override
    protected PayClassSqlServiceEntity fetchById(int Id) {

        return null;
    }

    @Override
    protected int update(PayClassSqlServiceEntity elem) {
        return 0;
    }

    @Override
    protected int removeById(PayClassSqlServiceEntity ele) {
        return 0;
    }

    //根据条件查询页
    @Override
    protected int getCount(SyncDataDto syncData) {
        setVariable(LOCK_KEY);
        return payRecordDaos.counts(syncData);
    }

    @Override
    protected List<PayClassSqlServiceEntity> getList(SyncDataDto syncData) {
        return payRecordDaos.getListPage(syncData);
    }


    @Override
    protected List<PayClassEntity> convertTo(List<PayClassSqlServiceEntity> dataList) {
        return dataList.stream()
                .map(this::convertEntity)
                .collect(Collectors.toList());
    }

    private PayClassEntity convertEntity(PayClassSqlServiceEntity sqlServiceEntity) {
        PayClassEntity entity = new PayClassEntity();
        entity.setId(sqlServiceEntity.getId());
        entity.setName(sqlServiceEntity.getName());
        entity.setClassId(sqlServiceEntity.getClassId());
        entity.setAccId(sqlServiceEntity.getAccountid());
        entity.setFettle(sqlServiceEntity.getFettle());
        entity.setCreateTime(LocalDateTime.now());
        return entity;
    }
    @Override
    protected int[] insertBatch(List<?> elems) {

        return payClassDao.insertBatch((List<PayClassEntity>) elems);
    }

    @Override
    protected int[] insertBatchs(List<?> elems) {
        int[] ints = null;
        try {
            ints = payClassDao.insertBatchs((List<PayClassEntity>) elems);
        } catch (Exception e) {
            log.error("数据重复");
        }
        return ints;
    }

    //增量同步
    @Override

    public void SyncThenSync() {

        if (RedisLockDao.lock(INCREMENT)) {
            try {
                //获取最大的版本号
                while (isLock) {
                    SqlSyncVersionEntity name = sqlSyncVersionDao.getName(INCREMENT);

                    int s = Integer.parseInt(name== null ? "0" : String.valueOf(name.getVersion()));
                    //查询到最大的版本进行保存
                    int maxversion = payRecordDaos.maxVersion(s - 1);
                    List<PayClassEntity> lastChangeVersions = payRecordDaos.getLastChangeVersions(s-1);
                    List<PayClassEntity> filteredList = lastChangeVersions.stream()
                            .filter(entity -> entity.getId() != null)
                            .collect(Collectors.toList());
                    //保存查询到的数据    //一次处理一千条数据
                    payClassDao.insertBatch(filteredList);
                    //删除过的数据永远没有id  只能通过最后一条日志进行删除
                    //删除     新表
                    List<Integer>  filteredIds =  payRecordDaos.getDeleteIds(s-1);
                    payClassDao.deleteBatch(filteredIds);
                    sqlSyncVersionDao.insertandupdateVersions(INCREMENT, maxversion);
                }
            } catch (Exception ex) {
                log.error(ex.getMessage(), ex);
            } finally {
                RedisLockDao.unlock(INCREMENT);
            }
        }
        log.error("正圯同步中.........");
    }



    //取消增量同步
    @Override
    public void StopSyncThenSync() {
        isLock = false;
    }
}