2022/08/06 JavaDo データアクセスについて聞きたい・話したい！ n+1問題に気を付けよう ～Python Djangoで起きた悲しいこと～ 村上 將志

もくじ はじめに 発生した事象 原因・対策 DEMO この発表の概要、自己紹介などをお話します。 どんな問題が発生したかをお話します。 問題が起きた原因、解決の流れを解説します。 問題の事象の確認、修正の確認について、デモでご覧いただきます。 2022/08/06 1 まとめ フレームワークのORM機能を使う上での 注意をまとめます。

はじめに 2022/08/06 2

はじめに 本日の発表は、私が実際に仕事で失敗した経 験を皆様に共有いたします。 本資料は、後ほど共有する予定ですので、スラ イドの内容をメモする必要はございません。 2022/08/06 3

自己紹介 2022/08/06 4 村上 將志（むらかみ まさし）と申します。 ビットスター株式会社に所属し、Webシステムの主にバックエンド側を開発しています。 https://bitstar.jp/ これまで、Python3、Djangoでの開発してきましたが、会社ではPHP、Laravelを使う案件が多く、Laravelを勉 強しなきゃなぁ、、、という感じです。 前職では主にJavaを使っていましたが、もう10年以上Javaのコードを書いてません。 ちゃんとしたシステムやサイトを作らなきゃならないんだから、本当ならJavaで作りたい・・・。（ひとりごと） 2018年度まで札幌に住んでいて、Java Doのスタッフもしていましたが、2019年の春に大阪に転勤となり、今も 大阪市に住んでいます。

おことわり 2022/08/06 5 • Java Doの勉強会ですが、仕事で使っているPython Djangoで説明いたします。 • DjangoでRDB(リレーショナルデータベース)のMariaDB(MySQL互換)のテーブルを扱います。 • しかし、他の言語・フレームワークでも同じような問題が発生する可能性もありますので、実装の際に留意するキッカ ケにして頂ければ幸いです。

発生した事象 2022/08/06 6

• Python Djangoで開発しており、DjangoのORMの機能で、RDBのテーブルからの検索を実装しました。 • 関連するテーブルが多く、JOINして検索する必要がありました。 ※主となるデータの検索は、マスタも含めて、16個のテーブルをjoinする必要があります。 ※すべてのテーブルの、すべての項目を取得すると、項目数の合計は3桁となります。 • ページングを実装しており、1ページあたり最大100行表示します。 ※よくあるサイトだと、10～20行、多くても50行くらい • CSVダウンロード機能があります。 ※条件にヒットしたデータの全件をCSV形式のファイルに出力します。 ※ページングなどはせず、最大1万件まで出力可能とし、1万件を超えるような条件であれば条件を絞って頂くよう メッセージで案内。 まず、問題のあったWebサイトの要件・仕様をざっくりと 2022/08/06 7

問題発生 2022/08/06 8 本番リリースは完了しましたが、本番運用でデータが増えていくと、下記のような問題が発生しました。 • Webの表示 • 100行分を表示しようとすると、30秒以上待たされたり、アクセスが多いタイミングなどでは、「504 Gateway Timeout」になることがありました。 • 検索ヒット数が20～30行の場合でも、表示に数秒かかり、体感として重いと感じるようになりました。 • CSVダウンロード • 200～300件ヒットする条件を指定すると、1分以上待たされたり、「504 Gateway Timeout」も頻発 しました。

原因・対策 2022/08/06 9

サンプルのデータベース定義 2022/08/06 10 DEMOのため、簡単なサンプルを作りました。 （GitHubにアップしてあるので、最後にURLをお知らせします。） • 個人情報をDB管理 • 都道府県、地方（関東地方、近畿地方、など）、性別（男、女）をマスタ管理 個人情報本体 性別マスタ 都道府県マスタ 地方マスタ Model : Person Model : Sex Model : Prefecture Model : District

