含重复WHERE子句的用户分支查询能否优化？

你当前用于查询指定分支用户的SQL语句如下：

SELECT u.first, u.last, u.id, 
    (SELECT u1.user_type
    FROM users_branches AS u1
    WHERE u.id = u1.user_id AND u1.branch_id = @foo
    LIMIT 1)
FROM users AS u
WHERE EXISTS
    (SELECT 1
    FROM users_branches AS u0
    WHERE u.id = u0.user_id AND u0.branch_id = @foo)

对应的EF Core LINQ查询代码：

int branchId = 123;
var users = await Users
    .Where(x => x.UsersBranches.Any(ub => ub.BranchId == branchId))
    .Select(user => new
    {
        UserId = user.Id,
        FirstName = user.First,
        LastName = user.Last,
        UserType = user.UsersBranches.FirstOrDefault(x => x.BranchId == branchId).UserType
    }).ToListAsync();

你希望避免重复编写分支ID的筛选逻辑，同时获得更高效的查询效果，下面是几种更优的实现方式：

1. 使用INNER JOIN替代子查询与EXISTS #

这种方式可以一次性完成用户筛选和用户类型的获取，避免重复查询users_branches表：

优化后的SQL语句 #

SELECT u.first, u.last, u.id, ub.user_type
FROM users AS u
INNER JOIN users_branches AS ub ON u.id = ub.user_id AND ub.branch_id = @foo

注：如果一个用户在同一分支下有多条users_branches记录，会返回多条重复的用户数据；如果确保每个用户每个分支只有一条记录，这个写法最简洁高效。如果存在多条记录，可结合DISTINCT或GROUP BY去重。

对应的EF Core LINQ实现 #

int branchId = 123;
var users = await Users
    .Join(UsersBranches,
          user => user.Id,
          ub => ub.UserId,
          (user, ub) => new { user, ub })
    .Where(x => x.ub.BranchId == branchId)
    .Select(x => new
    {
        UserId = x.user.Id,
        FirstName = x.user.First,
        LastName = x.user.Last,
        UserType = x.ub.UserType
    })
    .ToListAsync();

或者利用导航属性的SelectMany简化：

int branchId = 123;
var users = await Users
    .SelectMany(user => user.UsersBranches.Where(ub => ub.BranchId == branchId),
                (user, ub) => new
                {
                    UserId = user.Id,
                    FirstName = user.First,
                    LastName = user.Last,
                    UserType = ub.UserType
                })
    .ToListAsync();

2. GROUP BY方案的适用性 #

如果你的场景中存在一个用户对应同一分支多条记录的情况，GROUP BY可以聚合用户数据，确保每个用户只返回一条结果：

SQL版本 #

SELECT u.first, u.last, u.id, MAX(ub.user_type) AS user_type
FROM users AS u
INNER JOIN users_branches AS ub ON u.id = ub.user_id AND ub.branch_id = @foo
GROUP BY u.first, u.last, u.id

这里用MAX只是示例，你可以根据实际需求选择合适的聚合函数（比如MIN，或者如果user_type在同用户同分支下一致，任意聚合函数都可以）。

EF Core LINQ版本 #

int branchId = 123;
var users = await Users
    .SelectMany(user => user.UsersBranches.Where(ub => ub.BranchId == branchId),
                (user, ub) => new { user, ub.UserType })
    .GroupBy(x => new { x.user.Id, x.user.First, x.user.Last })
    .Select(g => new
    {
        UserId = g.Key.Id,
        FirstName = g.Key.First,
        LastName = g.Key.Last,
        UserType = g.Max(x => x.UserType)
    })
    .ToListAsync();

性能对比 #

• JOIN方案：在用户与分支记录是一对一或一对多但不需要去重的场景下，性能最优，因为数据库只需执行一次关联操作，没有额外的聚合或子查询开销。
• GROUP BY方案：适合需要去重的一对多场景，但会增加聚合操作的开销，性能略低于单纯的JOIN。

相比你原来的写法，这两种方案都避免了重复的分支ID筛选逻辑，代码更简洁，同时减少了数据库的查询次数，整体性能更优。

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