MySQL D7 热表与 Doris Binlog 删除过滤探索方案

1. 决策摘要

当前问题不是 Binlog 文件持续占用空间，而是事实表、关系表和二级索引每天继续形成新的持久化数据。Binlog 可以按保留周期滚动清理，但写入产生的数据页和索引页会长期保留，因此“Binlog 基本不增长”和“数据库每天增长”并不矛盾。

本方案选择：

1. 为日期事实表建立最近 7 天热表，近期计算不再扫描长期原表。
2. 原表降级为 Doris Binlog 同步缓冲，由异步同步任务写入，不再承担业务读取。
3. 探索现有 Doris Binlog 同步是否能够只处理 INSERT、UPDATE 并排除 DELETE，使 MySQL 的物理保留周期与 Doris 的历史保留周期解耦。
4. 只有该方向验证可行后，原表记录才按同步安全水位滚动删除，使 MySQL 空间从“历史累计增长”变成“固定窗口循环复用”。
5. 超过 7 天的迟到更新不进入热表计算，但仍异步写入原表缓冲并产生可同步的 Binlog 变更。

该设计的最终目标不是减少一次写入的行宽，而是改变 MySQL 的数据生命周期：MySQL 只保留近期可变数据，Doris 保留历史事实数据。

2. 当前容量与数据量报告

2.1 实例容量压力

数据库后台观测值：

如果不改变数据生命周期，按 5.87 GB/天估算：

因此，仅扩容只能延后问题；必须让 MySQL 中可裁剪事实表的空间进入稳定窗口。

2.2 已识别大表

以上三张表合计约 46.22 GB，但它们不是同一种问题：

• 两张 Smart+ 表是关系维表，不能按 7 个自然日直接删除，否则可能丢失仍在投放的历史关系。
• tiktok_adsets 是当前状态维表，应处理完整 JSON，而不是创建 D7 日期表。
• D7 方案优先处理 ad_insights、ad_material_insights 这类按 insight_date 增长的事实表。

2.3 每日事实数据量模型

已知 ad_insights 每天约有 15 万条 data_type=TOTAL 活跃记录。设每条广告平均拆出 N 条 ad_material_insights，则：

ad_insights 每日行数             = 15 万
ad_material_insights 每日行数    = 15 万 × N
每日事实总行数                   = 15 万 × (1 + N)
D7 事实总行数                    = 105 万 × (1 + N)

不同素材倍率下的行数：

这说明即使单行不大，只要每天重复形成广告、素材和多种 data_type 行，一年也会进入数亿到十亿行量级。表增长的主要矛盾是“每日基数 × 素材倍率 × 保留天数 × 索引份数”。

上表只计算 TOTAL 对应的基础模型；如果同时保存其它 data_type、国家、时区或其它拆分维度，实际行数还要乘以相应的维度放大系数。

2.4 D7 空间估算

必须使用生产 DATA_LENGTH + INDEX_LENGTH 除以行数得到实际物理行宽。尚未取得两张事实表的精确平均物理行宽前，可以用以下区间评估：

计算公式：

D7 空间 ≈ D7 行数 ×（数据行平均字节 + 每行索引平均字节）

原表如果再保留 2 天同步缓冲，MySQL 稳态约保存 9 天数据，而不是无限累计。对于纯日期追加事实数据，9 天相对 365 天的理论保留比例约为 2.47%。

2.5 迁移峰值风险

影子写阶段原表仍按原速度增长，同时 D7 再保存一份近期数据。以后台 5.87 GB/天作为整个数据库增长的上限估算，连续影子写 7 天的最坏情况为：

预计使用 = 196.13 + 原增长 5.87×7 + D7 新增 5.87×7
         = 278.31 GB

这会使 300 GB 实例达到约 92.8%，不满足安全余量。实际 D7 只覆盖部分事实表，真实峰值会低于这个上限，但上线前必须按表测量每日增量，不能直接在 300 GB 实例上按最坏情况执行 7 天全量影子双写。

建议的启动条件：

• 峰值预测不超过实例容量的 70%～75%；或
• D7 放在独立 MySQL 实例；或
• 临时扩容后进行影子验证；或
• 将影子周期缩短，并在 DELETE 过滤验证通过后尽早开始原表小批清理。

2.6 上线前必须补齐的生产测量

先查询两张事实表当前数据、索引和估算物理行宽：

SELECT
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS data_gb,
    ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS index_gb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / NULLIF(TABLE_ROWS, 0), 0) AS avg_physical_bytes
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('tbl_ad_insights', 'tbl_ad_material_insights');

