基于Dapper ORM优化SQL Server大表查询性能的技术求助

应用采用Dapper ORM与SQL Server数据库交互，核心表OperationSteps数据量达500万至1500万条，查询时因索引扫描耗时2-3秒，此前尝试修改表结构和创建索引时出现超时锁表问题，现需在不中断应用可用性的前提下优化查询性能。

表结构 #

CREATE TABLE [dbo].[OperationSteps](
    [UserId] [int] NOT NULL,
    [OperationName] [nvarchar](300) NOT NULL,
    [FatherName] [nvarchar](50) NOT NULL,
    [EventType] [nvarchar](5) NOT NULL,
    [StepId] [nvarchar](100) NOT NULL,
    [TransactionId] [nvarchar](200) NOT NULL,
    [Amount] [float] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [OperationIdExternal] [nvarchar](max) NULL,
    [TaxesAmount] [decimal](19, 4) NULL,
    [Round] [nvarchar](max) NULL,
    [FatherId] [smallint] NULL,
 CONSTRAINT [PK_OperationSteps] PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC,
    [TransactionId] ASC,
    [StepsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

查询涉及列 #

SELECT
    UserId,
    OperationName,
    OperationIdExternal AS GameId,
    ProviderId,
    ProviderName,
    EventType,
    StepId,
    TransactionId,
    Amount,
    TimeStamp,
    TaxesAmount,
    Round

查询WHERE条件 #

1. 固定条件查询：

WHERE [ProviderId] = @ProviderId
       AND [OperationIdExternal] = @GameId
       AND [Round] = @Round
       AND [EventType] = @EventType

2. 动态条件查询（C#代码拼接）：

WHERE [ProviderId] = @ProviderId
       AND (@TransactionId IS NULL OR [TransactionId] = @TransactionId)
       AND (@Round IS NULL OR [Round] = @Round)
       AND (@EventType IS NULL OR [EventType] = @EventType)
       AND (@OperationId IS NULL OR [OperationIdExternal] = @OperationId)

代码片段：

FROM dbo.OperationsSteps 
WHERE ProviderId = @ProviderId";

var parameters = new DynamicParameters();
parameters.Add("@ProviderId", providerId, DbType.Int16, ParameterDirection.Input);
if (!string.IsNullOrEmpty(transactionId))
{
    query += " AND TransactionId = @TransactionId";
    parameters.Add("@TransactionId", transactionId, DbType.String, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(round))
{
    query += " AND Round = @Round";
    parameters.Add("@Round", round, DbType.String, ParameterDirection.Input);
}

3. Session维度查询：

WHERE [SessionId] = @SessionId
  AND [Round] = @Round
  AND [EventType] = @EventType

注：表结构中未定义SessionId字段，需确认是否为笔误或关联其他表字段

已尝试的优化操作 #

曾尝试修改Round列类型并创建索引，但操作超时并锁表：

ALTER TABLE dbo.OperationSteps
ALTER COLUMN Round nvarchar(100);

CREATE NONCLUSTERED INDEX IX_OperationSteps_Operation_Round_EventType
ON dbo.OperationSessions (StepId, Round, EventType);

可行优化方案 #

1. 在线修改列类型+创建索引（避免锁表） #

直接修改nvarchar(max)列会锁表，推荐分步操作：

-- 1. 添加临时列
ALTER TABLE dbo.OperationSteps ADD Round_new nvarchar(100) NULL;

-- 2. 分批更新数据（避免一次性更新全表锁表）
DECLARE @BatchSize INT = 10000;
DECLARE @RowCount INT = @BatchSize;
WHILE @RowCount = @BatchSize
BEGIN
    UPDATE TOP (@BatchSize) dbo.OperationSteps
    SET Round_new = SUBSTRING(Round, 1, 100)
    WHERE Round_new IS NULL;
    SET @RowCount = @@ROWCOUNT;
END

-- 3. 调整列属性（若原Round非空，设置新列为NOT NULL）
ALTER TABLE dbo.OperationSteps ALTER COLUMN Round_new nvarchar(100) NOT NULL;

-- 4. 切换列名
EXEC sp_rename 'dbo.OperationSteps.Round', 'Round_old';
EXEC sp_rename 'dbo.OperationSteps.Round_new', 'Round';

-- 5. 在线创建索引（ONLINE=ON 避免阻塞读写）
CREATE NONCLUSTERED INDEX IX_OperationSteps_Round_EventType
ON dbo.OperationSteps (Round, EventType)
INCLUDE (UserId, OperationName, StepId, TransactionId, Amount, TimeStamp, TaxesAmount)
WITH (ONLINE = ON, MAXDOP = 1); -- MAXDOP=1 降低资源消耗

2. 针对查询模式创建覆盖索引 #

根据三个查询的过滤条件，创建包含返回列的覆盖索引，避免书签查找：

• 针对固定条件查询：

CREATE NONCLUSTERED INDEX IX_OperationSteps_Provider_Game_Round_EventType
ON dbo.OperationSteps (ProviderId, OperationIdExternal, Round, EventType)
INCLUDE (UserId, OperationName, StepId, TransactionId, Amount, TimeStamp, TaxesAmount)
WITH (ONLINE = ON);

• 针对动态条件查询：

CREATE NONCLUSTERED INDEX IX_OperationSteps_Provider_Transaction_Round
ON dbo.OperationSteps (ProviderId, TransactionId, Round, OperationIdExternal, EventType)
INCLUDE (UserId, OperationName, StepId, Amount, TimeStamp, TaxesAmount)
WITH (ONLINE = ON);

• 针对Session维度查询（假设SessionId为实际存在的字段）：

CREATE NONCLUSTERED INDEX IX_OperationSteps_Session_Round_EventType
ON dbo.OperationSteps (SessionId, Round, EventType)
INCLUDE (UserId, OperationName, StepId, TransactionId, Amount, TimeStamp, TaxesAmount)
WITH (ONLINE = ON);

3. 优化动态查询执行计划 #

动态拼接的查询可能出现参数嗅探或执行计划不匹配问题，可通过以下方式优化：

• 在查询末尾添加OPTION (RECOMPILE)，让SQL Server每次生成最优计划（适合查询频率不极高的场景）：

// 拼接完条件后添加
query += " OPTION (RECOMPILE)";

• 确保Dapper参数化逻辑正确，避免SQL注入风险的同时保障执行计划稳定性。

4. 维护统计信息与索引碎片 #

• 更新统计信息：让查询优化器获取准确的数据分布：

UPDATE STATISTICS dbo.OperationSteps WITH FULLSCAN;

• 开启异步统计更新：避免统计更新阻塞查询：

ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC ON;

• 重组/重建索引：低峰期维护索引碎片，提升扫描效率：

-- 重组索引（轻量，在线操作）
ALTER INDEX ALL ON dbo.OperationSteps REORGANIZE;

-- 重建索引（需ONLINE=ON避免锁表）
ALTER INDEX ALL ON dbo.OperationSteps REBUILD WITH (ONLINE = ON);

5. 确认表结构关联字段 #

查询中涉及ProviderId和ProviderName，但表结构中无这两个字段，需确认：

• 是否为笔误，实际字段存在于OperationSteps表中？
• 是否需要关联其他表（如Providers）？若为关联查询，需确保关联字段有索引，避免全表关联开销。

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