Zenn
❓

SQLAlchemy の relationship 付きモデルの SELECT が遅い

2024/02/07に公開
MySQL
SQLAlchemy
PlanetScale
tech

🎯目的

SQLAlchemy で relationship を定義したモデルクラスの SELECT 処理が遅い事象が発生しないようにする

💡前提

  • Python で SQLAlchemy を利用して、MySQL からデータを取得、保存を行っています。
  • データベースは PlanetScale を利用しています。
  • 親テーブルと子テーブルで一対多のリレーションがあるテーブルから親、子テーブルのデータを SELECT しようとしたら、レイテンシーが遅い事象が発生しました。

💡実装コード

実装コードは以下のようになっています。

from sqlalchemy.orm import declarative_base

DataBase = declarative_base()


class 親テーブル(DataBase):
    __tablename__ = "親テーブル"
    __table_args__ = ({"mysql_charset": "utf8mb4", "mysql_engine": "InnoDB"})

    id: Mapped[int] = mapped_column(Integer(), primary_key=True, nullable=False, autoincrement=True)
    # ...

    # NOTE : PlanetScale では ForeignKey をサポートしていないのでprimaryjoin,foreign_keysを指定している
    子テーブル一覧: Mapped[list[子テーブル]] = relationship(
        '子テーブル',
        primaryjoin='親テーブル.id == 子テーブル.parent_id',
        foreign_keys='子テーブル.parent_id'
    )


class 子テーブル(DataBase):
    __tablename__ = "子テーブル"

    id: Mapped[int] = mapped_column(Integer(), primary_key=True, nullable=False, autoincrement=True)

    # NOTE : PlanetScale では ForeignKey をサポートしていないので ForeignKey は指定しない
    parent_id: Mapped[str] = mapped_column(Integer(), nullable=False, index=True)

このとき、親、子テーブルからデータを取得し、何かしらの処理を行いたいとします。ですが、SELECT 時に親テーブルの一覧取得に1回の SELECT 処理 + それぞれの親テーブルに紐づく子テーブルの取得に1回の SELECT 処理が走り、N+1問題が発生し、合計処理に10秒ほど時間がかかりました。

with Session(bind=engine) as session:
    # ここで1回の SELECT クエリが発行
    親テーブル一覧: list[親テーブル] = session.query(親テーブル).all()
    for e in 親テーブル一覧:
        # ここで子テーブルに対して1回の SELECT クエリを発行
        print(e.子テーブル一覧)
　　　　　　　　# 親テーブルの数だけ子テーブルにそれぞれ1回の SELECT クエリが発行されてしまう

🔈結論

relationship に lazy='joined' を指定し、親テーブルの SELECT 時に子テーブルも JOIN して SELECT させる

class 親テーブル(DataBase):
    __tablename__ = "親テーブル"
    __table_args__ = ({"mysql_charset": "utf8mb4", "mysql_engine": "InnoDB"})

    id: Mapped[int] = mapped_column(Integer(), primary_key=True, nullable=False, autoincrement=True)
    # ...

    # NOTE : PlanetScale では ForeignKey をサポートしていないのでprimaryjoin,foreign_keysを指定している
    子テーブル一覧: Mapped[list[子テーブル]] = relationship(
        '子テーブル',
        primaryjoin='親テーブル.id == 子テーブル.parent_id',
+       lazy='joined',
        foreign_keys='子テーブル.parent_id'
    )

これで、0.6~0.8 秒ほどになりました！

🔗 APPENDIX

Discussion