Railsでのクエリ改善の話

by ANDPAD inc

Slide 1

Slide 1 text

3BJMTͰͷΫΤϦ վળͷ࿩ "/%1"%5FDI-JWF ࠤ౻ཽ೭հ !USJDLOPUFT

Slide 2

Slide 2 text

!USJDLOPUFT 3VCZ+BWB4DSJQU "/%1"%ͰͷऔΓ૊Έ 3BJMTඪ४Խ ύϑΥʔϚϯεվળ Πϯϑϥվળ

Slide 3

Slide 3 text

ࠓ೔ͷ࿩ "/%1"%Ͱ࣮ࡍʹऔΓ ૊ΜͩΫΤϦͷύϑΥʔ Ϛϯεվળʹ͍ͭͯ

Slide 4

Slide 4 text

ࠓ೔ͷ͓୊

Slide 5

Slide 5 text

ࠓ೔ͷ͓୊ ະಡ਺දࣔͷΫΤϦ

Slide 6

Slide 6 text

σʔλʹ͍ͭͯ 1 n 1 1 n n ඇৗʹ਺͕ଟ͍ .BYϢʔβʔʷҊ݅ 6TFS 0SEFS /PUJpDBUJPO 5FBN Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌

Slide 7

Slide 7 text

w Ҋ݅͝ͱͷνϟοτϧʔϜͰ ͷϝϯγϣϯ w ଞͷϢʔβʔىҼͷΞΫγϣ ϯͷ͓஌Βͤ w ʜͳͲ "/%1"%Ͱͷ௨஌ͱ͸

Slide 8

Slide 8 text

"/%1"%Ͱͷະಡͱ͸ w Ϣʔβʔ͸ͨ͘͞ΜͷҊ݅ʹࢀՃͰ͖Δ w Ҋ݅Ͱ͸ɺϢʔβʔʹରͯͨ͘͠͞Μ௨஌ ͕࡞੒͞ΕΔ w ௨஌ͻͱͭͻͱͭʹରͯ͠ɺະಡɾطಡ͕ ͋Δ w ҰཡͰ͸ະಡ਺ͷଟ͍ॱʹҊ͕݅ฒͿ

Slide 9

Slide 9 text

༨ஊ ະಡ͸೉͍͠ w ݅਺͚ͩͰ͍͍ͷ͔ w ͳʹΛͲ͜·ͰΈ͔ͨ΋ඞཁ͔ w ࣃൈ͚Λߟྀ͢Δ͔ w ͲΕ͘Β͍ͷظؒඞཁͳͷ͔ w Ͳ͜·ͰͷzະಡzΛϢʔβʔ͸஌Γͨ ͍ͷ͔ w ະಡ͕ଟ͍ͱͲ͏ද͕ࣔมΘΔ͔

Slide 10

Slide 10 text

ະಡ਺ͷදࣔ "/%1"%Ͱ͸ඇৗʹେྔͷ εϩʔΫΤϦΛൃߦ͢Δ͜ ͱͰ஌ΒΕ͍ͯͨ ௨শεϩʔΫΤϦࡾܑఋ

Slide 11

Slide 11 text

ϝτϦΫεͷ֬ೝ

Slide 12

Slide 12 text

ࢦඪʹ͍߲ͨ͠໨Ͱ ෛՙͷߴ͍΋ͷ͔Β֬ೝ ϝτϦΫεͷ֬ೝ αʔϏεͷੑ࣭ʹదͨ͠ ظؒΛࢦఆ ϘτϧωοΫʹͳ͍ͬͯͦ͏ͳ ࢦඪΛબ୒

Slide 13

Slide 13 text

4JEFLJRͳͷͰϤγ ߴස౓ɺ͔ͭॏ͍ΫΤϦ ϝτϦΫεͷ֬ೝ

Slide 14

Slide 14 text

Έͳ͞ΜͳΒ Ͳ͏͍͏վળͷΞϓϩʔν Λࢥ͍ͭ͘Ͱ͠ΐ͏͔ʁ

Slide 15

Slide 15 text

ࠓ೔ͷ಺༰ w ύϑΥʔϚϯεվળͷΞϓϩʔνͷ͝঺հ w ύϑΥʔϚϯεͱ޲͖߹͏ํͷࢀߟʹͳΕ͹

Slide 16

Slide 16 text

w ํ਑ཱͯ w औΓ૊Έ ͓඼ॻ͖ ͜Ε͔ΒύϑΥʔϚϯεվળʹ޲͖߹͏ํͷ ࢀߟʹͳΕ͹ͱ ύϑΥʔϚϯεվળͷΞϓϩʔνͷ͝঺հ

Slide 17

Slide 17 text

ํ਑ཱͯ

Slide 18

Slide 18 text

·ͣ͸ݱঢ় Ҋ݅ͱ௨஌Λ+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

Slide 19

Slide 19 text

