sql - 如何强制 MERGE 语句与 DELETE 使用索引查找？

Question

我为我的 Facebook 应用程序制作了 MS SQL 2014 数据库，该应用程序可以与 friend 一起使用。我在 DB 中为所有用户保留好友，并在应用程序启动时从 Facebook 更新他们。为此，我使用了 MERGE 语句(表变量 @FriendUserIds 包含好友 ID 列表；表 UserFriends 具有聚集主键(UserId、FriendUserId)):

MERGE UserFriends
    USING (
        SELECT
                UserId
            FROM @FriendUserIds
    ) AS source (FriendUserId)
        ON UserFriends.UserId = @UserId
            AND UserFriends.FriendUserId = source.FriendUserId
    WHEN NOT MATCHED BY TARGET
        THEN INSERT (UserId, FriendUserId)
            VALUES (@UserId, source.FriendUserId)
    WHEN NOT MATCHED BY SOURCE
        AND UserFriends.UserId = @UserId
        THEN DELETE;

问题是查询优化器无法识别它可以在 UserFriends 上使用 INDEX SEEK。它使用 SCAN 代替，我不知道强制 SEEK 的方法。现在，我通过将操作拆分为两个查询(用于添加新 friend 的 MERGE 和用于删除不再 friend 的 DELETE)来规避该问题，这仍然比单个 MERGE 语句快得多(没有 DELETE 语句的 MERGE 使用 SEEK):

DELETE
    FROM UserFriends
    WHERE UserFriends.UserId = @UserId
        AND UserFriends.FriendUserId NOT IN (
            SELECT
                    UF.UserId
                FROM @FriendUserIds UF
        )

MERGE UserFriends
    USING (
        SELECT
                UserId
            FROM @FriendUserIds
    ) AS source (FriendUserId)
        ON UserFriends.UserId = @UserId
            AND UserFriends.FriendUserId = source.FriendUserId
    WHEN NOT MATCHED BY TARGET
        THEN INSERT (UserId, FriendUserId)
            VALUES (@UserId, source.FriendUserId);

Answer 1

尝试使用通用表表达式 (CTE) 作为“目标”:

;WITH UserFriends_CTE
     AS (SELECT [UserID],
                [FriendUserID]
         FROM   [UserFriends]
         WHERE  [UserID] = @UserId)
MERGE UserFriends_CTE
USING (SELECT [UserId]
       FROM   @FriendUserIds) AS source ([FriendUserId])
ON UserFriends_CTE.[UserId] = @UserId
   AND UserFriends_CTE.[FriendUserId] = source.[FriendUserId]
WHEN NOT MATCHED BY TARGET THEN
  INSERT ([UserId],
          [FriendUserId])
  VALUES (@UserId,
          source.[FriendUserId])
WHEN NOT MATCHED BY SOURCE THEN
  DELETE; 

MERGE 语句的性能通常比拆分为多个语句要差，并且 there are a few known problems with MERGE 。使用 CTE可能导致问题 according to Paul White in this answer ，所以测试一下。

如果您确实使用拆分版本，我将如何实现它:

DELETE uf
FROM   [UserFriends] uf
WHERE  uf.[UserId] = @UserId
       AND NOT EXISTS
               (SELECT 1
                FROM   @FriendUserIds fu
                WHERE  uf.[FriendUserId] = fu.[FriendUserId]);

INSERT INTO [UserFriends]
            ([UserId],
             [FriendUserId])
SELECT @UserId,
       fu.[FriendUserId]
FROM   @FriendUserIds fu
WHERE  NOT EXISTS
           (SELECT 1
            FROM   [UserFriends] uf
            WHERE  fu.[FriendUserId] = uf.[FriendUserId]
                   AND uf.[UserId] = @UserId);