SQL Server 开发系列（第三期）：DML 语句进阶——INSERT、UPDATE、DELETE、MERGE 的深度优化

绩隐金
0 阅读10分钟

SQL Server 开发系列（第三期）：DML 语句进阶——INSERT、UPDATE、DELETE、MERGE 的深度优化

上一期我们学习了 T-SQL 编程基础。这一期，我们深入到最常用的 DML 语句——增、删、改、合。你以为 INSERT 就是简单的 VALUES？UPDATE 就是 SET 一下？DELETE 就是删几行？其实，这些看似简单的操作背后藏着大量性能陷阱。比如：批量插入用什么最快？更新百万行数据会不会锁表？删除大表为什么日志会爆炸？这一期，我们把这些“隐形坑”一一填平。

一、INSERT：四种批量插入方式深度对比

1.1 逐行插入（最慢，坚决避免）

-- ❌ 极差：循环逐行插入
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT INTO Orders (OrderID, Amount) VALUES (@i, 100);
    SET @i = @i + 1;
END
-- 耗时：数分钟，产生大量日志，频繁锁开销

1.2 多行 VALUES（适合小批量）

-- ✅ 中等：一条语句插入多行（SQL Server 2008+）
INSERT INTO Orders (OrderID, Amount) VALUES
(1, 100),
(2, 200),
(3, 300),
...  -- 最多 1000 行（推荐不超过 100）
(1000, 1000);

-- 优点：减少网络往返
-- 缺点：单条语句太长，解析开销大，超过 1000 行需拆分为多条

1.3 INSERT INTO ... SELECT（推荐）

-- ✅ 好：从源表批量插入
INSERT INTO Orders_Archive (OrderID, OrderDate, Amount)
SELECT OrderID, OrderDate, Amount
FROM Orders
WHERE OrderDate < '2023-01-01';

1.4 BULK INSERT / BCP（最快）

-- ✅ 最佳：大数据量导入
BULK INSERT Orders
FROM 'D:\Data\orders.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,      -- 跳过标题行
    TABLOCK,           -- 减少日志，提升性能
    BATCHSIZE = 10000  -- 分批提交
);

-- 使用 OPENROWSET(BULK) 从文件导入
INSERT INTO Orders (OrderID, Amount)
SELECT OrderID, Amount
FROM OPENROWSET(
    BULK 'D:\Data\orders.csv',
    FORMATFILE = 'D:\Data\orders.fmt',
    FIRSTROW = 2
) AS data;

1.5 四种方式性能对比（10万行）

方式耗时日志量锁影响推荐场景
逐行循环30-60秒极大严重❌ 绝不使用
多行 VALUES2-5秒大中等少量数据（<1000行）
INSERT SELECT0.5-1秒中中等表对表复制
BULK INSERT0.1-0.3秒小（TABLOCK）大表锁大数据量导入

1.6 最小化日志记录

在特定条件下，SQL Server 可以最小化日志（只记录页分配，不记录每行），速度提升数倍。

-- 满足以下条件可最小化日志：
-- 1. 数据库恢复模式为 SIMPLE 或 BULK_LOGGED
-- 2. 使用 TABLOCK 提示
-- 3. 目标表是空表（或堆表）或有聚集索引但无非聚集索引

-- 示例：空表 + TABLOCK
INSERT INTO Orders_Empty WITH (TABLOCK)
SELECT * FROM Orders;

-- 临时切换恢复模式（谨慎！会影响日志备份链）
ALTER DATABASE YourDB SET RECOVERY BULK_LOGGED;
BULK INSERT Orders FROM 'data.csv' WITH (TABLOCK);
ALTER DATABASE YourDB SET RECOVERY FULL;  -- 立即做完整备份

二、UPDATE：隐藏的性能陷阱

2.1 更新 vs 索引维护

-- 看起来简单的更新
UPDATE Orders SET Status = 'Completed' WHERE OrderID = 100;

-- 背后的代价（如果 Status 列有非聚集索引）：
-- 1. 定位行（使用聚集索引）
-- 2. 更新聚集索引中的 Status（如果聚集索引包含 Status）
-- 3. 更新所有非聚集索引中的 Status（每个索引都要维护）
-- 4. 记录完整的事务日志

-- ❌ 坏：更新聚集索引键
UPDATE Orders SET OrderID = OrderID + 1 WHERE OrderID = 100;
-- 后果：导致所有非聚集索引的指针更新（巨大的 I/O 和锁开销）

-- ✅ 好：永不更新主键/聚集键
-- 设计时确保主键不可变

2.2 批量更新的最佳实践

-- ❌ 坏：一次性更新 1000 万行
UPDATE Orders SET Status = 'Archived' WHERE OrderDate < '2020-01-01';
-- 问题：长事务，锁表，日志爆炸，可能超时

