SQL Server varchar(13)复合主键获取下一个编号的查询优化问询

现有方案的问题 #

现有查询的超时主要来自两个核心问题：

1. ORDER BY key DESC + TOP 1的执行效率低于直接取最大值的逻辑，当符合前缀条件的行数量较多时，排序会额外消耗性能。
2. 范围查询+ROWLOCK XLOCK会锁定所有符合前缀条件的行甚至间隙，高并发下锁冲突严重，大量请求排队等待锁释放导致超时。

可选优化方案（按改造成本从低到高排序） #

方案1：不修改任何现有结构，仅优化查询语句 #

直接用MAX()函数取最大主键，避免排序操作，执行效率提升明显，锁范围也会更小：

SELECT @key = MAX([key]) 
FROM [table] WITH (ROWLOCK, XLOCK, HOLDLOCK)
WHERE [key] LIKE @const + '%'

说明：HOLDLOCK是为了避免幻读，保证当前事务内拿到的最大值不会被其他事务插入的新记录覆盖，key为SQL关键字，建议加方括号转义。

方案2：用应用级锁降低锁冲突（无需修改表结构） #

如果优化语句后仍有并发冲突，可使用SQL Server内置的sp_getapplock实现前缀粒度的互斥锁，只有同年度同单据类型的主键生成请求会互斥，不同前缀的请求完全不影响，锁粒度远小于行锁，并发能力大幅提升：

BEGIN TRANSACTION
DECLARE @lockRes INT
-- 以前缀作为锁资源，同前缀请求串行执行
EXEC @lockRes = sp_getapplock 
    @Resource = @const,
    @LockMode = 'Exclusive',
    @LockOwner = 'Transaction',
    @LockTimeout = 3000 -- 可根据业务调整超时时间，避免死等

IF @lockRes < 0
BEGIN
    ROLLBACK TRANSACTION
    -- 此处可添加重试逻辑或返回异常
    RETURN
END

-- 执行主键查询逻辑（可直接用方案1的MAX查询）
SELECT @key = MAX([key]) FROM [table] WHERE [key] LIKE @const + '%'

-- 此处添加生成新主键、插入数据的业务逻辑

COMMIT TRANSACTION -- 事务提交后自动释放应用锁

方案3：新增辅助序号表（性能最优，不影响原有表逻辑） #

如果允许新增独立的辅助表（不修改原有业务表结构），可以用序号缓存表把主键生成逻辑和原业务表解耦，性能提升10倍以上，且完全兼容原有第三方应用的主键规则：

1. 先创建辅助表：

CREATE TABLE DocKeySequence (
    SeqPrefix VARCHAR(6) PRIMARY KEY, -- 存储年份+单据类型前缀，如21xxxx
    LastSeq INT NOT NULL DEFAULT 0
)

2. 主键生成逻辑：

BEGIN TRANSACTION
-- 直接更新序号表获取最新流水号
UPDATE DocKeySequence WITH (ROWLOCK,XLOCK)
SET LastSeq = LastSeq + 1
WHERE SeqPrefix = @const

-- 前缀首次生成时从原表初始化最大流水号
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO DocKeySequence (SeqPrefix, LastSeq)
    SELECT @const, ISNULL(CAST(RIGHT(MAX([key]),7) AS INT), 0) + 1
    FROM [table]
    WHERE [key] LIKE @const + '%'
END

-- 获取最新流水号，格式化前导零后拼接前缀即可得到新主键
DECLARE @newSeq INT, @newKey VARCHAR(13)
SELECT @newSeq = LastSeq FROM DocKeySequence WHERE SeqPrefix = @const
SET @newKey = @const + RIGHT('0000000' + CAST(@newSeq AS VARCHAR(7)),7)

-- 此处添加插入原表的业务逻辑

COMMIT TRANSACTION

说明：该方案下原第三方应用的原有逻辑仍可正常运行，两边生成的主键规则完全一致不会冲突，高并发下几乎不会出现锁超时问题。

注意事项 #

• 所有主键生成和插入数据的逻辑必须放在同一个事务中，避免出现主键生成后未插入导致的跳号或重复问题。
• 使用辅助表的话首次上线前建议把所有已存在的前缀对应的最大流水号提前初始化到辅助表中，避免重复主键。

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