Railsでのクエリ改善の話

ANDPAD TechLive #3　プロダクトの成長と共に歩むRails開発改善の歴史と未来での発表資料です。
https://andpad.connpass.com/event/204747

1. 3BJMTͰͷΫΤϦ վળͷ࿩ 
   "/%1"%
   5FDI-JWF
    ࠤ౻ཽ೭հ !USJDLOPUFT
2. !USJDLOPUFT 3VCZ
   
   +BWB4DSJQU
   "/%1"%
   ͰͷऔΓ૊Έ
   3BJMT
   ඪ४Խ
   ύϑΥʔϚϯεվળ
   Πϯϑϥվળ
3. ࠓ೔ͷ࿩ "/%1"%
   Ͱ࣮ࡍʹऔΓ ૊ΜͩΫΤϦͷύϑΥʔ Ϛϯεվળʹ͍ͭͯ
4. ࠓ೔ͷ͓୊
5. ࠓ೔ͷ͓୊ ະಡ਺දࣔͷΫΤϦ
6. σʔλʹ͍ͭͯ 1 n 1 1 n n ඇৗʹ਺͕ଟ͍ .BY
   ϢʔβʔʷҊ݅ 6TFS 0SEFS /PUJpDBUJPO 5FBN Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌
7. w Ҋ݅͝ͱͷνϟοτϧʔϜͰ ͷϝϯγϣϯ
   w ଞͷϢʔβʔىҼͷΞΫγϣ ϯͷ͓஌Βͤ
   w ʜͳͲ "/%1"%
   Ͱͷ௨஌ͱ͸
8. "/%1"%
   Ͱͷະಡͱ͸ w Ϣʔβʔ͸ͨ͘͞ΜͷҊ݅ʹࢀՃͰ͖Δ
   w Ҋ݅Ͱ͸ɺϢʔβʔʹରͯͨ͘͠͞Μ௨஌ ͕࡞੒͞ΕΔ
   w ௨஌ͻͱͭͻͱͭʹରͯ͠ɺະಡɾطಡ͕ ͋Δ
   w ҰཡͰ͸ະಡ਺ͷଟ͍ॱʹҊ͕݅ฒͿ
9. ༨ஊ ະಡ͸೉͍͠ w ݅਺͚ͩͰ͍͍ͷ͔
   w ͳʹΛͲ͜·ͰΈ͔ͨ΋ඞཁ͔
   w ࣃൈ͚Λߟྀ͢Δ͔
   w ͲΕ͘Β͍ͷظؒඞཁͳͷ͔
   w Ͳ͜·ͰͷzະಡzΛϢʔβʔ͸஌Γͨ ͍ͷ͔
   w ະಡ͕ଟ͍ͱͲ͏ද͕ࣔมΘΔ͔
10. ະಡ਺ͷදࣔ "/%1"%
    Ͱ͸ඇৗʹେྔͷ εϩʔΫΤϦΛൃߦ͢Δ͜ ͱͰ஌ΒΕ͍ͯͨ
    ௨শ
    εϩʔΫΤϦࡾܑఋ
11. ϝτϦΫεͷ֬ೝ
12. ࢦඪʹ͍߲ͨ͠໨Ͱ
    ෛՙͷߴ͍΋ͷ͔Β֬ೝ ϝτϦΫεͷ֬ೝ αʔϏεͷੑ࣭ʹదͨ͠
    ظؒΛࢦఆ ϘτϧωοΫʹͳ͍ͬͯͦ͏ͳ
    ࢦඪΛબ୒
13. 4JEFLJR
    ͳͷͰϤγ  ߴස౓ɺ͔ͭॏ͍ΫΤϦ ϝτϦΫεͷ֬ೝ
14. Έͳ͞ΜͳΒ
    Ͳ͏͍͏վળͷΞϓϩʔν Λࢥ͍ͭ͘Ͱ͠ΐ͏͔ʁ
15. ࠓ೔ͷ಺༰ w ύϑΥʔϚϯεվળͷΞϓϩʔνͷ͝঺հ
    w ύϑΥʔϚϯεͱ޲͖߹͏ํͷࢀߟʹͳΕ͹
16. w ํ਑ཱͯ
    w औΓ૊Έ ͓඼ॻ͖ ͜Ε͔ΒύϑΥʔϚϯεվળʹ޲͖߹͏ํͷ ࢀߟʹͳΕ͹ͱ ύϑΥʔϚϯεվળͷΞϓϩʔνͷ͝঺հ
17. ํ਑ཱͯ
18. ·ͣ͸ݱঢ় Ҋ݅ͱ௨஌Λ
    +0*/
    ͍ͯͨ͠ SELECT orders.*, SUM(IF(notifications.id IS NULL, 0, 1)) AS unread_count FROM `orders` LEFT JOIN notifications ON notifications.order_id = orders.id AND notifications.user_id = ? AND notifications.state = 0 WHERE `orders`.`deleted_at` IS NULL GROUP BY orders.id
