项目
04/01/2012

优化 MERGE 语句性能

在 SQL Server 2008 中，通过使用 MERGE 语句，可以在单个语句中执行多个数据操作语言 (DML) 操作。例如，您可能需要根据在另一个表中找到的差异在一个表中插入、更新或删除行，从而对两个表进行同步。通常，可以通过执行包含各个 INSERT、UPDATE 和 DELETE 语句的存储过程或批处理来实现这一目的。然而，这意味着需要多次计算和处理源表和目标表中的数据；至少对每个语句计算和处理一次。

通过使用 MERGE 语句，可以使用单个语句替换各个 DML 语句。由于操作是在单个语句中执行的，因此可以提高查询性能，从而最大限度地减少处理源表和目标表中数据的次数。然而，性能的提升取决于是否进行了正确的索引和联接以及是否遵守了其他注意事项。本主题提供的最佳方法建议可帮助您在使用 MERGE 语句时获得最佳的性能。

有关索引的最佳做法

若要提高 MERGE 语句的性能，我们建议您遵循以下索引准则：

对源表的联接列创建唯一的涵盖索引。
对目标表的联接列创建唯一的聚集索引。

这些索引确保联接键唯一并且表中的数据经过排序。因为查询优化器不需要执行额外验证处理即可定位和更新重复的行，也不需要执行其他排序操作，所以查询性能得到了提高。

例如，在以下 MERGE 语句中，源表 dbo.Purchases 和目标表 dbo.FactBuyingHabits 在 ProductID 和 CustomerID 列上联接。若要提高此语句的性能，可以对 dbo.Purchases 表的 ProductID 和 CustomerID 列创建唯一键索引或主键索引（聚集或非聚集），对 dbo.FactBuyingHabits 表的 ProductID 和 CustomerID 列创建聚集索引。若要查看用于创建这些表的代码，请参阅使用 MERGE 插入、更新和删除数据。

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;

与 JOIN 有关的最佳做法

若要提高 MERGE 语句的性能并确保获得正确的结果，我们建议您遵循以下联接准则：

在 ON <merge_search_condition> 子句中仅指定可决定源表与目标表之间数据匹配标准的搜索条件。也就是说，仅指定与源表中的对应列进行比较的目标表列。不要包括与其他值（如常量）的比较。

若要从源表或目标表中筛选出行，请使用以下方法之一。

在适当的 WHEN 子句中指定用于行筛选的搜索条件。例如，WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT...。
对返回筛选行的源表或目标表定义视图，并且将该视图作为源表或目标表进行引用。如果该视图是针对目标表定义的，则针对该视图的任何操作都必须满足更新视图所需的条件。有关使用视图更新数据的详细信息，请参阅通过视图修改数据。
使用 WITH <通用表表达式> 子句从源表或目标表中筛选出行。此方法类似于在 ON 子句中指定附加搜索条件，并可能产生不正确的结果。建议您避免使用此方法，或者在采用它前进行全面测试。

有关详细信息，请参阅使用 MERGE 插入、更新和删除数据。

联接的查询优化

MERGE 语句中联接操作的优化方式与 SELECT 语句中联接操作的优化方式相同。也就是说，SQL Server 处理联接时，查询优化器会从多种可行的方法中选择最有效的方法来处理联接。有关联接的详细信息，请参阅联接基础知识和高级查询优化概念。如果源表和目标表的大小相似，并且对源表和目标表应用了先前在“有关索引的最佳做法”一节中介绍的索引准则，则 merge join 运算符是最有效的查询计划。这是由于对两个表都只扫描一次，并且无需对数据进行排序。如果源表小于目标表，则最好用 nested loops 运算符。

通过在 MERGE 语句中指定 OPTION (<query_hint>) 子句，可以强制使用某种特定联接。建议您不要将哈希联接用作 MERGE 语句的查询提示，因为该联接类型不使用索引。有关查询提示的详细信息，请参阅查询提示 (Transact-SQL)。以下示例在 OPTION 子句中指定了嵌套循环联接。