Python Djangoでの一般的なRDB検索方法 2022/08/06 11 person_list: List[Person] = Person.objects.all()[:10] person_list: List[Person] = ¥ Person.objects.filter(prefecture__name__contains = '京都')[:10] 「Django 検索」とかで探す（ググる）と、一般的な方法として、下記のような方法が見つかります。 全レコードから、先頭の10件を取得する例： 都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例： context[‘person_list’] = person_list 検索した結果をテンプレートに渡す {% for person in person_list %} {{ person.name }} {{ person.prefecture.district.name }} {{ person.prefecture.name }} {% endfor %} テンプレートでのデータの取得（例：ループを回して各行の都道府県を取得）

Python DjangoでのRDB検索の期待 2022/08/06 12 person_list: List[Person] = Person.objects.filter(prefecture__name__contains = '京都')[:10] 都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例： 関連するテーブルをJOINして、こんな感じで検索してくれると期待していました。 SELECT `p`.*, `ms`.*, `md`.*, `mp`.* FROM `person` AS `p` INNER JOIN `master_sex` AS `ms` ON `p`.`sex_id` = `ms`.`id` INNER JOIN `master_prefecture` AS `mp` ON `p`.`prefecture_id` = `mp`.`id` INNER JOIN `master_district` AS `md` ON `mp`.`district_id` = `md`.`id` WHERE `mp`.`name` LIKE '%京都%' ORDER BY `p`.`id` LIMIT 100 ; ところが、そんなに甘くはありませんでした。

Djangoで普通に検索したときの実際の動き(n+1問題) 2022/08/06 13 id name sex_id prefecture_id 5 大竹 稟 ・・・ 2 ・・・ 13 ・・・ 17 松島 晃一 ・・・ 1 ・・・ 13 ・・・ 58 池本 梨緒 ・・・ 2 ・・・ 13 ・・・ 71 相田 絵理 ・・・ 2 ・・・ 13 ・・・ 135 塚原 唯菜 ・・・ 2 ・・・ 13 ・・・ 149 神山 千春 ・・・ 2 ・・・ 13 ・・・ 156 池本 日菜子 ・・・ 2 ・・・ 13 ・・・ 246 楠 好男 ・・・ 1 ・・・ 13 ・・・ 402 松原 祐奈 ・・・ 2 ・・・ 13 ・・・ 504 磯崎 実結 ・・・ 2 ・・・ 13 ・・・ id name district_id 13 東京都 ・・・ 3 個人情報を検索→10行取得 次に、各マスタの名称を、テンプレートで表示等するタイ ミングで、各マスタの検索が走る ※②～④を個人情報10行について毎回実行 SELECT `p`.* FROM `person` AS `p` INNER JOIN `master_prefecture` AS `mp` ON `p`.`prefecture_id` = `mp`.`id` WHERE `mp`.`name` LIKE '%京都%' ORDER BY `p`.`id`LIMIT 100; ② 性別マスタ検索 SELECT * FROM `master_sex` WHERE `id` = 2; ③ 都道府県マスタ検索 SELECT * FROM `master_prefecture` WHERE `id` = 13; ④ 地方マスタ検索 SELECT * FROM `master_district` WHERE `id` = 3; まず、主となる個人情報のテーブルから検索 結果の取得で、表示で必要のない列まで取得してしまう。 ※列の多いテーブルでは無駄が多い。 →メモリ消費の増大 ① person_list: List[Person] = Person.objects.filter(prefecture__name__contains = '京都')[:10] 都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例：