TABLE_ROWS 对 InnoDB 是估算值，容量采购和迁移峰值应以连续同一时刻采集 3～7 天的 DATA_LENGTH + INDEX_LENGTH 差值为准。

统计最近 7 天实际行数和素材倍率：

SELECT
    insight_date,
    COUNT(*) AS ad_rows,
    COUNT(DISTINCT ad_id) AS ad_count
FROM tbl_ad_insights
WHERE insight_date >= CURDATE() - INTERVAL 6 DAY
  AND UPPER(data_type) = 'TOTAL'
GROUP BY insight_date
ORDER BY insight_date;

SELECT
    insight_date,
    COUNT(*) AS material_rows,
    COUNT(DISTINCT ad_id) AS ad_count,
    ROUND(COUNT(*) / NULLIF(COUNT(DISTINCT ad_id), 0), 2) AS materials_per_ad
FROM tbl_ad_material_insights
WHERE insight_date >= CURDATE() - INTERVAL 6 DAY
  AND UPPER(data_type) = 'TOTAL'
GROUP BY insight_date
ORDER BY insight_date;

最终使用下列实测值替换本文区间估算：

H_ad       = ad_insights 最近 7 天平均每日物理增量
H_material = ad_material_insights 最近 7 天平均每日物理增量
H          = H_ad + H_material
D7 稳态     ≈ 7H
原表缓冲    ≈ 2H
两者稳态    ≈ 9H

3. 为什么采用该方案

3.1 为什么需要 D7 热表

当前近期同步任务会按 insight_date + ad_id + data_type 或 insight_date + ad_id + material_id + data_type 查询旧值，再更新当日快照。长期原表越大，B-Tree、Buffer Pool、统计信息和锁竞争的成本越高。

D7 将近期计算的工作集限制在固定窗口，收益包括：

• 查询和更新只接触近期分区。
• 热索引规模固定，更容易进入 Buffer Pool。
• 日常清理可以删除整个分区，避免扫描长期历史。
• 近期计算与历史分析的数据库职责分离。

3.2 为什么原表仍作为短期缓冲

当前 Doris 通过原表 Binlog 获取变更。保留原表作为短期缓冲，可以在不立即改变现有同步入口的情况下先切换业务工作集，并保留回滚能力。

原表不再永久保存历史后，它的职责类似短期同步缓冲：

• 接收异步同步任务写入的数据。
• 产生 INSERT/UPDATE Binlog。
• 等 CDC 位点确认后删除过期行。
• 删除产生的 Binlog 由 CDC 读取后过滤。

后续如果 CDC 支持将 D7 表直接映射到原 Doris 目标表，可以再取消原表缓冲和一次异步写入；这属于第二阶段优化，不作为本次切换前提。

3.3 为什么探索 Doris 过滤 DELETE

MySQL 删除在本方案中表达的是“热数据或同步缓冲到期”，不是“业务事实不存在”。如果 CDC 将这些 DELETE 同步到 Doris，MySQL 的 7 天保留策略会同时删除 Doris 历史数据，历史查询将失去意义。

因此值得探索能否把生命周期清理产生的 DELETE 与业务删除分开处理：

该探索不能直接扩大为“所有表都忽略 DELETE”。事实表的保留周期删除和真正的业务删除语义不同，具体区分方式不在本方案中设计，由 Doris 同步方向探索后另行确定。

3.4 为什么不能只按 7 天处理所有更新

事实表包含 D14、D30、D60、D90、D180、D360 等生命周期字段，今天发生的收入可能更新数周或数月以前 insight_date 的记录。

所以 D7 只定义“近期计算工作集”，不定义“允许更新 Doris 的最大历史范围”。超出 D7 的迟到更新仍要异步写入原表同步缓冲，否则生命周期收入会停止更新。

3.5 预期收益与代价

预期收益：

• ad_insights、ad_material_insights 的 MySQL 保留量从历史累计收敛为约 9H。
• 热表行数、索引规模和每日计算扫描范围固定。
• 原表空间删除后可以循环复用，数据库每日净增长显著下降。
• 历史复杂查询继续由 Doris 承担，不受 MySQL 热窗口影响。

需要承担的代价：

• 迁移期存在 D7 和原表双写，写 IOPS 和 Binlog 量会上升。
• 业务读取 D7 后，Doris 相比近期计算存在异步写入延迟与 CDC 延迟。
• DELETE 过滤方向如果可行，仍需单独确认真正业务删除的数据修正方式。
• 原表历史清理不会自动降低云盘已购买容量，只会释放可复用空间；需要重建表或迁移实例才能物理缩容。