USE AdventureWorks2008R2;
GO
BEGIN TRAN;
MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) 
       FROM Sales.SalesOrderDetail AS sod
       JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
         AND soh.OrderDate BETWEEN '20030701' AND '20030731'
       GROUP BY ProductID) AS src(ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 
    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 
    THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*
OPTION (LOOP JOIN);
GO
ROLLBACK TRAN;

有关参数化的最佳做法

如果执行不带参数的 SELECT、INSERT、UPDATE 或 DELETE 语句，SQL Server 查询优化器可能会选择在内部对语句进行参数化处理。也就是说，使用参数替换查询中包含的任何文字值。例如，语句 INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) 可能在内部替换为 INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2) 来执行。此过程称为简单参数化，它可提高关系引擎将新的 SQL 语句与先前编译的现有执行计划进行匹配的能力。由于减少了查询编译和重新编译的频率，因此可提高查询性能。查询优化器不会对 MERGE 语句应用简单参数化过程。由于在每次执行 MERGE 语句时都需要编译一个新计划，因此包含文字值的 MERGE 语句的性能表现可能低于各个 INSERT、UPDATE 或 DELETE 语句。

若要提高查询性能，我们建议您遵循以下参数化准则：

参数化 MERGE 语句的 ON <merge_search_condition> 子句和 WHEN 子句中的所有文字值。例如，可以将 MERGE 语句合并到存储过程中，并用适当的输入参数替换文字值。
如果不能参数化该语句，可创建 TEMPLATE 类型的计划指南，并在计划指南中指定 PARAMETERIZATION FORCED 查询提示。有关详细信息，请参阅使用计划指南指定查询参数化行为。
如果频繁对数据库执行 MERGE 语句，请考虑将数据库的 PARAMETERIZATION 选项设置为 FORCED。设置此选项时请谨慎从事。PARAMETERIZATION 选项是数据库级别设置，并且影响针对数据库的所有查询的处理方式。有关详细信息，请参阅强制参数化。

与 TOP 子句有关的最佳做法

在 MERGE 语句中，TOP 子句指定在对源表和目标表进行联接之后（或在删除不符合执行插入、更新或删除操作条件的行之后）受影响的行的数量或百分比。TOP 子句将联接行的数量进一步减少为指定值，并且以一种无序方式对其余联接行应用插入、更新或删除操作。也就是说，在 WHEN 子句中定义的操作中，这些行是无序分布的。例如，如果指定 TOP (10)，将会影响 10 行；在这些行中，可能会更新 7 行而插入 3 行，或者可能删除 1 行，更新 5 行并且插入 4 行，依此类推。

使用 TOP 子句对大型表分批执行数据操作语言 (DML) 操作是一种常见的做法。如果出于此目的而在 MERGE 语句中使用 TOP 子句，请务必了解以下影响。

I/O 性能可能会受到影响。

MERGE 语句对源表和目标表都进行完全表扫描。使操作分批执行可减少每批执行的写入操作的数量；但在每个批处理中都将对源表和目标表执行完全表扫描。产生的读取活动可能会影响查询的性能。
可能产生不正确的结果。

务必确保所有后续批处理都以新行作为处理目标，否则可能发生意想不到的行为，例如在目标表中错误地插入重复的行。如果源表包含的某行未包括在目标批处理中，但却包含在总目标表中，便会发生此情况。

