如何高效将动态源表中首次出现的ID行写入静态目标表？

我有一张持续写入新数据的海量动态源表，其中包含ID列，同一ID值会随时间在源表中出现多次。
请问是否可实现扫描动态源表，仅提取每个ID的首次出现行，并将其仅写入目标表一次？目标表初始为空，希望能检查源表新行的ID是否已存在于目标表，仅当ID不存在时才写入。
我面临的困境是：源表数据量极大，如何避免每次都使用row number/partitioning按ID分区？这种操作成本过高，无法持续运行。

示例表结构及数据：

CREATE TABLE example_table (
    id VARCHAR(6),
    event_timestamp TIMESTAMP,
    col1 VARCHAR(50),
    col2 INT
);

INSERT INTO example_table (id, event_timestamp, col1, col2) VALUES
('834726', '2024-08-15 10:00:00', 'First entry', 100),
('834726', '2024-08-15 11:00:00', 'Second entry', 200),
('834726', '2024-08-15 12:00:00', 'Third entry', 300),
('834726', '2024-08-15 13:00:00', 'Fourth entry', 400);

上述示例中，假设4行数据按顺序逐步写入，且源表包含数十亿行，各行ID可能重复如示例所示。最终希望仅将第一行('834726', '2024-08-15 10:00:00', 'First entry', 100)写入与源表结构一致的目标表。

完全可以实现，核心是避免全表扫描/全局分区排序，改用增量处理+ID快速校验的低成本方案，以下是具体实践：

1. 给目标表加ID唯一约束 #

先给目标表的id列设置主键或唯一约束，让数据库自动维护ID唯一性，这是后续所有操作的基础：

CREATE TABLE target_table (
    id VARCHAR(6) PRIMARY KEY, -- 或者用 UNIQUE KEY (id)
    event_timestamp TIMESTAMP,
    col1 VARCHAR(50),
    col2 INT
);

有了这个约束，重复ID的写入会直接被数据库拦截，无需提前做全表查询校验。

2. 只处理源表的增量数据 #

绝对不要每次扫描全表，只读取新增的行：

• 如果源表有时间戳字段（比如示例里的event_timestamp），每次记录上次处理完成的最大时间戳，下次只查询event_timestamp > 上次最大时间戳的数据；
• 用CDC工具（比如Debezium、Flink CDC）直接捕获源表的INSERT操作，只处理新写入的行，彻底跳过历史数据。

3. 增量数据的去重写入 #

针对增量获取的新数据，分两种场景处理：

小批量/单条写入 #

直接用INSERT IGNORE（MySQL）或ON CONFLICT DO NOTHING（PostgreSQL）语法，利用目标表的唯一约束自动跳过已存在的ID：

-- MySQL 示例
INSERT IGNORE INTO target_table (id, event_timestamp, col1, col2)
SELECT id, event_timestamp, col1, col2
FROM example_table
WHERE event_timestamp > '2024-08-15 09:59:59'; -- 这里填上次处理的最大时间戳

这条语句会自动忽略因ID重复导致的写入错误，只保留每个ID的首次出现行。

大批量写入 #

如果增量数据量很大，先在临时表中对增量数据做本地去重（只保留每个ID的最早行），再写入目标表：

-- 第一步：创建临时表存储去重后的增量数据
CREATE TEMPORARY TABLE temp_increment AS
SELECT id, MIN(event_timestamp) AS event_timestamp, col1, col2
FROM example_table
WHERE event_timestamp > '2024-08-15 09:59:59'
GROUP BY id, col1, col2; -- 确保同一ID只保留最早的那条记录

-- 第二步：写入目标表，利用唯一约束跳过已存在的ID
INSERT IGNORE INTO target_table (id, event_timestamp, col1, col2)
SELECT id, event_timestamp, col1, col2
FROM temp_increment;

临时表的分组只针对增量数据，数据量远小于全表，计算成本极低。

4. 实时流处理场景优化（如果用流框架） #

如果是实时处理源表的新数据，用Flink、Spark Streaming这类流处理框架：

• 用框架的State机制维护已处理过的ID，每条新数据进来先查State，不存在则写入目标表并更新State；
• 结合Watermark机制，确保只处理每个ID的最早事件，避免重复计算。

5. 数据库索引优化 #

• 给源表的event_timestamp和id建立联合索引，加速增量查询和本地去重的速度；
• 目标表的id主键本身就是索引，ID存在性校验是O(1)操作，几乎无成本。

内容的提问来源于stack exchange，提问作者Rob