mongo库切换mysql库操作步骤

最近有个需求是需要把mongo库切换到mysql库的需求，项目中有30多张mongo表，历时接近一个月才完成了，用户无感知切换数据库，在此，分享下我的操作步骤。

一.申请新的mysql库
工单申请mysql库，获取到mysql库的相关配置信息，账号，密码，地址等

二.创建mysql表
1.创建表并只建主键索引（其他索引等数据洗进去后再建）
2.注意点：创建表时由于mongo表中是json格式的数据，有些表数据有多层json，在mysql表中不支持，故在创建mysql表时需要建相关的关联表；并且mongo表中支持的一些字段，例如order，sort在mysql表中是关键字不支持作为字段，故需要相应的更改字段。

三.洗数据
1.新建对应的mysql表的实体类以及xml文件
2.开发分批多线程洗数据的接口（由于数据量比较大），故每张表写一个接口去洗数据。
3.分批执行，每批多线程执行1000条。

/**
     * 批量导入mongo表mall_content数据，到mysql表iyb_mall_content
     *
     * @return
     */
    @GetMapping(value = "/mallContentBatch")
    public IybResultBean mallContentBatch() {
        ThreadPool.execute(new MongoDataMoveController.MallContentTask(storeContentDao, mallContentMapper));
        return IybResultBeanUtil.success();
    }

    /**
     * iyb_mall_content表批量任务
     */
    private static class MallContentTask extends BatchTask {
        private final StoreContentDao   storeContentDao;
        private final MallContentMapper mallContentMapper;

        public MallContentTask(StoreContentDao storeContentDao, MallContentMapper mallContentMapper) {
            super(storeContentDao.countByCondition(Criteria.where(BaseField.GMT_CREATED).gt(BEGIN_TIME)).intValue(),
                    ThreadPool.pool);
            this.storeContentDao = storeContentDao;
            this.mallContentMapper = mallContentMapper;
        }

        /**
         * 处理该范围内的数据
         *
         * @param start
         * @param end
         */
        @Override
        protected void executeByRange(int start, int end) {
            log.info("query range {} ~ {}", start, end);
            List<MallContent> list = Lists.newArrayList();
            queryByCondition(start).forEach(e -> {
                MallContent mallContent = JsonUtils.toObject(JsonUtils.toJsonStr(e), MallContent.class);
                list.add(mallContent);
            });
            mallContentMapper.batchInsert(list);
        }

        /**
         * 分页查询，每次执行1000条
         *
         * @param start
         * @return
         */
        private synchronized List<StoreContentDO> queryByCondition(int start) {
            final BasePaging paging = BasePaging.builder().skip(start).limit(1000).build();
            final Criteria cr = Criteria.where(BaseField.GMT_CREATED).gt(BEGIN_TIME);
            return storeContentDao.queryByPage(paging, cr).getRows();
        }
    }

@Slf4j
public abstract class BatchTask implements Runnable {

    private static final int DEFAULT_PER_SIZE = 1000;

    private ThreadPoolExecutor pool;

    private List<SplitListUtil.SubRange> rangeList;

    public BatchTask(int total) {
        IybAssert.isTrue(total > 0, "total must greater than zero");

        initDefaultPool();

        splitList(total, DEFAULT_PER_SIZE);
    }

    public BatchTask(int total, int perSize) {
        IybAssert.isTrue(total > 0, "total must greater than zero");
        IybAssert.isTrue(perSize > 0, "per size must greater than zero");

        initDefaultPool();

        splitList(total, perSize);
    }

    public BatchTask(int total, ThreadPoolExecutor pool) {
        IybAssert.isTrue(total > 0, "total must greater than zero");
        IybAssert.isTrue(pool != null, "thread pool can not be null");

        this.pool = pool;

        splitList(total, DEFAULT_PER_SIZE);
    }

    public BatchTask(int total, int perSize, ThreadPoolExecutor pool) {
        IybAssert.isTrue(total > 0, "total must greater than zero");
        IybAssert.isTrue(perSize > 0, "per size must greater than zero");
        IybAssert.isTrue(pool != null, "thread pool can not be null");

        this.pool = pool;

        splitList(total, perSize);
    }

    /**
     * 处理该范围内的数据
     */
    protected abstract void executeByRange(int start, int end);

    @Override
    public void run() {
        long st = DateUtils.nowMill();
        CountDownLatch latch = new CountDownLatch(rangeList.size());

        for (SplitListUtil.SubRange range : rangeList) {
            CompletableFuture.runAsync(() -> {
                try {
                    executeByRange(range.getStart(), range.getEnd());

                    log.info("BATCH TASK >> execute range {} ~ {} finish", range.getStart(), range.getEnd());
                } catch (Exception e) {
                    log.error("BATCH TASK >> execute range {} ~ {} error [{}]", range.getStart(), range.getEnd(), e.getMessage(), e);
                } finally {
                    latch.countDown();
                }
            }, pool);
        }

        try {
            latch.await();
        } catch (InterruptedException ignored) {
        }

        log.info("BATCH TASK >> finish execute all task cost time: [{}]ms", DateUtils.nowMill() - st);
    }

    /**
     * 初始化默认线程池
     */
    private void initDefaultPool() {
        if (pool != null) return;

        final AtomicInteger index = new AtomicInteger(0);

        pool = new ThreadPoolExecutorWrapper(10,
                10,
                60, TimeUnit.SECONDS,
                new ArrayBlockingQueue<>(100),
                r -> {
                    Thread t = new Thread(r);
                    t.setName("batch-task-pool-" + index.incrementAndGet());
                    return t;
                },
                new ThreadPoolExecutorWrapper.CallerRunsPolicy());

        pool.prestartAllCoreThreads();
    }

    private void splitList(int total, int perSize) {
        rangeList = SplitListUtil.averageAssign(total, perSize);
    }

}

4.mongo表数据全部洗进去mysql库后建下mysql表中的索引（索引要在洗数据后建，在建表时建会影响洗数据效率，索引需要花费一定的时间）

5.将所有对mongo数据库表操作更新，添加，删除的地方也同步到mysql表中
6.发布版本，上线后洗数据到mysql表中。

四。操作mongo库的相关代码换成操作mysql表
重新拉一个分支，将所有操作mongo库的代码都改为操作mysql表，发布上线。