全量Office关联Plan数据查询及SQL性能优化需求

我来帮你解决这个大规模数据下的SQL性能问题。先明确你的核心需求：为每个Office返回对应的Plan配置——如果Office有独立的Plan记录就用自己的，没有则继承Firm级的默认配置（也就是OfficeId为NULL的那条）。你的原查询性能差主要是因为关联逻辑绕了不必要的弯路，而且没有充分利用索引，下面是具体的优化方案：

优先推荐第一种方案，逻辑清晰且性能高效：

方案一：直接关联+COALESCE #

SELECT
    COALESCE(b.Id, a.Id) AS Id,
    o.Firm_Id,
    o.Office_id,
    COALESCE(b.PlanId, a.PlanId) AS PlanID
FROM Office o
-- 关联当前Firm的默认Plan配置
LEFT JOIN FirmOfficePlan a 
    ON a.Firm_Id = o.Firm_Id 
    AND a.Office_Id IS NULL
-- 关联当前Office的专属Plan配置（如果存在）
LEFT JOIN FirmOfficePlan b 
    ON b.Firm_Id = o.Firm_Id 
    AND b.Office_Id = o.Office_id
WHERE o.Firm_Id = 54;

为什么这个写法更快？ #

• 直接从Office表出发，省去了原查询中绕到Firm表再关联的多余步骤，减少了关联层级
• 两次LEFT JOIN都是基于Firm_Id的等值匹配，数据库能更高效地执行关联逻辑
• 没有嵌套子查询，执行计划会更简洁，数据库优化器更容易生成高效的执行路径

方案二：使用APPLY操作符（适配SQL Server/PostgreSQL等） #

如果你的数据库支持APPLY操作符，这个写法在处理大量Office时也很高效，逻辑上更直观：

SELECT
    COALESCE(p.Id, fp.Id) AS Id,
    o.Firm_Id,
    o.Office_id,
    COALESCE(p.PlanId, fp.PlanId) AS PlanID
FROM Office o
-- 获取当前Firm的默认Plan
CROSS APPLY (
    SELECT Id, PlanId 
    FROM FirmOfficePlan 
    WHERE Firm_Id = o.Firm_Id 
      AND Office_Id IS NULL
) fp
-- 获取当前Office的专属Plan（不存在则返回NULL）
OUTER APPLY (
    SELECT Id, PlanId 
    FROM FirmOfficePlan 
    WHERE Firm_Id = o.Firm_Id 
      AND Office_Id = o.Office_id
) p
WHERE o.Firm_Id = 54;

性能提升的关键是让数据库快速定位数据，避免全表扫描。一定要给这两个表创建以下索引：

给FirmOfficePlan表创建复合索引 #

CREATE NONCLUSTERED INDEX IX_FirmOfficePlan_FirmOffice 
ON FirmOfficePlan (Firm_Id, Office_Id)
INCLUDE (Id, PlanId);

这个索引让数据库可以直接通过Firm_Id和Office_Id快速找到对应的记录，不需要扫描整个表。

给Office表创建索引 #

CREATE NONCLUSTERED INDEX IX_Office_FirmId 
ON Office (Firm_Id)
INCLUDE (Office_id);

这个索引能让数据库快速筛选出指定Firm下的所有Office，避免全表扫描Office表。

执行优化后的SQL时，建议查看数据库的执行计划，确保：

• 没有出现Table Scan或Clustered Index Scan（小表除外）
• 所有关联操作都使用了我们创建的索引（显示为Index Seek）

这样调整后，即使面对8万+Firm、每个Firm30-40个Office的场景，查询性能会有非常明显的提升。

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