SQLAlchemy 多对多性能

Question

我有一个数据库关系，涉及到多对多的关联，但这个关联表本身包含了很多需要访问的属性，所以我创建了三个类：

class User(Base):
    id = Column(Integer, primary_key=True)
    attempts = relationship("UserAttempt", backref="user", lazy="subquery")

class Challenge(Base):
    id = Column(Integer, primary_key=True)
    attempts = relationship("UserAttempt", backref="challenge", lazy='subquery')

class UserAttempt(Base):
    challenge_id = Column(Integer, ForeignKey('challenge.id'), primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)

当然，这只是一个简化的例子，我省略了其他需要访问的属性。这里的目的是，每个 User 可以尝试任意数量的 Challenge，因此有了 UserAttempt 表，这个表描述了某个用户在某个挑战上的尝试。

现在的问题是：当我查询所有用户，然后查看每个尝试时，一切都很好。但是当我查看这个尝试的挑战时，就会出现很多子查询。这显然对性能不好。

我其实想要的是从 SQLAlchemy 中一次性拉取所有（或相关的）挑战，然后把它们和相关的尝试关联起来。如果拉取所有挑战也没关系，或者只拉取那些有实际关联的挑战也可以，因为挑战的数量只有100到500个之间。

我现在的解决方案其实并不优雅：我分别拉取所有相关的尝试、挑战和用户，然后手动关联：遍历所有尝试，把挑战和用户添加进去，然后再把挑战和用户添加到尝试中。这对我来说感觉像是一种粗暴的解决方案，应该不需要这样。

然而，每种方法（比如调整“懒加载”参数、改变查询等）都导致查询数量从几百到几千。我也尝试写简单的 SQL 查询，得到我想要的结果，像是 SELECT * FROM challenge WHERE id IN (SELECT challenge_id FROM attempts)，这个查询效果很好，但我无法把它转换成 SQLAlchemy 的形式。

非常感谢你们提供的任何指导。

Answer 1

我其实想要的是一次性从SQLAlchemy中获取所有（或者所有相关的）挑战，然后把它们和相关的尝试关联起来。如果获取所有挑战或者只获取那些后面有实际关联的挑战，这对我来说都没什么大不了的。

首先，你需要把relationship()中的“lazy='subquery'”这个指令去掉；因为总是加载所有内容的关系设置，导致你查询的数量暴增。具体来说，你在每次懒加载UserAttempt->Challenge时，都会得到Challenge->attempts的预加载，这样设计出来的加载组合是最糟糕的 :）。

解决这个问题后，有两种方法可以选择。

第一种是记住，通常情况下，多对一的关联是先从会话中通过主键在内存中获取的，如果找到了，就不会发出SQL查询。因此，我觉得你可以用我常用的一种技巧，达到你想要的效果：

all_challenges = session.query(Challenge).all()

for user in some_users:    # however you got these
    for attempt in user.attempts:   # however you got these
        do_something_with(attempt.challenge)  # no SQL will be emitted

如果你想用上面的方法，确切地说是“从挑战中选择所有，条件是id在（从尝试中选择challenge_id）中”：

all_challenges = session.query(Challenge).\
                  filter(Challenge.id.in_(session.query(UserAttempt.challenge_id))).all()

不过，使用JOIN可能会更高效：

all_challenges = session.query(Challenge).\
                  join(Challenge.attempts).all()

或者DISTINCT，我想JOIN会返回与UserAttempt中相同的challenge.id：

all_challenges = session.query(Challenge).distinct().\
                  join(Challenge.attempts).all()

第二种方法是更具体地使用预加载。你可以在一个查询中同时查询多个用户/尝试/挑战，这样会发出三个SELECT语句：

users = session.query(User).\
              options(subqueryload_all(User.attempts, UserAttempt.challenge)).all()

或者因为UserAttempt->Challenge是多对一的，使用JOIN可能会更好：

users = session.query(User).\
                  options(subqueryload(User.attempts), joinedload(UserAttempt.challenge)).all()

仅从UserAttempt获取：

attempts = session.query(UserAttempt).\
                  options(joinedload(UserAttempt.challenge)).all()