深度剖析INSERT IGNORE去重：游戏数据仓库从翻车到稳如老狗的全过程

为民治数的柳哥
25 阅读18分钟

作者的话：这篇文章不是那种"INSERT IGNORE怎么用"的入门教程——那种文章网上一搜一大把。本文要讲的是一个真实项目里，INSERT IGNORE从"以为会去重"到"根本不去重"再到"终于搞明白该怎么用"的完整踩坑链路。如果你正在做数据仓库的增量同步，这篇文章能帮你少走至少一周的弯路。

核心结论先说：INSERT IGNORE本身没有任何去重能力，它只是"遇到唯一键冲突就跳过"。去重的能力100%取决于唯一索引的设计。没有唯一索引的INSERT IGNORE，就是一行普通的INSERT。

一、故事的开端：一个看起来毫无问题的去重方案

某游戏公司运营着一款卡牌手游，DAU约80万，日均产生玩家行为日志1500万条左右。数据团队搭建了标准的三层数仓：

ODS层（原始日志） → DW层（清洗明细） → DM层（业务汇总）

其中DM层有一张核心表 dm_player_daily，记录每个玩家每天的行为汇总：

CREATE TABLE dm_player_daily (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    player_id BIGINT NOT NULL COMMENT '玩家ID',
    stat_date DATE NOT NULL COMMENT '统计日期',
    login_times INT DEFAULT 0 COMMENT '登录次数',
    battle_times INT DEFAULT 0 COMMENT '战斗次数',
    gacha_times INT DEFAULT 0 COMMENT '抽卡次数',
    recharge_amount DECIMAL(12,2) DEFAULT 0 COMMENT '充值金额',
    online_seconds INT DEFAULT 0 COMMENT '在线时长',
    etl_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'ETL时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据团队负责ETL开发的同学叫小陈，他用INSERT IGNORE做增量写入，逻辑很简单：

INSERT IGNORE INTO dm_player_daily
(player_id, stat_date, login_times, battle_times, gacha_times, recharge_amount, online_seconds, etl_time)
SELECT player_id, stat_date, login_times, battle_times, gacha_times, recharge_amount, online_seconds, NOW()
FROM dw_player_daily
WHERE stat_date = '2026-04-20';

新人小陈的思路很清晰：INSERT IGNORE遇到重复就跳过，天然幂等，多跑几次也不怕。

上线第一周，一切正常。小陈还跟同事吹牛："这个去重方案，一行SQL搞定，优雅！"

然而，他不知道的是，一颗定时炸弹已经埋下了。

二、翻车现场：数据重复到令人发指

上线第十天，周五下午，运营部门的数据分析师在工作群里@了小陈：

"dm_player_daily表里同一个玩家同一天有多条记录，你们ETL是不是跑重了？我算出来的DAU比实际高了30%！这数据直接影响了昨天的运营决策会！"

小陈心里咯噔一下，赶紧查数据：

SELECT player_id, stat_date, COUNT(*) AS cnt
FROM dm_player_daily
GROUP BY player_id, stat_date
HAVING cnt > 1
ORDER BY cnt DESC
LIMIT 20;

结果触目惊心：

+-----------+------------+-----+
| player_id | stat_date  | cnt |
+-----------+------------+-----+
|   8801234 | 2026-04-18 |   4 |
|   7700567 | 2026-04-18 |   3 |
|   6600890 | 2026-04-19 |   3 |
|   ...     | ...        | ... |
+-----------+------------+-----+
20 rows in set (0.37 sec)

同一个玩家同一天最多有4条记录！INSERT IGNORE完全没起作用！

小陈当时就懵了——"IGNORE了，但又没完全IGNORE"。

三、排查：INSERT IGNORE为什么不去重？

3.1 第一层排查：INSERT IGNORE的生效前提

小陈翻遍了MySQL官方文档，终于找到关键信息：

INSERT IGNORE只会忽略唯一键（UNIQUE KEY 或 PRIMARY KEY）冲突。如果表上没有唯一约束，INSERT IGNORE等价于普通INSERT。

他回头看建表语句，瞬间石化——

CREATE TABLE dm_player_daily (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 只有自增主键
    player_id BIGINT NOT NULL,
    stat_date DATE NOT NULL,
    ...
    -- 没有 (player_id, stat_date) 的唯一索引！
);

这张表根本没有业务唯一键！ 自增主键id每次INSERT都会生成新值，永远不会冲突，所以INSERT IGNORE永远等于普通INSERT。

3.2 为什么第一周没发现？

因为第一周ETL任务只跑了一次，没有重复执行。后来运维同事配置了Crontab，但因为时间配置错误，同一个任务在5分钟内被触发了两次，重复数据就此产生。

3.3 问题的本质

INSERT IGNORE的去重逻辑：

  INSERT IGNORE → 检查唯一键是否冲突 → 冲突则跳过，不冲突则插入
                                    ↑
                          这一步依赖表上的唯一索引！
                          没有唯一索引 = 永远不冲突 = 永远插入

这不是MySQL的bug，而是INSERT IGNORE的设计机制：它只负责"忽略冲突"，不负责"判断什么是重复"。判断重复的责任在唯一索引上。

四、修复：加唯一索引就完事了？

4.1 加唯一索引

小陈立刻加上了唯一索引：

ALTER TABLE dm_player_daily
ADD UNIQUE KEY uk_player_date (player_id, stat_date);

然后继续用INSERT IGNORE，测试通过——重复数据确实被跳过了。

小陈以为问题解决了，直到第三天……

4.2 新问题：数据不更新了

运营又来找麻烦了：

"玩家8801234在4月22日的充值金额明明是648元，为什么你们表里还是0？"

小陈查了一下DW层的数据：

SELECT player_id, stat_date, recharge_amount
FROM dw_player_daily
WHERE player_id = 8801234 AND stat_date = '2026-04-22';
-- 结果：recharge_amount = 648.00

再查DM层：

SELECT player_id, stat_date, recharge_amount
FROM dm_player_daily
WHERE player_id = 8801234 AND stat_date = '2026-04-22';
-- 结果：recharge_amount = 0.00

DW层有数据，DM层却是0！

原因很简单：ETL任务第一次跑的时候，DW层还没算出充值金额（充值数据延迟到达），所以DM层写入的是0。后来DW层更新了，但INSERT IGNORE发现唯一键已存在，直接跳过了——0永远不会被更新成648。

时间线：
  10:00  ETL第一次跑 → DW层充值=0 → INSERT IGNORE写入DM层 → 成功（recharge_amount=0）
  12:00  充值数据到达 → DW层更新 → recharge_amount=648
  14:00  ETL第二次跑 → DW层充值=648 → INSERT IGNORE → 唯一键冲突 → 跳过！
         DM层仍然是0！

五、三种去重方案深度对比

到这一步，小陈才意识到问题远比想象中复杂。他系统梳理了MySQL的三种去重方案：

5.1 INSERT IGNORE

INSERT IGNORE INTO dm_player_daily (player_id, stat_date, ...)
SELECT ... FROM dw_player_daily WHERE stat_date = '2026-04-22';
维度说明
重复时行为静默跳过，不报错
已有数据完全不动
适用场景只增不改，数据一旦写入就不应该被覆盖
风险无法更新已有记录，即使源数据已修正

5.2 REPLACE INTO

REPLACE INTO dm_player_daily (player_id, stat_date, ...)
SELECT ... FROM dw_player_daily WHERE stat_date = '2026-04-22';
维度说明
重复时行为先DELETE旧记录，再INSERT新记录
已有数据完全替换
适用场景全量覆盖，不关心旧数据
风险自增ID会变！ 如果其他表通过id关联，关联关系断裂

小陈验证了ID变化的问题：

-- 第一次插入
REPLACE INTO dm_player_daily (id, player_id, stat_date) VALUES (100, 8801234, '2026-04-22');
-- id = 100

-- 第二次替换
REPLACE INTO dm_player_daily (player_id, stat_date) VALUES (8801234, '2026-04-22');
-- 旧记录id=100被删除，新记录id=101（或更大）
-- 关联此id的其他表数据全部失效！

5.3 INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO dm_player_daily
(player_id, stat_date, login_times, battle_times, gacha_times, recharge_amount, online_seconds, etl_time)
SELECT player_id, stat_date, login_times, battle_times, gacha_times, recharge_amount, online_seconds, NOW()
FROM dw_player_daily
WHERE stat_date = '2026-04-22'
ON DUPLICATE KEY UPDATE
    login_times = VALUES(login_times),
    battle_times = VALUES(battle_times),
    gacha_times = VALUES(gacha_times),
    recharge_amount = VALUES(recharge_amount),
    online_seconds = VALUES(online_seconds),
    etl_time = VALUES(etl_time);
维度说明
重复时行为更新指定字段
已有数据部分更新（只更新指定的字段）
适用场景需要更新已有记录，同时保留不想覆盖的字段
风险SQL较长，需要明确列出每个要更新的字段

六、最终方案：分层策略

小陈仔细分析了DM层的数据写入场景，发现不能一刀切——不同场景需要不同的去重策略。

6.1 场景分析

场景1：ODS层写入（源库 → 临时库 → ODS层）
  特点：ODS层是原始数据的镜像，写入后不应被修改
  策略：INSERT IGNORE ✅（只增不改）

场景2：DM层基础指标写入（DW层 → DM层）
  特点：DW层可能因数据延迟而修正，DM层需要同步更新
  策略：ON DUPLICATE KEY UPDATE ✅（可增可改）

场景3：DM层AI填充字段（DM层 → Dify API → DM层更新）
  特点：product_cate等字段由AI填充，ODS/DW层没有这些字段
  策略：INSERT IGNORE ✅（不能覆盖AI已填充的字段）

6.2 关键洞察：ON DUPLICATE KEY UPDATE的"覆盖陷阱"

场景3是最容易踩坑的。假设DM层有一个字段vip_label是由AI模型填充的：

-- ❌ 错误做法：ON DUPLICATE KEY UPDATE会覆盖AI填充的字段
INSERT INTO dm_player_daily
(player_id, stat_date, login_times, recharge_amount, vip_label)
SELECT player_id, stat_date, login_times, recharge_amount, NULL  -- ODS层没有vip_label
FROM dw_player_daily
ON DUPLICATE KEY UPDATE
    login_times = VALUES(login_times),
    recharge_amount = VALUES(recharge_amount),
    vip_label = VALUES(vip_label);  -- NULL覆盖了AI填充的值！

解决方案：ON DUPLICATE KEY UPDATE只更新从DW层来的字段，不更新AI填充的字段：

-- ✅ 正确做法：只更新DW层有的字段
INSERT INTO dm_player_daily
(player_id, stat_date, login_times, recharge_amount, vip_label)
SELECT player_id, stat_date, login_times, recharge_amount, NULL
FROM dw_player_daily
ON DUPLICATE KEY UPDATE
    login_times = VALUES(login_times),
    recharge_amount = VALUES(recharge_amount);
    -- 注意：vip_label不在UPDATE子句中，不会被覆盖

6.3 最终的分层策略

def sync_ods_layer(conn, data, env):
    """ODS层：INSERT IGNORE，只增不改"""
    sql = f"""
    INSERT IGNORE INTO ods_player_log_{env}
    (log_id, player_id, action, action_time, server_id)
    VALUES (%s, %s, %s, %s, %s)
    """
    execute_many(conn, sql, data)


def sync_dm_basic_metrics(conn, dt):
    """DM层基础指标：ON DUPLICATE KEY UPDATE，可增可改"""
    sql = f"""
    INSERT INTO dm_player_daily
    (player_id, stat_date, login_times, battle_times, gacha_times,
     recharge_amount, online_seconds, etl_time)
    SELECT player_id, stat_date, login_times, battle_times, gacha_times,
           recharge_amount, online_seconds, NOW()
    FROM dw_player_daily
    WHERE stat_date = %s
    ON DUPLICATE KEY UPDATE
        login_times = VALUES(login_times),
        battle_times = VALUES(battle_times),
        gacha_times = VALUES(gacha_times),
        recharge_amount = VALUES(recharge_amount),
        online_seconds = VALUES(online_seconds),
        etl_time = VALUES(etl_time)
    """
    execute_sql(conn, sql, (dt,))


def sync_dm_ai_fields(conn, player_id, stat_date, vip_label):
    """DM层AI字段：INSERT IGNORE，不覆盖已有值"""
    sql = """
    INSERT IGNORE INTO dm_player_daily
    (player_id, stat_date, vip_label)
    VALUES (%s, %s, %s)
    """
    execute_sql(conn, sql, (player_id, stat_date, vip_label))

七、前缀索引：长字段唯一键的救星

在实施过程中，小陈还遇到了一个棘手的问题。

DM层还有一张表 dm_player_task，记录玩家任务完成情况，业务唯一键是 (player_id, stat_date, task_name)：

CREATE TABLE dm_player_task (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    player_id BIGINT NOT NULL,
    stat_date DATE NOT NULL,
    task_name VARCHAR(200) NOT NULL COMMENT '任务名称',
    task_status TINYINT DEFAULT 0 COMMENT '0未完成 1已完成',
    complete_time DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

直接建唯一索引：

ALTER TABLE dm_player_task
ADD UNIQUE KEY uk_player_date_task (player_id, stat_date, task_name);

MySQL直接报错：

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

小陈一开始也觉得奇怪：task_name VARCHAR(200) 在utf8mb4编码下占 200×4=800 字节，加上 player_id(8字节) 和 stat_date(3字节)，总长度811字节，明明没超3072啊？

后来他查了MySQL文档才搞明白——InnoDB的索引长度限制不是按单个索引算的，而是受 innodb_page_size 和行格式共同约束。当使用 COMPACT 或 REDUNDANT 行格式时，索引前缀最大长度为767字节；只有使用 DYNAMIC 或 COMPRESSED 行格式且开启 innodb_large_prefix 时，才能达到3072字节。而他们生产环境的MySQL版本较老，默认行格式是 COMPACT，所以800字节的 task_name 加上其他字段直接就超了767字节的限制。

更麻烦的是，游戏里的任务名称经常很长，比如"2026春节限定·瑞兽降临·七日签到·第四日奖励"，200个字符都不一定够用，未来还可能加长。

解决方案：前缀索引

ALTER TABLE dm_player_task
ADD UNIQUE KEY uk_player_date_task (player_id, stat_date, task_name(50));

只取 task_name 的前50个字符参与唯一索引。在游戏场景中，任务名称的前50个字符足以区分不同任务（因为不同任务的前缀命名规则不同）。

但前缀索引有个前提：前50个字符必须能唯一区分记录。如果不能保证，就需要引入额外的去重字段，比如 task_id：

ALTER TABLE dm_player_task
ADD COLUMN task_id VARCHAR(64) NOT NULL COMMENT '任务唯一标识' AFTER stat_date,
ADD UNIQUE KEY uk_player_date_taskid (player_id, stat_date, task_id);

用确定性的短字段替代不确定的长字段，这才是更稳健的方案。

八、存量数据修复：重复数据怎么清理？

加完唯一索引后，存量数据里的重复记录会导致ALTER TABLE失败。必须先清理重复数据。

8.1 查看重复情况

SELECT player_id, stat_date, COUNT(*) AS cnt
FROM dm_player_daily
GROUP BY player_id, stat_date
HAVING cnt > 1
ORDER BY cnt DESC;

8.2 保留最新记录，删除旧记录

DELETE t1 FROM dm_player_daily t1
INNER JOIN dm_player_daily t2
ON t1.player_id = t2.player_id
AND t1.stat_date = t2.stat_date
AND t1.id < t2.id;

逻辑：对于同一组 (player_id, stat_date)，保留id最大的（即最新插入的），删除id较小的。

8.3 大表删除注意事项

dm_player_daily 表有170万行数据，直接DELETE可能锁表。小陈用了分批删除：

def clean_duplicates(conn, batch_size=5000):
    """分批清理重复数据"""
    while True:
        with conn.cursor() as cursor:
            sql = """
            DELETE t1 FROM dm_player_daily t1
            INNER JOIN dm_player_daily t2
            ON t1.player_id = t2.player_id
            AND t1.stat_date = t2.stat_date
            AND t1.id < t2.id
            LIMIT %s
            """
            cursor.execute(sql, (batch_size,))
            conn.commit()
            if cursor.rowcount == 0:
                break
            logger.info(f"清理重复数据：删除 {cursor.rowcount} 条")

8.4 清理完成后再加唯一索引

ALTER TABLE dm_player_daily
ADD UNIQUE KEY uk_player_date (player_id, stat_date);

这次成功了。

九、完整的建表规范

经过这次踩坑，小陈总结了一套DM层建表规范：

-- ✅ 正确的DM层建表模板
CREATE TABLE dm_xxx (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- 业务字段
    player_id BIGINT NOT NULL COMMENT '玩家ID',
    stat_date DATE NOT NULL COMMENT '统计日期',

    -- 指标字段
    metric_1 INT DEFAULT 0,
    metric_2 DECIMAL(12,2) DEFAULT 0,

    -- AI填充字段（标注来源）
    ai_label VARCHAR(100) DEFAULT NULL COMMENT 'AI分类标签-由Dify填充',

    -- ETL元数据
    etl_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'ETL时间',

    -- 关键：业务唯一键
    UNIQUE KEY uk_player_date (player_id, stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心原则：

  1. 必须有业务唯一键：没有唯一键的表，INSERT IGNORE形同虚设
  2. 唯一键用业务字段组合：不要用自增id，自增id无法判断业务重复
  3. 长字段用前缀索引或引入短标识字段：避免索引长度超限
  4. AI填充字段要标注来源：防止被ON DUPLICATE KEY UPDATE误覆盖

十、方案对比总结

方案去重更新ID不变适用场景推荐度
INSERT IGNORE（无唯一索引）❌❌✅无⛔ 别用
INSERT IGNORE（有唯一索引）✅❌✅ODS层、AI字段写入⭐⭐⭐⭐
REPLACE INTO✅✅❌全量覆盖、无外键关联⭐⭐
ON DUPLICATE KEY UPDATE✅✅✅DM层基础指标更新⭐⭐⭐⭐⭐

一句话选型：

  • 数据写入后不改 → INSERT IGNORE + 唯一索引
  • 数据需要更新 → ON DUPLICATE KEY UPDATE（注意不要覆盖AI字段）
  • 全量覆盖不关心ID → REPLACE INTO

十一、写在最后

这次踩坑让小陈（也让整个数据团队）深刻认识到一个道理：

INSERT IGNORE不是"智能去重"，它只是"遇到唯一键冲突就跳过"。去重的能力取决于唯一索引的设计，而不是INSERT IGNORE本身。

换句话说，INSERT IGNORE和唯一索引是一对搭档，缺一不可。没有唯一索引的INSERT IGNORE，就像没有雷达的导弹——看着吓人，实际上打不中任何目标。

最后整理一下完整的避坑清单，建议截图保存：

  1. ✅ 建表时必须设计业务唯一键——这是INSERT IGNORE生效的前提
  2. ✅ ODS层用INSERT IGNORE——原始数据只增不改
  3. ✅ DM层基础指标用ON DUPLICATE KEY UPDATE——支持数据修正
  4. ✅ DM层AI字段用INSERT IGNORE——防止覆盖已填充的值
  5. ✅ 长字段唯一键用前缀索引或引入短标识——避免索引超限
  6. ✅ 加唯一索引前先清理存量重复数据——ALTER TABLE会因重复数据失败
  7. ✅ 大表清理重复数据要分批——避免长时间锁表

灵魂拷问：回头看看你手上的项目，那些用了INSERT IGNORE的表，真的都有唯一索引吗？

如果没有，赶紧加上。别等运营找上门才发现——那时候数据已经乱了，修复成本是加索引的十倍。

如果你也踩过INSERT IGNORE的坑，评论区聊聊你的经历！你用的是哪种去重方案？有没有遇到过更离谱的翻车？点赞收藏防走丢，下次遇到去重问题翻出来看看~

世局板块

1.国际财经：美伊达成关键谅解，油价大跳水超6%。

在卡塔尔斡旋下，美伊就伊朗被冻结金融资产问题达成关键谅解，双方有望近期正式宣布协议。受和谈预期大幅升温影响，国际金融市场剧烈波动。截至北京时间5月26日6:40，WTI原油跌破91美元/桶，日内跌超6.4%；布伦特原油昨夜收跌6.6%。与此同时，避险与通胀逻辑发生转换，贵金属市场迎来反弹，周一纽约尾盘现货黄金涨1.35%，现货白银涨3.35%，周二早盘初延续了上涨态势。全球股市也受此利好情绪带动，呈现集体走强趋势。

2.国家主权：中国海警依法驱离非法闯入钓鱼岛领海日方船只。

5月26日，日本“狮子”号渔船非法进入中国钓鱼岛领海。中国海警舰艇迅速响应，依法对其采取必要管控措施，并予以警告驱离。中国海警局新闻发言人姜略明确表示，钓鱼岛及其附属岛屿是中国固有领土，中方敦促日方立即停止在该海域的一切侵权挑衅行径。中国海警将持续在钓鱼岛海域开展常态化维权执法行动，坚决维护国家主权和海洋权益。此次执法行动再次彰显了中国海警捍卫领土主权的坚定决心与高效专业的处置能力。

3.科技突破：华为发表“韬定律”，麒麟2026芯片性能大跨越。

华为正式发表半导体新定律“韬（τ）定律”，提出以“时间缩微”替代“几何缩微”。基于该定律，将于今年秋季面世的麒麟2026芯片首发逻辑折叠技术，晶体管密度达238MTr/mm²，大幅提升53.5%，P核能效提升41%，频率提升12.7%。华为规划到2031年，高端芯片将实现等效1.4纳米工艺密度。过去6年，华为已基于该定律量产381款芯片，技术差距显著缩小，标志着中国半导体在自主创新路径上迈出关键一步。

4.产业动态：麒麟2026实为麒麟9050，将首发搭载于Mate 90系列。

据最新数码爆料，备受关注的麒麟2026工程芯片正式命名为麒麟9050系列。该系列芯片预计将延续双芯布局策略，包含标准版麒麟9050与高阶版麒麟9050 Pro。今年9月，华为Mate 90系列将全球首发搭载该芯片，并出厂预装全新一代鸿蒙7系统。凭借底层架构的革新与软硬协同的深度优化，麒麟9050系列将带来算力与能效的全面飞跃，预计将在下半年高端旗舰手机市场引发强烈反响。

5.前沿技术：国产毫米级芯片级主动散热方案进入实测阶段。

国产芯片级主动散热技术取得重要进展，目前国产MEMS主动散热风扇已进入工程实测阶段。该方案厚度成功压缩至毫米级，可紧贴处理器实现芯片级精准主动散热。相较于传统内置风扇方案，新技术几乎不产生运行噪音，且热传导效率实现了跨越式提升。这并非停留在理论层面的构想，而是基于先进国产制程工艺的实践性技术验证。从微观结构到散热机制的全面重新设计，将为高性能芯片的稳定运行提供强有力的散热保障。


avatar
文章
阅读
粉丝