Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Railsでのクエリ改善の話

 Railsでのクエリ改善の話

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

7dedc55a5e5c3e4008386473a179eb4b?s=128

Ryunosuke Sato

March 02, 2021
Tweet

Transcript

  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ඇґଘͰ࢖

    ͑·͢ ·ͱΊ