࠷ॳͷํ਑ܾΊ ʲԾઆʳ ʮҊ݅ͷॴଐʯʹະಡ਺Λॻ͖͜ΜͰ͓͚͹ɺ +0*/ର৅ͷϨίʔυ਺ΛݮΒͤͯߴ଎ԽͰ͖Δ

Slide 20

Slide 20 text

ະಡ਺ͷදࣔ 6TFS 0SEFS /PUJpDBUJPO 5FBN 1 n n 1 1 n ඇৗʹ਺͕ଟ͍ .BYϢʔβʔʷҊ݅ ͜͜Λࢀর͍ͯͨ͠ͷΛ΍Ίͯ… Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌

Slide 21

Slide 21 text

ະಡ਺ͷදࣔ 1 n 1 1 1. ʮະಡ਺ʯΛΧϥϜͱͯ͠௥Ճ͢Δ 2. σʔλߋ৽࣌ʹɺ ʮະಡ਺ʯΛॻ͖ࠐΉ n ͪ͜ΒΛࢀরͰ͖Δ͔ /PUJpDBUJPO 6TFS 0SEFS 5FBN Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌

Slide 22

Slide 22 text

࠷ॳͷํ਑ܾΊ ʲݕূ݁Ռʳ ഒఔ౓ͷվળޮՌ͕ݟࠐ·Εͨ

Slide 23

Slide 23 text

࠷ॳͷํ਑ܾΊ

Slide 24

Slide 24 text

ଞͷީิͨͪ w Χ΢ϯλʔΩϟογϡํࣜ w ผͷετϨʔδΛ࢖͏ ˠख਺͕ඞཁͦ͏ͳͷͰɺ ࠷ॳͷҊͰޮՌ͕ͳ͚Ε͹΍Δ

Slide 25

Slide 25 text

͍ͬͨΜͷΰʔϧ ʲ#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

Slide 26

Slide 26 text

ʲ"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*/͢ΔςʔϒϧͷϨίʔυྔ͕࡟ݮ͞Εͨ

Slide 27

Slide 27 text

࠷ॳͷํ਑ܾΊ ͔͠͠ɺʮҊ݅ͷॴଐʯʹରͯ͠ͷະಡ਺ߋ৽ʹ ͋ͨͬͯɺෛՙ͕૿͑Δ͜ͱ͕ݒ೦͞Εͨ ࢀরʹ͍ͭͯ͸͜ͷํ਑ͰΑͦ͞͏

Slide 28

Slide 28 text

͞ΒͳΔվળͷҊग़͠

Slide 29

Slide 29 text

w ͜Ε͸ແཧͩΖ͏ɺͱࢥΘΕΔΑ͏ͳҊ Ͱ΋ࢥ͍ͭ͘ݶΓग़ͯ͠ΈΔ w Ұ୴ൃࢄͤ͞Δ w ʮະಡ਺ͷදࣔΛ΍ΊΔʯͱ͔΋0, ͞ΒͳΔվળͷҊग़͠

Slide 30

Slide 30 text

վળҊͷ୨Է͠

Slide 31

Slide 31 text

w ίεύ͕Αͦ͞͏ͳ΋ͷɺଞʹӨڹΛ༩͑ ͦ͏ͳ΋ͷΛ༏ઌ͢Δ w ม਺͕େ͖͍΋ͷ͸ޙճ͠ վળҊͷ୨Է͠ ͋ͱ͸΍͍ͬͯ͘ͷΈ🔥

Slide 32

Slide 32 text

ԾઆˠݕূˠԾઆʜͷϧʔϓ ࢼ͖ͬͯ͠े෼ͳޮՌ͕ಘΒΕͳ͚Ε͹ɺ ·ͨผͷҊΛࢼ͢

Slide 33

Slide 33 text

w ΫΤϦͷվળ͕ओ؟ͩͬͨͷͰɺຊ൪ͱ ಉ౳ͷσʔλྔɾੑೳͷ3%4Λ४උ͢Δ w ݕূ؀ڥͰ͸࠶ݱͷ೉͍͠৔߹ ఆৗతͳ ෛՙɺͳͲ ͸ɺຊ൪Ͱ҆શʹݕূ͢Δ ຊ൪ΛͳΔ΂͘࠶ݱ

Slide 34

Slide 34 text

࣮ࡍʹϘτϧωοΫʹͳΓͦ͏ͳ ঢ়گΛ࠶ݱ͢Δ w ߴෛՙɺͨ͘͞ΜͷҊ݅ʹॴଐ͍ͯ͠Δ w ߴෛՙɺະಡ͕ͨ͘͞Μͨ·͍ͬͯΔ w தԝ஋ͬΆ͍σʔλ΋࠶ݱ͢Δ ຊ൪ΛͳΔ΂͘࠶ݱ

Slide 35

Slide 35 text

ຊ൪ΛͳΔ΂͘࠶ݱ ˠϚεΫ͞Εͨ%#Λར༻

Slide 36

Slide 36 text

͍͔ͭ͘ͷ औΓ૊Έͱ݁Ռ

Slide 37

Slide 37 text