Djangoで普通に検索したときの実際の動き(n+1問題) 2022/08/06 14 id name sex_id prefecture_id 5 大竹 稟 ・・・ 2 ・・・ 13 ・・・ 17 松島 晃一 ・・・ 1 ・・・ 13 ・・・ 58 池本 梨緒 ・・・ 2 ・・・ 13 ・・・ 71 相田 絵理 ・・・ 2 ・・・ 13 ・・・ 135 塚原 唯菜 ・・・ 2 ・・・ 13 ・・・ 149 神山 千春 ・・・ 2 ・・・ 13 ・・・ 156 池本 日菜子 ・・・ 2 ・・・ 13 ・・・ 246 楠 好男 ・・・ 1 ・・・ 13 ・・・ 402 松原 祐奈 ・・・ 2 ・・・ 13 ・・・ 504 磯崎 実結 ・・・ 2 ・・・ 13 ・・・ id name district_id 13 東京都 ・・・ 3 個人情報を検索→10行取得 次に、各マスタの名称を、テンプレートで表示等するタイ ミングで、各マスタの検索が走る ※②～④を個人情報10行について毎回実行 SELECT `p`.* FROM `person` AS `p` INNER JOIN `master_prefecture` AS `mp` ON `p`.`prefecture_id` = `mp`.`id` WHERE `mp`.`name` LIKE '%京都%' ORDER BY `p`.`id`LIMIT 100; ② 性別マスタ検索 SELECT * FROM `master_sex` WHERE `id` = 2; ③ 都道府県マスタ検索 SELECT * FROM `master_prefecture` WHERE `id` = 13; ④ 地方マスタ検索 SELECT * FROM `master_district` WHERE `id` = 3; まず、主となる個人情報のテーブルから検索 結果の取得で、表示で必要のない列まで取得してしまう。 ※列の多いテーブルでは無駄が多い。 →メモリ消費の増大 ① person_list: List[Person] = Person.objects.filter(prefecture__name__contains = '京都')[:10] 都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例： 100行表示とかで、これだと、 重くて使いものにならない

Djangoでの検索をJOINで一気に取ってくる方法はないか？ 2022/08/06 15 関連するテーブルをJOINして、こんな感じで検索する方法はないか？ SELECT `p`.*, `ms`.*, `md`.*, `mp`.* FROM `person` AS `p` INNER JOIN `master_sex` AS `ms` ON `p`.`sex_id` = `ms`.`id` INNER JOIN `master_prefecture` AS `mp` ON `p`.`prefecture_id` = `mp`.`id` INNER JOIN `master_district` AS `md` ON `mp`.`district_id` = `md`.`id` WHERE `mp`.`name` LIKE '%京都%' ORDER BY `p`.`id` LIMIT 100 ; • JOINするテーブルの参照項目を定義（テーブル名ではなく、参照フィールド） • SELECTで取得する項目を定義 • モデルでfilterする前にselect_relatedメソッドを呼び出す（引数：JOINするテーブルの参照項目） • filterの後でvaluesメソッドを呼び出す（引数：取得する項目） 探してみたら、あった！！

サンプルのデータベース定義（おさらい） 2022/08/06 16 10を超えるテーブルでサンプルを作るのは大変なので、簡単なサンプルを作りました。 （gitlabにアップしてあるので、最後にURLをお知らせします。） • 個人情報をDB管理 • 都道府県、地方（関東地方、近畿地方、など）、性別（男、女）をマスタ管理 個人情報本体 性別マスタ 都道府県マスタ 地方マスタ Model : Person Model : Sex Model : Prefecture Model : District

JOINするテーブルの参照を定義（テーブル名ではなく、Personから見た参照フィールド） select_related_tables = [ 'prefecture', 'prefecture__district', 'sex', ] DjangoでのJOINで検索する方法 （１） 2022/08/06 17

SELECTで取得する項目を定義 select_fields = [ 'id', 'name', 'name_kana', 'sex__name', 'telephone', 'telephone_mobile', 'mail_address', 'zip_code', 'prefecture__district__name', 'prefecture__name', 'address1', 'address2', 'birthday', ・・・ 'created_at', 'updated_at', ] DjangoでのJOINで検索する方法 （２） 2022/08/06 18

person_list: List[Person] = ¥ Person.objects ¥ .select_related(*select_related_tables) ¥ .filter(prefecture__name__contains = '京都') ¥ .values(*select_fields)[:100] select_relatedメソッドの引数で、JOINして検索するテーブルの参照項目を、 valuesメソッドの引数で、SELECTで取得する項目を、 それぞれ*argsの形で指定 • 関連して取得するテーブルをselect_relatedで指定することで、一気にJOINして検索してくれる。 • 取得する列をvaluesで指定することで、無駄な列を取得しなくて済む。 ※指定した列名をキーとする辞書型で取得する形となる。 DjangoでのJOINで検索する方法 （３） 2022/08/06 19