-- ✅ 好：分批更新
DECLARE @BatchSize INT = 50000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (@BatchSize) Orders
    SET Status = 'Archived'
    WHERE OrderDate < '2020-01-01'
      AND Status <> 'Archived';  -- 避免重复更新
    
    SET @RowsAffected = @@ROWCOUNT;
    
    -- 批次间检查点，减少日志
    CHECKPOINT;
    
    -- 可选：批次间暂停，降低锁竞争
    WAITFOR DELAY '00:00:01';
END

2.3 UPDATE 多表关联

-- 基础语法：根据另一个表更新
UPDATE o
SET o.Status = 'VIP'
FROM Orders o
JOIN VIPCustomers v ON o.CustomerID = v.CustomerID
WHERE o.Amount > 10000;

-- 使用 CTE 更新
WITH OrderStats AS (
    SELECT OrderID, SUM(Amount) AS TotalAmount
    FROM OrderDetails
    GROUP BY OrderID
)
UPDATE o
SET o.TotalAmount = s.TotalAmount
FROM Orders o
JOIN OrderStats s ON o.OrderID = s.OrderID;

2.4 UPDATE 的 OUTPUT 子句

-- 返回被更新的数据（可用于审计、同步）
DECLARE @UpdatedOrders TABLE (
    OrderID INT,
    OldStatus VARCHAR(20),
    NewStatus VARCHAR(20)
);

UPDATE Orders
SET Status = 'Shipped',
    UpdateTime = GETDATE()
OUTPUT 
    INSERTED.OrderID,
    DELETED.Status AS OldStatus,
    INSERTED.Status AS NewStatus
INTO @UpdatedOrders
WHERE OrderID = 100;

-- 查看更新结果
SELECT * FROM @UpdatedOrders;

三、DELETE：大表删除的艺术

3.1 DELETE vs TRUNCATE

特性DELETETRUNCATE
删除速度慢（逐行记录日志）极快（只记录页释放）
可回滚是（事务内）是（事务内）
触发触发器是否
重置标识列否（ID 继续递增）是（ID 从种子值开始）
表锁行锁或页锁表锁
日志量每行记录极小
有外键约束可以不行
-- TRUNCATE 极快
TRUNCATE TABLE OrderDetails;  -- 清空表，瞬间完成

-- DELETE 慢但可控
DELETE FROM OrderDetails WHERE OrderDate < '2020-01-01';

3.2 分批删除大表数据

-- ✅ 推荐：循环分批删除
DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    DELETE TOP (@BatchSize) 
    FROM OrderDetails
    WHERE OrderDate < '2020-01-01';
    
    SET @RowsAffected = @@ROWCOUNT;
    
    -- 每批次提交事务（如果不在显式事务中，自动提交）
    -- 批次间检查点
    CHECKPOINT;
END

3.3 删除 + 重建表（替换方案）

对于删除大部分数据的场景，重建表比逐行删除快得多。

-- 场景：保留最近 3 个月数据，删除 1000 万行中的 900 万行

-- ❌ 坏：DELETE 900 万行（数小时）
DELETE FROM Orders WHERE OrderDate < '2024-01-01';

-- ✅ 好：将保留的数据复制到新表
BEGIN TRANSACTION;

-- 1. 创建新表（结构与原表相同）
SELECT * INTO Orders_New FROM Orders WHERE 1=0;

-- 2. 插入保留的数据
INSERT INTO Orders_New WITH (TABLOCK)
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01';

-- 3. 删除原表
DROP TABLE Orders;

-- 4. 重命名新表
EXEC sp_rename 'Orders_New', 'Orders';

-- 5. 重建索引、约束
-- ...

COMMIT;
-- 耗时：数分钟，日志量小

3.4 使用分区表实现快速删除

-- 使用分区表后，删除整个分区是元数据操作
ALTER TABLE Orders
SWITCH PARTITION 1 TO Orders_Archive PARTITION 1;

-- 然后可以 TRUNCATE 或 DROP 归档表
TRUNCATE TABLE Orders_Archive;

四、MERGE：一条语句搞定插入/更新/删除

4.1 基础语法

