如何优化SQL Server中约1亿行数据的插入与更新性能

针对1亿行爬虫数据的暂存表插入+主价格表更新场景，以下是落地性强的优化方案：

一、暂存表插入阶段优化 #

1. 采用最小日志批量插入
   • 使用BULK INSERT或OPENROWSET(BULK...)替代普通INSERT，这两个命令支持大容量日志模式，大幅减少日志生成量。
   • 确保数据库恢复模式设置为简单或大容量日志（操作完成后可改回完整模式），执行命令前可加：

     ALTER DATABASE YourDB SET RECOVERY BULK_LOGGED;
     

2. 暂存表用堆表（无聚集索引）
   • 堆表的插入速度远高于带聚集索引的表，无需维护索引顺序。待全部数据插入完成后，再按需创建非聚集索引，且创建时指定SORT_IN_TEMPDB = ON，将排序操作转移到tempdb，降低主库IO压力：

     CREATE NONCLUSTERED INDEX IX_Staging_ProductID ON StagingTable(ProductID)
     WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF); -- 离线创建更快，若允许锁表
     

3. 禁用暂存表的约束与触发器
   • 插入前禁用暂存表的外键、检查约束、触发器，避免逐行验证拖慢速度，插入完成后再启用：

     -- 禁用约束
     ALTER TABLE StagingTable NOCHECK CONSTRAINT ALL;
     -- 禁用触发器
     DISABLE TRIGGER ALL ON StagingTable;
     -- 插入完成后启用
     ALTER TABLE StagingTable CHECK CONSTRAINT ALL;
     ENABLE TRIGGER ALL ON StagingTable;
     

4. 拆分批量插入批次
   • 不要一次性插入1亿行，拆分为每次100万-500万行的批次，避免一次性占用过多内存与日志资源。可通过循环或SSIS批量任务实现。

二、主价格表更新阶段优化 #

1. 用MERGE语句实现集合式同步
   • 替代单独的UPDATE+INSERT，MERGE可一次完成匹配更新、不匹配插入（若需新增产品），减少两次全表扫描的开销。确保主表与暂存表的连接键（如ProductID）有索引：

     MERGE INTO PriceTable AS Target
     USING StagingTable AS Source
     ON Target.ProductID = Source.ProductID
     WHEN MATCHED AND Target.Price <> Source.Price THEN
         UPDATE SET Target.Price = Source.Price, Target.LastUpdated = GETDATE()
     WHEN NOT MATCHED THEN
         INSERT (ProductID, Price, LastUpdated)
         VALUES (Source.ProductID, Source.Price, GETDATE());
     

2. 优化主表索引策略
   • 主表仅保留必要索引：聚集索引优先建在ProductID（唯一标识）上，非聚集索引仅保留更新/查询必需的字段，避免过多索引导致更新时的维护开销。
3. 分区表缩小扫描范围
   • 若主表数据量极大，将主表与暂存表按ProductID哈希分区或按LastUpdated范围分区，更新时仅操作目标分区，大幅减少数据扫描量。
4. 开启并行执行
   • 针对更新语句设置合理的并行度（MAXDOP），匹配CPU核心数（如8核设置为8），提升多核利用率：

     MERGE INTO PriceTable AS Target
     USING StagingTable AS Source
     ON Target.ProductID = Source.ProductID
     ...
     OPTION (MAXDOP 8);
     

5. 绝对避免逐行操作
   • 禁止使用游标、WHILE循环逐行更新，集合操作的效率比逐行操作高几个数量级。

三、数据库层面配置调优 #

1. Tempdb优化
   • 创建与CPU核心数相等的数据文件（最多8个），每个文件大小一致（如100GB），自动增长设置为固定大小（如10GB），避免碎片化；将tempdb部署在SSD存储上。
2. 内存配置
   • 设置SQL Server最大服务器内存为物理内存的80%左右（如64GB内存设置为50GB），确保足够内存缓存数据，减少磁盘IO。
3. 存储与IO优化
   • 数据库文件（含暂存表所在文件组）部署在SSD或高性能SAN上，确保IOPS满足批量操作需求；避免将数据文件与日志文件放在同一磁盘。
4. 更新统计信息
   • 插入与更新前更新表统计信息，让查询优化器生成最优执行计划：

     UPDATE STATISTICS StagingTable WITH SAMPLE 10 PERCENT; -- 大表用采样替代全扫描
     UPDATE STATISTICS PriceTable WITH FULLSCAN;
     

四、额外优化技巧 #

1. 增量同步缩减数据量
   • 爬虫记录每个产品的上次抓取时间与价格，仅同步自上次运行以来价格变动或新增的产品，无需每次处理1亿行全量数据。
2. Columnstore索引加速查询
   • 暂存表可创建非聚集列存储索引，批量插入时启用批量模式，后续与主表连接时列存储的扫描速度远高于行存储：

     CREATE NONCLUSTERED COLUMNSTORE INDEX CIX_Staging ON StagingTable(ProductID, Price);
     

3. 离线执行
   • 在非业务高峰时段执行操作，避免与核心业务争用CPU、内存、IO资源。

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