person_list: List[Person] = ¥ Person.objects ¥ .select_related(*select_related_tables) ¥ .filter(prefecture__name__contains = '京都') ¥ .values(*select_fields)[:100] select_relatedメソッドの引数で、JOINして検索するテーブルの参照項目を、 valuesメソッドの引数で、SELECTで取得する項目を、 それぞれ*argsの形で指定 • 関連して取得するテーブルをselect_relatedで指定することで、一気にJOINして検索してくれる。 • 取得する列をvaluesで指定することで、無駄な列を取得しなくて済む。 ※指定した列名をキーとする辞書型で取得する形となる。 DjangoでのJOINで検索する方法 （３） 2022/08/06 20

DEMO 2022/08/06 21

サンプルDEMO 2022/08/06 22 • 今回は、Python3、Djangoで、簡単なサンプルを作成しました。 • サンプルは、私のGitHubに公開します。 https://github.com/murakami0923/django-nplusone-sample • サンプルを動かす際には、LinuxにインストールしたDocker、Docker Compose (v2)が必要です。 ※私はOracle VM VirtualBoxにUbuntu 22.04のゲストOSを作成し、Dockerをインストールして使用します。 ※Windowsの場合、WSL2でUbuntuをインストールしても使えるはずです。 • アプリケーションのコンテナで、アプリケーション実行ユーザーを、ホスト側のグループとユーザーと同じもので作成し、ボ リュームを参照するようにしています。

Docker環境 2022/08/06 23

サンプルの準備 2022/08/06 24 • GitHubからサンプルを取得します。 （git clone https://github.com/murakami0923/django-nplusone-sample.git） • サンプルのディレクトリに移動します。(cd django-nplusone-sample/) • Dockerfileをビルドします。(./docker-build.sh)

実行・確認の流れ 2022/08/06 25 • まず、Docker Composeで定義したコンテナ起動します。（./start-docker-compose.sh) ※必要なボリュームのディレクトリ作成を含みます。 • しばらく（実行環境にもよりますが、およそ10秒ほど）待ちます。 • ターミナル等でボリュームの中のクエリログをless等で開いておきます。 （sudo less -S volumes/mysql/log/mysql.log） ※-S : 折り返さずに表示 • ブラウザで、n+1問題対応前のページへアクセスします。 （http://{Dockerホストのホスト名orIPアドレス}:8001/person/001/） • クエリログで、各マスタの取得が個別に行われていることを確認します。 • ブラウザで、n+1問題対応後のページへアクセスします。 （http://{Dockerホストのホスト名orIPアドレス}:8001/person/002/） • クエリログで、マスタも含めてjoinで一括で取得していることを確認します。 • 確認が終わったら、Docker Composeで定義したコンテナを停止します。（ ./stop-docker-compose.sh ）

まとめ 2022/08/06 26

まとめ 2022/08/06 27 • フレームワークの機能を使う場合は、その仕様や制約などにより、今回のような問題が発生する可 能性があります。 • 今回はPythonのDjangoを使いましたが、ほかの言語・フレームワークでも、こうした仕様・制約 で、使う側での工夫・対応が求められるかもしれません。 • フレームワークのORM機能を使う場合、開発環境で、DBのクエリログの出力を有効化しておき、 開発の段階で実行されたクエリをこまめに確認し、n+1のような問題が起きていないか確認するこ とをおすすめします。

補足 2022/08/06 28 • システムの開発に携わって20年ほど経ちますが、その中の経験から、RDBからの検索機能でのパ フォーマンス問題は、下記のような問題で解決できることが多かったです。 • クエリ自体の問題（特にグループ化） • インデックスが適切に設定されていない • n+1問題（今回紹介した内容） • その中で、クエリの問題と、インデックス問題では、クエリの実行自体に時間がかかるケースもあ ります。（スロークエリ） • 本番環境においても、スロークエリを検知するため、スロークエリのログを有効化することもおす すめです。 • クラウドのマネージドのRDBでも、スロークエリのログの設定が可能なケースもあります。 （例：AWS RDS MySQLの設定）

ご清聴ありがとうございました 村上 將志 Twitter : @masashi0923 Facebook : masashi0923 2022/08/06 29