高效比较两个结构相同的数据库表：探索SQL优化技巧
2025-01-22 08:19:30    1.4k 字    #mysql
This post is also available in English and alternative languages.

比较两个结构相同的表中的数据是一个在特定场景下才会出现的需求，比如下列场景：

  1. 数据迁移验证： 当进行数据库迁移（如从旧系统迁移到新系统）时，需要验证迁移后的数据是否与源数据完全一致。
  2. 数据备份验证： 在执行数据备份后，可能需要比较备份数据与原始数据是否完全相同，以确保备份的完整性和准确性。
  3. 生产环境与测试环境同步： 确保测试环境的数据与生产环境保持一致，特别是在进行大规模测试或调试时。
  4. 分布式系统数据一致性检查： 在分布式系统中，可能需要定期检查不同节点上的数据是否一致。
  5. 数据复制验证： 在主从复制或多数据中心部署中，验证复制的数据是否与源数据完全一致。
  6. 历史数据比对： 比较当前数据与历史快照，以识别随时间变化的记录。
  7. 数据恢复验证： 在进行数据恢复操作后，验证恢复的数据是否与原始数据或备份数据完全一致。

可以使用以下方法对比两个表中相应的行数据。真正使用时，需要根据实际情况（表数据量、索引）进行选择、调整。


1. JOIN

1.1. INNER JOIN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT t1.*
FROM table1 t1
         JOIN table2 t2 ON t1.id = t2.id -- 使用主键索引
WHERE NOT (
    t1.name <=> t2.name AND
    t1.age <=> t2.age AND
    t1.salary <=> t2.salary
    );


SELECT t1.*
FROM table1 t1
         JOIN table2 t2 ON t1.id = t2.id AND t1.name <=> 'Bob Johnson' -- 使用主键索引 & 更早地过滤掉不需要的行
WHERE NOT (
    t1.name <=> t2.name AND
    t1.age <=> t2.age AND
    t1.salary <=> t2.salary
    );
  • JOIN table2 t2 ON t1.id = t2.id：内连接（INNER JOIN），通过 id 字段将 table1 和 table2 连接起来。
  • WHERE NOT (...)：用于筛选出两个表中不完全匹配的行。
  • <=> 操作符是 NULL 安全的等价操作符，它将 NULL 值视为相等。如果所有列都相等，那么 <=> 运算符将返回 TRUE。

1.2. LEFT JOIN

1
2
3
4
5
6
7
SELECT t1.*
FROM table1 t1
         LEFT JOIN table2 t2 ON t1.id = t2.id
    AND t1.name <=> t2.name
    AND t1.age <=> t2.age
    AND t1.salary <=> t2.salary
WHERE t2.id IS NULL;

2. EXISTS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT t1.*
FROM table1 t1
WHERE NOT EXISTS (SELECT *
                  FROM table2 t2
                  WHERE t1.id = t2.id -- 使用主键索引
                    AND t1.name <=> t2.name
                    AND t1.age <=> t2.age
                    AND t1.salary <=> t2.salary);


SELECT t1.*
FROM table1 t1
WHERE t1.name <=> 'Bob Johnson' -- 使用主键索引 & 更早地过滤掉不需要的行
  AND NOT EXISTS (SELECT *
                  FROM table2 t2
                  WHERE t1.id = t2.id
                    AND t1.name <=> t2.name
                    AND t1.age <=> t2.age
                    AND t1.salary <=> t2.salary);
  • WHERE NOT EXISTS (...)：这是查询的核心。会找出在table1中存在，但在table2中找不到完全匹配的记录
  • 子查询：
    • (SELECT * FROM table2 t2 WHERE ...)：这个子查询在 table2 中寻找匹配的记录。
    • 匹配条件：
      • t1.id = t2.id：首先，id必须匹配。这通常是最快的，因为id往往是主键。
      • t1.name <=> t2.name、t1.age <=> t2.age 、 t1.salary <=> t2.salary：同样比较姓名、年龄和薪水。
  • WHERE t1.name <=> 'Bob Johnson'：在 table1 中筛选出 name 为 ‘Bob Johnson’ 的记录，然后检查这些记录在 table2 中是否存在完全匹配的记录。

3. 使用临时表

适用于大数据集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建临时表存储 table1 的哈希值
CREATE TEMPORARY TABLE temp_hash AS
SELECT id, MD5(CONCAT_WS('|', name, age, salary)) AS hash
FROM table1;

-- 创建索引以提高性能
ALTER TABLE temp_hash
    ADD INDEX (id),
    ADD INDEX (hash);

-- 比较 table2 与临时表
SELECT t2.*
FROM table2 t2
         LEFT JOIN temp_hash th ON t2.id = th.id
    AND MD5(CONCAT_WS('|', t2.name, t2.age, t2.salary)) = th.hash
WHERE th.id IS NULL;

-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_hash;
  • 创建一个临时表 temp_hash，存储 table1 的 id 和其他字段的哈希值。
  • CONCAT_WS('|', name, age, salary) 将 name、age 和 salary 字段用 ‘|’ 连接成一个字符串。
  • MD5() 函数计算这个字符串的哈希值，生成一个 32 字符的十六进制字符串。
  • 在临时表上创建了两个索引：一个在 id 列，一个在 hash 列。
  • 使用 LEFT JOIN 来比较 table2 和临时表 temp_hash。
  • 连接条件包括 id 匹配和哈希值匹配。
  • WHERE th.id IS NULL：筛选出在 temp_hash 中找不到匹配的记录，即 table2 中独有的或与 table1 不同的记录。
  • 最后删除临时表，释放资源。

使用注意事项：

  • 确保有足够的临时表空间。
  • 在完成操作后应及时删除临时表释放资源。

4. 建表SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 创建表1
CREATE TABLE table1
(
    id     INT PRIMARY KEY,
    name   VARCHAR(50)    NOT NULL COMMENT '姓名',
    age    INT            NULL COMMENT '年龄',
    salary DECIMAL(10, 2) NOT NULL COMMENT '薪水',
    INDEX idx_name (name),
    INDEX idx_age (age),
    INDEX idx_salary (salary)
);

-- 创建表2
CREATE TABLE table2
(
    id     INT PRIMARY KEY,
    name   VARCHAR(50)    NOT NULL COMMENT '姓名',
    age    INT            NULL COMMENT '年龄',
    salary DECIMAL(10, 2) NOT NULL COMMENT '薪水',
    INDEX idx_name (name),
    INDEX idx_age (age),
    INDEX idx_salary (salary)
);

-- 向表1插入数据
INSERT INTO table1 (id, name, age, salary)
VALUES (1, 'John Doe', 30, 50000.00),
       (2, 'Jane Smith', 28, 55000.00),
       (3, 'Bob Johnson', 35, 60000.00),
       (4, 'Alice Brown', 32, 58000.00),
       (5, 'Charlie Davis', NULL, 52000.00);

-- 向表2插入数据（部分数据与table1不同）
INSERT INTO table2 (id, name, age, salary)
VALUES (1, 'John Doe', 30, 50000.00),
       (2, 'Jane Smith', 29, 55000.00),  -- 年龄不同
       (3, 'Bob Johnson', 35, 61000.00), -- 薪水不同
       (4, 'Alice Brown', 32, 58000.00),
       (5, 'Charlie Davis', 40, 52000.00); -- 年龄不为NULL