1500万条数据跨表插入SQL语句优化求助

哇，1500万条数据的跨表插入确实容易碰到性能瓶颈，我来分享几个经过实战验证的优化方案，针对你的场景应该能大幅提升速度：

一次性插入百万级数据会占满事务日志、长时间锁表，而且容易触发超时。改成小批次插入，每次处理1-5万条（具体大小看你的数据库性能调整），既能减轻日志压力，也能降低锁竞争。

示例代码：

DECLARE @BatchSize INT = 10000; -- 每次插入1万条
DECLARE @RowCount INT = 1;

WHILE @RowCount > 0
BEGIN
    INSERT INTO collection (Id, CreateTime, UpdateTime, Col1, Col2, Code, pool, Status)
    SELECT TOP (@BatchSize) 
           Id, GETDATE(), GETDATE(), 
           '00000000-0000-0000-0000-000000000000', 
           '00000000-0000-0000-0000-000000000000', 
           Code, pool, 1
    FROM collectiontemp
    WHERE pool = '0929B522-AF2A-4B36-xxxx-xxxxxxxxxxxx'
    AND Id NOT IN (SELECT Id FROM collection); -- 可选，防止重复插入

    SET @RowCount = @@ROWCOUNT;
    WAITFOR DELAY '00:00:01'; -- 可选，给数据库留一点缓冲时间
END

如果你的数据库用的是完整恢复模式，每一条插入都会被完整记录到日志，这对百万级操作来说开销极大。临时切换到大容量日志模式可以让批量操作只记录最小化的日志，能大幅减少IO开销：

-- 先切换恢复模式
ALTER DATABASE YourDatabaseName SET RECOVERY BULK_LOGGED;

-- 执行你的插入操作（不管是分批还是一次性）

-- 操作完成后切回完整模式，记得做一次完整备份
ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
BACKUP DATABASE YourDatabaseName TO DISK = 'D:\Backups\AfterBulkInsert.bak';

插入数据时，数据库需要维护目标表的所有索引和检查约束，这会消耗大量时间。可以先禁用非聚集索引和非主键约束，插入完成后再重建：

-- 禁用非聚集索引
ALTER INDEX ALL ON collection DISABLE;
-- 禁用外键/检查约束（如果有的话）
ALTER TABLE collection NOCHECK CONSTRAINT ALL;

-- 执行插入操作（分批或一次性）

-- 重建索引（比重新插入时维护索引快得多）
ALTER INDEX ALL ON collection REBUILD;
-- 启用约束
ALTER TABLE collection CHECK CONSTRAINT ALL;

⚠️ 注意：不要禁用聚集索引（否则表会变成堆，重建开销更大），而且操作期间要确保没有其他业务修改目标表，避免数据不一致。

如果目标表是新建的，或者你可以暂时替换原表，SELECT INTO是最快的批量插入方式——它直接从源表创建新表并插入数据，在大容量日志模式下几乎不产生日志：

SELECT 
       Id, GETDATE() AS CreateTime, GETDATE() AS UpdateTime, 
       '00000000-0000-0000-0000-000000000000' AS Col1,
       '00000000-0000-0000-0000-000000000000' AS Col2,
       Code, pool, 1 AS Status
INTO collection_new -- 直接创建新表
FROM collectiontemp
WHERE pool = '0929B522-AF2A-4B36-xxxx-xxxxxxxxxxxx';

-- 替换原表
EXEC sp_rename 'collection', 'collection_old';
EXEC sp_rename 'collection_new', 'collection';

-- 重建原表的索引、约束（如果需要）
CREATE CLUSTERED INDEX IX_collection_Id ON collection (Id);
-- 其他非聚集索引和约束...

你的插入语句依赖WHERE pool = 'xxx'筛选数据，确保collectiontemp表的pool字段有索引，而且包含需要提取的Id和Code字段，避免全表扫描和书签查找：

CREATE NONCLUSTERED INDEX IX_collectiontemp_pool 
ON collectiontemp (pool) 
INCLUDE (Id, Code); -- 把需要的字段包含进来，提升查询速度

• 增大事务日志文件的初始大小，把自动增长设置为固定值（比如1GB），避免频繁扩容导致的阻塞；
• 给SQL Server分配足够的内存，让更多数据缓存到内存，减少磁盘IO；
• 如果磁盘IO是瓶颈，优先使用SSD存储，或者把数据文件和日志文件分散到不同磁盘。

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