如何优化SQL多对多关联查询：筛选全归档发票的采购订单

场景 #

Invoice（发票）与purchase_orders（采购订单）为多对多关联，中间表为npayment_links，包含外键invoice_id、purchase_order_id。

技术栈 #

Rails 5.x、PostgreSQL

示例数据 #

invoices表 #

purchase_orders表 #

npayment_links表 #

需求 #

编写SQL查询，返回所有关联发票均为archived状态的采购订单，预期结果为id=1（first po）和id=4（fourth po）的采购订单。

当前问题 #

现有Rails Active Record实现存在性能瓶颈，数据量大时查询效率极低：

Invoice.find(100).purchase_orders.each do |po|
  if po.invoices.all? { |inv| inv.archived? }
    # po.update(status: :done) # 后续将执行此类操作
  end
end

这段代码会触发N+1查询：先获取Invoice 100关联的采购订单，再对每个采购订单单独查询所有关联发票，数据量越大，查询次数呈线性增长，性能急剧下降。

优化方案 #

1. 纯SQL查询 #

推荐两种高效的SQL写法，均只需一次查询即可得到结果：

方法一：LEFT JOIN 排除法

SELECT DISTINCT po.id, po.title
FROM purchase_orders po
JOIN npayment_links pl ON po.id = pl.purchase_order_id
LEFT JOIN invoices inv ON pl.invoice_id = inv.id AND inv.status != 'archived'
WHERE inv.id IS NULL;

逻辑：将采购订单关联到中间表，再左连接到非归档状态的发票，过滤掉能匹配到非归档发票的记录，剩下的就是所有关联发票均为归档的采购订单。

方法二：GROUP BY + HAVING 统计法

SELECT po.id, po.title
FROM purchase_orders po
JOIN npayment_links pl ON po.id = pl.purchase_order_id
JOIN invoices inv ON pl.invoice_id = inv.id
GROUP BY po.id, po.title
HAVING COUNT(CASE WHEN inv.status != 'archived' THEN 1 END) = 0;

逻辑：按采购订单分组，统计每组中非归档发票的数量，数量为0的即为符合条件的采购订单。

2. Rails Active Record 优化写法 #

用Active Record语法实现上述SQL逻辑，避免N+1问题：

基于LEFT JOIN的写法

PurchaseOrder.joins(:invoices)
             .left_joins(:invoices)
             .where("invoices.status != 'archived'")
             .where(invoices: { id: nil })
             .distinct

基于GROUP BY + HAVING的写法

PurchaseOrder.joins(:invoices)
             .group(:id, :title)
             .having("COUNT(CASE WHEN invoices.status != 'archived' THEN 1 END) = 0")

批量更新优化

如果要批量更新符合条件的采购订单状态，直接用批量更新语句代替循环更新，性能提升显著：

PurchaseOrder.joins(:invoices)
             .group(:id)
             .having("COUNT(CASE WHEN invoices.status != 'archived' THEN 1 END) = 0")
             .update_all(status: :done)

性能优化补充建议 #

• 给以下字段创建索引，加速关联和过滤：
  • npayment_links.purchase_order_id、npayment_links.invoice_id（多对多外键索引）
  • invoices.status（过滤和统计时的查询索引）
• 始终避免在循环中执行数据库操作，优先用批量查询/更新替代。

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