19. ࠷ॳͷํ਑ܾΊ ʲԾઆʳ
    ʮҊ݅ͷॴଐʯʹະಡ਺Λॻ͖͜ΜͰ͓͚͹ɺ +0*/
    ର৅ͷϨίʔυ਺ΛݮΒͤͯߴ଎ԽͰ͖Δ
20. ະಡ਺ͷදࣔ 6TFS 0SEFS /PUJpDBUJPO 5FBN 1 n n 1 1 n ඇৗʹ਺͕ଟ͍ .BY
    ϢʔβʔʷҊ݅ ͜͜Λࢀর͍ͯͨ͠ͷΛ΍Ίͯ… Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌
21. ະಡ਺ͷදࣔ 1 n 1 1 1. ʮະಡ਺ʯΛΧϥϜͱͯ͠௥Ճ͢Δ 2. σʔλߋ৽࣌ʹɺ ʮະಡ਺ʯΛॻ͖ࠐΉ n ͪ͜ΒΛࢀরͰ͖Δ͔ /PUJpDBUJPO 6TFS 0SEFS 5FBN Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌
22. ࠷ॳͷํ਑ܾΊ ʲݕূ݁Ռʳ
    ഒఔ౓ͷվળޮՌ͕ݟࠐ·Εͨ
23. ࠷ॳͷํ਑ܾΊ
24. ଞͷީิͨͪ w Χ΢ϯλʔΩϟογϡํࣜ
    w ผͷετϨʔδΛ࢖͏ ˠ
    ख਺͕ඞཁͦ͏ͳͷͰɺ
    ࠷ॳͷҊͰޮՌ͕ͳ͚Ε͹΍Δ
25. ͍ͬͨΜͷΰʔϧ ʲ#FGPSFʳ SELECT orders.*, SUM(IF(notifications.id IS NULL, 0, 1)) AS unread_count FROM `orders` LEFT JOIN notifications ON notifications.order_id = orders.id AND notifications.user_id = ? AND notifications.state = 0 WHERE `orders`.`deleted_at` IS NULL GROUP BY orders.id
26. ʲ"GUFSʳ SELECT orders.*, CASE WHEN t.notifications_yet_count IS NULL THEN 0 ELSE t.notifications_yet_count END AS unread_count FROM `orders` LEFT JOIN teams AS t ON t.order_id = orders.id AND t.deleted_at IS NULL AND t.user_id = ? WHERE `orders`.`deleted_at` IS NULL ͍ͬͨΜͷΰʔϧ ˠ
    +0*/
    ͢ΔςʔϒϧͷϨίʔυྔ͕࡟ݮ͞Εͨ
    
27. ࠷ॳͷํ਑ܾΊ ͔͠͠ɺʮҊ݅ͷॴଐʯʹରͯ͠ͷະಡ਺ߋ৽ʹ ͋ͨͬͯɺෛՙ͕૿͑Δ͜ͱ͕ݒ೦͞Εͨ ࢀরʹ͍ͭͯ͸͜ͷํ਑ͰΑͦ͞͏
28. ͞ΒͳΔվળͷҊग़͠
29. w ͜Ε͸ແཧͩΖ͏ɺͱࢥΘΕΔΑ͏ͳҊ Ͱ΋ࢥ͍ͭ͘ݶΓग़ͯ͠ΈΔ
    w Ұ୴ൃࢄͤ͞Δ
    w ʮະಡ਺ͷදࣔΛ΍ΊΔʯͱ͔΋0, ͞ΒͳΔվળͷҊग़͠
30. վળҊͷ୨Է͠
31. w ίεύ͕Αͦ͞͏ͳ΋ͷɺଞʹӨڹΛ༩͑ ͦ͏ͳ΋ͷΛ༏ઌ͢Δ
    w ม਺͕େ͖͍΋ͷ͸ޙճ͠ վળҊͷ୨Է͠ ͋ͱ͸΍͍ͬͯ͘ͷΈ🔥
32. ԾઆˠݕূˠԾઆʜͷϧʔϓ
    ࢼ͖ͬͯ͠े෼ͳޮՌ͕ಘΒΕͳ͚Ε͹ɺ
    ·ͨผͷҊΛࢼ͢
33. w ΫΤϦͷվળ͕ओ؟ͩͬͨͷͰɺຊ൪ͱ ಉ౳ͷσʔλྔɾੑೳͷ
    3%4
    Λ४උ͢Δ
    w ݕূ؀ڥͰ͸࠶ݱͷ೉͍͠৔߹ ఆৗతͳ ෛՙɺͳͲ ͸ɺຊ൪Ͱ҆શʹݕূ͢Δ ຊ൪ΛͳΔ΂͘࠶ݱ
34. ࣮ࡍʹϘτϧωοΫʹͳΓͦ͏ͳ
    ঢ়گΛ࠶ݱ͢Δ
    w ߴෛՙɺͨ͘͞ΜͷҊ݅ʹॴଐ͍ͯ͠Δ
    w ߴෛՙɺະಡ͕ͨ͘͞Μͨ·͍ͬͯΔ
    w தԝ஋ͬΆ͍σʔλ΋࠶ݱ͢Δ ຊ൪ΛͳΔ΂͘࠶ݱ
