千万级数据行MERGE操作过慢问题优化咨询

API 探路者

2026-7-2

问题背景与优化需求

将100万至1000万行数据合并到一张包含2000万+行数据的目标表中，目标表主键为NormalID。由于存在记录主键变更的情况（如记录"A"的主键从"12345678"变为"4567890"），MERGE语句中加入了标记删除逻辑，且每次仅拉取2个月的源数据，但当前MERGE操作运行极慢，耗时数小时，需优化。

源表建表语句

CREATE TABLE [dbo].[slowmergetbl_source](
    [sEntity] [varchar](50) NOT NULL,
    [wYear] [smallint] NOT NULL,
    [wPeriod] [smallint] NOT NULL,
    [sAccount] [varchar](50) NOT NULL,
    [wBracket] [smallint] NOT NULL,
    [sCurrency] [varchar](3) NULL,
    [dValue] [float] NULL,
    [bDirty] [bit] NULL DEFAULT 1,
    [dFactValue] [float] NULL,
    [wEntityId] [int] NULL,
    [wAccountId] [int] NULL,
    [wTimeId] [int] NULL,
    [wExtDimId1] [int] NOT NULL DEFAULT 0,
    [NormalID] [int]  NOT NULL,
CONSTRAINT [PK_SourceNormalID] PRIMARY KEY CLUSTERED ([NormalID] ASC)
);

目标表建表语句

CREATE TABLE [reporting].[slowmergetbl_target](
    [sEntity] [varchar](50) NOT NULL,
    [wYear] [smallint] NOT NULL,
    [wPeriod] [smallint] NOT NULL,
    [sAccount] [varchar](50) NOT NULL,
    [wBracket] [smallint] NOT NULL,
    [sCurrency] [varchar](3) NULL,
    [dValue] [float] NULL,
    [bDirty] [bit] NULL DEFAULT 1,
    [dFactValue] [float] NULL,
    [wEntityId] [int] NULL,
    [wAccountId] [int] NULL,
    [wTimeId] [int] NULL,
    [wExtDimId1] [int] NOT NULL DEFAULT 0,
    [NormalID] [int]  NOT NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifyType] [varchar](10) NULL,
CONSTRAINT [PK_NormalID] PRIMARY KEY CLUSTERED ([NormalID] ASC),
INDEX [IDX_Year] NONCLUSTERED ([wYear] ASC),
INDEX [IDX_Period] NONCLUSTERED ([wPeriod] ASC),

INDEX [IX_ReportQuery] NONCLUSTERED
(
[wYear],
[dFactValue]
)
INCLUDE ([sEntity],[wPeriod],[sAccount],[wBracket],[wAccountId]),


INDEX [IX_ReportQuery2] NONCLUSTERED
(
[sAccount],
[wBracket])
INCLUDE ([sEntity],[wYear],[wPeriod],[dValue],[wEntityId]),

INDEX [IDX_Normal_ModifyType] NONCLUSTERED ([ModifyType] ASC)

);

当前MERGE语句

merge reporting.slowmergetbl_target as a
using dbo.slowmergetbl_source as b on a.normalid = b.normalid

WHEN NOT MATCHED BY SOURCE and a.wyear in (
2024
) and a.wPeriod in (9,10 )

THEN UPDATE SET
a.modifieddate=getdate(),
a.modifytype='DELETED'

when not matched by target
then insert (
[sEntity],
[wYear],
[wPeriod],
[sAccount],
[wBracket],
[sCurrency],
[dValue],
[bDirty],
[dFactValue],
[wEntityId],
[wAccountId],
[wTimeId],
[wExtDimId1],
[NormalID],
[ModifiedDate],
[ModifyType]
) 
values (
b.[sEntity],
b.[wYear],
b.[wPeriod],
b.[sAccount],
b.[wBracket],
b.[sCurrency],
b.[dValue],
b.[bDirty],
b.[dFactValue],
b.[wEntityId],
b.[wAccountId],
b.[wTimeId],
b.[wExtDimId1],
b.[NormalID],
getdate(),'INSERT')

