SQL Server多条件更新性能优化及返回修改记录技术问询

阿华AIGC实验室

2026-5-13

优化SQL多语句更新与返回修改记录的实用建议

针对你的场景——每秒调用一次、更新1到数千条记录，还要在SERIALIZABLE隔离级别下提升性能，同时返回被修改的记录，我整理了几个针对性的优化方案：

1. 合并多个UPDATE为单语句，减少锁持有时间

你现在的三个UPDATE都是操作同一张UserSession表，只是State的过滤条件不同。把它们合并成一个带条件分支的UPDATE，能大幅减少表扫描次数和锁的持有时间（这对SERIALIZABLE隔离级别特别重要，因为它会把锁持到事务结束）。

改造后的代码如下：

DECLARE @SPAN INT; SET @SPAN = 1;
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE UserSession
SET 
    PauseSpan = CASE WHEN State & 8 = 8 THEN PauseSpan + @SPAN ELSE PauseSpan END,
    PauseSpanTotal = CASE WHEN State & 8 = 8 THEN PauseSpanTotal + @SPAN ELSE PauseSpanTotal END,
    Span = CASE WHEN State & 1 = 1 THEN Span + @SPAN ELSE Span END,
    PendSpan = CASE WHEN State & 4 = 4 THEN PendSpan + @SPAN ELSE PendSpan END,
    -- 注意：原代码这里可能有笔误，原句是PendSpanTotal = PendSpan + @SPAN，应该是PendSpanTotal + @SPAN吧？
    PendSpanTotal = CASE WHEN State & 4 = 4 THEN PendSpanTotal + @SPAN ELSE PendSpanTotal END
WHERE 
    State & 8 = 8 
    OR State & 1 = 1 
    OR State & 4 = 4;

这样做的好处：

只扫描一次表，降低IO开销
减少锁操作次数，降低锁竞争概率
务必检查第三个UPDATE的PendSpanTotal赋值逻辑，避免数据错误

2. 用`OUTPUT`子句直接返回修改记录，替代额外SELECT

你当前的逻辑是先更新再查询，但OUTPUT子句能在UPDATE的同时直接捕获修改后的行数据，完全不需要额外的表扫描，效率高很多。

场景1：返回所有被修改的记录

直接在UPDATE后加OUTPUT即可：

DECLARE @SPAN INT; SET @SPAN = 1;
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE UserSession
SET 
    PauseSpan = CASE WHEN State & 8 = 8 THEN PauseSpan + @SPAN ELSE PauseSpan END,
    PauseSpanTotal = CASE WHEN State & 8 = 8 THEN PauseSpanTotal + @SPAN ELSE PauseSpanTotal END,
    Span = CASE WHEN State & 1 = 1 THEN Span + @SPAN ELSE Span END,
    PendSpan = CASE WHEN State & 4 = 4 THEN PendSpan + @SPAN ELSE PendSpan END,
    PendSpanTotal = CASE WHEN State & 4 = 4 THEN PendSpanTotal + @SPAN ELSE PendSpanTotal END
OUTPUT 
    inserted.UserSessionId AS Id,
    inserted.UserId,
    inserted.HostId,
    inserted.State,
    inserted.Span,
    inserted.BilledSpan,
    inserted.PendTime,
    inserted.PendSpan,
    inserted.EndTime,
    inserted.CreatedById,
    inserted.CreatedTime,
    inserted.Slot,
    inserted.PendSpanTotal,
    inserted.PauseSpan,
    inserted.PauseSpanTotal
WHERE 
    State & 8 = 8 
    OR State & 1 = 1 
    OR State & 4 = 4;

COMMIT;

inserted代表修改后的行数据，这样更新完成后直接返回所有被修改的记录，省去了后续的SELECT操作。

场景2：仅返回原需求中State&1=1的修改记录

如果还是只需要返回State&1=1的记录，可以用临时表先存所有修改记录，再筛选：

