优化EF生成的双Exists+OR SQL查询：性能问题排查与重构

问题背景 #

我正在排查EF从LINQ生成的SQL查询性能极差的问题，核心结构是：

Select Count(*) From Table
Where Exists (A) or Exists(B)

已创建合适索引，SQL Server调优工具无优化建议，但单独执行任一Exists条件的计数查询都能秒出结果：

Select Count(*) From Table
Where Exists (A)

Select Count(*) From Table
Where Exists(B)

但用OR组合后，统计5万条数据耗时长达2分钟，完整SQL如下：

SELECT COUNT_BIG(*)  FROM [dbo].[TournamentParticipants] AS [t]
WHERE EXISTS 
( --检查记录是否共享给指定身份
        SELECT 1      
        FROM [dbo].[Permissions] AS [p1]      
        INNER JOIN [dbo].[RecordShares] AS [r] ON [p1].[Id] = [r].[PermissionId]      
        WHERE [r].[Identity] =  'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [r].[EntityName] = 'TournamentParticipants' AND [r].[RecordId] = [t].[Id] AND [p1].[Name] = 'TournamentParticipantsRead' 
)  
OR EXISTS 
( --RBAC检查：身份自身有权限，或所属组有权限      
    SELECT 1      
    FROM (          
        SELECT [p].[Name]          
        FROM [dbo].[Permissions] AS [p]          
        INNER JOIN [dbo].[SecurityRolePermissions] AS [s] ON [p].[Id] = [s].[PermissionId]          
        INNER JOIN [dbo].[SecurityRoleAssignments] AS [s0] ON [s].[SecurityRoleId] = [s0].[SecurityRoleId]          
        INNER JOIN (              
            SELECT [s1].[Id], [s1].[CreatedById], [s1].[CreatedOn], [s1].[ModifiedById], [s1].[ModifiedOn], [s1].[Name], [s1].[OwnerId], [s1].[RowVersion], [s1].[ExternalId], [s1].[IsBusinessUnit], [s2].[Id] AS [Id0], [s2].[CreatedById] AS [CreatedById0], [s2].[CreatedOn] AS [CreatedOn0], [s2].[IdentityId], [s2].[ModifiedById] AS [ModifiedById0], [s2].[ModifiedOn] AS [ModifiedOn0], [s2].[OwnerId] AS [OwnerId0], [s2].[RowVersion] AS [RowVersion0], [s2].[SecurityGroupId]              
            FROM [dbo].[SecurityGroups] AS [s1]              
            INNER JOIN [dbo].[SecurityGroupMembers] AS [s2] ON [s1].[Id] = [s2].[SecurityGroupId]              
            WHERE [s2].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'          
        ) AS [t1] ON [s0].[IdentityId] = [t1].[Id]          
        UNION          
        SELECT [p0].[Name]          
        FROM [dbo].[Permissions] AS [p0]          
        INNER JOIN [dbo].[SecurityRolePermissions] AS [s3] ON [p0].[Id] = [s3].[PermissionId]          
        INNER JOIN [dbo].[SecurityRoleAssignments] AS [s4] ON [s3].[SecurityRoleId] = [s4].[SecurityRoleId]          
        WHERE [s4].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'      
    ) AS [t0]      
    WHERE [t0].[Name] = 'TournamentParticipantsReadGlobal' OR 
    
        ([t0].[Name] = 'TournamentParticipantsRead' AND ([t].[OwnerId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'    OR EXISTS (
        SELECT 1          
        FROM [dbo].[SecurityGroups] AS [s5]          
        INNER JOIN [dbo].[SecurityGroupMembers] AS [s6] ON [s5].[Id] = [s6].[SecurityGroupId]          
        WHERE [s6].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [s5].[Id] = [t].[OwnerId]))) 
        
        OR ([t0].[Name] = 'TournamentParticipantsReadBU' AND EXISTS (
            SELECT 1          
            FROM [dbo].[SecurityGroupMembers] AS [s7]          
            INNER JOIN [dbo].[SecurityGroups] AS [s8] ON [s7].[SecurityGroupId] = [s8].[Id]          
            WHERE [s7].[IdentityId] = [t].[OwnerId] AND [s8].[IsBusinessUnit] = CAST(1 AS bit) AND EXISTS (
            SELECT 1              
            FROM [dbo].[SecurityGroupMembers] AS [s9]              
            WHERE [s9].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [s9].[SecurityGroupId] = [s8].[Id])))
)

性能问题原因 #

• 查询优化器执行计划失效：单独的Exists条件能利用索引快速定位数据，但OR组合后，SQL Server无法平衡两个差异较大的过滤逻辑，被迫采用全表扫描或低效嵌套循环，无法复用各自的高效执行计划。
• RBAC子查询复杂度叠加：第二个Exists内部包含多层嵌套、UNION和关联主表的条件，OR组合后主表每条记录都要同时验证两个复杂Exists条件，计算量呈指数级增长。
• 重复计算开销：OR条件下部分符合条件的记录会被两次检查，增加不必要的计算成本。

