SQLAlchemy 2.0 の Eager load 入門

ビザスク開発１グループエキスパート/lite 開発チームのよしけーです！

もうすぐ風来のシレン6が発売されますね。自分は初代とアスカ見参！しか経験がないのですが、14年ぶりの新作ということで久しぶりに手を出してみようかと思ってワクワクしている今日この頃です。

にしても。自分は現在30代後半なのですが、スラムダンクが映画化したり、るろうに剣心が再アニメ化したり、幽☆遊☆白書が実写化したり、紅白でポケビブラビが出たりしたりで子どもと一緒にワイワイできるコンテンツが多くていい時代ですね！自分は子どもいませんけど！

本記事について

弊社では現在、サービスの成長に伴いモノレポからのサービス分割に取り組んでいます。サービスごとに技術スタックが多少異なるのですが、自分が担当しているサービスのバックエンドでは FastAPI を使用しています。 FastAPI のマニュアルでは SQL のツールキットに SQLAlchemy を取り上げており、また担当しているサービスでも SQLAlchemy を採用しています。

個人的に SQLAlchemy を使ったことがなかったためこの機会に学ぶことにしました。SQLAlchemy の基本については既にまとまっている記事が Qiita や Zenn などにありますので、本記事では Eager ロードに焦点を当てていきたいと思います。

Eager ロードについて

ここまで読まれている方に改めての説明は不要かと思いますが Eager ロードについて簡単に説明すると。

N+1 問題を解決するテクニック
関連するデータを一度の処理で取得し遅延読み込みを回避する
これによりデータベースアクセスの最適化とアプリケーションのパフォーマンス向上が見込める

ですね。

SQLAlchemy 2.0 の Eager ロードについて

SQLAlchemy の Eager ロードの詳細については公式マニュアルをご参照ください。Relationship Loading Techniques

SQLAlchemy ではいくつか Eager ロードの機能を提供していますが、メインとしては次の３つになると思います。

select IN loading (一対多、多対多向け)
joined loading (多対一向け)
contains_eager (リレーション先に対して制約を付与したい場合)

では実際にどのように使うのかサンプルコードとともに見ていきたいと思います。

前提

動作検証のサンプルとして次のようなリレーションを前提としてモデルなどは省略します。

登場人物は生徒とメールアドレス
生徒は複数のメールアドレスを持つことができる

SQLAlchemy のモデルの書き方については公式マニュアルを参照ください。Basic Relationship Patterns

Eager ロードしない場合(Lazy Loding)

まずは Eager ロードを使わない場合を見ていきましょう。検証コードは次のとおりです。

stmt = select(Student)
result = session.execute(stmt)
students = result.scalars().all()
for student in students:
    print(
        f"### student.id[{student.id}]"
        f" student.name[{student.name}]"
        f" student.emails[{[email.email for email in student.emails]}]"
    )

発行する SQL クエリは次のとおりです。

SELECT students.id, students.name, students.gender, students.address, students.score, students.updated_at 
FROM students

SELECT emails.email AS emails_email, emails.student_id AS emails_student_id 
FROM emails 
WHERE %(param_1)s = emails.student_id
-- binding: {'param_1': 2}

SELECT emails.email AS emails_email, emails.student_id AS emails_student_id 
FROM emails 
WHERE %(param_1)s = emails.student_id
-- binding: {'param_1': 3}

SELECT emails.email AS emails_email, emails.student_id AS emails_student_id 
FROM emails 
WHERE %(param_1)s = emails.student_id
-- binding: {'param_1': 4}

-- 以降 emails への select が続く

初回のクエリで生徒の一覧を取得し、その後は emails テーブルに生徒IDで select してメールアドレスを取得しています。メールアドレスを取得するクエリは Python コード側の下記の print で email を取得するたびに発行します。

    print(
        f"### student.id[{student.id}]"
        f" student.name[{student.name}]"
        f" student.emails[{[email.email for email in student.emails]}]"
    )

なので、もし100万人の生徒がいた場合には100万回の select クエリを発行することになります。これが N+1 問題と言われる動きで、データベースのパフォーマンス低下などの要因ですね。

SQLAlchemy のマニュアルにも説明がありますのでご参照ください。(Lazy Loading)

Select IN loading

Select IN loading は一対多もしくは多対多向けの Eager ロードです。次のように使用します。

stmt = select(Student).options(selectinload(Student.emails))
result = session.execute(stmt)
for student in result.scalars():
    print(
        f"### student.id[{student.id}]"
        f" student.name[{student.name}]"
        f" student.emails[{[email.email for email in student.emails]}]"
    )

発行する SQL クエリは次のとおりです。

SELECT students.id, students.name, students.gender, students.address, students.score, students.updated_at
FROM students

SELECT emails.student_id AS emails_student_id, emails.email AS emails_email
FROM emails
WHERE emails.student_id IN (%(primary_keys_1)s, %(primary_keys_2)s, %(primary_keys_3)s)
-- binding: {'primary_keys_1': 2, 'primary_keys_2': 3, 'primary_keys_3': 4}

クエリが2回発行されます。1回目は生徒情報を取得するためのクエリで、2回目はメールアドレスを取得するためのクエリですね。

