バックエンドエンジニアの宮澤です。
Railsアプリを開発していると関連テーブルを取得するactiverecordのincludes, eager_load, preloadメソッドはよく使いますよね。
アプリケーションのある箇所でスロークエリが出ているのを見つかって対応した際に、テーブル関連付けの種類によるこれらのメソッドの挙動について調べてみました。
テーブル設計
サンプルとしてシンプルに地域 => 国 => 都市と1:Nの関係でテーブルを作成します
マイグレーション
class CreateRegions < ActiveRecord::Migration[6.0] def change create_table :regions do |t| t.string :name t.timestamps end end end class CreateCountries < ActiveRecord::Migration[6.0] def change create_table :countries do |t| t.references :region t.string :name t.timestamps end end end class CreateCities < ActiveRecord::Migration[6.0] def change create_table :cities do |t| t.references :country t.string :name t.integer :population t.timestamps end end end
モデル
class Region < ApplicationRecord has_many :countries end class Country < ApplicationRecord belongs_to :region has_many :cities end class City < ApplicationRecord belongs_to :country end
サンプルデータ
asia = Region.create(name: 'アジア') europe = Region.create(name: 'ヨーロッパ') japan = asia.countries.create(name: '日本') indonesia = asia.countries.create(name: 'インドネシア') russian = europe.countries.create(name: 'ロシア') germany = europe.countries.create(name: 'ドイツ') japan.cities.create(name: '京都', population: 1_400_000) japan.cities.create(name: '東京', population: 9_200_000) indonesia.cities.create(name: 'ジャカルタ', population: 9_600_000) indonesia.cities.create(name: 'ボルネオ', population: 16_000_000) russian.cities.create(name: 'モスクワ', population: 11_000_000) russian.cities.create(name: 'サンクトペテルブルグ', population: 5_000_000) germany.cities.create(name: 'ベルリン', population: 3_700_000) germany.cities.create(name: 'ハンブルグ', population: 1_800_000)
問題となったコードとスロークエリ
irb(main):045:0> Country.includes(:region, :cities).limit(10) Country Load (1.1ms) SELECT `countries`.* FROM `countries` LIMIT 10 Region Load (5.4ms) SELECT `regions`.* FROM `regions` WHERE `regions`.`id` IN (1, 2) City Load (0.3ms) SELECT `cities`.* FROM `cities` WHERE `cities`.`country_id` IN (1, 2, 3, 4)
機能としては国の一覧情報を表示するページで、国の個別情報に地域と都市の一覧も含めて表示したいというものです。国の一覧なのでCountryモデルを起点とし、N+1Queryが発生しないように地域と都市情報をincludesでキャッシュしているようでした。
ここまでは普通のSQLですね。
これに地域での絞り込み機能を追加してみます。joinsでregionsテーブルををINNER JOINし、WHERE句でregions.nameカラムへの条件を設定します。
irb(main):043:0> Country.includes(:region, :cities).joins(:region).where(regions: {name: 'アジア'}).limit(10) SQL (0.5ms) SELECT DISTINCT `countries`.`id` FROM `countries` INNER JOIN `regions` ON `regions`.`id` = `countries`.`region_id` LEFT OUTER JOIN `cities` ON `cities`.`country_id` = `countries`.`id` WHERE `regions`.`name` = 'アジア' LIMIT 10 SQL (0.4ms) SELECT `countries`.`id` AS t0_r0, `countries`.`region_id` AS t0_r1, `countries`.`name` AS t0_r2, `regions`.`id` AS t1_r0, `regions`.`name` AS t1_r1, `cities`.`id` AS t2_r0, `cities`.`country_id` AS t2_r1, `cities`.`name` AS t2_r2, `cities`.`population` AS t2_r3 FROM `countries` INNER JOIN `regions` ON `regions`.`id` = `countries`.`region_id` LEFT OUTER JOIN `cities` ON `cities`.`country_id` = `countries`.`id` WHERE `regions`.`name` = 'アジア' AND `countries`.`id` IN (1, 2)
①のSQLは検索条件に合致する国を検索し、②のSQLではその結果（country_idの配列）からcountriesテーブルとincludesで指定した関連テーブルregions, citiesのレコードを取得しています。
SQLを見るといろいろと怪しいですね。。。
まず①のSQLですが、検索条件がregionsテーブルのカラムなのでregionsテーブルをINNER JOINするのはいいのですが、citiesテーブルをLEFT OUTER JOINしているのは無駄です。citiesはSELECT句でもWHERE句でも使用していないにも関わらずJOINしている上に、has_many関連なので重複除去のためにSELECT句にDISTINCTも指定されています。
EXPLAINの結果はこちら
mysql> EXPLAIN SELECT DISTINCT `countries`.`id` -> FROM `countries` -> INNER JOIN `regions` ON `regions`.`id` = `countries`.`region_id` -> LEFT OUTER JOIN `cities` ON `cities`.`country_id` = `countries`.`id` -> WHERE `regions`.`name` = 'アジア' -> LIMIT 10; +----+-------------+-----------+------------+------+--------------------------------------+------------------------------+---------+----------------------------------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+--------------------------------------+------------------------------+---------+----------------------------------------------+------+----------+------------------------------+ | 1 | SIMPLE | regions | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 | 50.00 | Using where; Using temporary | | 1 | SIMPLE | countries | NULL | ref | PRIMARY,index_countries_on_region_id | index_countries_on_region_id | 9 | activerecord_sample_development.regions.id | 2 | 100.00 | Using index | | 1 | SIMPLE | cities | NULL | ref | index_cities_on_country_id | index_cities_on_country_id | 9 | activerecord_sample_development.countries.id | 2 | 100.00 | Using index; Distinct | +----+-------------+-----------+------------+------+--------------------------------------+------------------------------+---------+----------------------------------------------+------+----------+------------------------------+
extraに Using temporary と DISTINCT が出ていますね。DISTINCTは一時テーブルが必要になるためスロークエリになりがちです。
②のSQLでは既に条件に一致したcountry_idの配列を持っているので、それを条件に関連テーブルまでJOINすればいいだけのはずです。ですが、よく見るとここでも WHERE regions.name = 'アジア' とWHERE条件が付いてしまっています。
どうしてこんなことに？
includesの挙動
今回の事象に対応するにあたって、改めてincludesの挙動を調査してみました。Railsのコードを読むとともに、こちらのブログを大変参考にさせてもらいました。
includesの挙動について、「preloadとeager_loadをよろしく使い分けしてくれるもの」と認識しているRailsエンジニアは結構いるのではないでしょうか？
そういったエンジニアの方の中には、先日の私のようにN+1問題の対策で深く考えずに「includesつけとけばOKでしょ。ほらbulletのN+1のアラート止まったし。」という方もいらっしゃるのではと思っています。
はい。まさに自分ですね。
Country.includes(:region, :cities).joins(:region).where(regions: {name: 'アジア'})
このコードでは
- eager_loadは指定されていないが
- includesに指定しているregionをjoinsにも指定している
ので、includesはeagler_loadを使用します。
問題はincludesでまとめてアソシエーション指定すると、アソシエーションごとにeager_loadとpreloadを選択するのではなく、eager_loadを使用する条件に一致するアソシエーションが一つでもあったら、全てのアソシエーションをeager_laodで処理してしまうことです。
どうすればいい？
アソシエーションにhas_many, belongs_toの関連が混ざる場合はeager_load, preloadを明示的に使い分けましょう。
belongs_to, has_one
eager_loadを使います。
アソシエーション先をJOINして1クエリで全ての情報を取得できますし、N対1, 1対1の関連なのでJOINによって結果レコードの件数が増えることがないので重複排除のためにDISTINCTが発生することもありません。
has_many
preloadを使います。
ただし起点となるモデルのレコード数が多く、絞り込みが十分にされていないとIN句に大量のIDが入ってしまいます。例えば100万レコードあるtable_aからhas_many関連となっているtable_bがあった場合に table_a.preload(table_b) として全件取得しようとすると WHERE table_b.table_a_id IN (1,2,3,....) となりIN句の中に100万個のIDが入ってしまいます。そうなるとSQLの文字数制限に引っかかったり、INDEXが効かなくなってメモリを大量に消費するようになります。
preloadを使うときはページングなどで件数が十分に絞り込まれるようにしましょう。
コード修正
今回の機能を実現するにはこう書きます。
irb(main):061:0> Country.eager_load(:region).preload(:cities).joins(:region).where(regions: {name: 'アジア'}).limit(10) SQL (0.5ms) SELECT `countries`.`id` AS t0_r0, `countries`.`region_id` AS t0_r1, `countries`.`name` AS t0_r2, `countries`.`created_at` AS t0_r3, `countries`.`updated_at` AS t0_r4, `regions`.`id` AS t1_r0, `regions`.`name` AS t1_r1, `regions`.`created_at` AS t1_r2, `regions`.`updated_at` AS t1_r3 FROM `countries` INNER JOIN `regions` ON `regions`.`id` = `countries`.`region_id` WHERE `regions`.`name` = 'アジア' LIMIT 10 City Load (0.3ms) SELECT `cities`.* FROM `cities` WHERE `cities`.`country_id` IN (1, 2)
belongs_to関連のregionはeager_laodで指定し、has_many関連のcitiesはpreloadで指定します。
これによって、countries, regionsはレコードが重複することもなく1クエリで条件に一致するレコードの情報が一度に取得できて、citiesは外部キーのcountry_idのみで検索できまました！
まとめ
includesでの関連テーブルの読み込みは、アソシエーションの種類によってRailsがいい感じなSQLを構築してくれますが、複数のアソシエーション指定や検索条件が複雑化でスロークエリの要因ともなります。
includesを使う場合は必ず実際に構築されたSQLを確認して、eager_load と preload のどちらが使われているか、SQLに違和感はないかを確認しましょう。
特に既存のincludes, eager_load, preloadにアソシエーションを追加したときには要注意です。
おまけ
has_many, belongs_to, has_oneといったRailsでの関連の種類によって解説してきましたが、RailsアプリではDBでの関連とは異なる関連を定義できます。
class Country < ApplicationRecord has_many :cities has_one :popular_city, -> { order(po: :desc) }, class_name: 'City' end
countriesテーブルとcitiesテーブルはDB上では1対Nとなっていますが、こうすることでRailsアプリ上ではpopular_cityというhas_oneのアソシエーションができます。
このpopular_cityを使って国の一覧とその国で一番人口が多い都市名を取得するコードを書こうと思います。この場合に「has_oneだからeager_loadだろう」と考えていたら、こんな結果になりました。
Country.eager_load(:popular_city).map { |country| { country.name => country.popular_city.name } } SQL (0.4ms) SELECT `countries`.`id` AS t0_r0, `countries`.`region_id` AS t0_r1, `countries`.`name` AS t0_r2, `countries`.`created_at` AS t0_r3, `countries`.`updated_at` AS t0_r4, `cities`.`id` AS t1_r0, `cities`.`country_id` AS t1_r1, `cities`.`name` AS t1_r2, `cities`.`population` AS t1_r3, `cities`.`created_at` AS t1_r4, `cities`.`updated_at` AS t1_r5 FROM `countries` LEFT OUTER JOIN `cities` ON `cities`.`country_id` = `countries`.`id` => [{"日本"=>"京都"}, {"インドネシア"=>"ジャカルタ"}, {"ロシア"=>"モスクワ"}, {"ドイツ"=>"ベルリン"}]
おかしいですね。日本の人口は東京のほうが多いはずなのに京都が入ってしまいました。SQLにはORDER BYも入っていません。
preloadで取得してみましょう。
irb(main):033:0> Country.preload(:popular_city).map { |country| { country.name => country.popular_city.name } } Country Load (0.5ms) SELECT `countries`.* FROM `countries` City Load (0.5ms) SELECT `cities`.* FROM `cities` WHERE `cities`.`country_id` IN (1, 2, 3, 4) ORDER BY `cities`.`population` DESC => [{"日本"=>"東京"}, {"インドネシア"=>"ボルネオ"}, {"ロシア"=>"モスクワ"}, {"ドイツ"=>"ベルリン"}]
今度は正しく人口でソートされた都市が出ました。
こういった罠もあるので、やはりRailsでのクエリ構築は実際のSQLを確認することが大事ですね。