求助：基于SQL的增量抽取，能做到100%不丢数据吗？

在数据仓库、数据湖的搭建过程中，把业务数据库（像 MySQL、Oracle、TiDB、PostgreSQL 这些）里的数据同步到分析平台，是一切数据分析工作的起点。 

要是不采用 Binlog/CDC（Change Data Capture）这类数据库底层技术，而是在应用层用 SQL 做定期轮询（Polling），那最常用的方案就是依靠 modify_time（修改时间）字段来实现增量抽取。 

乍一看，这逻辑没毛病：记录好上次任务的运行时间，然后查询大于上次时间、小于当前时间的数据就行。 

但我要跟你说个扎心的事实：要是你直接这么写 SQL，数据大概率会丢！今天咱们就来扒一扒这个藏在背后的 “隐形杀手”——Late Event（迟到事件），以及怎么通过合理设计，实现（近乎）100% 的数据完整性。

假设我们有一个订单表 orders，表结构设计极其规范，包含主键、逻辑删除标识和自动更新的时间戳。

CREATETABLE orders (
    id BIGINTPRIMARYKEY,
    order_no VARCHAR(64),
    amount DECIMAL(10,2),
    is_deleted TINYINTDEFAULT0,-- 0:正常, 1:删除
    modify_time TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
INDEX idx_modify_time (modify_time)
);

调度任务每 5 分钟执行一次。

• 上次执行时间 (last_run_time)：10:00:00

• 本次执行时间 (current_run_time)：10:05:00

一般大家会这么写代码逻辑：

SELECT*FROM orders 
WHERE modify_time >='2023-10-27 10:00:00'
AND modify_time <'2023-10-27 10:05:00';

执行完之后，把 last_run_time 更新成 10:05:00，就等着下一次调度。是不是觉得这就完事了？可偏偏就是这样，数据不知不觉就丢了。

问题的根源，就出在数据库的 MVCC（多版本并发控制）和事务提交机制上。先记住两个关键知识点：

1）SQL 中的 CURRENT_TIMESTAMP 通常是在 SQL 语句开始执行或事务开始时确定的，而不是事务提交时。

2）ETL 抽取 SQL 只能读到已提交（Committed）的数据（Read Committed 隔离级别下）。

下面咱们通过一个具体的 “丢数” 场景，看看问题到底怎么发生的：

假设 ETL 任务在 10:05:00 准时启动，然后一系列操作按时间线展开： 

1）10:04:59（事务 A 开始）：业务系统开启了一个大事务 A，插入了一条订单记录 Order_X。这时候数据库会生成 modify_time = 10:04:59，但要注意，事务 A 还没提交，其他会话是查不到这条数据的。 

2）10:05:00（ETL 开始）：调度系统启动抽取任务，执行 SQL：SELECT ... WHERE modify_time < '10:05:00'。ETL 任务只会读取所有已提交的数据，由于 Order_X 还没提交，所以 ETL 根本读不到它。等 ETL 任务结束，系统会把 last_run_time 更新成 10:05:00。 

3）10:05:01（事务 A 提交）：业务事务 A 完成所有逻辑，执行 COMMIT 操作。Order_X 正式写入磁盘，它的 modify_time 还是 10:04:59。

下一次抽取（10:10:00 启动），SQL 条件变为：

WHERE modify_time >= '10:05:00' AND modify_time < '10:10:00'

悲剧就此发生！Order_X 的 modify_time 是 10:04:59： 

• 上一次抽取（条件是 < 10:05:00）因为它没提交，没读到。 

• 这一次抽取（条件是 >= 10:05:00）直接把它排除在外。 

• 最后，Order_X 永久丢失了。 

这就是 Late Event（迟到事件）。在高并发或者有长事务的场景下（比如批量更新、网络抖动），这种情况会频繁出现，让人防不胜防。

既然最新的数据不可靠，那我就不读最新的。

策略： 每次抽取只读 N 分钟之前 的数据。假设在这个 N 分钟内，所有长事务都已经提交完毕。

SQL 示例： 当前时间 10:05:00，但我只取 10:00:00 之前的数据。

SELECT * FROM orders 
WHERE modify_time >= '09:55:00' 
  AND modify_time <  '10:00:00';

优点： 简单，极大降低丢数概率。

