定时任务同步设计

发表于 2020-12-27 阅读次数：

需求，将一个表的内容同步到另一个表中，
只同步从某个时间段到现在有更新的记录；

解决思路是：

如果可以修改表结构，可以加一个【sync_status（同步状态）】字段：同步完成后，修改下这个记录；有修改时恢复这个记录；

如果无法修改表结构，但是可以通过【update_time（修改时间）】字段来过滤，数据量太多可以分批处理

建立一个任务配置表（t_job_config），在这个配置表中，保存上次的同步信息（如：同步名称，同步时间，同步到了哪个 id，等等）
根据名称，从表中获取任务配置，如果没有就为这个名称新建立一条记录；开始时间为 null
记录下当前时间；作为结束时间
取一个最大条数 MAX；从数据库中根据修改时间和 id 获取记录，对这批数据做处理；
记录不为空，取上次获得的最后一条 id 做为下次查询的开始 id，如下面代码所示

@Component
@Slf4j
public class SyncStudentJob {
    private static volatile boolean isSync = false;
    @Autowired
    StudentService studentService;

    @Autowired
    JobConfigService jobConfigService;

    @Scheduled(cron = "0/10 * * * * *")
    public void syncStudent() {
        if (isSync) {
            log.info("上次还没同步完成");
            return;
        }

        try {
            isSync = true;
            final long t1 = System.currentTimeMillis();
            log.info("【Job syncStudent】， 开始同步");
            AtomicLong count = doSyncStudent();
            final long t2 = System.currentTimeMillis();
            log.info("【Job syncStudent】， 完成全部同步，耗时：{}ms，数量：{} ", (t2 - t1) / 1000, count.get());
        } finally {
            isSync = false;
        }
    }

    private AtomicLong doSyncStudent() {
        AtomicLong count = new AtomicLong();
        final int max = 500;
        final JobConfigEntity jobConfig = getOrCreateJobConfig();
        Objects.requireNonNull(jobConfig, "获取或创建jobConfig失败");

        final LocalDateTime endTime = LocalDateTime.now();
        final LocalDateTime lastUpdateTime = getLastUpdateTime(jobConfig);
        String lastUpdateId = getLastUpdateId(jobConfig);
        List<StudentEntity> list;

        do {
            final long start = System.currentTimeMillis();
            list = studentService.lambdaQuery()
                    .gt(lastUpdateTime != null, StudentEntity::getGmtModified, lastUpdateTime)
                    .lt(StudentEntity::getGmtModified, endTime)
                    .gt(StudentEntity::getId, lastUpdateId)
                    .orderByAsc(StudentEntity::getId)
                    .last(String.format("limit %s", max))
                    .list();
            if (list.isEmpty()) {
                break;
            }

            doList(list);
            count.addAndGet(list.size());

            final StudentEntity lastStudent = list.get(list.size() - 1);
            lastUpdateId = String.valueOf(lastStudent.getId());
            updateJobConfig(jobConfig, lastUpdateTime, lastUpdateId);

            final long end = System.currentTimeMillis();
            log.info("【Job syncStudent】， 完成一组同步，耗时：{}ms，数量：{} ", (end - start) / 1000, count.get());
        } while (list.size() >= max);

        // 更新成新的时间
        updateJobConfig(jobConfig, endTime, null);
        return count;
    }

    private void updateJobConfig(JobConfigEntity configEntity, LocalDateTime lastUpdateTime, String lastUpdateId) {
        jobConfigService.lambdaUpdate()
                .set(JobConfigEntity::getLastUpdateTime, lastUpdateTime)
                .set(JobConfigEntity::getLastUpdateId, lastUpdateId)
                .eq(JobConfigEntity::getId, configEntity.getId())
                .update();
    }

    private void doList(List<StudentEntity> list) {
        log.info("(<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<");
        log.info(list.toString());
        try {
            TimeUnit.SECONDS.sleep(1);
        } catch (InterruptedException ignored) {
        }
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>)");
    }

    private LocalDateTime getLastUpdateTime(JobConfigEntity jobConfig) {
        return jobConfig.getLastUpdateTime();
    }

    private String getLastUpdateId(JobConfigEntity jobConfig) {
        final String lastUpdateId = jobConfig.getLastUpdateId();
        if (Strings.isEmpty(lastUpdateId)) {
            return "0";
        }
        return lastUpdateId;
    }

    private JobConfigEntity getOrCreateJobConfig() {
        String jobName = "sync.student.name";
        final Optional<JobConfigEntity> configEntityOptional = jobConfigService.lambdaQuery()
                .eq(JobConfigEntity::getName, jobName)
                .oneOpt();
        if (configEntityOptional.isPresent()) {
            return configEntityOptional.get();
        }
        final JobConfigEntity configEntity = new JobConfigEntity();
        configEntity.setName(jobName);
        jobConfigService.save(configEntity);
        return configEntity;
    }
}



