带CTE的INSERT语句性能优化求助：海量表数据插入内部表

阿华AIGC实验室

2026-5-7

嘿，这个大数据量下的插入性能问题我太熟了！你的核心痛点其实是NOT IN子查询在大表上的性能灾难——尤其是当A表数据量极大时，每次判断B的id是否存在都会触发重复的全表扫描，哪怕用了CTE也没解决本质问题。下面给你几个实战验证过的优化思路：

1. 用带索引的临时表预处理A的ID

这是我处理这类问题最常用的方案，本质是把A的ID缓存到本地带索引的临时表，让后续的存在性判断变成高效的索引查找：

-- 第一步：创建带唯一索引的临时表存储A的ID（引擎选对应数据库支持的类型）
CREATE TEMPORARY TABLE temp_a_ids (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO temp_a_ids SELECT id FROM A;

-- 第二步：全量插入A到C
INSERT INTO C SELECT * FROM A;

-- 第三步：插入B中不在A的部分，用NOT EXISTS关联临时表
INSERT INTO C 
SELECT * FROM B 
WHERE NOT EXISTS (SELECT 1 FROM temp_a_ids WHERE id = B.id);

临时表的索引会把原来的全表扫描变成O(logN)的索引查找，而且临时表数据优先存在内存（如果内存足够），能极大降低跨链路查询的IO开销。

2. 用LEFT JOIN + IS NULL替代NOT IN/NOT EXISTS

很多数据库的优化器对JOIN的优化比子查询更友好，尤其是哈希连接或合并连接场景：

INSERT INTO C SELECT * FROM A;

INSERT INTO C 
SELECT B.* 
FROM B 
LEFT JOIN A ON B.id = A.id 
WHERE A.id IS NULL;

注意：如果A表的id存在NULL值，NOT IN会直接返回空结果（这是SQL的隐性坑），而LEFT JOIN不会有这个问题，同时优化器能更高效地处理JOIN逻辑。

3. 合并两次插入为单次操作（减少事务开销）

如果业务允许，把两次INSERT合并成一次，能减少事务提交、日志刷盘的开销：

INSERT INTO C
-- 先取A的全量数据
SELECT * FROM A
UNION ALL
-- 再取B中不在A的部分
SELECT B.* 
FROM B 
WHERE NOT EXISTS (SELECT 1 FROM A WHERE id = B.id);

或者用UNION模拟全外连接（适合不支持FULL OUTER JOIN的数据库如MySQL）：

INSERT INTO C
SELECT * FROM A
UNION ALL
SELECT B.* FROM B 
LEFT JOIN A ON B.id = A.id 
WHERE A.id IS NULL;

单次插入能避免两次事务的锁竞争和日志写入，大数据量下性能差异很明显。

4. 检查并优化索引（最基础但最容易忽略）

确保A表的id字段有主键/唯一索引，B表的id字段也有普通索引！如果A是外部表没法加索引，那回到方案1用临时表存ID加索引，这是必做的优化。没有索引的话，任何存在性判断都会变成全表扫描，大数据量下完全不可行。

5. 分批插入（避免大事务锁表）

如果数据量实在大到单次插入会锁表、日志暴涨，就分批处理：

-- 分批插入A到C
DECLARE @start_id INT = 0;
DECLARE @batch_size INT = 10000; -- 批次大小根据数据库性能调整

WHILE EXISTS (SELECT 1 FROM A WHERE id > @start_id)
BEGIN
    INSERT INTO C SELECT * FROM A WHERE id > @start_id LIMIT @batch_size;
    SET @start_id = @start_id + @batch_size;
    COMMIT; -- 每批提交一次，释放锁并刷写日志
END

-- 分批插入B中不在A的部分（复用之前的临时表temp_a_ids）
DECLARE @b_start_id INT = 0;
WHILE EXISTS (SELECT 1 FROM B WHERE id > @b_start_id)
BEGIN
    INSERT INTO C 
    SELECT B.* 
    FROM B 
    LEFT JOIN temp_a_ids ON B.id = temp_a_ids.id
    WHERE B.id > @b_start_id AND temp_a_ids.id IS NULL
    LIMIT @batch_size;
    SET @b_start_id = @b_start_id + @batch_size;
    COMMIT;
END

分批能避免大事务长时间占用表锁，减少数据库的负载压力，也能降低日志文件暴涨的风险。