我有一个多对多关联的数据库关系,但是关联表本身包含了很多需要访问的属性,所以我做了三个类:

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
非常感谢您提供的任何指导。

最佳答案



您首先要先从relationship() 中去掉“lazy='subquery'”指令;修复关系以始终加载所有内容,这就是查询激增的原因。特别是在这里,您将完全针对 UserAttempt->Challenge 的每个延迟加载获得 Challenge->attemptseagerload,因此您在这里设计了最糟糕的加载组合:)。

有了这个固定,有两种方法。

一个是要记住,在通常情况下,多对一关联首先通过主键从内存中的 Session 获取,如果存在,则不会发出 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

如果您想将上述方法与“Select * from Challenge where id in (select challenge_id from attempt)”一起使用:
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,我想连接将返回与 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 是多对一的,加入可能更好:
users = session.query(User).\
                  options(subqueryload(User.attempts), joinedload(UserAttempt.challenge)).all()

仅来自 UserAttempt:
attempts = session.query(UserAttempt).\
                  options(joinedload(UserAttempt.challenge)).all()

关于python - SQLAlchemy 多对多性能,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15214967/

10-12 23:17