如何修复 Python ORM 工具 SQLAlchemy 的常见陷阱

对象关系映射 (ORM) 让应用程序开发人员的生活更轻松，这在很大程度上是因为它允许您使用您可能熟悉的语言（例如 Python）而不是原始 SQL 查询来与数据库进行交互。SQLAlchemy 是一个 Python ORM 工具包，它提供使用 Python 访问 SQL 数据库的功能。它是一个成熟的 ORM 工具，增加了模型关系、强大的查询构建范例、简单的序列化等等优点。然而，它的易用性使得人们很容易忘记幕后发生的事情。使用 SQLAlchemy 做出的看似微小的选择可能会对性能产生重要影响。

本文解释了开发人员在使用 SQLAlchemy 时遇到的一些主要性能问题以及如何修复它们。

当您只需要计数时却检索了整个结果集

有时开发人员只需要结果的计数，但他们没有使用数据库计数，而是获取所有结果并在 Python 中使用 len 进行计数。

count = len(User.query.filter_by(acct_active=True).all())

使用 SQLAlchemy 的 count 方法将在服务器端进行计数，从而大大减少发送到客户端的数据量。在前一个示例中调用 all() 也会导致模型对象的实例化，如果行数足够多，这可能会很快变得非常昂贵。

除非需要计数以外的更多信息，否则只需使用 count 方法。

count = User.query.filter_by(acct_active=True).count()

当您只需要几个列时却检索了整个模型

在许多情况下，发出查询时只需要几个列。SQLAlchemy 可以只获取您感兴趣的列，而不是返回整个模型实例。这不仅减少了发送的数据量，还避免了实例化整个对象的需要。使用列数据元组而不是模型可能会快得多。

result = User.query.all()
for user in result:
    print(user.name, user.email)

相反，使用 with_entities 方法仅选择所需的列。

result = User.query.with_entities(User.name, User.email).all()
for (username, email) in result:
    print(username, email)

在循环内一次更新一个对象

避免使用循环来单独更新集合。虽然数据库可以非常快速地执行单个更新，但应用程序和数据库服务器之间的往返时间会迅速累积。总的来说，在合理的情况下，尽量减少查询次数。

for user in users_to_update:
  user.acct_active = True
  db.session.add(user)

请改用批量更新方法。

query = User.query.filter(user.id.in_([user.id for user in users_to_update]))
query.update({"acct_active": True}, synchronize_session=False)

触发级联删除

ORM 允许轻松配置模型上的关系，但有些细微的行为可能会令人惊讶。大多数数据库通过外键和各种级联选项来维护关系完整性。SQLAlchemy 允许您使用外键和级联选项定义模型，但 ORM 有自己的级联逻辑，可能会抢占数据库。

考虑以下模型。

class Artist(Base):
    __tablename__ = "artist"

    id = Column(Integer, primary_key=True)
    songs = relationship("Song", cascade="all, delete")

class Song(Base):
    __tablename__ = "song"

    id = Column(Integer, primary_key=True)
    artist_id = Column(Integer, ForeignKey("artist.id", ondelete="CASCADE"))

删除艺术家将导致 ORM 在 Song 表上发出 delete 查询，从而阻止由于外键而发生的删除。对于复杂的关系和大量记录，此行为可能会成为瓶颈。

包含 passive_deletes 选项以确保数据库正在管理关系。但是，请确保您的数据库能够做到这一点。例如，SQLite 默认情况下不管理外键。

songs = relationship("Song", cascade="all, delete", passive_deletes=True)

当应该使用预先加载时却依赖惰性加载

惰性加载是 SQLAlchemy 关系的默认方法。从最后一个示例构建，这意味着加载艺术家不会同时加载他或她的歌曲。这通常是一个好主意，但如果某些关系始终需要加载，则单独的查询可能会造成浪费。

如果允许以惰性方式加载关系，则像 Marshmallow 这样的流行序列化框架可能会触发级联查询。

有几种方法可以控制此行为。最简单的方法是通过关系函数本身。

songs = relationship("Song", lazy="joined", cascade="all, delete")

这将导致左连接添加到任何艺术家的查询中，因此，songs 集合将立即可用。虽然返回给客户端的数据更多，但往返次数可能会少得多。

SQLAlchemy 为无法采用这种全面方法的情况提供了更细粒度的控制。joinedload() 函数可用于在每个查询的基础上切换连接加载。

from sqlalchemy.orm import joinedload

artists = Artist.query.options(joinedload(Artist.songs))
print(artists.songs) # Does not incur a roundtrip to load

使用 ORM 进行批量记录导入

当导入数千条记录时，构建完整模型实例的开销将成为主要的瓶颈。例如，想象一下从文件中加载数千首歌曲记录，其中每首歌曲首先被转换为字典。

for song in songs:
    db.session.add(Song(**song))

相反，绕过 ORM 并仅使用核心 SQLAlchemy 的参数绑定功能。

batch = []
insert_stmt = Song.__table__.insert()
for song in songs:
    if len(batch) > 1000:
       db.session.execute(insert_stmt, batch)
       batch.clear()
    batch.append(song)
if batch:
    db.session.execute(insert_stmt, batch)

请记住，此方法自然会跳过您可能依赖的任何客户端 ORM 逻辑，例如基于 Python 的列默认值。虽然此方法比将对象加载为完整模型实例更快，但您的数据库可能具有更快的批量加载方法。例如，PostgreSQL 具有 COPY 命令，该命令可能为加载大量记录提供最佳性能。

过早调用 commit 或 flush

在许多情况下，您需要将子记录与其父记录关联，反之亦然。一种显而易见的方法是刷新会话，以便将 ID 分配给有问题的记录。

artist = Artist(name="Bob Dylan")
song = Song(title="Mr. Tambourine Man")

db.session.add(artist)
db.session.flush()

song.artist_id = artist.id

每个请求提交或刷新多次通常是不必要的和不受欢迎的。数据库刷新涉及强制数据库服务器上的磁盘写入，并且在大多数情况下，客户端将阻塞，直到服务器可以确认数据已写入。

SQLAlchemy 可以在幕后跟踪关系和管理键。

artist = Artist(name="Bob Dylan")
song = Song(title="Mr. Tambourine Man")

artist.songs.append(song)

总结

我希望这份常见陷阱列表可以帮助您避免这些问题并保持应用程序平稳运行。与往常一样，在诊断性能问题时，测量是关键。大多数数据库都提供性能诊断，可以帮助您查明问题，例如 PostgreSQL pg_stat_statements 模块。