缺点： 数据延迟高（T+N），且理论上如果事务时长超过 N 分钟，依然会丢数。

我们要承认数据的不确定性，通过“宁可多读，不可少读”的策略来覆盖。

核心思想： 每次抽取时，我们将左边界（开始时间）回退一段安全时间（比如 5 分钟），即使上次已经把这 5 分钟的数据抽过了。

1）修改抽取 SQL（回退 5 分钟）：

假设当前是 10:05:00，上次成功抽取的时间点是 10:00:00。 我们不从 10:00:00 开始，而是从 09:55:00 开始读。

SELECT*FROM orders 
WHERE modify_time >='10:00:00'-INTERVAL5MINUTE-- 回溯窗口
AND modify_time <'10:05:00';-- 当前切片

2）结果集分析： 在这个查询结果中，会有两部分数据：

• 大部分： 是 09:55:00 到 10:00:00 之间已经被上次任务同步过的重复数据。

• 小部分： 是上次因为事务延迟未提交，而遗漏掉的“漏网之鱼”（比如上面那个 10:04:59 的 Order_X）。

3）下游处理（关键）： 因为读取了重复数据，下游必须支持幂等（Idempotency）写入。如果下游是数据库： 使用 INSERT ON DUPLICATE KEY UPDATE (MySQL) 或 MERGE INTO (Oracle/TiDB)。

优点：数据延迟较低，极大降低丢数概率。

缺点：下游需要做数据去重， 而且理论上如果事务时长超过了回退的安全时间，还是会有丢数的可能。

要是不能借助数据库底层信息，比如 Oracle 的 SCN（System Change Number）、Innodb 的系统表（information_schema.innodb_trx），只依靠通用的 SQL 能力，理论上是可以做到 100% 不丢数据的。 

既然 modify_time 不可靠，那咱们就换个思路，通过 SQL 强制串行化的方法，模拟 CDC 中 “事务提交顺序” 的强一致性。

我们不再依赖时间戳，而是通过一个单一、被锁定的版本号来作为全局水位线。

1) 全局版本控制表（序列化核心）

该表只有一行记录，其目的就是被所有事务竞争，从而实现对整个业务表的串行化。

CREATETABLE global_version_tracker (
    table_name VARCHAR(64)PRIMARYKEY,-- 'orders'
    max_version_id BIGINT,-- 当前最大版本号，作为 ETL 水位线
);
-- 初始化：INSERT INTO global_version_tracker VALUES ('orders', 0);

2) 业务表 (orders) 改造

业务表需要添加一个字段来记录其被修改时所获得的全局版本号。

ALTERTABLE orders ADDCOLUMN version_id BIGINTDEFAULT0;

无论是 INSERT、UPDATE 还是逻辑 DELETE，所有修改 orders 表的事务都必须按照以下流程执行：

ETL 任务变得极其简单，因为它完全依赖于严格递增的 version_id。

-- 只需要简单地基于版本号进行范围抽取，无需考虑时间重叠
SELECT*FROM orders 
WHERE version_id > {last_extracted_version} 
AND version_id <=(SELECT max_version_id FROM global_version_tracker WHERE table_name ='orders');

这种方案理论上能实现 100% 精准同步，但代价也很大 —— 会严重牺牲业务系统的并发性能，一般情况下不建议轻易使用。 

基于 SQL 的增量同步，要做到 “绝对理论上的 100% 不丢数据”，在分布式系统中难度极大，而且需要付出高昂的代价。不过通过重叠时间窗口策略，我们完全可以解决 99.99% 的工程实际问题。 

最后，给大家整理一份避坑指南： 

1）别迷信 last_run_time：每次抽取都要往前多读一点（也就是设置 Overlap 重叠窗口），避免遗漏延迟提交的数据。 

2）下游必须支持去重：多读必然会导致数据重复，要是下游没有 Upsert（更新或插入）能力，就会出现数据重复的问题。 

3）注意时间戳精度：尽量使用毫秒级时间戳，减少因为时间精度不够导致的排序不稳定情况。 

4）终极建议：如果数据准确性要求达到金融级别，或者业务表的数据量特别大，那就别再用 SQL 轮询了，直接拥抱 CDC 技术（比如 Canal、Debezium、TiCDC、OGG），这对 SQL 轮询来说就是降维打击，能从根本上解决数据丢失问题。