DECLARE @SPAN INT; SET @SPAN = 1;
DECLARE @ModifiedRecords TABLE (
    Id INT,
    UserId INT,
    HostId INT,
    State INT,
    Span INT,
    BilledSpan INT,
    PendTime DATETIME,
    PendSpan INT,
    EndTime DATETIME,
    CreatedById INT,
    CreatedTime DATETIME,
    Slot INT,
    PendSpanTotal INT,
    PauseSpan INT,
    PauseSpanTotal INT
);

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE UserSession
SET 
    PauseSpan = CASE WHEN State & 8 = 8 THEN PauseSpan + @SPAN ELSE PauseSpan END,
    PauseSpanTotal = CASE WHEN State & 8 = 8 THEN PauseSpanTotal + @SPAN ELSE PauseSpanTotal END,
    Span = CASE WHEN State & 1 = 1 THEN Span + @SPAN ELSE Span END,
    PendSpan = CASE WHEN State & 4 = 4 THEN PendSpan + @SPAN ELSE PendSpan END,
    PendSpanTotal = CASE WHEN State & 4 = 4 THEN PendSpanTotal + @SPAN ELSE PendSpanTotal END
OUTPUT 
    inserted.UserSessionId AS Id,
    inserted.UserId,
    inserted.HostId,
    inserted.State,
    inserted.Span,
    inserted.BilledSpan,
    inserted.PendTime,
    inserted.PendSpan,
    inserted.EndTime,
    inserted.CreatedById,
    inserted.CreatedTime,
    inserted.Slot,
    inserted.PendSpanTotal,
    inserted.PauseSpan,
    inserted.PauseSpanTotal
INTO @ModifiedRecords
WHERE 
    State & 8 = 8 
    OR State & 1 = 1 
    OR State & 4 = 4;

-- 仅筛选需要的记录返回
SELECT * FROM @ModifiedRecords WHERE State & 1 = 1;

COMMIT;

临时表的筛选是内存操作，比再次扫描原表高效得多。

3. 重新评估SERIALIZABLE隔离级别的必要性

SERIALIZABLE是最严格的隔离级别，会持有锁直到事务结束，很容易导致锁升级和阻塞，尤其是在你每秒调用的高频场景下。如果业务允许，建议降级到：

REPEATABLE READ：保证事务期间读取的数据不会被修改，避免不可重复读，允许幻读
READ COMMITTED SNAPSHOT ISOLATION (RCSI)：开启后用行版本控制，读写不阻塞，性能更好，同时避免脏读、不可重复读和幻读（需要数据库开启READ_COMMITTED_SNAPSHOT选项）

如果必须用SERIALIZABLE，一定要让事务执行时间尽可能短——合并UPDATE、用OUTPUT减少操作，都是缩短事务时间的关键。

4. 优化索引，缩小更新范围

针对你的位运算过滤条件，推荐两种索引优化方式：

过滤索引：针对每个状态条件创建单独的过滤索引，帮助SQL快速定位要修改的行：

CREATE INDEX IX_UserSession_State_1 ON UserSession (UserSessionId) WHERE State & 1 = 1;
CREATE INDEX IX_UserSession_State_4 ON UserSession (UserSessionId) WHERE State & 4 = 4;
CREATE INDEX IX_UserSession_State_8 ON UserSession (UserSessionId) WHERE State & 8 = 8;

计算列索引：把位运算结果做成持久化计算列，再创建索引，让SQL不用每次都计算：

ALTER TABLE UserSession ADD IsState1 AS (State & 1 = 1) PERSISTED;
ALTER TABLE UserSession ADD IsState4 AS (State & 4 = 4) PERSISTED;
ALTER TABLE UserSession ADD IsState8 AS (State & 8 = 8) PERSISTED;
CREATE INDEX IX_UserSession_IsState1 ON UserSession (UserSessionId) WHERE IsState1 = 1;
-- 同理创建IsState4和IsState8的索引

另外，确保UserSessionId是主键（聚集索引），这样更新时能快速定位行，减少锁的范围。