4. 目标

针对 ad_insights、ad_material_insights 等按日期持续增长、近期数据反复更新的大表，将 MySQL 的职责收敛为近期数据计算和短期同步缓冲：

• D7 热表保存最近 7 个自然日的数据，承担业务计算、增量更新和近期数据读取。
• 当前原表不再承担业务查询，只作为 Doris Binlog 同步的兼容缓冲表。
• 探索 Doris Binlog 同步只处理 INSERT、UPDATE 并排除 DELETE 的可行性。
• 如果删除过滤探索确认可行，原表中超过同步安全水位的数据再滚动删除。
• 历史查询、跨 7 天统计和导出统一走 Doris。

本方案第一阶段只覆盖日期事实表，不直接用于当前状态维表或关系维表。

5. 适用范围

5.1 第一阶段纳入

5.2 第一阶段不纳入

6. 总体架构

业务同步任务 ──────> tbl_*_d7 热表
                    近期计算和读取
                           │
                           │ 异步写入
                           v
                  当前原表（短期同步缓冲）
                           │
                    MySQL Binlog: I/U/D
                           │
                           v
               探索：CDC 是否可排除 DELETE
                           │
                           v
                    Doris 历史数据

核心原则：

1. D7 热表是近期业务计算的唯一 MySQL 数据源。
2. 原表是 Doris 同步缓冲，不再被业务逻辑读取。
3. 异步任务将 D7 数据写入原表，具体队列可靠性不在本方案范围内展开。
4. 原表清理必须以 Doris 删除过滤探索可行为前提。

7. D7 热表设计

7.1 数据窗口

• 逻辑窗口：今天及之前 6 天，共 7 个自然日。
• 每天提前创建下一天分区。
• 每天固定时间删除第 8 天及更早的分区。
• 可额外保留 1～2 天物理缓冲，但业务查询仍限定最近 7 天。

7.2 分区

D7 热表按 insight_date 使用 RANGE 日分区。清理使用 DROP PARTITION，不使用大批量逐行 DELETE。

使用分区时应满足 MySQL 约束：所有唯一键均包含分区字段 insight_date。自增主键的最终定义需要在测试库结合 MySQL 版本验证，避免主键不包含分区字段导致建表失败。

7.3 索引

热表不直接复制原表全部索引，只保留业务计算真实需要的索引：

• tbl_ad_insights_d7
  • 唯一键：(insight_date, ad_id, data_type)
  • 账号批量读取：(insight_date, account_id, data_type, ad_id)
• tbl_ad_material_insights_d7
  • 唯一键：(insight_date, ad_id, material_id, data_type)
  • 账号批量读取：(insight_date, account_id, data_type, ad_id)
  • 素材近期读取是否增加索引，由生产慢 SQL 和 EXPLAIN ANALYZE 决定

避免为低频查询继续增加宽索引；历史复杂查询应进入 Doris。

7.4 读路由

不建议在接口中临时 UNION ALL D7 + Doris，否则需要处理同步延迟区间内的重复数据。确需合并时必须定义明确水位：水位之前只读 Doris，水位之后只读 D7。

8. Doris Binlog 删除过滤探索范围

本方案不规定 Doris 同步组件的技术实现、表模型、配置方式或发布步骤，只提出需要探索的方向。

8.1 需要确认的问题

1. 当前 Binlog 同步链路能否针对指定事实表排除 DELETE，同时继续处理 INSERT、UPDATE。
2. 忽略 DELETE 后，同步位点是否仍会正常推进，是否会因为批量清理造成积压或中断。
3. MySQL DROP PARTITION 在当前链路中如何表现，是否会影响 Doris 已有历史数据或同步任务稳定性。
4. MySQL 记录删除后，如果未来以相同业务键重新写入，Doris 是否仍能得到正确的最终结果。
5. 当前链路如何区分生命周期清理与真正的业务删除，忽略 DELETE 会不会留下本应删除的错误数据。
6. 该能力能否只对 ad_insights、ad_material_insights 生效，而不影响其它维表和业务表。

8.2 探索输出

探索阶段只需要形成以下结论：

• 可行：指定事实表可以排除 DELETE，历史数据保留，同步位点和增量更新不受影响。
• 部分可行：普通 DELETE 可以排除，但分区清理、记录重建或业务删除存在限制，需要调整 MySQL 清理方式。
• 不可行：当前同步链路无法安全排除 DELETE，原表不能按本方案滚动清理，需要另行评估同步入口。

