Rails中基于has_many关联高效查询满足双条件的Person记录

需求明确 #

需要查询满足以下两个条件的Person记录：

• 该用户存在至少一条departure等于1个月前的Booking
• 该用户没有任何arrival≥当前时区今日的Booking

关联模型代码 #

class Booking
   has_many :persons, class_name: 'Person', through: :stays
end

class Stay
  belongs_to :booking, class_name: 'Booking'
  belongs_to :person, class_name: 'Person'
end

class Person
   has_many :bookings, class_name: 'Booking', through: :stays
end

错误查询的问题分析 #

你之前尝试的查询逻辑存在两处漏洞：

Person.joins(:bookings).where('bookings.departure < ? AND NOT EXISTS (SELECT 1 FROM bookings WHERE arrival >= ?)', 1.month.ago, Time.zone.today).find_each(&:clear)

1. NOT EXISTS子句未关联当前查询的Person，会错误过滤掉所有存在未来arrival Booking的Person，而非针对单个Person做判断；
2. 需求是departure等于1个月前，而非小于，条件匹配错误。

正确的数据库查询实现 #

可以直接通过Active Record生成高效SQL完成查询，无需结合Ruby逻辑处理，推荐两种写法：

写法一：子查询排除不符合条件的用户 #

target_date = 1.month.ago.to_date
today = Time.zone.today

Person.joins(:bookings)
      .where(bookings: { departure: target_date })
      .where.not(
        id: Person.joins(:bookings)
                  .where(bookings: { arrival: today.. })
                  .select(:id)
      )
      .distinct
      .find_each(&:clear)

写法二：LEFT JOIN + IS NULL判断 #

target_date = 1.month.ago.to_date
today = Time.zone.today

Person.joins(:bookings)
      .left_joins(:bookings => :stays)
      .where(bookings: { departure: target_date })
      .where('bookings_arrival.arrival >= ?', today)
      .where(bookings_arrival: { id: nil })
      .distinct
      .find_each(&:clear)

性能优化建议 #

针对380万条Person的大表场景，必须添加以下索引提升查询速度：

• 给stays表添加复合索引：add_index :stays, [:person_id, :booking_id]
• 给bookings表添加单字段索引：add_index :bookings, :departure、add_index :bookings, :arrival
• 可选优化：给bookings表添加复合索引add_index :bookings, [:departure, :arrival]，进一步压缩多条件查询的扫描范围

为什么不需要Ruby逻辑处理 #

你给出的循环判断方案会触发N+1查询（每个Person都要额外发起一次Booking查询），针对380万条数据来说，性能会极端低下，完全无法承受。而直接通过数据库查询可一次性完成筛选，利用索引将查询时间控制在可接受范围内。

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