Share
Embed
BulletmarkRepairer: auto corrector for N+1 queries
Rails アプリの
5,000 件の N+1 問題と
戦っている話
2023.10.27. Kaigi on Rails 2023
makicamel
October 27, 2023
Transcript
142 slides
-
Rails ΞϓϦͷ 5,000 ݅ͷ N+1 ͱ ઓ͍ͬͯΔ 2023.10.27.
Kaigi on Rails 2023 @makicamel -
plays = Play.limit(10) plays.each do |play| puts play.actors.map(&:name) end
SELECT "plays".* FROM "plays" LIMIT 10 SELECT "actors".* FROM "actors" INNER JOIN "play_actors" ON "actors"."id" = "play_actors"."actor_id" WHERE "play_actors"."play_id" = 1 SELECT "actors".* FROM "actors" INNER JOIN "play_actors" ON "actors"."id" = "play_actors"."actor_id" WHERE "play_actors"."play_id" = 2 SELECT "actors".* FROM "actors" INNER JOIN "play_actors" ON "actors"."id" = "play_actors"."actor_id" WHERE "play_actors"."play_id" = 3 ... 10 + 1 ճͷΫΤϦ͕ൃߦ͞ΕΔ N+1 ͱ -
ActiveRecord::QueryMethods#preload plays = Play.preload(:actors).limit(10) plays.each do |play| puts play.actors.map(&:name)
end SELECT "plays".* FROM "plays" LIMIT 10 SELECT "play_actors".* FROM "play_actors" WHERE "play_actors"."play_id" IN (1, 2, 3, ...) SELECT "actors".* FROM "actors" WHERE "actors"."id" IN (1, 2, 3, ...) 3 ͭʹ͚ΒΕͨΫΤϦ͕ൃߦ͞ΕΔ -
ActiveRecord::QueryMethods#eager_load plays = Play.eager_load(:actors).limit(10) plays.each do |play| puts play.actors.map(&:name)
end SELECT DISTINCT "plays"."id" FROM "plays" LEFT OUTER JOIN "play_actors" ON "play_actors"."play_id" = "plays"."id" LEFT OUTER JOIN "actors" ON "actors"."id" = "play_actors"."actor_id" LIMIT 10 SELECT "plays"."id" AS t0_r0, ... FROM "plays" LEFT OUTER JOIN "play_actors" ON "play_actors"."play_id" = "plays"."id" LEFT OUTER JOIN "actors" ON "actors"."id" = "play_actors"."actor_id" WHERE "plays"."id" IN (1, 2, 3, ...) LEFT OUTER JOIN ͨ͠ ΫΤϦ͕ൃߦ͞ΕΔ -
ActiveRecord::QueryMethods#includes ActiveRecord::QueryMethods#includes https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-includes ຊޠൃදऀ༁ Specify associations args to be eager
loaded to prevent N + 1 queries. A separate query is performed for each association, unless a join is required by conditions. N + 1 ΫΤϦΛ͙ͨΊʹ args associations Λ eager_load ͢ΔΑ͏ࢦఆ͠·͢ɻ ݅ʹΑͬͯ join ͕ඞཁͰͳ͚Εɺ ֤ association ͝ͱʹݸผͷΫΤϦ͕࣮ߦ͞Ε·͢ɻ -
ActiveRecord::QueryMethods#includes ActiveRecordͷjoinsͱpreloadͱincludesͱeager_loadͷҧ͍ / @k0kubun https://qiita.com/k0kubun/items/80c5a5494f53bb88dc58 •includes ͨ͠ςʔϒϧͰ where ʹΑΔߜΓࠐΈΛߦ͍ͬͯΔ •includes
ͨ͠ association ʹରͯ͠ joins ͔ references ݺΜͰ͍Δ •ҙͷ association ʹରͯ͠ eager_load ݺΜͰ͍Δ ͷ͏͍ͪͣΕ͔Λຬͨ͢߹ɺeager_load ͱಉ͡ڍಈ(LEFT JOIN)Λߦ͍ɺ ͦ͏Ͱͳ͚Ε preload ͱಉ͡ڍಈ(ΫΤϦΛ͚࣮ͯߦ)Λ͢Δɻ ߜΓࠐΈ͕ඞཁͳ࣌ʹྫ֎Λ͛ͣ eager_load ʹfallback͢Δ preloadɻ -
fl yerhzm / bullet https://github.com/ fl yerhzm/bullet Bullet 🔫 ɹN+1
ෆཁͳ eager_loadingͳͲΛ ɹϩάը໘ʹදࣔͯ͠ڭ͑ͯ͘ΕΔ -
# config/environments/test.rb Rails.application.configure do config.after_initialize do Bullet.enable = true
Bullet.raise = true end end Bullet 🔫 ɹraise Λࢦఆ͢ΔͱΤϥʔΛى͜͢ -
BulletmarkRepairer 🩹 class PlaysController < ApplicationController def index @plays
= Play.all end end <% @plays.each do |play| %> <% play.actors.each do |actor| %> <%= actor.name %> <% end %> <% end %> N+1 ൃੜ -
BulletmarkRepairer 🩹 class PlaysController < ApplicationController def index @plays
= Play.all end end <% @plays.each do |play| %> <% play.actors.each do |actor| %> <%= actor.name %> <% end %> <% end %> N+1 ൃੜ $ REPAIR=1 rspec -
BulletmarkRepairer 🩹 class PlaysController < ApplicationController def index @plays
= Play.all.includes([:actors]) end end <% @plays.each do |play| %> <% play.actors.each do |actor| %> <%= actor.name %> <% end %> <% end %> ɹ࣮ߦ࣌ʹ autocorrect Ͱ ɹincludes ΛՃ͢Δ $ REPAIR=1 rspec -
Case 1. class PlaysController < ApplicationController def index @plays
= Play.all end end <% @plays.each do |play| %> <% play.actors.each do |actor| %> <%= actor.name %> <% end %> <% end %> ɹϏϡʔͰ N+1 ͕ ɹൃੜ͢Δέʔε -
Case 1. class PlaysController < ApplicationController def index @plays
= Play.all end end <% @plays.includes(:actors).each do |play| %> <% play.actors.each do |actor| %> <%= actor.name %> <% end %> <% end %> ɹϏϡʔͰ ɹincludes ͨ͘͠ͳ͍ 🙅 -
Case 1. class PlaysController < ApplicationController def index @plays
= Play.all.includes(:actors) end end <% @plays.each do |play| %> <% play.actors.each do |actor| %> <%= actor.name %> <% end %> <% end %> ɹίϯτϩʔϥͰ ɹincludes ͍ͨ͠ 🙆 -
Case 2. class PlaysController < ApplicationController def index plays
= Play.all.map do |play| { title: play.name, actors: play.actors.map(&:name) } end render json: { data: plays } end end ɹίϯτϩʔϥͰ ɹൃੜ͢Δέʔε -
Case 2. class PlaysController < ApplicationController def index plays
= Play.all.includes(:actors).map do |play| { title: play.name, actors: play.actors.map(&:name) } end render json: { data: plays } end end ɹίϯτϩʔϥͰ ɹ͢ͱΑͦ͞͏ 🙆 -
Case 3. class PlaysController < ApplicationController before_action :set_plays def
index render json: { data: @plays.as_json } end private def set_plays @plays = Play.all end end ɹίʔϧόοΫͰ ɹActiveRecord Λ ɹΈཱͯΔέʔε -
Case 3. class PlaysController < ApplicationController before_action :set_plays def
index render json: { data: @plays.as_json } end private def set_plays @plays = Play.all.includes(:actors) end end ɹ͜ͷ߹ ɹίʔϧόοΫͰͯ͠ ɹΑͦ͞͏ 🙆 -
Case 4. class PlaysController < ApplicationController def index render
json: { data: scope.as_json } end def show render json: { data: scope.find(params[:id]).as_json } end private def scope Play.where('name like ?', params[:name]) end end ɹڞ௨ͷείʔϓΛ ɹ͍ͬͯΔ߹ -
Case 4. class PlaysController < ApplicationController def index render
json: { data: scope.as_json } end def show render json: { data: scope.find(params[:id]).as_json } end private def scope Play.where('name like ?', params[:name]) end end ɹΞΫγϣϯͰ ɹ͖͢…ʁ🤔 ɹ͜ͷ͘Β͍ͳΒ ɹείʔϓͰͯ͠ ɹΑͦ͞͏͚ͩͲ…🤔 ɹshow Ͱ includes ͢Δ ɹඞཁͳ͘ͳ͍ʁ🤔 -
Case 5. ɹίϯτϩʔϥ͔Β ɹݺͼग़ͨ͠ ɹϞσϧͷϝιουͰ ɹN+1
͕ൃੜ͢Δέʔε class PlaysController < ApplicationController def index plays = Play.all render json: { data: plays.map(&:as_json_with_actors) } end end class Play < ApplicationRecord def as_json_with_actors as_json.merge(actors: actors.map(&:name)) end end -
Case 5. class PlaysController < ApplicationController def index plays
= Play.all.includes(:actors) render json: { data: plays.map(&:as_json_with_actors) } end end class Play < ApplicationRecord def as_json_with_actors as_json.merge(actors: actors.map(&:name)) end end ɹίϯτϩʔϥͰ ɹ͢ͱΑͦ͞͏ 🙆 -
Case 6. ɹϞσϧͷίʔϧόοΫͰ ɹΫΤϦ্ͨ͠͠Ͱ ɹൃੜ͍ͯ͠Δέʔε class Play
< ApplicationRecord after_save :notify_actors, if: :notification_required? private def notify_actors actors.where(...).each(&:notify!) end end -
Case 6. class Play < ApplicationRecord after_save :notify_actors, if:
:notification_required? private def notify_actors actors.where(...).includes(:actors).each(&:notify!) end end ɹϞσϧͰ ɹ͢ͱΑͦ͞͏ʁ -
Case 7. class TransitionPastPlaysService def initialize(border_date) @border_date = border_date
end def execute plays = Play.where(created_at: ..@border_date) play_actors = plays.map do |play| { play_id: play.id, actor_id: play.actors.map(&:id) } end PastPlay.insert_all plays.map(&:as_json) PastPlayActor.insert_all play_actors plays.destroy_all end end ɹαʔϏεͰ ɹPlay.where ্ͨ͠Ͱ ɹൃੜ͢Δέʔε ※ ຊདྷ map(&:id) Ͱͳ͘ pluck(:id) ids Λ͑ N+1 ͷӨڹখ͘͞ͳΓ·͢ -
Case 7. class TransitionPastPlaysService def initialize(border_date) @border_date = border_date
end def execute plays = Play.where(created_at: ..@border_date).includes(:actors) play_actors = plays.map do |play| { play_id: play.id, actor_id: play.actors.map(&:id) } end PastPlay.insert_all plays.map(&:as_json) PastPlayActor.insert_all play_actors plays.destroy_all end end ※ ຊདྷ map(&:id) Ͱͳ͘ pluck(:id) ids Λ͑ N+1 ͷӨڹখ͘͞ͳΓ·͢ ɹαʔϏεͰ ɹ͔͢͠ͳ͍ -
•جຊతʹίϯτϩʔϥͰ includes ͢Δ •ϓϥΠϕʔτϝιουΑΓΞΫγϣϯͰ includes ͢Δ͜ͱ͕ଟ͍ •ActiveRecord ͷΈཱͯॳظͰ includes ͢Δ͜ͱ͕ଟ͍
•ΉΛಘͳ͚ΕϞσϧɾαʔϏεΫϥε etc Ͱ includes ͢Δ ;ͩΜͷࢥߟճ࿏ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end ɹBullet ͷϩά 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹελοΫτϨʔεʹ ɹίϯτϩʔϥ͕ͳ͍ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹमਖ਼͍ͨ͠ϑΝΠϧ ɹͬͪ͜ ɹελοΫτϨʔεʹ ɹίϯτϩʔϥ͕ͳ͍ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹϏϡʔͰ N+1 Λൃੜͤ͞Δ ɹଟ͘ͷ߹Πϯελϯεมʹೖ͍ͬͯΔ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹN+1 ൃੜߦΘ͔Δ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹରͬΆ͍ ɹΠϯελϯεม͕Θ͔Δʢצʣ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹURL ͕Θ͔Δ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹίϯτϩʔϥͱ ɹΞΫγϣϯ͕Θ͔Δ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹΠϯελϯεมʹ ɹೖ͢Δͱ͜Ζʹ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each
do |actor| %> <%= actor.name %> <% end %> <% end %> class PlaysController < ApplicationController def index @plays = Play.all.includes(:actors) end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:2:in `block in _app_views_plays _index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/ index.html.erb:1:in `_app_views_plays _index_html_erb__3206924200111145907_11500’ ɹincludes Λ͢✌ -
Ϗϡʔฤ <% @plays.each do |play| %> <% play.actors.each do
|actor| %> <% if @favorite %> <%= actor.name %> <% end %> <% end %> <% end %> ରͷΠϯελϯεม צͰ୳͍ͯ͠ΔͷͰ ͜Μͳίʔυͩͱಈ͔ͳ͍ N+1 ൃੜՕॴ ΠϯελϯεมΆ͍ จࣈྻͦͷ 1ʢμτʣ ΠϯελϯεมΆ͍ จࣈྻͦͷ 2ʢຊʣ -
Ϗϡʔฤ <% Play.all.each do |play| %> <% play.actors.each do
|actor| %> <%= actor.name %> <% end %> <% end %> ΠϯελϯεมΛ લఏͱ͍ͯ͠ΔͷͰ ͜Μͳίʔυಈ͔ͳ͍ -
ίϯτϩʔϥฤ class PlaysController < ApplicationController def index plays =
Play.all.map do |play| { title: play.name, actors: play.actors.map(&:name) } end render json: { data: plays } end end ɹ͜͜Ͱ includes ͢Δͱ ɹΑͦ͞͏ -
ίϯτϩʔϥฤ class PlaysController < ApplicationController def index @plays =
scope if params[:future] @plays = @plays.where(performed_at: Date.today..) end gon.as_json = @plays.as_json end def show @play = scope.find(params[:id]) end private def scope Actor.find(params[:actor_id]).plays end end ɹ͜͜Ͱ includes ͢Δͱ ɹΑͦ͞͏ -
ίϯτϩʔϥฤ class PlaysController < ApplicationController before_action :set_plays def index
render json: { data: @plays.as_json } end private def set_plays @plays = Play.all end end ɹ͜͜Ͱ includes ͢Δͱ ɹΑͦ͞͏ʁ -
ίϯτϩʔϥฤ class PlaysController < ApplicationController def index @plays =
scope if params[:future] @plays = @plays.where(performed_at: Date.today..) end gon.as_json = @plays.as_json end def show @play = scope.find(params[:id]) end private def scope Actor.find(params[:actor_id]).plays end end ɹͻͱͭͷϝιουͰ ɹෳճೖ͞Ε͍ͯΔ߹ ɹͲͪΒ͕ਖ਼͍͔͠ ɹػձతͳఆແཧ -
ίϯτϩʔϥฤ class PlaysController < ApplicationController def index @plays =
scope if params[:future] @plays = @plays.where(performed_at: Date.today..) end gon.as_json = @plays.as_json end def show @play = scope.find(params[:id]) end private def scope Actor.find(params[:actor_id]).plays end end ɹ࠷ॳʹೖ͢Δ΄͏͕ ɹϢʔεέʔεͱͯ͠ଟͦ͏ -
<% @plays.each do |play| %> <% play.actors.each do |actor|
%> <%= actor.name %> <% end %> <% end %> <% @plays.each do |play| %> <% play.actors.each do |actor| %> <%= actor.company.name %> <% end %> <% end %> ɹ͜Μͳײ͡ͷϏϡʔ͕ ɹ͋Δͱ͢Δ -
Case 1. 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE
eager loading detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__3206924200111145907_11500' class PlaysController < ApplicationController def index @plays = Play.all end end ɹجຊέʔε -
2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading
detected Play => [:actors] Add to your query: .includes([:actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__3206924200111145907_11500' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__3206924200111145907_11500' class PlaysController < ApplicationController def index @plays = Play.all.includes(:actors) end end Case 1. ɹجຊέʔε -
Case 2. class PlaysController < ApplicationController def index @plays
= Play.all end end ɹassociations ͕ ɹෳ͋Δέʔε 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:theaters] Add to your query: .includes([:theaters]) -
class PlaysController < ApplicationController def index @plays = Play.all.includes([:actors,
:theaters]) end end Case 2. ɹassociations ͕ ɹෳ͋Δέʔε 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:theaters] Add to your query: .includes([:theaters]) -
Case 3. 2023-10-26 14:37:36[WARN] user: makicamel GET /plays USE
eager loading detected Play => [{:actors=>[:company]}] Add to your query: .includes([:"{:actors=>[:company]}"]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__1665884924136242781_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_plays_index_html_erb__1665884924136242781_11760' class PlaysController < ApplicationController def index @plays = Play.all end end ɹωετ͍ͯ͠Δέʔε -
Case 3. 2023-10-26 14:37:36[WARN] user: makicamel GET /plays USE
eager loading detected Play => [{:actors=>[:company]}] Add to your query: .includes([:"{:actors=>[:company]}"]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__1665884924136242781_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_plays_index_html_erb__1665884924136242781_11760' class PlaysController < ApplicationController def index @plays = Play.all.includes([{:actors=>[:company]}]) end end ɹωετ͍ͯ͠Δέʔε -
Case 4. 2023-10-26 14:57:19[WARN] user: makicamel GET /plays
USE eager loading detected Play => [:awesome_actors] Add to your query: .includes([:awesome_actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_html_erb___4265071383862059390_11760’ /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb___4265071383862059390_11760' class PlaysController < ApplicationController def index @plays = Play.all end end ɹωετ͍ͯ͠Δ͔ͭ ɹassociation ʹผ໊͕͋Δέʔε class Play < ApplicationRecord has_many :play_actors has_many :awesome_actors, through: :play_actors, source: :actor end -
class PlaysController < ApplicationController def index @plays =
Play.all.includes({:awesome_actors=>[:company]}) end end Case 4. 2023-10-26 14:57:19[WARN] user: makicamel GET /plays USE eager loading detected Play => [:awesome_actors] Add to your query: .includes([:awesome_actors]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_html_erb___4265071383862059390_11760’ /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb___4265071383862059390_11760' ɹωετ͍ͯ͠Δ͔ͭ ɹassociation ʹผ໊͕͋Δέʔε class Play < ApplicationRecord has_many :play_actors has_many :awesome_actors, through: :play_actors, source: :actor end -
Case 5. 2023-10-26 14:46:46[WARN] user: makicamel GET /plays
USE eager loading detected Actor => [:company] Add to your query: .includes([:company]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:3:in `block (2 levels) in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__2119670124553133993_11760' class PlaysController < ApplicationController def index @plays = Play.includes(:actors) end end ɹҰ෦ includes ࡁͷέʔε -
Case 5. 2023-10-26 14:46:46[WARN] user: makicamel GET /plays
USE eager loading detected Actor => [:company] Add to your query: .includes([:company]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:3:in `block (2 levels) in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__2119670124553133993_11760' class PlaysController < ApplicationController def index @plays = Play.includes(:actors).includes({:actors=>[:company]}) end end ɹҰ෦ includes ࡁͷέʔε -
AssociationsBuilder module BulletmarkRepairer class AssociationsBuilder def build(marker) return if
marker.skip? if associations[marker.index] associations[marker.index].add(marker) else associations[marker.index] = Associations.new( marker, @application_associations, @loaded_associations ) end end end end ɹBullet ͷܯࠂΛநԽͨ͠ ɹΠϯελϯε -
AssociationsBuilder module BulletmarkRepairer class AssociationsBuilder def build(marker) return if
marker.skip? if associations[marker.index] associations[marker.index].add(marker) else associations[marker.index] = Associations.new( marker, @application_associations, @loaded_associations ) end end end end ɹN+1 ൃੜՕॴΛΩʔʹͯ͠ ɹ ɹ -
AssociationsBuilder module BulletmarkRepairer class AssociationsBuilder def build(marker) return if
marker.skip? if associations[marker.index] associations[marker.index].add(marker) else associations[marker.index] = Associations.new( marker, @application_associations, @loaded_associations ) end end end end ɹN+1 ൃੜՕॴΛΩʔʹͯ͠ ɹassociation ΛΈཱͯΔ -
AssociationsBuilder module BulletmarkRepairer class AssociationsBuilder def build(marker) return if
marker.skip? if associations[marker.index] associations[marker.index].add(marker) else associations[marker.index] = Associations.new( marker, @application_associations, @loaded_associations ) end end end end 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:actors] Add to your query: .includes([:actors]) 2023-10-23 07:21:04[WARN] user: makicamel GET /plays USE eager loading detected Play => [:theaters] Add to your query: .includes([:theaters]) ɹෳͷܯࠂΛ ɹͻͱͭʹ·ͱΊ͍ͨ ɹέʔε͕͋Δ -
AssociationsBuilder •γϯϘϧΛϋογϡʹͨ͠Γ [:actor]ɹˠɹ[{ actor: [:company] }] •ϋογϡͷόϦϡʔΛྻʹมͯ͠ΛՃͨ͠Γ [{
actors: :company }, :staffs] ɹˠɹ[{ actors: [:company, :works] }, :staffs] •ྻͷͷͻͱͭͱՃ͍ͨ͠Λϋογϡʹมͨ͠Γ [{ actors: :works }]ɹˠɹ[{ actors: { works: [:staffs] } }] ͳΜ͔͍ͯ͠Δ ɹෳͷܯࠂΛ ɹͻͱͭʹ·ͱΊ͍ͨ ɹέʔε͕͋Δ -
2023-10-26 14:46:46[WARN] user: makicamel GET /plays USE eager
loading detected Actor => [:company] Add to your query: .includes([:company]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:3:in `block (2 levels) in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__2119670124553133993_11760' class PlaysController < ApplicationController def index @plays = Play.includes(:actors) end end ɹҰ෦ includes ࡁͷέʔε LoadedAssociations -
2023-10-26 14:46:46[WARN] user: makicamel GET /plays USE eager
loading detected Actor => [:company] Add to your query: .includes([:company]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:3:in `block (2 levels) in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__2119670124553133993_11760' class PlaysController < ApplicationController def index @plays = Play.includes(:actors).includes({:actors=>[:company]}) end end ɹҰ෦ includes ࡁͷέʔε LoadedAssociations -
2023-10-26 14:46:46[WARN] user: makicamel GET /plays USE eager
loading detected Actor => [:company] Add to your query: .includes([:company]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:3:in `block (2 levels) in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__2119670124553133993_11760' class PlaysController < ApplicationController def index @plays = Play.includes(:actors).includes({:actors=>[:company]}) end end ɹBullet ͷܯࠂ͔Β ɹ@plays ͷϕʔεͷΫϥε͕ ɹԿ͔Θ͔Βͳ͍ LoadedAssociations -
2023-10-26 14:46:46[WARN] user: makicamel GET /plays USE eager
loading detected Actor => [:company] Add to your query: .includes([:company]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:3:in `block (2 levels) in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__2119670124553133993_11760' ɹBullet ͷܯࠂ͔ΒͲͪΒ͕ ɹਖ਼͍͔͠Θ͔Βͳ͍ LoadedAssociations class PlaysController < ApplicationController def index @plays = Play.includes(:actors).includes({:actors=>[:company]}) end end .includes([:company]) -
LoadedAssociations module BulletmarkRepairer module ActiveRecord module QueryMethod def includes(*args)
Thread.current[:bulletmark_repaier_loaded_associations][model.name][:includes].add(args) super(args) end def eager_load(*args) Thread.current[:bulletmark_repaier_loaded_associations][model.name][:eager_load].add(args) super(args) end def preload(*args) Thread.current[:bulletmark_repaier_loaded_associations][model.name][:preload].add(args) super(args) end end end end ɹಡΈࠐΈࡁͷ associations Λ ɹϝϞ͓ͯ͘͠ -
LoadedAssociations ɹassociation ΛΈཱͯΔ࣌ʹ ɹࢀর͢Δ class Associations def initialize(marker,
application_associations, loaded_associations) @marker = marker @application_associations = application_associations @loaded_associations = loaded_associations key = @loaded_associations.key(marker.base_class) @associations = { base: key ? { key => marker.associations } : marker.associations } end end -
2023-10-26 14:46:46[WARN] user: makicamel GET /plays USE eager
loading detected Actor => [:company] Add to your query: .includes([:company]) Call stack /Users/makicamel/awesome_app/app/views/plays/index.html.erb:3:in `block (2 levels) in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:2:in `block in _app_views_plays_index_html_erb__2119670124553133993_11760' /Users/makicamel/awesome_app/app/views/plays/index.html.erb:1:in `_app_views_plays_index_html_erb__2119670124553133993_11760' ɹactors includes ࡁͳͷͰ ɹωετ͢Δͷ͕ਖ਼͍͠ 💡 LoadedAssociations class PlaysController < ApplicationController def index @plays = Play.includes(:actors).includes({:actors=>[:company]}) end end .includes([:company]) -
•preload ͕·͍͠έʔε͕ଟ͍ •eager_load LEFT OUTER JOIN ͳͷͰϝϞϦΛ৯͍͍͢ •preload ͷ߹
IN ۟ʹೖΔ id ͷ͕ଟ͘ʢe.g. ສ݅ʣͳΔͱ ύϑΥʔϚϯε͕ஶ͘͠Լ͢Δ͜ͱ͕͋Δ •preload or eager_load Ҏ֎ͷํ๏͋Δ preload vs. eager_load ઈରղͳ͍ -
•includes ͩͱύϥϝʔλঢ়ଶʹΑΓ preload or eager_load ͕ ͍͚ΒΕΔ͜ͱ͕͋Δ •ϦΫΤετʹΑΓൃߦ SQL
͕มΘΔͱੳͮ͠Β͍ includes vs. preload or eager_load ੳɾӡ༻໘Ͱ includes ΑΓ preload or eager_load ͕·͍͠ -
ϝϞϦѹഭ͕৺ͳΜͰ͚͢Ͳ... N+1 → LEFT JOIN ͰϝϞϦ͕ʹͳΔͳΒ ଟ͘ͷ߹ N+1 ͷ࣌Ͱ
ʹͳ͍ͬͯΔͱࢥ͍·͢Α includes vs. preload or eager_load @tricknotes -
N+1 < includes < preload or eager_load (or ͦͷଞ) ͷ͍͚
includes vs. preload or eager_load -
ݱঢ়ͷANDPADͷվળ͍ͨ͠ͱ͜ΖΛ ざ ͬ͘ ば ΒΜʹʢANDPAD ࣾ։࠵ʣ 2020 5 ݄
22 @a_matsuda -
•ͨ·ͨ·ݟ͚ͭͨ N+1 ΛͱΓ͋͑ͣͯ͠ PR Λ࡞Δਓ •PR ΛϤγ 👈 ͯ͠औΓࠐΉਓ N+1
ୀ࣏൪ BulletmarkRepairer ͜ͷ෦ͷ αϙʔτπʔϧ 🩹 -
•ͨ·ͨ·ݟ͚ͭͨ N+1 ΛͱΓ͋͑ͣͯ͠ PR Λ࡞Δਓ •PR ΛϤγ 👈 ͯ͠औΓࠐΉਓ •ൃߦ͞ΕΔ
SQL ͷมԽͷ֬ೝ •࣮σʔλ্ͳͦ͞͏͔ͷ֬ೝ ͜ͷϓϩηεվળ ͔͚ͨͬͨ͠Ͳ ؒʹ߹Θͳ͔ͬͨ ͜ͷϓϩηε վળ͍ͨ͠ ʢؒʹ߹Θͳ͔ͬͨʣ N+1 ୀ࣏൪ -
•e.g. Ұ෦ͷ݅࣌ͷΈಡΈࠐΜͩϨίʔυ͕ར༻͞ΕΔέʔε → ෆཁͳΒಡΈࠐ·ͳ͍ •e.g. ϙϦϞʔϑΟοΫؔ࿈ͷ ಛఆͷؔ࿈ͰͷΈϨίʔυ͕ར༻͞ΕΔέʔε
→ ෆཁͳΒಡΈࠐ·ͳ͍ N+1 ͷղ๏ ෆཁͳΒ ಡΈࠐ·ͳ͍