when matched
and (
isnull(a.[sAccount],'')<>isnull(b.[sAccount],'')
or isnull(a.[sEntity],'')<>isnull(b.[sEntity],'')
or isnull(a.[wYear],0)<>isnull(b.[wYear],0)
or isnull(a.[wPeriod],0)<>isnull(b.[wPeriod],0)
or isnull(a.[wBracket],0)<>isnull(b.[wBracket],0)
or isnull(a.[wEntityId],0)<>isnull(b.[wEntityId],0)
or isnull(a.[wAccountId],0)<>isnull(b.[wAccountId],0)
or isnull(a.[sCurrency],'')<>isnull(b.[sCurrency],'')
or isnull(cast(a.[dValue] as float),0.00)<>isnull(cast(b.[dValue] as float),0.00)
or isnull(a.[bDirty],0)<>isnull(b.[bDirty],0)
or isnull(cast(a.[dFactValue] as float),0.00)<>isnull(cast(b.[dFactValue] as float),0.00)
or isnull(a.[wTimeId],0)<>isnull(b.[wTimeId],0)
or isnull(a.[wExtDimId1],0)<>isnull(b.[wExtDimId1],0)
)
then update set
a.[sAccount]=b.[sAccount],
a.[sEntity]=b.[sEntity],
a.[wYear]=b.[wYear],
a.[wPeriod]=b.[wPeriod],
a.[wBracket]=b.[wBracket],
a.[wEntityId]=b.[wEntityId],
a.[wAccountId]=b.[wAccountId],
a.[sCurrency]=b.[sCurrency],
a.[dValue]=b.[dValue],
a.[bDirty]=b.[bDirty],
a.[dFactValue]=b.[dFactValue],
a.[wTimeId]=b.[wTimeId],
a.[wExtDimId1]=b.[wExtDimId1],
a.modifieddate=getdate(),
a.modifytype='UPDATE';

测试数据示例

insert into [dbo].[slowmergetbl_source] values ('1234',2024,9,'Test1',0,'USD',500,1,500,4567,1788,202409,0,505489215);
insert into [dbo].[slowmergetbl_source] values ('6795',2024,10,'Test2',0,'USD',100,1,100,0986,8897,202410,0,515456210);

优化建议

1. 优化MERGE的NOT MATCHED BY SOURCE逻辑

当前WHEN NOT MATCHED BY SOURCE条件仅过滤wYear和wPeriod，但目标表仅靠单独的IDX_Year和IDX_Period索引无法高效定位这部分数据。创建联合非聚集索引：

CREATE NONCLUSTERED INDEX IDX_Year_Period_NormalID ON reporting.slowmergetbl_target (wYear, wPeriod, NormalID) INCLUDE (ModifyType, ModifiedDate);

该索引能让SQL Server快速定位到目标表中2024年9-10月的记录，避免全表扫描。

2. 拆分MERGE为独立的DELETE/INSERT/UPDATE操作

MERGE语句在处理大量数据时，容易出现执行计划效率低下的问题，尤其是同时包含三种操作类型时。拆分后可以针对性优化每个步骤：

标记删除：直接用UPDATE语句处理目标表中对应周期且不在源表的记录

UPDATE t
SET ModifiedDate = GETDATE(), ModifyType = 'DELETED'
FROM reporting.slowmergetbl_target t
WHERE t.wYear = 2024 AND t.wPeriod IN (9,10)
AND NOT EXISTS (SELECT 1 FROM dbo.slowmergetbl_source s WHERE s.NormalID = t.NormalID);

插入新记录：用INSERT...SELECT替换MERGE的插入分支

INSERT INTO reporting.slowmergetbl_target (
[sEntity],[wYear],[wPeriod],[sAccount],[wBracket],[sCurrency],[dValue],[bDirty],[dFactValue],[wEntityId],[wAccountId],[wTimeId],[wExtDimId1],[NormalID],[ModifiedDate],[ModifyType]
)
SELECT 
[sEntity],[wYear],[wPeriod],[sAccount],[wBracket],[sCurrency],[dValue],[bDirty],[dFactValue],[wEntityId],[wAccountId],[wTimeId],[wExtDimId1],[NormalID],GETDATE(),'INSERT'
FROM dbo.slowmergetbl_source s
WHERE NOT EXISTS (SELECT 1 FROM reporting.slowmergetbl_target t WHERE t.NormalID = s.NormalID);

更新变更记录：用UPDATE...FROM处理匹配且有变更的记录，同时简化字段比较逻辑（避免对每个字段都用ISNULL，可直接比较，NULL不等于任何值包括NULL）

