从两张大数据量MySQL表中获取不匹配数据

这问题我太有共鸣了！百万级别的表用NOT EXISTS确实容易因为执行计划或者内存瓶颈直接中断，哪怕加了索引也顶不住——毕竟MySQL处理这种子查询的时候，有时候会把整个B表的数据加载到内存里做匹配，数据量一大就崩了。给你几个经过实战验证的方案，按优先级推荐：

方案1：用LEFT JOIN + IS NULL替代NOT EXISTS #

很多时候MySQL优化器对JOIN的处理比NOT EXISTS更高效，尤其是当其中一张表（这里是B表，200万条）更小的时候：

SELECT A.mobile_no
FROM A
LEFT JOIN B ON A.mobile_no = B.mobile_no
WHERE B.mobile_no IS NULL;

原理很简单：LEFT JOIN会保留A表的所有记录，当A的mobile_no在B中找不到匹配时，B对应的字段会被设为NULL，我们只需要过滤这些NULL记录即可。这个写法的执行计划通常会用到你已经创建的mobile_no索引，避免全表扫描。

方案2：用EXCEPT（MySQL 8.0+适用） #

如果你的MySQL版本是8.0及以上，直接用EXCEPT语法会更简洁，优化器也会自动做高效的差集运算：

SELECT mobile_no FROM A
EXCEPT
SELECT mobile_no FROM B;

EXCEPT会自动返回A表存在但B表不存在的唯一记录（自动去重），如果需要保留A表中的重复记录，可以用EXCEPT ALL。

方案3：分批处理避免内存溢出 #

如果上面两种方案还是因为数据量太大中断，可以试试分批查询，把大任务拆成多个小任务：

方式一：按mobile_no范围拆分 #

因为mobile_no是BIGINT类型，可以按数值范围切分查询：

-- 示例：查询mobile_no在10000000000到10000009999之间的不匹配记录
SELECT A.mobile_no
FROM A
LEFT JOIN B ON A.mobile_no = B.mobile_no
WHERE B.mobile_no IS NULL
AND A.mobile_no BETWEEN 10000000000 AND 10000009999;

你可以写个简单的脚本（比如Python、Shell）遍历所有可能的数值范围，把结果汇总到临时表或者文件里。

方式二：用LIMIT分批 #

如果mobile_no没有连续范围，也可以用LIMIT和偏移量分批：

-- 每次取10万条，偏移量逐步增加
SELECT A.mobile_no
FROM A
LEFT JOIN B ON A.mobile_no = B.mobile_no
WHERE B.mobile_no IS NULL
LIMIT 0, 100000;

注意：偏移量太大的时候会影响性能，尽量优先用范围查询。

方案4：临时表预处理B表数据 #

如果B表存在重复的mobile_no，可以先把B的mobile_no去重后放到临时表，再做关联，减少关联次数：

-- 创建临时表并添加主键索引
CREATE TEMPORARY TABLE temp_b_mobile (mobile_no BIGINT(20) PRIMARY KEY);
-- 导入去重后的B表mobile_no
INSERT INTO temp_b_mobile SELECT DISTINCT mobile_no FROM B;

-- 用临时表做关联查询
SELECT A.mobile_no
FROM A
LEFT JOIN temp_b_mobile ON A.mobile_no = temp_b_mobile.mobile_no
WHERE temp_b_mobile.mobile_no IS NULL;

临时表的索引更轻量，关联效率会比直接用B表高不少。

额外优化建议 #

• 确认你的mobile_no索引是单列索引，不是复合索引的一部分，这样MySQL才能在关联时直接命中索引。
• 适当调整MySQL配置：比如调大join_buffer_size和sort_buffer_size（不要过度，避免占用过多内存），让MySQL处理JOIN时有足够的内存空间。
• 如果不需要在客户端查看结果，直接导出到文件，避免客户端内存不足：

SELECT A.mobile_no
FROM A
LEFT JOIN B ON A.mobile_no = B.mobile_no
WHERE B.mobile_no IS NULL
INTO OUTFILE '/path/to/your/output.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

记得提前确保MySQL有该路径的写入权限。

内容的提问来源于stack exchange，提问作者harish_sng