ߋ৽ॲཧͷඇ ಉظԽ

Slide 38

Slide 38 text

ʲԾઆʳ ߋ৽ॲཧΛඇಉظʹ͢ΔͱɺϨεϙϯε଎ ౓ΛվળͰ͖Δ ฒྻԽ͢Δ͜ͱͰɺॲཧதͷδϣϒΛૣ͘ ׬ྃͰ͖Δ ߋ৽ॲཧͷඇಉظฒྻԽ ʲલఏʳ ߋ৽ॲཧʹෛՙ͕͔͔Δ͜ͱ͕ݟࠐ·Εͨ

Slide 39

Slide 39 text

ʲݕূ݁Ռʳ ࣮ࡍʹ͸ɺ!BMMͷϝϯγϣϯͰෛՙͷߴ͍ ΫΤϦ͕ಉ࣌ଟൃతʹൃੜ͢Δ͜ͱ͕൑໌ ˠ3%4ͷ$16࢖༻཰͕͸Ͷ্͕ͬͨͷͰ Ϙπʹ ߋ৽ॲཧͷඇಉظฒྻԽ

Slide 40

Slide 40 text

σʔλͷ࡟আ

Slide 41

Slide 41 text

σʔλͷ࡟আ ʲલఏʳ ະಡ͕஝ੵ͍ͯ͠ΔϢʔβʔ͕Ұఆ਺͍Δ ʲԾઆʳ ͋Δఔ౓աڈͷσʔλʹ͍ͭͯ͸ɺ࡟আͯ͠ ͠·ͬͯ΋ϢʔβʔӨڹ͸ͳ͍ͩΖ͏ σʔλͷ୳ࡧൣғ͕ڱ͘ͳΔ͜ͱͰվળޮ Ռ͕ಘΒΕΔͷͰ͸

Slide 42

Slide 42 text

σʔλͷ࡟আ ʲݕূ݁Ռʳ 8)&3&۟ͷ৚݅Λݟ௚͚ͩ͢Ͱे෼ͳޮ Ռ͕ಘΒΕͨ ҰํɺҰ౓ૹ৴ͨ͠௨஌Λ࡟আ͢Δͱɺ࢓ ༷ʹӨڹΛ༩͑ΔͷͰ৻ॏͳ൑அ͕ٻΊΒ Εͨ

Slide 43

Slide 43 text

σʔλͷ࡟আ ʲݕূ݁Ռʳ ˠ8)&3&ͷݕࡧର৅ߜΓࠐΈΛ࠾༻ͨ͠

Slide 44

Slide 44 text

ΠϯσοΫε ͷ௥Ճ

Slide 45

Slide 45 text

ΠϯσοΫεͷ௥Ճ ʲલఏʳ ະಡ਺औಘͷࡍɺඞͣ૊Έ߹Θͤͯ࢖ΘΕΔ 8)&3&͕۟͋ͬͨ ֘౰৚݅ʹରͯ͠ͷෳ߹ΠϯσοΫε͸ͳ͍ ʲԾઆʳ ΠϯσοΫεΛ௥Ճ͢Δͱɺݕࡧൣғ͕ߜ Γࠐ·Εͯߴ଎Խ͢Δ

Slide 46

Slide 46 text

ΠϯσοΫεͷ௥Ճ ʲݕূ݁Ռʳ ΠϯσοΫεΛ௥Ճ࣮ͯ͠ߦܭըΛ֬ೝ͢Δ ͱɺ࠷ॳʹར༻͞ΕΔΠϯσοΫεͱͯ͠ද ࣔ͞Ε͍ͯͨ

Slide 47

Slide 47 text

ˠdഒͷվળޮՌ͕͋ͬͨ ΠϯσοΫεͷ௥Ճ

Slide 48

Slide 48 text

੒Ռ

Slide 49

Slide 49 text

w ѹ౗తվળޮՌʜͱݴ͍͍ͨͱ͜ΖͰ͕͢ɺ ໌೔ϦϦʔε༧ఆ w ·ͣ͸ɺෛՙͷ௿͍࣌ؒଳΛૂͬͯෛՙͷ༷ ࢠΛΈͭͭঃʑʹ։์༧ఆ w ظ଴஋͸ɺߋ৽͕dY஗͘ͳΔࢀর͕ dYഒૣ͘ͳΔ͜ͱ ੒Ռ

Slide 50

Slide 50 text

·ͱΊ

Slide 51

Slide 51 text

w Ծઆͷஈ֊Ͱ͸ߜΓࠐΈ͗͢ͳ͍ w ݕূ͸·ͣ࠷খͷํ๏Ͱߦ͍ɺޮՌ͕ಘ ΒΕͨΒ࣮૷Λߦ͏ w ݕূʹ͸ద੾ͳσʔλ४උͱՄࢹԽ͕ޮ ՌతͰ͢ w վળͷΞϓϩʔν͸ݴޠɾ'8ඇґଘͰ࢖ ͑·͢ ·ͱΊ