MERGE 目标表的子集

一尘不染

MERGE 目标表的子集

sql

我正在尝试使用MERGE语句从表中插入或删除行，但我只想对这些行的子集进行操作。的文档MERGE有一个措辞非常强烈的警告：

仅指定目标表中用于匹配目的的列很重要。也就是说，指定目标表中与源表的对应列进行比较的列。不要试图通过在 ON 子句中过滤掉目标表中的行来提高查询性能，例如通过指定 AND NOT target_table.column_x = value。这样做可能会返回意外和不正确的结果。

但这正是我必须做的MERGE工作。

我拥有的数据是一个标准的多对多项目到类别的连接表（例如，哪些项目包含在哪些类别中），如下所示：

CategoryId   ItemId
==========   ======
1            1
1            2
1            3
2            1
2            3
3            5
3            6
4            5

我需要做的是用新的项目列表有效地替换特定类别中的所有行。我最初的尝试是这样的：

MERGE INTO CategoryItem AS TARGET
USING (
  SELECT ItemId FROM SomeExternalDataSource WHERE CategoryId = 2
) AS SOURCE
ON SOURCE.ItemId = TARGET.ItemId AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( CategoryId, ItemId )
    VALUES ( 2, ItemId )
WHEN NOT MATCHED BY SOURCE AND TARGET.CategoryId = 2 THEN
    DELETE ;

这似乎在我的测试中起作用，但我正在做 MSDN 明确警告我不要做的事情。这让我担心我以后会遇到意想不到的问题，但是我看不到任何其他方法可以使我MERGE只影响具有特定字段值 ( CategoryId = 2) 的行并忽略其他类别的行。

有没有“更正确”的方法来达到同样的结果？MSDN 警告我的“意外或不正确的结果”是什么？

阅读 84

2022-10-26

共1个答案

一尘不染

该MERGE语句具有复杂的语法和更复杂的实现，但本质上其想法是连接两个表，过滤到需要更改（插入、更新或删除）的行，然后执行请求的更改。给定以下示例数据：

DECLARE @CategoryItem AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL,

    PRIMARY KEY (CategoryId, ItemId),
    UNIQUE (ItemId, CategoryId)
);

DECLARE @DataSource AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL

    PRIMARY KEY (CategoryId, ItemId)
);

INSERT @CategoryItem
    (CategoryId, ItemId)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 3),
    (3, 5),
    (3, 6),
    (4, 5);

INSERT @DataSource
    (CategoryId, ItemId)
VALUES
    (2, 2);

目标

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          2 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      3 ║
║          3 ║      5 ║
║          4 ║      5 ║
║          3 ║      6 ║
╚════════════╩════════╝

资源

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

期望的结果是用源中的数据替换目标中的数据，但仅限于CategoryId = 2. 按照MERGE上面给出的描述，我们应该编写一个仅在键上连接源和目标的查询，并仅在WHEN子句中过滤行：

MERGE INTO @CategoryItem AS TARGET
USING @DataSource AS SOURCE ON 
    SOURCE.ItemId = TARGET.ItemId 
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY SOURCE 
    AND TARGET.CategoryId = 2 
    THEN DELETE
WHEN NOT MATCHED BY TARGET 
    AND SOURCE.CategoryId = 2 
    THEN INSERT (CategoryId, ItemId)
        VALUES (CategoryId, ItemId)
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

这给出了以下结果：

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
╚═════════╩════════════╩════════╝
╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      2 ║
║          3 ║      5 ║
║          3 ║      6 ║
║          4 ║      5 ║
╚════════════╩════════╝

执行计划是：合并计划

请注意，两个表都被完全扫描。我们可能认为这效率低下，因为只有目标表中的行CategoryId = 2才会受到影响。这就是联机丛书中的警告出现的地方。一种错误的尝试优化以仅接触目标中必要的行是：

MERGE INTO @CategoryItem AS TARGET
USING 
(
    SELECT CategoryId, ItemId
    FROM @DataSource AS ds 
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

子句中的逻辑ON作为连接的一部分应用。在这种情况下，联接是完全外部联接（请参阅此联机丛书条目了解原因）。作为外部连接的一部分，对目标行应用类别 2 的检查最终会导致删除具有不同值的行（因为它们与源不匹配）：

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          1 ║      1 ║
║ DELETE  ║          1 ║      2 ║
║ DELETE  ║          1 ║      3 ║
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
║ DELETE  ║          3 ║      5 ║
║ DELETE  ║          3 ║      6 ║
║ DELETE  ║          4 ║      5 ║
╚═════════╩════════════╩════════╝

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

根本原因与谓词在外连接ON子句中的行为与在子句中指定时的行为不同的原因相同WHERE。MERGE语法（以及取决于指定子句的连接实现）只是让我们更难看出这是真的。

联机丛书中的指南（在优化性能条目中扩展）提供的指南将确保使用MERGE语法表达正确的语义，而用户不必了解所有实现细节，或说明优化器可能合法地重新排列的方式出于执行效率的原因。

该文档提供了三种实现早期过滤的潜在方法：

WHEN在子句中指定过滤条件可以保证正确的结果，但可能意味着从源表和目标表中读取和处理的行数超过了严格必要的行数（如第一个示例所示）。

通过包含过滤条件的视图进行更新也可以保证正确的结果（因为必须可以访问更改的行以通过视图进行更新），但这确实需要一个专用视图，并且需要一个遵循更新视图的奇数条件的视图。

使用公用表表达式与将谓词添加到ON子句有类似的风险，但原因略有不同。在许多情况下它是安全的，但需要对执行计划进行专家分析以确认这一点（以及广泛的实际测试）。例如：

WITH TARGET AS 
(
    SELECT * 
    FROM @CategoryItem
    WHERE CategoryId = 2
)
MERGE INTO TARGET
USING 
(
    SELECT CategoryId, ItemId
    FROM @DataSource
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

这会产生正确的结果（不重复）和更优化的计划：

合并计划2

该计划仅从目标表中读取类别 2 的行。MERGE如果目标表很大，这可能是一个重要的性能考虑因素，但使用语法很容易出错。

MERGE有时，将它们编写为单独的 DML 操作更容易。这种方法甚至可以比单一的执行得更好MERGE，这一事实常常让人们感到惊讶。

DELETE ci
FROM @CategoryItem AS ci
WHERE ci.CategoryId = 2
AND NOT EXISTS 
(
    SELECT 1 
    FROM @DataSource AS ds 
    WHERE 
        ds.ItemId = ci.ItemId
        AND ds.CategoryId = ci.CategoryId
);

INSERT @CategoryItem
SELECT 
    ds.CategoryId, 
    ds.ItemId
FROM @DataSource AS ds
WHERE
    ds.CategoryId = 2;

2022-10-26