LoginSignup
9
3

More than 1 year has passed since last update.

LIGHTzAdvent Calendar 2022Day 14lightz
@nassy20in株式会社LIGHTz

SQLAlchemy の joined loading (eager loading) の動作を確認。N+1問題を倒す。

Posted at

はじめに

先日、N+1 問題による web API のスループット低下に直面しました。(私は 1+N と呼びたいが、以降 N+1)
N+1 問題の解決方法の1つに eager loading (SQLAlchemy では joined loading)が有効な手段の1つであることを知りました。
joined loading がどのような動きをするのか調べてみたいと思います。

N+1 問題 とは

リレーションが貼られている2つのテーブルのデータをORMを利用して取得した際に引き起こされる、スループット低下問題として知られているそうです🤔

ex. Post に対するいいね情報を取り出す

  • Post テーブル と いいね テーブルにリレーションがはられている
  • Post の一覧を取得するのに、1回のクエリが投げられ、N個のPostオブジェクトを取得
  • N個のPost オブジェクトからいいねの情報を取得する際に、N回クエリが投げられる

joined loading とは

今回確認する対象の joined loading とは、 relationship のロード方法の1つです。
relationship のロード方法は大きく分類すると、以下の3つに分けられます。

  • lazy loading
  • eager loading
  • no loading

joined loading は eager loading に分類される方法です。

さらに、公式ドキュメントで lazy loading、 eager loading、 no loading について確認してみましょう。

(本家ドキュメントを DeepL 翻訳したものを修正しています。)
SQLAlchemy の大きな特徴は、クエリ実行時に関連オブジェクトをどのように読み込むかを幅広く制御できることです 。~中略~ リレーションシップのロードは、レイジー・ロード、イーガー・ロード、ノー・ロードの3つのカテゴリに分類されます。レイジーローディングとは、クエリから返されるオブジェクトが、関連するオブジェクトを最初に読み込むことなく返されることを指します。与えられたコレクションや参照が特定のオブジェクトに最初にアクセスされたとき、要求されたコレクションがロードされるように、追加のSELECT文が発行されます。イーガーロードとは、関連するコレクションやスカラー参照がすでにロードされた状態でクエリから返されるオブジェクトを指します。Query は、通常 JOIN で発行される SELECT 文を補強して関連する行を同時にロードするか、主文の後に追加の SELECT 文を発行してコレクションまたはスカラー参照を一度にロードすることでこれを実現します。

...分かるような、分からないような感じですね😊

戻って、 joined loading について本家ドキュメントを確認してみましょう。

この形式のロードは、指定した SELECT 文に JOIN を適用し、関連する行が同じ結果セットにロードされるようにするものです。

"関連する行が同じ結果セットにロードされるようにする" の部分がよく分からないので動作を確認してみましょう。

検証用のテーブルの用意

以下のようなテーブルとリレーションを用意しました。
Post の子にあたる PostLike や PostComment を取得する際にどのようなクエリが生成されるかを確認します。

スクリーンショット 2022-12-08 23.26.44.png

挙動確認

挙動の確認には SQLAlchemy 1.4 を使用しました。

relationship に lazy="joined" を設定した場合

relationship の設定は以下です。