35. ຊ൪ΛͳΔ΂͘࠶ݱ ˠ
    ϚεΫ͞Εͨ
    %#
    
    Λར༻
36. ͍͔ͭ͘ͷ
    औΓ૊Έͱ݁Ռ
37. ߋ৽ॲཧͷඇ ಉظԽ
38. ʲԾઆʳ
    ߋ৽ॲཧΛඇಉظʹ͢ΔͱɺϨεϙϯε଎ ౓ΛվળͰ͖Δ
    ฒྻԽ͢Δ͜ͱͰɺॲཧதͷδϣϒΛૣ͘ ׬ྃͰ͖Δ ߋ৽ॲཧͷඇಉظฒྻԽ ʲલఏʳ
    ߋ৽ॲཧʹෛՙ͕͔͔Δ͜ͱ͕ݟࠐ·Εͨ
    
39. ʲݕূ݁Ռʳ
    ࣮ࡍʹ͸ɺ!BMM
    ͷϝϯγϣϯͰෛՙͷߴ͍ ΫΤϦ͕ಉ࣌ଟൃతʹൃੜ͢Δ͜ͱ͕൑໌
    ˠ
    3%4
    ͷ
    $16
    ࢖༻཰͕͸Ͷ্͕ͬͨͷͰ Ϙπʹ ߋ৽ॲཧͷඇಉظฒྻԽ
40. σʔλͷ࡟আ
41. σʔλͷ࡟আ ʲલఏʳ
    ະಡ͕஝ੵ͍ͯ͠ΔϢʔβʔ͕Ұఆ਺͍Δ
    ʲԾઆʳ
    ͋Δఔ౓աڈͷσʔλʹ͍ͭͯ͸ɺ࡟আͯ͠ ͠·ͬͯ΋ϢʔβʔӨڹ͸ͳ͍ͩΖ͏
    σʔλͷ୳ࡧൣғ͕ڱ͘ͳΔ͜ͱͰվળޮ Ռ͕ಘΒΕΔͷͰ͸
42. σʔλͷ࡟আ ʲݕূ݁Ռʳ
    8)&3&
    ۟ͷ৚݅Λݟ௚͚ͩ͢Ͱे෼ͳޮ Ռ͕ಘΒΕͨ
    ҰํɺҰ౓ૹ৴ͨ͠௨஌Λ࡟আ͢Δͱɺ࢓ ༷ʹӨڹΛ༩͑ΔͷͰ৻ॏͳ൑அ͕ٻΊΒ Εͨ
43. σʔλͷ࡟আ ʲݕূ݁Ռʳ ˠ
    8)&3&
    ͷݕࡧର৅ߜΓࠐΈΛ࠾༻ͨ͠
44. ΠϯσοΫε ͷ௥Ճ
45. ΠϯσοΫεͷ௥Ճ ʲલఏʳ
    ະಡ਺औಘͷࡍɺඞͣ૊Έ߹Θͤͯ࢖ΘΕΔ
    8)&3&
    ͕۟͋ͬͨ
    ֘౰৚݅ʹରͯ͠ͷෳ߹ΠϯσοΫε͸ͳ͍ ʲԾઆʳ
    ΠϯσοΫεΛ௥Ճ͢Δͱɺݕࡧൣғ͕ߜ Γࠐ·Εͯߴ଎Խ͢Δ
46. ΠϯσοΫεͷ௥Ճ ʲݕূ݁Ռʳ
    ΠϯσοΫεΛ௥Ճ࣮ͯ͠ߦܭըΛ֬ೝ͢Δ ͱɺ࠷ॳʹར༻͞ΕΔΠϯσοΫεͱͯ͠ද ࣔ͞Ε͍ͯͨ
47. ˠ
    dഒͷվળޮՌ͕͋ͬͨ ΠϯσοΫεͷ௥Ճ
48. ੒Ռ
49. w ѹ౗తվળޮՌʜͱݴ͍͍ͨͱ͜ΖͰ͕͢ɺ ໌೔ϦϦʔε༧ఆ
    w ·ͣ͸ɺෛՙͷ௿͍࣌ؒଳΛૂͬͯෛՙͷ༷ ࢠΛΈͭͭঃʑʹ։์༧ఆ
    w ظ଴஋͸ɺߋ৽͕dY஗͘ͳΔ
    
    ࢀর͕ dYഒૣ͘ͳΔ͜ͱ ੒Ռ
50. ·ͱΊ
51. w Ծઆͷஈ֊Ͱ͸ߜΓࠐΈ͗͢ͳ͍
    w ݕূ͸·ͣ࠷খͷํ๏Ͱߦ͍ɺޮՌ͕ಘ ΒΕͨΒ࣮૷Λߦ͏
    w ݕূʹ͸ద੾ͳσʔλ४උͱՄࢹԽ͕ޮ ՌతͰ͢
    w վળͷΞϓϩʔν͸ݴޠɾ'8ඇґଘͰ࢖ ͑·͢ ·ͱΊ