Source Server : 127.0.0.1
Source Server Type : MySQL
Source Server Version : 50731
Source Host : 127.0.0.1:3306
Source Schema : school

Target Server Type : MySQL
Target Server Version : 50731
File Encoding : 65001

Date: 28/12/2020 18:37:34
\*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

-- Table structure for t_job_config

---

DROP TABLE IF EXISTS `t_job_config`;
CREATE TABLE `t_job_config` (
`id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '要同步的业务名称',
`last_update_time` datetime(0) NULL DEFAULT NULL COMMENT '上次同步时间',
`last_update_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上次同步到了哪里(排序的 id 才有意义，结合 last_update_time 使用)',
`memo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
`ext` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备用字段',
`gmt_create` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`gmt_modified` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
`disabled` tinyint(1) NULL DEFAULT 0 COMMENT '是否已经禁用',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_name`(`name`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

---

-- Table structure for t_student

---

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生 ID',
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生名称',
`nick_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学生昵称',
`class_id` int(11) NULL DEFAULT NULL COMMENT '班级 ID',
`email` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
`avatar` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '大头贴',
`gmt_create` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`gmt_modified` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
`disabled` tinyint(1) NULL DEFAULT 0 COMMENT '是否已经禁用',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_name`(`name`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1000 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;

INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (1, 'student001', '学生 001', 2, 'student001@coocaa.com', '15212345001', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-28 15:56:56', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (2, 'student002', '学生 002', 2, 'student002@coocaa.com', '15212345002', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (3, 'student003', '学生 003', 1, 'student003@coocaa.com', '15212345003', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (4, 'student004', '学生 004', 9, 'student004@coocaa.com', '15212345004', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (5, 'student005', '学生 005', 8, 'student005@coocaa.com', '15212345005', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (6, 'student006', '学生 006', 7, 'student006@coocaa.com', '15212345006', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (7, 'student007', '学生 007', 6, 'student007@coocaa.com', '15212345007', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (8, 'student008', '学生 008', 5, 'student008@coocaa.com', '15212345008', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (9, 'student009', '学生 009', 4, 'student009@coocaa.com', '15212345009', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (10, 'student010', '学生 010', 9, 'student010@coocaa.com', '15212345010', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (11, 'student011', '学生 011', 8, 'student011@coocaa.com', '15212345011', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (12, 'student012', '学生 012', 7, 'student012@coocaa.com', '15212345012', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (13, 'student013', '学生 013', 6, 'student013@coocaa.com', '15212345013', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (14, 'student014', '学生 014', 5, 'student014@coocaa.com', '15212345014', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (15, 'student015', '学生 015', 4, 'student015@coocaa.com', '15212345015', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (16, 'student016', '学生 016', 3, 'student016@coocaa.com', '15212345016', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (17, 'student017', '学生 017', 2, 'student017@coocaa.com', '15212345017', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (18, 'student018', '学生 018', 1, 'student018@coocaa.com', '15212345018', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (19, 'student019', '学生 019', 9, 'student019@coocaa.com', '15212345019', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (20, 'student020', '学生 020', 5, 'student020@coocaa.com', '15212345020', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (21, 'student021', '学生 021', 4, 'student021@coocaa.com', '15212345021', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (22, 'student022', '学生 022', 3, 'student022@coocaa.com', '15212345022', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (23, 'student023', '学生 023', 2, 'student023@coocaa.com', '15212345023', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (24, 'student024', '学生 024', 1, 'student024@coocaa.com', '15212345024', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (25, 'student025', '学生 025', 9, 'student025@coocaa.com', '15212345025', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (26, 'student026', '学生 026', 8, 'student026@coocaa.com', '15212345026', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (27, 'student027', '学生 027', 7, 'student027@coocaa.com', '15212345027', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (28, 'student028', '学生 028', 6, 'student028@coocaa.com', '15212345028', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (29, 'student029', '学生 029', 5, 'student029@coocaa.com', '15212345029', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);
INSERT INTO `school`.`t_student`(`id`, `name`, `nick_name`, `class_id`, `email`, `phone`, `avatar`, `gmt_create`, `gmt_modified`, `disabled`) VALUES (30, 'student030', '学生 030', 1, 'student030@coocaa.com', '15212345030', 'http://bpic.588ku.com/element_pic/20/06/30/e510dda05e0b09e44cec4e474546a014.jpg', '2020-12-22 09:33:35', '2020-12-22 09:33:35', 0);

SET FOREIGN_KEY_CHECKS = 1;