class Post(BaseModel):
    __tablename__ = "posts"
    __table_args__ = (
        UniqueConstraint("title", "tenant_id"),
        {"comment": "投稿", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    title = Column(String(255), nullable=False)
    detail = Column(String(255), nullable=False)
    tenant_id = Column(String(36), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    posted_by_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)

    tenant = relationship("Tenant", back_populates="posts")
    posted_by = relationship("TenantUser", back_populates="posts")
    post_likes = relationship("PostLike", back_populates="post", cascade="all", passive_deletes=True, lazy="joined")
    post_comments = relationship("PostComment", back_populates="post", cascade="all", passive_deletes=True, lazy="joined")


class PostLike(BaseModel):
    __tablename__ = "post_likes"
    __table_args__ = (
        UniqueConstraint("post_id", "tenant_user_id"),
        {"comment": "投稿いいね", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    post_id = Column(BigInteger, ForeignKey("posts.id", ondelete="CASCADE"), nullable=False)
    tenant_user_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)

    post = relationship("Post", back_populates="post_likes")
    tenant_user = relationship("TenantUser", back_populates="post_likes")


class PostComment(BaseModel):
    __tablename__ = "post_comments"
    __table_args__ = (
        UniqueConstraint("post_id", "tenant_user_id"),
        {"comment": "投稿コメント", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    post_id = Column(BigInteger, ForeignKey("posts.id", ondelete="CASCADE"), nullable=False)
    tenant_user_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)
    comment = Column(String(255), nullable=False)

    post = relationship("Post", back_populates="post_comments")
    tenant_user = relationship("TenantUser", back_populates="post_comments")

こちらに対して、以下のコードを実行してみます。

tenant, tenant_user = create_tenant_and_users(session)

post = models.Post(title="title", detail="detail", tenant_id=tenant.id, posted_by_id=tenant_user.id)
session.add(post)
session.flush()

post_like = models.PostLike(post_id=post.id, tenant_user_id=tenant_user.id)
session.add(post_like)
session.flush()

post_comment = models.PostComment(comment="comment", post_id=post.id, tenant_user_id=tenant_user.id)
session.add(post_comment)
session.commit()

query = session.query(models.Post)
print(query.statement.compile(compile_kwargs={"literal_binds": True}))
print(query.one())

すると、以下のクエリが出力されました。

SELECT posts.created_at,
       posts.updated_at,
       posts.id,
       posts.title,
       posts.detail,
       posts.tenant_id,
       posts.posted_by_id,
       post_likes_1.created_at AS created_at_1,
       post_likes_1.updated_at AS updated_at_1,
       post_likes_1.id AS id_1,
       post_likes_1.post_id,
       post_likes_1.tenant_user_id,
       post_comments_1.created_at AS created_at_2,
       post_comments_1.updated_at AS updated_at_2,
       post_comments_1.id AS id_2,
       post_comments_1.post_id AS post_id_1,
       post_comments_1.tenant_user_id AS tenant_user_id_1,
       post_comments_1.comment
FROM posts
LEFT OUTER JOIN post_likes AS post_likes_1 ON posts.id = post_likes_1.post_id
LEFT OUTER JOIN post_comments AS post_comments_1 ON posts.id = post_comments_1.post_id

そして、以下のオブジェクトがPostオブジェクトが出力されました。

<Post(
updated_at=datetime.datetime(2022, 12, 8, 23, 36, 58),
id=5,
detail='detail',
tenant_id='08400e14-2011-4152-8153-a8f70b7aba4c',
created_at=datetime.datetime(2022, 12, 8, 23, 36, 58),
title='title',
posted_by_id='17ea7c79-577c-48e5-9f68-f9e6b3689ac5',
post_comments=[
<PostComment(
 created_at=datetime.datetime(2022, 12, 8, 23, 36, 58),
 post_id=5,
 comment='comment', updated_at=datetime.datetime(2022, 12, 8, 23, 36, 58),
 id=1,
 tenant_user_id='17ea7c79-577c-48e5-9f68-f9e6b3689ac5')
>],
post_likes=[
<PostLike(
updated_at=datetime.datetime(2022, 12, 8, 23, 36, 58),
 id=5,
 tenant_user_id='17ea7c79-577c-48e5-9f68-f9e6b3689ac5',
 created_at=datetime.datetime(2022, 12, 8, 23, 36, 58),
 post_id=5)
>])>

なるほど、"この形式のロードは、指定した SELECT 文に JOIN を適用し、関連する行が同じ結果セットにロードされるようにするものです。" の意味がわかります。

relationship に lazy="joined" を設定しない (つまり lazy loading )の場合

relation ship は以下の設定です。

class Post(BaseModel):
    __tablename__ = "posts"
    __table_args__ = (
        UniqueConstraint("title", "tenant_id"),
        {"comment": "投稿", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    title = Column(String(255), nullable=False)
    detail = Column(String(255), nullable=False)
    tenant_id = Column(String(36), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    posted_by_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)

    tenant = relationship("Tenant", back_populates="posts")
    posted_by = relationship("TenantUser", back_populates="posts")
    post_likes = relationship("PostLike", back_populates="post", cascade="all", passive_deletes=True)
    post_comments = relationship("PostComment", back_populates="post", cascade="all", passive_deletes=True)


class PostLike(BaseModel):
    __tablename__ = "post_likes"
    __table_args__ = (
        UniqueConstraint("post_id", "tenant_user_id"),
        {"comment": "投稿いいね", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    post_id = Column(BigInteger, ForeignKey("posts.id", ondelete="CASCADE"), nullable=False)
    tenant_user_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)

    post = relationship("Post", back_populates="post_likes")
    tenant_user = relationship("TenantUser", back_populates="post_likes")


class PostComment(BaseModel):
    __tablename__ = "post_comments"
    __table_args__ = (
        UniqueConstraint("post_id", "tenant_user_id"),
        {"comment": "投稿コメント", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    post_id = Column(BigInteger, ForeignKey("posts.id", ondelete="CASCADE"), nullable=False)
    tenant_user_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)
    comment = Column(String(255), nullable=False)

    post = relationship("Post", back_populates="post_comments")
    tenant_user = relationship("TenantUser", back_populates="post_comments")

先ほどと同じ、クエリ出力とオブジェクト出力を行います。

SELECT posts.created_at,
       posts.updated_at,
       posts.id,
       posts.title,
       posts.detail,
       posts.tenant_id,
       posts.posted_by_id
FROM posts

<Post(
created_at=datetime.datetime(2022, 12, 8, 23, 46, 9),
 title='title',
 tenant_id='bdab323a-6973-411e-8779-9b97df9f1b7b',
 posted_by_id='e6b79009-707a-4127-a7e0-85063ec3f25c',
 updated_at=datetime.datetime(2022, 12, 8, 23, 46, 9),
 id=6,
 detail='detail')>

上記は、PostLike と PostComment へのアクセスを行っていないので期待通りです。

(おまけ) relationship に lazy="joined" を設定せず、option の joinedload を使用してクエリする

relationship は以下の設定です。

class Post(BaseModel):
    __tablename__ = "posts"
    __table_args__ = (
        UniqueConstraint("title", "tenant_id"),
        {"comment": "投稿", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    title = Column(String(255), nullable=False)
    detail = Column(String(255), nullable=False)
    tenant_id = Column(String(36), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    posted_by_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)

    tenant = relationship("Tenant", back_populates="posts")
    posted_by = relationship("TenantUser", back_populates="posts")
    post_likes = relationship("PostLike", back_populates="post", cascade="all", passive_deletes=True)
    post_comments = relationship("PostComment", back_populates="post", cascade="all", passive_deletes=True)


class PostLike(BaseModel):
    __tablename__ = "post_likes"
    __table_args__ = (
        UniqueConstraint("post_id", "tenant_user_id"),
        {"comment": "投稿いいね", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    post_id = Column(BigInteger, ForeignKey("posts.id", ondelete="CASCADE"), nullable=False)
    tenant_user_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)

    post = relationship("Post", back_populates="post_likes")
    tenant_user = relationship("TenantUser", back_populates="post_likes")


class PostComment(BaseModel):
    __tablename__ = "post_comments"
    __table_args__ = (
        UniqueConstraint("post_id", "tenant_user_id"),
        {"comment": "投稿コメント", "info": {}},
    )
    id = Column(BigInteger, primary_key=True)
    post_id = Column(BigInteger, ForeignKey("posts.id", ondelete="CASCADE"), nullable=False)
    tenant_user_id = Column(String(36), ForeignKey("tenant_users.id", ondelete="CASCADE"), nullable=False)
    comment = Column(String(255), nullable=False)

    post = relationship("Post", back_populates="post_comments")
    tenant_user = relationship("TenantUser", back_populates="post_comments")

以下のスクリプトを実行します。
(qury に joinedload を追加して、PostLike だけの join を試みる。)

tenant, tenant_user = create_tenant_and_users(session)

post = models.Post(title="title", detail="detail", tenant_id=tenant.id, posted_by_id=tenant_user.id)
session.add(post)
session.flush()

post_like = models.PostLike(post_id=post.id, tenant_user_id=tenant_user.id)
session.add(post_like)
session.flush()

post_comment = models.PostComment(comment="comment", post_id=post.id, tenant_user_id=tenant_user.id)
session.add(post_comment)
session.commit()

query = session.query(models.Post).options(joinedload(models.Post.post_likes))
print(query.statement.compile(compile_kwargs={"literal_binds": True}))
print(query.one())

結果は以下のようになりました。

SELECT posts.created_at,
       posts.updated_at,
       posts.id,
       posts.title,
       posts.detail,
       posts.tenant_id,
       posts.posted_by_id,
       post_likes_1.created_at AS created_at_1,
       post_likes_1.updated_at AS updated_at_1,
       post_likes_1.id AS id_1,
       post_likes_1.post_id,
       post_likes_1.tenant_user_id
FROM posts
LEFT OUTER JOIN post_likes AS post_likes_1 ON posts.id = post_likes_1.post_id

<Post(
created_at=datetime.datetime(2022, 12, 8, 23, 57, 50),
 title='title',
 tenant_id='8e0afe7f-684c-4861-bf54-4fd052b7caac',
 posted_by_id='a179db5f-788e-40c7-a491-c5f1076946c9',
 updated_at=datetime.datetime(2022, 12, 8, 23, 57, 50),
 id=8,
 detail='detail',
 post_likes=[
<PostLike(
created_at=datetime.datetime(2022, 12, 8, 23, 57, 50),
 post_id=8, updated_at=datetime.datetime(2022, 12, 8, 23, 57, 50),
 id=8,
 tenant_user_id='a179db5f-788e-40c7-a491-c5f1076946c9'
)>]
)>

クエリ、取得オブジェクト、共に PostLike が追加されてました。

以上、 joined loading の動作確認でした。

最後に

最後まで読んで頂きありがとうございました。
弊社、SQLAlchemy を使っています。

弊社、別プロダクトで絶賛採用中です :muscle: :muscle:
https://www.wantedly.com/projects/1181162

9
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
3