このように Select IN loding はリレーション先を外部キーによる Select IN で取得する Eager ロードです。リレーションの数だけクエリが増え、また親側の外部キーの数だけ IN 句の対象が増えていく、というシンプルなクエリを複数回発行するのが特徴かと思います。

注意事項

マニュアルにも書かれていますが Select IN loding には次のような注意点があります。

IN 句を使用するためデータベースのサポートに応じて IN 句の上限やクエリ文字列の上限などの考慮が必要
複合ユニークキーの場合などでは IN 句がタプルになる。データベースがこの形式をサポートしている必要がある

Joined Eager Loading

Joined Eager Loading は多対一向けの Eager ロードです。次のように使用します。

stmt = select(Email).options(joinedload(Email.student, innerjoin=False))
result = session.execute(stmt)
for email in result.scalars().all():
    print(
        f"### student.id[{email.student.id}]"
        f" student.name[{email.student.name}]"
        f" student.emails[{email.email}]"
    )

発行する SQL クエリは次のとおりです。

SELECT emails.email, emails.student_id, students_1.id, students_1.name, students_1.gender, students_1.address, students_1.score, students_1.updated_at 
FROM emails LEFT OUTER JOIN students AS students_1 ON students_1.id = emails.student_id

join によるクエリを発行しています。

このように Joined Eager Loading はリレーション先を join して取得する Eager ロードです。クエリの発行が1回ですみますが、リレーション先が多いとクエリが複雑化していきそうですね。

Joined Eager Loading で一対多

Joined Eager Loading は一対多でも使うことができます。

stmt = select(Student).options(joinedload(Student.emails, innerjoin=False))
result = session.execute(stmt)
for student in result.scalars().unique().all():
    print(
        f"### student.id[{student.id}]"
        f" student.name[{student.name}]"
        f" student.emails[{[email.email for email in student.emails]}]"
    )

発行する SQL クエリは次のとおりです。

SELECT students.id, students.name, students.gender, students.address, students.score, students.updated_at, emails_1.email, emails_1.student_id 
FROM students LEFT OUTER JOIN emails AS emails_1 ON students.id = emails_1.student_id

多側の生徒をベースに join していますね。差異としては下記のように Python 側の実装で unique() を付与している点です。

for student in result.scalars().unique().all():

一対多で join すると生徒側(一側)のレコードに重複が発生します。ですが Python 側としては一意のモデルインスタンスが必要なので unique() が必要となる、という感じです。もし unique() がなかった場合は InvalidRequestError の例外となります。

詳細はマニュアルに書かれていますので、なぜこのような仕組みになっているか興味がある方はご参照ください。

contains_eager

ところで皆さんはリレーション先を where でフィルタしたり order by で並び替えをしたいケースなどはないでしょうか？自分はよくあります。

そんなときは contains_eager を使うことで実現できます。例えば生徒からのリレーションで、メールアドレスを昇順で並び替えた結果を取得する場合には次のように使用します。

stmt = (
    select(Student)
    .join(Student.emails)
    .options(contains_eager(Student.emails))
    .order_by(Email.email)
)
result = session.execute(stmt)
for student in result.scalars().unique():
    print(
        f"### student.id[{student.id}]"
        f" student.name[{student.name}]"
        f" student.emails[{[email.email for email in student.emails]}]"
    )

発行する SQL クエリは次のとおりです。

SELECT emails.email, emails.student_id, students.id, students.name, students.gender, students.address, students.score, students.updated_at 
FROM students INNER JOIN emails ON students.id = emails.student_id ORDER BY emails.email

生徒をベースにメールアドレスを join し、 order by で mail を並び替えていますね。 contains_eager を使うとこの結果をモデルに反映してくれます。

自分としては、 contains_eager があれば Joined Eager Load の出番は少なめな気持ちですが。。使い分けとしては次のようなところでしょうか。

フィルタや並び替えが不要でシンプルなコードにする: Joined Eager Load
フィルタや並び替えが必要: contains_eager

あとがき

今回は SQLAlchemy の Eager ロードに焦点を当て、その使い方について簡単に解説しました。学習の過程で SQLAlchemy の公式マニュアルをそこそこ一通り眺めたのですが、マニュアルは多くの情報が書かれていますが正直わかりにくかったです。。しかし必要なことはどこかに記載されているはずなので根気よく探してみてください。

その他の情報源としては SQLAlchemy の GitHub Discussions が良かったです。こちらは Google 検索でヒットしないので、エラーなどの際にはエラー内容で直接検索してみると良いかと思います。

今回の記事で紹介したコードや学んだ内容は、以下のリポジトリにアップしてありますのでご自由にお使いください。使い方は README に記載してあります。また何か質問やコメントがありましたらお知らせください。

https://github.com/yoshik159753/sqlalchemy-learning

今回のサンプルコードはそれぞれ次のとおりです(記事のコードと差異がある旨をご了承ください)。

Lazy Loding はこちら
Select IN loading はこちら
Joined Eager Loading はこちら
contains_eager はこちら

以上です！ Let’s Happy Coding!!

VISASQ Dev Blog

ビザスク開発ブログ

SQLAlchemy 2.0 の Eager load 入門

本記事について

Eager ロードについて

SQLAlchemy 2.0 の Eager ロードについて

前提

Eager ロードしない場合(Lazy Loding)

Select IN loading

注意事項

Joined Eager Loading

Joined Eager Loading で一対多

contains_eager

あとがき