重构方案 #

方案1：拆分计数后求和（避免重复统计） #

将原查询拆分为两个独立的计数查询，用UNION ALL合并后求和，确保每个子查询沿用自身高效执行计划：

SELECT SUM(CountVal) AS TotalCount
FROM (
    -- 统计共享权限的记录数
    SELECT COUNT_BIG(*) AS CountVal
    FROM [dbo].[TournamentParticipants] AS [t]
    WHERE EXISTS (
        SELECT 1      
        FROM [dbo].[Permissions] AS [p1]      
        INNER JOIN [dbo].[RecordShares] AS [r] ON [p1].[Id] = [r].[PermissionId]      
        WHERE [r].[Identity] =  'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [r].[EntityName] = 'TournamentParticipants' AND [r].[RecordId] = [t].[Id] AND [p1].[Name] = 'TournamentParticipantsRead' 
    )
    UNION ALL
    -- 统计RBAC权限的记录数（排除已被共享权限统计的记录）
    SELECT COUNT_BIG(*) AS CountVal
    FROM [dbo].[TournamentParticipants] AS [t]
    WHERE EXISTS (
        SELECT 1      
        FROM (          
            SELECT [p].[Name]          
            FROM [dbo].[Permissions] AS [p]          
            INNER JOIN [dbo].[SecurityRolePermissions] AS [s] ON [p].[Id] = [s].[PermissionId]          
            INNER JOIN [dbo].[SecurityRoleAssignments] AS [s0] ON [s].[SecurityRoleId] = [s0].[SecurityRoleId]          
            INNER JOIN (              
                SELECT [s1].[Id], [s1].[CreatedById], [s1].[CreatedOn], [s1].[ModifiedById], [s1].[ModifiedOn], [s1].[Name], [s1].[OwnerId], [s1].[RowVersion], [s1].[ExternalId], [s1].[IsBusinessUnit], [s2].[Id] AS [Id0], [s2].[CreatedById] AS [CreatedById0], [s2].[CreatedOn] AS [CreatedOn0], [s2].[IdentityId], [s2].[ModifiedById] AS [ModifiedById0], [s2].[ModifiedOn] AS [ModifiedOn0], [s2].[OwnerId] AS [OwnerId0], [s2].[RowVersion] AS [RowVersion0], [s2].[SecurityGroupId]              
                FROM [dbo].[SecurityGroups] AS [s1]              
                INNER JOIN [dbo].[SecurityGroupMembers] AS [s2] ON [s1].[Id] = [s2].[SecurityGroupId]              
                WHERE [s2].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'          
            ) AS [t1] ON [s0].[IdentityId] = [t1].[Id]          
            UNION          
            SELECT [p0].[Name]          
            FROM [dbo].[Permissions] AS [p0]          
            INNER JOIN [dbo].[SecurityRolePermissions] AS [s3] ON [p0].[Id] = [s3].[PermissionId]          
            INNER JOIN [dbo].[SecurityRoleAssignments] AS [s4] ON [s3].[SecurityRoleId] = [s4].[SecurityRoleId]          
            WHERE [s4].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'      
        ) AS [t0]      
        WHERE [t0].[Name] = 'TournamentParticipantsReadGlobal' OR 
        
            ([t0].[Name] = 'TournamentParticipantsRead' AND ([t].[OwnerId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'    OR EXISTS (
            SELECT 1          
            FROM [dbo].[SecurityGroups] AS [s5]          
            INNER JOIN [dbo].[SecurityGroupMembers] AS [s6] ON [s5].[Id] = [s6].[SecurityGroupId]          
            WHERE [s6].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [s5].[Id] = [t].[OwnerId]))) 
            
            OR ([t0].[Name] = 'TournamentParticipantsReadBU' AND EXISTS (
                SELECT 1          
                FROM [dbo].[SecurityGroupMembers] AS [s7]          
                INNER JOIN [dbo].[SecurityGroups] AS [s8] ON [s7].[SecurityGroupId] = [s8].[Id]          
                WHERE [s7].[IdentityId] = [t].[OwnerId] AND [s8].[IsBusinessUnit] = CAST(1 AS bit) AND EXISTS (
                SELECT 1              
                FROM [dbo].[SecurityGroupMembers] AS [s9]              
                WHERE [s9].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [s9].[SecurityGroupId] = [s8].[Id])))
    )
    AND NOT EXISTS (
        SELECT 1      
        FROM [dbo].[Permissions] AS [p1]      
        INNER JOIN [dbo].[RecordShares] AS [r] ON [p1].[Id] = [r].[PermissionId]      
        WHERE [r].[Identity] =  'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [r].[EntityName] = 'TournamentParticipants' AND [r].[RecordId] = [t].[Id] AND [p1].[Name] = 'TournamentParticipantsRead' 
    )
) AS Counts

