LEFT JOIN替代方案：优化Invoice/Note关联DocumentBookingDate的慢查询

表结构 #

Invoice表 #

Id| Number | Amount
1 |   001  |  10

Note表 #

Id| Number | Amount
2 |   002  |  20

DocumentBookingDate表（外键关联：InvoiceId对应Invoice.Id，NoteId对应Note.Id） #

NoteId| InvoiceId | Date
 NULL |   1       | 02-02-2022
  2   |   NULL    | 02-03-2022  

原查询及性能问题 #

原查询通过LEFT JOIN关联日期表后执行UNION ALL，性能极差；移除LEFT JOIN后速度恢复正常，且存在部分Invoice/Note无对应预订日期的场景：

SELECT inv.Id as ID, inv.Number as Number, inv.Amount, dbd.Date as BookingDate 
FROM Invoice inv
LEFT JOIN DocumentBookingDate dbd on dbd.InvoiceId = inv.Id

UNION ALL 

SELECT note.Id as ID, note.Number as Number, note.Amount, dbd.Date as BookingDate 
FROM Note note
LEFT JOIN DocumentBookingDate dbd on dbd.NoteId = note.Id

将LEFT JOIN移至UNION ALL外部后，查询速度进一步变慢。

替代优化方案 #

1. 子查询过滤后关联 #

先分别提取Invoice和Note对应的日期数据，减少JOIN时的数据集，避免重复全表扫描日期表：

SELECT inv.Id as ID, inv.Number as Number, inv.Amount, inv_dates.Date as BookingDate
FROM Invoice inv
LEFT JOIN (
    SELECT InvoiceId, Date
    FROM DocumentBookingDate
    WHERE InvoiceId IS NOT NULL
) inv_dates ON inv_dates.InvoiceId = inv.Id

UNION ALL

SELECT note.Id as ID, note.Number as Number, note.Amount, note_dates.Date as BookingDate
FROM Note note
LEFT JOIN (
    SELECT NoteId, Date
    FROM DocumentBookingDate
    WHERE NoteId IS NOT NULL
) note_dates ON note_dates.NoteId = note.Id

子查询提前过滤出仅属于Invoice/Note的记录，让数据库能更高效地利用索引，减少JOIN的计算开销。

2. 使用OUTER APPLY（适用于SQL Server等支持的数据库） #

如果每个Invoice/Note对应最多一条日期记录，用APPLY替代LEFT JOIN可以生成更轻量的执行计划：

SELECT inv.Id as ID, inv.Number as Number, inv.Amount, dbd.Date as BookingDate
FROM Invoice inv
OUTER APPLY (
    SELECT TOP 1 Date
    FROM DocumentBookingDate
    WHERE InvoiceId = inv.Id
) dbd

UNION ALL

SELECT note.Id as ID, note.Number as Number, note.Amount, dbd.Date as BookingDate
FROM Note note
OUTER APPLY (
    SELECT TOP 1 Date
    FROM DocumentBookingDate
    WHERE NoteId = note.Id
) dbd

APPLY会逐行匹配主表数据，避免LEFT JOIN可能带来的不必要数据膨胀，配合索引使用时性能提升明显。

3. 补充针对性索引 #

性能差的核心原因大概率是缺少合适的索引，给DocumentBookingDate表添加两个关联专用索引：

-- 针对Invoice关联的索引
CREATE INDEX IX_DocumentBookingDate_InvoiceId 
ON DocumentBookingDate(InvoiceId) 
INCLUDE(Date);

-- 针对Note关联的索引
CREATE INDEX IX_DocumentBookingDate_NoteId 
ON DocumentBookingDate(NoteId) 
INCLUDE(Date);

添加索引后，原查询的性能可能直接达标，无需修改查询逻辑。

4. 统一数据结构后单次关联 #

先合并Invoice、Note的数据集，同时将日期表的数据统一格式，再进行一次JOIN：

WITH UnifiedDates AS (
    SELECT InvoiceId AS DocId, Date FROM DocumentBookingDate WHERE InvoiceId IS NOT NULL
    UNION ALL
    SELECT NoteId AS DocId, Date FROM DocumentBookingDate WHERE NoteId IS NOT NULL
),
AllDocs AS (
    SELECT Id AS DocId, Number, Amount FROM Invoice
    UNION ALL
    SELECT Id AS DocId, Number, Amount FROM Note
)
SELECT ad.DocId AS ID, ad.Number, ad.Amount, ud.Date AS BookingDate
FROM AllDocs ad
LEFT JOIN UnifiedDates ud ON ad.DocId = ud.DocId

这种方式减少了JOIN的次数，适合数据量较大的场景，让数据库可以一次性处理所有关联逻辑。

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