1. 概述
1.1 应用场景
| 对象 | 问题 | 用户期望 |
|---|---|---|
| 业务数据库（新增大量数据/更新部分数据） | 业务系统每天都会新增大量数据，写入业务数据库 | 业务数据库数据量过多，希望能够保证并提高业务数据库的性能 |
| 存在不再使用但又不能删除的数据（比如医院的患者档案），或者不会再大量修改只会进行查询的数据 | 这类数据进行迁移备份 | |
| 一些数据分析工具，例如 FineReport 、FineBI，会对业务数据库中的数据进行加工处理与应用 | 避免数据分析工具对业务系统的性能影响 |
针对上述问题，本文提供解决方案。
1.2 实现思路
本方案实现业务数据库的读写分离。下面序号为实现步骤的先后顺序：
| 对象 | 说明 |
|---|---|
| 业务数据库 | 只保留最近一段时间的业务数据，避免存放数据过多对业务数据库性能造成影响 |
| 中间库 | 1）业务数据库中的数据实时同步到中间库中，数据分析工具对中间库的数据进行加工处理与应用，避免数据分析工具对业务数据库的读取压力，为业务数据库添加性能保护屏障 2）为提高中间库的读取性能，中间库不能存放过多数据，所以中间库删除数据方式为「物理删除」，与业务数据库数据实时保持一致 |
| 归档库 | 业务数据库数据迁移备份到归档库。说明如下： 1）由于业务数据库数据变动后，会实时同步到中间库，所以归档库先同步数据，业务数据库再删除数据 2）归档库不与业务数据库实时同步原因： 基于本文场景，若想实现归档库与业务数据库的实时同步，需要再新建一个数据管道任务，会对业务数据库的读写性能造成影响；而且归档库只是为了备份数据，没必要与业务数据实时同步 |
1.3 任务展示
FineDataLink 中的数据处理过程，详情参见：https://demo.finedatalink.com/
1）管道任务：
2）定时任务：「数据归档1-」。
2. 操作步骤
2.1 场景模拟
业务数据库 demo1 中存放着「客户订单」表、「库存」表，这两张表每天都会接收大量新增数据、部分更新数据，为保证业务数据库的性能，希望将这两张表信息备份到归档库 demotest2 中。同时，想避免数据分析工具对业务数据库的读取压力（数据分析工具对近三年数据进行分析）。
具体表信息如下表所示：
| 数据库 | 表信息 |
|---|---|
| 业务数据库 demo1 | |
| 归档库 demotest2 | stock.xls 「order_information」表中，需要将「订单ID」设为主键；「stock」表中，需要将「ID」设为主键 |
2.2 方案说明
注：中间库的存在原因请参见本文 1.2 节内容。
| 步骤 | 说明 | ||||||
|---|---|---|---|---|---|---|---|
| 步骤一 | 业务数据库数据实时同步到中间库中：
| ||||||
| 步骤二 | 每天凌晨，筛选出_fdl_update_timestamp字段值为前一天的数据，同步到归档库中 归档库中的表有物理主键，若主键冲突，覆盖目标表数据，实现数据的更新 | ||||||
| 步骤三 | 根据表中的时间戳字段，删除业务数据库三年前的数据 由于业务数据库数据变动后，会实时同步到中间库，所以归档库先同步数据，业务数据库再删除三年前数据 |
2.3 业务数据库实时同步到中间库
2.3.1 准备工作
数据管道详细介绍请参见：配置数据管道任务
1）数据管道支持的数据源详情参见：数据管道支持的数据源
2）前提条件请参见：使用数据管道的前提条件
3）配置传输队列，其实就是配置暂存来源库数据，方便目标库写入数据的「数据管道」，目前是通过 Kafka 实现的。
详情参见：配置传输队列
2.3.2 选择数据源
首先选择需要数据同步的来源数据。
点击「数据管道>新建任务」，进入任务设置界面，选择来源数据库以及需要进行数据同步的数据表，默认读取方式为 Binlog，默认先对所有存量数据同步，然后持续同步新增变化。
将已经存在的「客户订单」表、「库存」表选到「要同步的表」中。
如下图所示：
2.3.3 选择数据去向
由于数据分析工具从「中间库」中读取数据，为保证「中间库」的性能，「中间库」与「业务数据库」一样，只保留最近三年的数据，所以执行物理删除；并且在目标表追加时间戳字段，记录数据新增或更新的时间，便于筛选每天变化的数据同步到归档库中。
点击「下一步」进入「选择去向」界面，选定目标数据库。勾选「目标端执行物理删除」，开启「同步时标记时间戳」、「同步源表结构变化」按钮。如下图所示：
2.3.4 设置表字段映射
1）点击「下一步」进入「字段映射」设置界面。
2）首先设置来源表「客户订单」的同步，去向设置为自动建表，命名目标表为「客户订单副本」，映射方式选择「同名映射」，目标表中设置「订单ID」字段为主键。如下图所示：
之后设置来源表「库存」的同步，去向设置为自动建表，命名目标表为「库存副本」中，映射方式选择「同名映射」，目标表中设置「ID」字段为主键。如下图所示：
在此界面下划，可看到管道同步数据时，系统将在目标表中新建字段_fdl_update_timestamp（毫秒级时间戳的形式记录数据在数据库中实际新增和更新的时间），同步时将所有数据传入时间戳。如下图所示：
2.3.5 设置管道控制
1）点击「下一步」进行数据管道的任务设置。
2）数据同步允许一定的容错，比如字段类型、长度不匹配、主键冲突等等问题，可以设置产生的脏数据上限，达到上限则自动终止管道任务，同时设置当任务异常时的通知。如下图所示：
2.3.6 效果查看
点击「保存并启动」按钮，执行管道任务。任务执行成功后，业务数据库表信息已经同步到中间库中。如下图所示：
2.4 中间库数据同步到归档库
1）新建定时任务，新增「数据同步」节点，重命名，筛选出中间库 「库存副本」表前一天的数据 。如下图所示：
SELECT * FROM demotest.库存副本
WHERE FROM_UNIXTIME(_fdl_update_timestamp/1000,'%Y-%m-%d') = date_sub(curdate(),interval 1 day)
2）点击「数据去向与映射」将筛选出的数据同步到归档库 demotest2 的 stock 表中，取消 _fdl_update_timestamp 字段映射，字段 _fdl_update_timestamp 不同步到目标表中。如下图所示：
3）点击「写入方式」，写入方式选择「直接将数据写入目标表」，设置主键映射为 ID 字段，当主键冲突时，覆盖目标表数据，实现数据的更新。如下图所示：
2.5 删除业务库三年前数据
增加一个「SQL脚本」节点，与「数据同步」节点相连，删除业务数据库中「库存」表三年前的数据。如下图所示：
DELETE FROM demo1.库存 WHERE 日期 < DATE_SUB(NOW(), INTERVAL 3 YEAR)
后续步骤：
「客户订单」表与 「库存」表执行的步骤相同。
1）筛选出中间库 「客户订单副本」 表前一天的数据。如下图所示：
SELECT * FROM demotest.客户订单副本
WHERE FROM_UNIXTIME(_fdl_update_timestamp/1000,'%Y-%m-%d') = date_sub(curdate(),interval 1 day)
2）点击「数据去向与映射」，设置数据同步到归档库 demotest2 的 order_information 表中。 取消 _fdl_update_timestamp 字段映射，字段 _fdl_update_timestamp 不同步到目标表中。如下图所示：
3）点击「写入方式」，写入方式选择「直接将数据写入目标表」，设置主键映射为 ID 字段，当主键冲突时，覆盖目标表数据，实现数据的更新。如下图所示：
4）增加一个「SQL脚本」节点，与前一个「数据同步」节点相连，删除业务数据库中「客户订单 」表三年前的数据。如下图所示：
DELETE FROM demo1.客户订单 WHERE 订购日期 < DATE_SUB(NOW(), INTERVAL 3 YEAR)
2.6 设置定时调度
1）保存后发布定时任务到生产模式，点击「调度计划>定时调度>添加单个调度」，设置任务每天 0 点执行。如下图所示：
2）点击右上角「运行」按钮。
3. 结果展示
定时任务执行后：
1）中间库的前一天数据同步到归档库中。如下图所示：
2）业务数据库中的「客户订单」表、「库存」表已删除三年前数据。如下图所示：
「库存」表删除前后最小日期对比：
「客户订单信息」表删除前后最小日期对比：
3）业务数据库删除数据后，中间库 demotest 中，「库存副本」和「客户订单副本」表中，也已删除三年前数据，效果与业务数据库相同，此处不再赘述。