方案2：预计算有效ID后去重计数 #

先查询两类权限对应的记录ID，合并去重后统计总数，减少主表关联次数：

SELECT COUNT_BIG(DISTINCT Id) AS TotalCount
FROM (
    -- 获取共享权限的记录ID
    SELECT [t].[Id]
    FROM [dbo].[TournamentParticipants] AS [t]
    WHERE EXISTS (
        SELECT 1      
        FROM [dbo].[Permissions] AS [p1]      
        INNER JOIN [dbo].[RecordShares] AS [r] ON [p1].[Id] = [r].[PermissionId]      
        WHERE [r].[Identity] =  'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [r].[EntityName] = 'TournamentParticipants' AND [r].[RecordId] = [t].[Id] AND [p1].[Name] = 'TournamentParticipantsRead' 
    )
    UNION
    -- 获取RBAC权限的记录ID
    SELECT [t].[Id]
    FROM [dbo].[TournamentParticipants] AS [t]
    WHERE EXISTS (
        SELECT 1      
        FROM (          
            SELECT [p].[Name]          
            FROM [dbo].[Permissions] AS [p]          
            INNER JOIN [dbo].[SecurityRolePermissions] AS [s] ON [p].[Id] = [s].[PermissionId]          
            INNER JOIN [dbo].[SecurityRoleAssignments] AS [s0] ON [s].[SecurityRoleId] = [s0].[SecurityRoleId]          
            INNER JOIN (              
                SELECT [s1].[Id], [s1].[CreatedById], [s1].[CreatedOn], [s1].[ModifiedById], [s1].[ModifiedOn], [s1].[Name], [s1].[OwnerId], [s1].[RowVersion], [s1].[ExternalId], [s1].[IsBusinessUnit], [s2].[Id] AS [Id0], [s2].[CreatedById] AS [CreatedById0], [s2].[CreatedOn] AS [CreatedOn0], [s2].[IdentityId], [s2].[ModifiedById] AS [ModifiedById0], [s2].[ModifiedOn] AS [ModifiedOn0], [s2].[OwnerId] AS [OwnerId0], [s2].[RowVersion] AS [RowVersion0], [s2].[SecurityGroupId]              
                FROM [dbo].[SecurityGroups] AS [s1]              
                INNER JOIN [dbo].[SecurityGroupMembers] AS [s2] ON [s1].[Id] = [s2].[SecurityGroupId]              
                WHERE [s2].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'          
            ) AS [t1] ON [s0].[IdentityId] = [t1].[Id]          
            UNION          
            SELECT [p0].[Name]          
            FROM [dbo].[Permissions] AS [p0]          
            INNER JOIN [dbo].[SecurityRolePermissions] AS [s3] ON [p0].[Id] = [s3].[PermissionId]          
            INNER JOIN [dbo].[SecurityRoleAssignments] AS [s4] ON [s3].[SecurityRoleId] = [s4].[SecurityRoleId]          
            WHERE [s4].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'      
        ) AS [t0]      
        WHERE [t0].[Name] = 'TournamentParticipantsReadGlobal' OR 
        
            ([t0].[Name] = 'TournamentParticipantsRead' AND ([t].[OwnerId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61'    OR EXISTS (
            SELECT 1          
            FROM [dbo].[SecurityGroups] AS [s5]          
            INNER JOIN [dbo].[SecurityGroupMembers] AS [s6] ON [s5].[Id] = [s6].[SecurityGroupId]          
            WHERE [s6].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [s5].[Id] = [t].[OwnerId]))) 
            
            OR ([t0].[Name] = 'TournamentParticipantsReadBU' AND EXISTS (
                SELECT 1          
                FROM [dbo].[SecurityGroupMembers] AS [s7]          
                INNER JOIN [dbo].[SecurityGroups] AS [s8] ON [s7].[SecurityGroupId] = [s8].[Id]          
                WHERE [s7].[IdentityId] = [t].[OwnerId] AND [s8].[IsBusinessUnit] = CAST(1 AS bit) AND EXISTS (
                SELECT 1              
                FROM [dbo].[SecurityGroupMembers] AS [s9]              
                WHERE [s9].[IdentityId] = 'A0461D73-E979-449C-B24F-A3B3D6711D61' AND [s9].[SecurityGroupId] = [s8].[Id])))
    )
) AS ValidIds

对应的.NET LINQ重构（方案2实现） #

var identityId = Guid.Parse("A0461D73-E979-449C-B24F-A3B3D6711D61");

// 查询共享权限的记录ID
var sharedIds = db.TournamentParticipants
    .Where(t => db.Permissions
        .Join(db.RecordShares, p => p.Id, r => r.PermissionId, (p, r) => new { p,