UPDATE t
SET 
t.[sAccount] = s.[sAccount],
t.[sEntity] = s.[sEntity],
t.[wYear] = s.[wYear],
t.[wPeriod] = s.[wPeriod],
t.[wBracket] = s.[wBracket],
t.[wEntityId] = s.[wEntityId],
t.[wAccountId] = s.[wAccountId],
t.[sCurrency] = s.[sCurrency],
t.[dValue] = s.[dValue],
t.[bDirty] = s.[bDirty],
t.[dFactValue] = s.[dFactValue],
t.[wTimeId] = s.[wTimeId],
t.[wExtDimId1] = s.[wExtDimId1],
t.ModifiedDate = GETDATE(),
t.ModifyType = 'UPDATE'
FROM reporting.slowmergetbl_target t
JOIN dbo.slowmergetbl_source s ON t.NormalID = s.NormalID
WHERE 
t.[sAccount] <> s.[sAccount] OR t.[sEntity] <> s.[sEntity]
OR t.[wYear] <> s.[wYear] OR t.[wPeriod] <> s.[wPeriod]
OR t.[wBracket] <> s.[wBracket] OR ISNULL(t.[wEntityId],0) <> ISNULL(s.[wEntityId],0)
OR ISNULL(t.[wAccountId],0) <> ISNULL(s.[wAccountId],0) OR t.[sCurrency] <> s.[sCurrency]
OR t.[dValue] <> s.[dValue] OR t.[bDirty] <> s.[bDirty] OR t.[dFactValue] <> s.[dFactValue]
OR ISNULL(t.[wTimeId],0) <> ISNULL(s.[wTimeId],0) OR t.[wExtDimId1] <> s.[wExtDimId1];

注：对于允许NULL的字段，保留ISNULL处理，确保NULL和0被视为相等；非NULL字段直接比较即可。

3. 优化源表的筛选与预处理

如果源表中存在超出当前处理周期的数据（虽然描述中说仅存1-2个月），先过滤出目标周期的数据，减少JOIN的数据量：

-- 先创建临时表存储当前要处理的源数据
SELECT * INTO #SourceData
FROM dbo.slowmergetbl_source
WHERE wYear = 2024 AND wPeriod IN (9,10);

-- 后续的UPDATE/INSERT/UPDATE都使用#SourceData代替原源表

临时表默认会创建统计信息，且数据量更小，能提升JOIN效率。

4. 禁用目标表的非必要索引

在执行批量操作前，禁用除主键和上述新建的IDX_Year_Period_NormalID之外的非聚集索引（如IX_ReportQuery、IX_ReportQuery2），操作完成后再重建：

-- 禁用索引
ALTER INDEX IX_ReportQuery ON reporting.slowmergetbl_target DISABLE;
ALTER INDEX IX_ReportQuery2 ON reporting.slowmergetbl_target DISABLE;
ALTER INDEX IDX_Normal_ModifyType ON reporting.slowmergetbl_target DISABLE;

-- 执行批量操作

-- 重建索引
ALTER INDEX IX_ReportQuery ON reporting.slowmergetbl_target REBUILD;
ALTER INDEX IX_ReportQuery2 ON reporting.slowmergetbl_target REBUILD;
ALTER INDEX IDX_Normal_ModifyType ON reporting.slowmergetbl_target REBUILD;

批量更新时维护多个索引会大幅增加IO开销，临时禁用能显著提升速度。

5. 调整SQL Server批量操作配置

增大MAXDOP（最大并行度）：如果服务器有足够CPU资源，设置语句级并行度：

OPTION (MAXDOP 8); -- 根据服务器CPU核心数调整，如8核设为8

启用TABLOCK：在INSERT/UPDATE语句中添加WITH (TABLOCK)，减少锁竞争：

INSERT INTO reporting.slowmergetbl_target WITH (TABLOCK) (...) SELECT ...;

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

千万级数据行MERGE操作过慢问题优化咨询

源表建表语句 #

目标表建表语句 #

当前MERGE语句 #

测试数据示例 #

1. 优化MERGE的NOT MATCHED BY SOURCE逻辑 #

2. 拆分MERGE为独立的DELETE/INSERT/UPDATE操作 #

3. 优化源表的筛选与预处理 #

4. 禁用目标表的非必要索引 #

5. 调整SQL Server批量操作配置 #