记一次同步千万数据需求

需求

数据库客户表新增一个字段, 而字段的值需要从另一张表里拿, 新增的数据还需要同步到索引, 共有一千六百万条数据, 如何在线上数据库同步?

思考

1 : 千万数据, 如果需要同步, 需要数分钟时间, 为了不影响用户体验, 只能在晚上来刷新数据
2 : 刷新数据要可以保证数据不丢失, 还可以保证速度

流程

//创建一个有20个线程的线程池
protected static final ExecutorService EXECUTOR_SERVICE = TtlExecutors
            .getTtlExecutorService(Executors.newFixedThreadPool(20, new LNamedThreadFactory("solr-uploader")));


public void updateByCustomerId(Long minSequence, Long maxSequence){
//sequence : 是指在数据库中自增的某一索引, 根据索引查询数据速度会非常快
        //数据库最小sequence
        if (Objects.isNull(minSequence) || minSequence==0) {
            minSequence = customerDao.getMinSequence();
        }
        //数据库最大sequence
        if (Objects.isNull(maxSequence) || maxSequence==0) {
            maxSequence = customerDao.getMaxSequence();
        }
        /*为什么分最小最大sequence, 还有游标(间距)
        * 因为数据量太大, 而我们开启的20个线程可以根据游标来互不干扰的工作
        */
        //当前游标
        Long currentSequence = minSequence;
        for (;;){
            //判断是否执行完毕
            if(maxSequence < currentSequence){
                LOGGER.info("提交结束[{},{}]",currentSequence,currentSequence+STEP);
                break;
            }
            LOGGER.info("当前区间[{},{}]",currentSequence,currentSequence+STEP);
            EXECUTOR_SERVICE.execute(new CurrentRunable(currentSequence,currentSequence+STEP));
            currentSequence=currentSequence+STEP;
        }
    }

    public void update( Long minSequence,Long maxSequence) {
        LOGGER.info("批量更新客户线索来源clue_source [{},{}]", minSequence, maxSequence);
        //分页查询条数
        int page=0;
        while(true){
            long a = System.currentTimeMillis();
            //向上取整
            int ceil = (int)Math.ceil((double)STEP / SIZE);
            if(ceil <= page){
                break;
            }
            //根据createType与最大最小sequence拿到对应范围的数据
            Long start = minSequence + page*SIZE;
            Long end = start+SIZE;
            List<Customer> customers = customerDao.getByCreateType(start, end, CLUE_CREATE);
            if(CollectionUtils.isNotEmpty(customers)){
                insertSearch(customers);
            }else {
                LOGGER.info("该区间[{},{}]无线索创建的客户", start, end);
            }
            //page=page+SIZE;
            long b = System.currentTimeMillis();
            LOGGER.info("单批次数据执行时间[{},{}] {}, 批次 {}", start, end, b-a, page);
            page++;
        }
    }

    private void insertSearch(List<Customer> customers) {
        //如果没有拿到数据 则视为所有数据都已经处理完毕
        Map<String, Object> indexMap = new HashMap<>();
        List<IndexEvent> indexEvents = new ArrayList<>();
        int failNum = 0;
        int succNum = 0;
        for (Customer cus : customers) {
            //根据店铺code' userId 获取对应线索
            UnifiedClue unClue =  unifiedClueDao.getByShopCodeAndUserId(cus.getShopCode(), cus.getCrmUserId());
            if(unClue != null) {
                //设置索引传入的要修改参数
                JSONObject jsonObject = buildCustomerMaps(cus.getId(), unClue.getPlatform(), unClue.getClueCategory());
                indexMap.put(TABLE_NAME, jsonObject);
                IndexEvent indexEvent = new IndexEvent(UPDATE, indexMap);
                elasticSearchSPI.insert(Lists.newArrayList(indexEvent));
                indexEvents.add(indexEvent);
                succNum++;
            }else{
                LOGGER.info("失败客户ID {}", cus.getId());
                failNum++;
            }
        }
        LOGGER.info("线程名称 {} , 成功数量 {} , 失败数量 {}", Thread.currentThread().getName(), succNum, failNum);
        //indexService.insert(INDEX_NAME, indexEvents, ORIGIN);
    }
//将数据同步到索引
    private JSONObject buildCustomerMaps(String id, String platform, String clueCategory) {
        JSONObject indexJson = new JSONObject();
        indexJson.put(IndexEnum.ID.getIndex(), id);
        indexJson.put(IndexEnum.PLATFORM.getIndex(), platform);
        indexJson.put(IndexEnum.CLUE_SOURCE.getIndex(), clueCategory);
        return indexJson;
    }

}

总结

1 : 将数据有批次的进行同步会比一起同步要快的多, 千万级数据同步只需要不到五分钟就可以完成大量访问数据库的同步任务
2 : 注意不要将数据库搞跨了, 否则是一次重大事故
3 : 在测试数据库记录好有无失败的数据, 并找出原因
4 : log一定要在重要的位置配置, 否则出错后, 找不到原因