确保获得正确的结果：
- 使用 ON 子句确定哪些源行影响现有目标行以及哪些是全新的。
- 在 WHEN MATCHED 子句中使用附加条件来确定目标行是否已由先前的批处理进行了更新。
因为只有在应用这些子句之后才会应用 TOP 子句，所以每次执行会插入一个确实不匹配的行，或者更新一个现有行。以下示例将创建源表和目标表，然后展示使用 TOP 子句在批处理操作中修改目标的正确方法。
```
CREATE TABLE dbo.inventory(item_key int NOT NULL PRIMARY KEY, amount int, is_current bit);
GO
CREATE TABLE dbo.net_changes(item_key int NOT NULL PRIMARY KEY, amount int);
GO

MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key
WHEN MATCHED AND inventory.is_current = 0
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) VALUES(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```
以下示例说明了实现 TOP 子句的错误方法。对 is_current 列的检查是在与源表的联接条件中指定的。也就是说，在一个批处理中使用的源行将在下一个批处理中被视为“不匹配”，从而导致不需要的插入操作。
```
MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key AND inventory.is_current = 0
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```
以下示例也说明了一种不正确的方法。通过使用通用表表达式 (CTE) 来限制为批处理读取的行数，任何具有匹配目标行的源行（通过 TOP(1) 选择的行除外）都会被视为“不匹配”，从而导致不需要的插入操作。此外，此方法仅限制可以更新的行的数量；每个批处理都将尝试插入所有“不匹配”的源行。
```
WITH target_batch AS (
  SELECT TOP(1) *
  FROM dbo.inventory
  WHERE is_current = 0
  )
MERGE target_batch
USING dbo.net_changes
ON target_batch.item_key = net_changes.item_key
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```

有关大容量加载的最佳做法

通过将 OPENROWSET(BULK…) 子句指定为表源，可使用 MERGE 语句高效地将源数据文件中的数据大容量加载到目标表中。通过这种方式，可以在单个批中处理整个文件。

若要改进大容量合并过程的性能，我们建议您遵循以下准则：

对目标表中的联接列创建聚集索引。
在 OPENROWSET (BULK … ) 子句中使用 ORDER 和 UNIQUE 提示以指定如何对源数据文件排序。

默认情况下，大容量操作假定数据文件未排序。因此，请务必根据目标表的聚集索引对源数据进行排序并使用 ORDER 提示指示该顺序，以便查询优化器可以生成更有效的查询计划。在运行时将对提示进行验证；如果数据流不符合指定的提示，则会引发错误。

上述准则确保联接键唯一并且源文件中数据的排序顺序与目标表相符。因为不需要执行其他排序操作和不必要的数据复制，所以提高了查询的性能。下面的示例使用 MERGE 语句将平面文件 StockData.txt 中的数据大容量加载到目标表 dbo.Stock 中。通过对目标表中的 StockName 定义主键约束，对与源数据进行联接的该列创建了一个聚集索引。对数据源中的 Stock 列应用 ORDER 和 UNIQUE 提示，该列映射到目标表中的聚集索引键列。

运行此示例之前，请在文件夹 C:\SQLFiles\ 中创建一个名为“StockData.txt”的文本文件。此文件应具有两列以逗号分隔的数据。例如，使用下面的数据。

阿尔派山地自行车 100 辆

Brake set,22

Cushion,5

接下来，在文件夹 C:\SQLFiles\ 中创建名为“BulkloadFormatFile.xml”的 xml 格式的文件。请使用以下信息。

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Stock (StockName nvarchar(50) PRIMARY KEY, Qty int CHECK (Qty > 0));
GO
MERGE dbo.Stock AS s
USING OPENROWSET (
    BULK 'C:\SQLFiles\StockData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000,
    ORDER (Stock) UNIQUE) AS b
ON s.StockName = b.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta);
GO

测量和诊断 MERGE 性能

以下功能可帮助您测量和诊断 MERGE 语句的性能。

在 sys.dm_exec_query_optimizer_info 动态管理中使用 merge stmt 计数器可返回针对 MERGE 语句进行的查询优化次数。
在 sys.dm_exec_plan_attributes 动态管理函数中使用 merge_action_type 属性可返回用作 MERGE 语句结果的触发器执行计划的类型。
使用 SQL 跟踪收集 MERGE 语句的故障排除数据，其方式与用于其他数据操作语言 (DML) 语句的方式相同。有关详细信息，请参阅SQL 跟踪简介。