Azure SQL特定查询超时（敏感索引）及EF Core查询优化咨询

问题背景 #

我们基于Azure SQL Server和EF Core 6开发，使用如下模型查询发票数据：

public class Invoice : OwnedByBase, ITransaction<LineItem>
{
    public int Id { get; set; }
    public List<LineItem> LineItems { get; set; }
//...
}

当前查询逻辑是筛选指定公司下、关联指定项目的发票，并加载对应的明细行：

var invoices = await dbContext.Invoices
    .AsSplitQuery()
    .Include(i => i.LineItems)
    .Where(i => i.CompanyGuid == companyGuid)
    .Where(i => i.LineItems.Any(idet => idet.ProjectGuid == projectGuid))
    .ToListAsync();

生成的SQL通过EXISTS关联LineItems表筛选数据，小租户下性能正常，但大租户数据量增长后出现30秒查询超时，且后续该租户所有查询均超时，重建/重组索引后恢复。除了定期维护索引，可通过改写查询进一步提升性能。

查询改写方案 #

1. 反向查询：从LineItems入手缩小数据集 #

先过滤出符合ProjectGuid的明细行，再关联发票并去重，避免直接扫描大体积的Invoices表。这种方式能利用ProjectGuid的索引快速定位小范围数据，减少后续关联的开销：

var invoices = await dbContext.LineItems
    .Where(li => li.ProjectGuid == projectGuid)
    .Where(li => li.Invoice.CompanyGuid == companyGuid)
    .Select(li => li.Invoice)
    .Distinct()
    .Include(i => i.LineItems)
    .AsSplitQuery()
    .ToListAsync();

2. 用Join替代Exists #

部分场景下，SQL Server查询优化器对JOIN的执行计划优化优于EXISTS，尤其是当索引统计信息出现波动时。注意添加Distinct避免因多明细行导致的发票重复：

var invoices = await dbContext.Invoices
    .Join(
        dbContext.LineItems.Where(li => li.ProjectGuid == projectGuid),
        invoice => invoice.Id,
        lineItem => lineItem.InvoiceId,
        (invoice, _) => invoice
    )
    .Where(i => i.CompanyGuid == companyGuid)
    .Distinct()
    .Include(i => i.LineItems)
    .AsSplitQuery()
    .ToListAsync();

3. 强制指定索引（极端场景） #

如果查询优化器未自动选择最优索引，可通过EF Core的索引提示功能强制使用目标索引。前提是LineItems表已创建复合索引IX_LineItems_ProjectGuid_InvoiceId（覆盖ProjectGuid和InvoiceId）：

var invoices = await dbContext.Invoices
    .AsSplitQuery()
    .Include(i => i.LineItems)
    .Where(i => i.CompanyGuid == companyGuid)
    .Where(i => i.LineItems.Any(idet => idet.ProjectGuid == projectGuid))
    .WithIndex(i => i.LineItems, "IX_LineItems_ProjectGuid_InvoiceId") // 替换为实际索引名
    .ToListAsync();

补充索引优化建议 #

除了查询改写，确保以下复合索引存在，能最大化查询效率：

• LineItems表：(ProjectGuid, InvoiceId) —— 覆盖筛选和关联字段，避免回表查询
• Invoices表：(OwnedBy, CompanyGuid, Id) —— 覆盖全局过滤器、公司筛选和排序字段

另外，开启Azure SQL的自动统计信息更新（默认已开启），定期更新统计信息比单纯重建索引更轻量，能帮助优化器生成更优的执行计划。

内容的提问来源于stack exchange，提问作者Superman.Lopez