mysql - 使用校验和比较多列上的两个表？

Question

我想比较两个具有相同列的巨大表，以查找具有不同值的记录。 (两个表中的许多行是相同的)

需要大量的时间，性能很差，查询现在是这样的:

SELECT  *
    FROM  table1 a
    INNER JOIN  table2 b
       ON a.value!=b.value
      OR  a.value!=b.value
      OR  a.value!=b.value
      OR  a.value!=b.value
      OR  a.value!=b.value
      OR  a.value!=b.value
      OR  a.value!=b.value OR...
    WHERE  a.id=1
      AND  b.id=2
      AND  a.specialvalue=b.specialvalue;

我的想法是为我需要比较的所有值创建一个校验和，并且只比较行的校验和。我的问题是:这是一个好方法吗？会有性能提升吗？是否有可能在合理的时间内比较两行，每行都有大约 10 万条记录？

Answer 1

如果您需要将 100K 行中的每一行与 100K 行中的其他行进行比较，则大约需要进行 50亿 次比较。这将花费比你喜欢的更长的时间。但是你可以这样做:

CREATE Cksums (
    md5 CHAR(32) CHARACTER SET ascii,
    id ...,   -- PK of the row
    INDEX(md5)
);

INSERT INTO Cksums (md5, id)
    SELECT
           MD5(CONCAT_WS("\t", col2, col3, ...),
           id
        FROM your_table;

然后查找重复的行:

SELECT md5,
       GROUP_CONCAT(id) AS dup_ids
    FROM Cksums
    GROUP BY md5
    HAVING COUNT(*) > 1;

DROP TABLE Cksums;  -- cleanup

这将通过两次 100K 表扫描(一次用于构建 Cksums，一次用于检查它)有效地进行 500 亿次比较。

注意事项:

• 我不知道这是否能很好地处理 NULL。
• 我假设任何列中都没有使用“制表符”。
• BLOB 应通过 HEX(the_blob) 提取，以避免 CONCAT_WS 中的各种问题。
• 如果太多行相同(数百)，GROUP_CONCAT 将截断列表。
• 我声称(有些人不同意)128 位 MD5 就足够了。如果您有 9 万亿个 md5，则两个错误相同的可能性只有 9 万亿分之一。