如何通过SQL Join高效找出两个系统间ID不一致的僵尸数据

<p>用 LEFT JOIN 找出 A 表有而 B 表没有的记录，核心语句为：SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL；其原理是左连接保留 A 表全部记录，B 表无匹配时字段为 NULL，再通过 WHERE b.id IS NULL 筛出差异行。</p>

用 LEFT JOIN 找出 A 系统有、B 系统没有的 ID

核心思路是把主系统（比如订单库）当左表，把待比对系统（比如用户画像库）当右表，用 LEFT JOIN + WHERE ... IS NULL 精准定位“只存在于左表”的记录。这比 NOT IN 更可靠，尤其当右表的 ID 字段含 NULL 时，NOT IN 会直接返回空结果——这是最常见的翻车点。

实操建议：

• 确保两表用于关联的字段类型一致（比如都是 BIGINT，别一个是 VARCHAR 存数字）；类型不匹配会导致隐式转换，索引失效，查询慢到超时
• 在右表的关联字段上建索引，例如：CREATE INDEX idx_user_id ON user_profile(user_id);
• 示例语句：

  SELECT a.order_id FROM orders a LEFT JOIN user_profile b ON a.user_id = b.user_id WHERE b.user_id IS NULL;

用 RIGHT JOIN 或反向 LEFT JOIN 补全 B 系统独有 ID

单靠 LEFT JOIN 只能查出“A 有 B 没有”，但僵尸数据可能也藏在“B 有 A 没有”的方向里（比如同步脚本漏删、测试环境脏数据残留）。这时候要么换 RIGHT JOIN，要么保持 LEFT JOIN 但调换左右表顺序——后者更易读，也避免部分数据库对 RIGHT JOIN 优化不佳的问题。

注意点：

• 别用 FULL OUTER JOIN 做一次性比对：MySQL 不支持，PostgreSQL 虽支持但执行计划复杂，容易 OOM；拆成两次 LEFT JOIN 更稳
• 如果两表 ID 字段名不同（如 orders.uid vs profile.id），ON 条件必须显式写清，不能依赖别名混淆
• 加 LIMIT 100 先试跑，确认逻辑正确再删掉——万一对错列了，扫全表代价太大

用 UNION ALL 合并双向差异，避免重复扫描

要一次性拿到全部不一致 ID（A 独有 + B 独有），最高效的方式是用 UNION ALL 拼两个 LEFT JOIN 结果，而不是用子查询套 NOT EXISTS——后者会让优化器难以复用执行计划，尤其数据量过百万时，性能差距可达数倍。

典型写法：

SELECT 'orders_only' AS source, order_id AS id FROM orders o LEFT JOIN user_profile p ON o.user_id = p.user_id WHERE p.user_id IS NULLUNION ALLSELECT 'profile_only' AS source, user_id AS id FROM user_profile p LEFT JOIN orders o ON p.user_id = o.user_id WHERE o.user_id IS NULL;

关键细节：

• UNION ALL 比 UNION 快，因为我们不需要去重——两边结果天然互斥
• 务必给每列起明确别名（如 source, id），否则某些 BI 工具或导出工具会报列名冲突
• 如果 ID 字段在两边长度/精度不同（如一边是 CHAR(32) UUID，一边是 BIGINT），先用 CAST 或 CONVERT 统一类型再 JOIN，否则匹配永远为假

警惕字符集和大小写导致的“假不一致”

看起来 ID 相同，JOIN 却匹配不上？大概率是字符集或排序规则惹的祸。比如 MySQL 中 utf8mb4_general_ci 默认忽略大小写和末尾空格，而 utf8mb4_bin 则严格区分。若 A 系统用前者存 "ABC "，B 系统用后者存 "ABC"，JOIN 就会失败，误判为僵尸数据。

排查步骤：

• 查字段排序规则：SHOW FULL COLUMNS FROM orders LIKE 'user_id'; 和 SHOW FULL COLUMNS FROM user_profile LIKE 'user_id';
• 临时强制一致比较：ON TRIM(o.user_id) = TRIM(p.user_id)（仅调试用，上线前必须修复源头字符集）
• 生产环境严禁用函数包裹 JOIN 条件（如 LOWER()），否则索引完全失效

真正要动的是建表 DDL，不是 SQL 写法。不统一底层 collation，这类问题会反复出现，且越来越难定位。