在形成明确的可行结论之前，本方案只建设和验证 D7 热表，不执行生产原表历史删除。

9. 原表滚动清理

原表不立即改造成 D7 表，而是作为短期同步缓冲保留。清理条件不能只看日期，还必须看同步安全水位。

一条记录允许删除应同时满足：

1. updated_at 早于缓冲保留时间，例如 48 小时以前。
2. 对应 Binlog 位点已经被 CDC 确认消费。
3. 最近一次 Doris 对账未发现该日期或业务键缺失。

第一阶段采用小批量主键删除，例如每批 1,000～5,000 行并控制间隔，观察锁等待、Undo、Binlog 和 CDC 延迟。原表成为稳定短期缓冲后，再评估建立专用按写入日期分区的 CDC staging 表，替代逐行删除。

需要注意：InnoDB 删除历史行后通常只会释放为表内可复用空间，不一定立即降低云盘已分配容量。目标首先是停止继续增长；是否重建表释放空间应另行安排维护窗口。

10. 迟到更新处理

D7 只保存最近 7 天，但当前事实表包含 D14、D30、D60、D90、D180、D360 等生命周期指标。必须先统计生产环境最近 24 小时实际更新记录的 insight_date 年龄。

SELECT
    DATEDIFF(CURDATE(), insight_date) AS age_days,
    COUNT(*) AS rows_count,
    MAX(updated_at) AS last_updated_at
FROM tbl_ad_insights
WHERE updated_at >= NOW() - INTERVAL 1 DAY
GROUP BY age_days
ORDER BY age_days DESC;

tbl_ad_material_insights 执行同样统计。

对于 insight_date 已超出 D7 的更新：

• 不重新放入 D7 参与近期计算。
• 通过异步任务写入原表同步缓冲，由 Binlog 更新 Doris。
• 同步确认并超过缓冲期后，从原表再次清理。

这样 D7 的体积保持稳定，同时 D14～D360 的迟到更新仍能到达 Doris。

11. 切换步骤

阶段一：准备

1. 统计两张事实表每日行数、实际更新窗口和业务键重复情况。
2. 建立 D7 热表、异步同步任务和监控。
3. 与 Doris 同步维护方确认第 8 节问题，形成可行、部分可行或不可行结论。

阶段二：影子写入

1. 原业务仍读写原表。
2. 影子写入 D7，但业务读取仍保持原表。
3. 连续对账 3～7 天，比较行数和核心指标。
4. 验证 D7 分区创建和过期分区删除。

阶段三：业务切换

1. 开启 D7 写入和读取路由。
2. 开启异步写原表任务。
3. 近期计算和旧值读取切到 D7。
4. 历史接口保持 Doris。
5. 保留快速切回原表的配置开关。

阶段四：原表清理

1. 仅在 Doris 删除过滤探索结论为可行时进入本阶段。
2. 先选择一个低风险历史日期进行小规模删除。
3. 验证历史数据和同步状态符合探索结论。
4. 分批扩大清理范围，原表最终只保留同步缓冲窗口内的数据。

12. 监控与告警

核心对账指标至少包括：

• 业务唯一键行数。
• SUM(spend)、SUM(impressions)、SUM(clicks)。
• 充值和广告回收相关金额字段。
• MAX(updated_at)。
• 按 insight_date + channel + data_type 分组的差异。

13. 回滚策略

出现以下任一情况立即停止原表清理并切回原表读写：

• 异步写原表任务持续积压或失败。
• Doris 删除过滤探索结果与预期不符。
• 原表清理影响历史数据或同步链路。
• D7 缺少近期计算所需的旧值。
• 迟到更新无法正确覆盖 Doris 历史记录。

回滚时保留 D7 数据，关闭 D7 读路由，恢复原表读写。

14. 验收标准

1. D7 热表始终只承载最近 7 天逻辑数据，分区任务连续运行正常。
2. 近期业务计算不再扫描原始历史大表。
3. Doris 删除过滤方向形成明确的可行性结论和限制清单。
4. 仅当探索结论可行时，原表清理才进入生产验证。
5. D14～D360 的迟到更新可以通过异步任务和原表缓冲继续形成同步变更。
6. 原表只保留短期同步缓冲数据，每日净增长趋于稳定。
7. 连续 7 天核心指标对账无不可解释差异。