mysql系列2-同步记录表的设计 2016-01-22 00:53:35
分类:Mysql
155人阅读
关键词:synchronize,design

mysql系列2:同步记录表的设计

文章结构


设计结果

同步记录表

    CREATE TABLE `log` (
        `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `action` TEXT NOT NULL,
        `type` INT(1) NOT NULL COMMENT '1user2product3order',
        `subject` VARCHAR(50) NOT NULL COMMENT '操作物品id',
        `addtime` INT(11) NOT NULL,
        `status` INT(1) NULL DEFAULT '0' COMMENT '0失败1成功2重复冲突',
        `site` INT(1) NULL DEFAULT NULL COMMENT '所属站点',
        `operator` INT(4) NULL DEFAULT NULL COMMENT '操作者',
        `operator_name` VARCHAR(20) NULL DEFAULT NULL,
        `summary` INT(1) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=265;

同步起始点表

    CREATE TABLE `log_start` (
        `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `type` INT(1) NOT NULL COMMENT '1user2product3order',
        `end_subject` VARCHAR(50) NOT NULL COMMENT '每次同步的最大值',
        `scope` INT(1) NULL DEFAULT NULL COMMENT '所属站点',
        PRIMARY KEY (`id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=myisam
    AUTO_INCREMENT=1;

初次设计

同步统计表

    CREATE TABLE `syn_log` (
        `syn_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `type` INT(1) NOT NULL COMMENT '1user2product3order',
        `addtime` INT(11) NOT NULL,
        `start` INT(10) NOT NULL default 0 comment '同步开始的记录ID',
        `end` INT(10) NOT NULL default 0 comment '同步结束的记录ID',
        `syn_num` INT(10) NOT NULL default 0 comment '同步遍历的记录条数',
        `success_num` INT(10) NOT NULL default 0 comment '同步成功的条数',
        `fail_num` INT(10) NOT NULL default 0 comment '同步失败的条数',
        `duplicate_num` INT(10) NOT NULL default 0 comment '同步重复的条数',
        `site` INT(1) NULL DEFAULT NULL COMMENT '所属站点',
        `operator` INT(4) NULL DEFAULT NULL COMMENT '操作者',
        `operator_name` VARCHAR(20) NULL DEFAULT NULL,
        PRIMARY KEY (`syn_id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=1;

同步详情表

    CREATE TABLE `syn_detail` (
        `detail_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `syn_id` INT(10) UNSIGNED NOT NULL,
        `subject` VARCHAR(50) NOT NULL COMMENT '订单号用户ID等',
        `status` INT(1) NULL DEFAULT '0' COMMENT '0失败1成功2重复冲突',
        `site` INT(1) NULL DEFAULT NULL COMMENT '所属站点',
        PRIMARY KEY (`detail_id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=1;

初始的设计想法:

将同步统计结果与详情分开,统计结果有错误时,再去查询详情发现同步问题。下一次同步时,可以轻松获取上次同步的最终ID(end栏位)。

在实现同步功能时才发现有很多不合理及麻烦之处:

  • 逻辑矛盾。 详情表里syn_id需要同步统计表插入数据后才能生成,而在同步时,是从其它服务器获取的数据,遍历一条就插入一条,这样才能统计同步的结果,即先插入了同步详情,才有了同步的统计结果。这样就带来了鸡生蛋蛋生鸡的死圈圈。

  • 显示同步记录时,要两表join才能展示全面信息。 即除了发现同步记录哪些失败了,也要能够知道当时是谁同步的,以及又是何时同步的。

当然,最重要的就是设计逻辑产生了矛盾,所以必须弃用,重新设计。

新设计的思想:

将同步统计结果和同步详情放在一起,用表示同步状态的栏位(status)来同时表示是否是统计记录。创建log_start表的目的是为了减少获取上次同步的最终记录ID所花的时间,因为log表最终会数据越来越庞大,查询时间必定会延长。

新设计带来的好处:

  • 存储同步方便。 遍历获取同步的数据,一个个的插入,根据当下每条记录的插入成功与否来插入相对应的同步记录。顺便统计好成功失败等数据,同步完成后再插入同步统计数据。依次进行,很是方便。
  • 显示详细的同步记录。 根据(status)栏位就可以查到统计记录或是同步失败的记录,无须join其它表就可看到完整的同步记录信息,给代码编写带来了很大方便。

醉酒三问

  • 一开始同步时,log_start并没有数据,那该从哪笔记录开始同步呢? 暂时是靠人工干预,在第一次同步时,设置一个起始点,程序判断log_start表为空时就抓取该设置值。对于一个几十万记录的表,再从0开始同步是不可取的(初始数据是导入的,现在想来,可以在导入数据后插入一笔记录到log_start表里)。