-- 同步目标表与源表
MERGE INTO Products AS Target
USING NewProducts AS Source
ON Target.ProductID = Source.ProductID
WHEN MATCHED AND Target.Price <> Source.Price THEN
    UPDATE SET Target.Price = Source.Price, Target.UpdateTime = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price) VALUES (Source.ProductID, Source.ProductName, Source.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

4.2 MERGE 的常见陷阱

陷阱1：OUTPUT 子句返回的奇怪结果

-- ❌ OUTPUT 会返回所有受影响的行，但 $action 列不准确
MERGE INTO Products AS Target
USING NewProducts AS Source
ON Target.ProductID = Source.ProductID
WHEN MATCHED THEN UPDATE SET Price = Source.Price
WHEN NOT MATCHED THEN INSERT (ProductID, Price) VALUES (Source.ProductID, Source.Price)
OUTPUT $action, inserted.ProductID;  -- $action 可能为 'UPDATE' 或 'INSERT'

陷阱2：多次匹配导致意外更新

-- ❌ 如果源表有重复的 ProductID，MERGE 会报错或产生不可预测结果
-- 解决方案：确保源表在 ON 列上唯一
WITH SourceDedup AS (
    SELECT DISTINCT ProductID, Price FROM NewProducts
)
MERGE INTO Products AS Target
USING SourceDedup AS Source
ON Target.ProductID = Source.ProductID
...

陷阱3：性能问题

-- MERGE 可能比分开写 INSERT/UPDATE 慢（尤其是大数据量）
-- 测试环境对比后再决定使用
-- 某些版本存在 MERGE 相关的 bug（如 2012/2014）

4.3 替代方案：分开处理更安全

-- 替代 MERGE 的写法（更清晰，更可控）
BEGIN TRANSACTION;

-- 更新已存在的
UPDATE p
SET p.Price = s.Price
FROM Products p
JOIN NewProducts s ON p.ProductID = s.ProductID
WHERE p.Price <> s.Price;

-- 插入不存在的
INSERT INTO Products (ProductID, ProductName, Price)
SELECT s.ProductID, s.ProductName, s.Price
FROM NewProducts s
LEFT JOIN Products p ON s.ProductID = p.ProductID
WHERE p.ProductID IS NULL;

-- 删除多余的
DELETE FROM Products
WHERE ProductID IN (
    SELECT p.ProductID
    FROM Products p
    LEFT JOIN NewProducts s ON p.ProductID = s.ProductID
    WHERE s.ProductID IS NULL
);

COMMIT;

五、OUTPUT 子句：被低估的神器

5.1 返回插入的数据

-- 插入后获取自动生成的 ID
DECLARE @NewOrder TABLE (OrderID INT, OrderDate DATETIME);

INSERT INTO Orders (CustomerID, Amount)
OUTPUT INSERTED.OrderID, INSERTED.OrderDate
INTO @NewOrder
VALUES (100, 500), (101, 300);

-- 使用获取的 ID
SELECT * FROM @NewOrder;

5.2 删除时保留数据

-- 删除前先备份
DELETE FROM Orders
OUTPUT DELETED.*
INTO Orders_Deleted_Backup
WHERE OrderDate < '2020-01-01';

5.3 更新时的新旧值对比

-- 记录变更日志
UPDATE Orders
SET Status = 'Shipped', ShipDate = GETDATE()
OUTPUT 
    DELETED.OrderID,
    DELETED.Status AS OldStatus,
    INSERTED.Status AS NewStatus,
    DELETED.ShipDate AS OldShipDate,
    INSERTED.ShipDate AS NewShipDate,
    GETDATE() AS ChangeDate
INTO OrderStatusLog
WHERE OrderID = 100;

六、实战：完整的 ETL 批处理模板

CREATE PROC usp_ETL_ProcessDailySales
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @StartTime DATETIME = GETDATE();
    DECLARE @BatchSize INT = 50000;
    DECLARE @RowsAffected INT = 1;
    DECLARE @ErrorMessage NVARCHAR(4000);
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- ===== 1. 清理临时表 =====
        TRUNCATE TABLE Staging_Sales;
        
        -- ===== 2. 批量导入原始数据 =====
        BULK INSERT Staging_Sales
        FROM 'D:\SalesData\daily_sales.csv'
        WITH (
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = '\n',
            FIRSTROW = 2,
            TABLOCK,
            BATCHSIZE = @BatchSize
        );
        
        -- ===== 3. 数据清洗 =====
        UPDATE Staging_Sales
        SET Status = 'Validated'
        WHERE Amount > 0 AND CustomerID IS NOT NULL;
        
        -- ===== 4. 增量更新目标表（分批） =====
        WHILE @RowsAffected > 0
        BEGIN
            UPDATE TOP (@BatchSize) t
            SET t.Amount = s.Amount,
                t.UpdateTime = GETDATE()
            FROM SalesTarget t
            JOIN Staging_Sales s ON t.SaleID = s.SaleID
            WHERE t.Status = 'Active'
              AND s.Status = 'Validated'
              AND t.Amount <> s.Amount;
            
            SET @RowsAffected = @@ROWCOUNT;
        END;
        
        -- ===== 5. 插入新数据 =====
        INSERT INTO SalesTarget (SaleID, CustomerID, Amount, Status)
        SELECT SaleID, CustomerID, Amount, 'Active'
        FROM Staging_Sales s
        WHERE NOT EXISTS (
            SELECT 1 FROM SalesTarget t WHERE t.SaleID = s.SaleID
        );
        
        -- ===== 6. 记录处理日志 =====
        INSERT INTO ETL_Log (ProcName, StartTime, EndTime, RowsProcessed, Status)
        VALUES (
            'usp_ETL_ProcessDailySales',
            @StartTime,
            GETDATE(),
            @@ROWCOUNT,
            'SUCCESS'
        );
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK;
        
        SET @ErrorMessage = ERROR_MESSAGE();
        
        INSERT INTO ETL_Log (ProcName, StartTime, EndTime, Status, ErrorMsg)
        VALUES ('usp_ETL_ProcessDailySales', @StartTime, GETDATE(), 'FAILED', @ErrorMessage);
        
        THROW;
    END CATCH
END

七、性能优化检查清单

操作问题优化方案
INSERT逐行插入使用 BULK INSERT 或 INSERT SELECT
INSERT日志爆炸使用 TABLOCK + SIMPLE 恢复模式
UPDATE更新聚集键重新设计，永不更新主键
UPDATE长事务锁表分批更新 + 检查点
DELETE删除大量数据分批删除或使用 TRUNCATE/重建表
DELETE外键约束导致慢先禁用外键（谨慎）或使用分区切换
MERGE源表有重复先去重，或分开写 INSERT/UPDATE
所有 DML无索引确保 WHERE/JOIN 列有索引
所有 DML统计信息过期定期 UPDATE STATISTICS

八、核心总结

知识点核心要点
批量插入BULK INSERT > INSERT SELECT > 多行 VALUES > 逐行循环
最小化日志TABLOCK + SIMPLE/BULK_LOGGED 恢复模式
批量更新分批更新（TOP + WHILE）+ 检查点
大表删除分批删除，或重建表，或使用分区切换
TRUNCATE清空表首选，但需满足条件（无外键）
MERGE功能强大但有坑，大数据量测试后再用
OUTPUT返回变更数据，用于审计或获取自增 ID

一句话记住本期内容：

批量操作是 DML 性能的核心——BULK INSERT 导数据，分批 UPDATE/DELETE 控日志，TRUNCATE 清空表，OUTPUT 留审计，MERGE 虽好需谨慎。

动手练习

-- 场景：需要将 1000 万行日志表（Logs）中 6 个月前的数据归档到 Logs_Archive 表，并删除原表数据

-- 问题1：如何设计高效的归档 + 删除方案？

-- 问题2：如果 Logs 表有 5 个非聚集索引，DELETE 会有什么影响？

-- 问题3：如何用 OUTPUT 子句在删除的同时备份？
点击查看参考答案

问题1 推荐方案（分区切换最优）：

-- 方案A：使用分区表（如果已分区）
ALTER TABLE Logs SWITCH PARTITION 1 TO Logs_Archive PARTITION 1;

-- 方案B：分批插入 + 分批删除
DECLARE @BatchSize INT = 10000;
WHILE 1=1
BEGIN
    -- 先复制到归档表
    INSERT INTO Logs_Archive
    SELECT TOP (@BatchSize) *
    FROM Logs
    WHERE LogDate < '2024-01-01';
    
    IF @@ROWCOUNT = 0 BREAK;
    
    -- 再删除（使用 CTE 或 TOP）
    WITH CTE AS (
        SELECT TOP (@BatchSize) *
        FROM Logs
        WHERE LogDate < '2024-01-01'
    )
    DELETE FROM CTE;
    
    CHECKPOINT;
END

问题2：每个非聚集索引都需要维护，DELETE 速度会慢 5-10 倍。建议：删除前删除非聚集索引，删除后重建。

问题3：

DELETE FROM Logs
OUTPUT DELETED.* INTO Logs_Archive
WHERE LogDate < '2024-01-01';

下一期预告

连接与子查询——JOIN 的底层逻辑与性能调优

  • Nested Loops、Hash Match、Merge Join 的底层原理
  • 什么时候用 INNER JOIN，什么时候用 LEFT JOIN？
  • EXISTS vs IN vs JOIN 的性能对比
  • 子查询优化：相关子查询 vs 非相关子查询
  • 实战：将慢查询从 30 秒优化到 0.5 秒

📌 本文代码已在 SQL Server 2019+ 验证。生产环境执行批量 DML 前，务必在测试环境评估日志增长和锁影响。

本系列持续更新中，点击关注不错过第四期。

avatar
文章
阅读
粉丝