SQLチューニングの基礎~基礎から振り返るExplainコマンド~/chugokudb26-sql-tuning

by Takahashi Ikki

Slide 1

Slide 1 text

SQLνϡʔχϯάͷجૅ ~ վΊͯৼΓฦΔExplainίϚϯυ ~ 2019-02-02 ୈ26ճ தࠃ஍ํDBษڧձinԬࢁ 1

Slide 2

Slide 2 text

஫ҙࣄ߲ • εϥΠυ͸ެ։͍ͯ͠·͢ɻ • ࣭໰ͱ͔͝ࢦఠͱ͔ॾʑͷϑΟʔυόοΫ͕͍͟͝·ͨ͠Β #ChugokuDB ΁ ͓ئ͍க͠·͢ɻ • தࠃ஍ํDBษڧձ͸ ໺࣍ ηογϣϯதͰ΋ؾܰʹ࣭໰ͯ͠ྑ͍ͷͰ ෼͔Βͳ͍ॴ͸ηογϣϯதʹݴͬͯ௖͍ͯ΋ߏ͍·ͤΜɻ • PostgreSQLʹׂͱ࠷దԽ͞Εͨ࿩ʹͳͬͪΌ͍ͬͯ·͢ɻ • ࣮ફతͳSQLνϡʔχϯάํ๏͸ग़ͯ͜ͳ͍Ͱ͢ɺ͢Έ·ͤΜm(_ _)m 2

Slide 3

Slide 3 text

஫ҙࣄ߲ μϝθολΠʂ 3

Slide 4

Slide 4 text

ຊ೔ͷDBษڧձ ࡢࠓɺRDSͱ͔Λར༻͢ΔࣄʹΑͬͯ ͙͢σϓϩΠग़དྷΔ͠ DBΛߏங͢Δϋʔυϧ͸Լ͕ͬͨɻ ͱ͸ݴ͏΋ͷͷ DB͕٧·ͬͯɾɾɾͱ͔كʹΑ͋͘Δɻ σʔλϕʔε͸ϘτϧωοΫʹͳΓ͕ͪɻ 4

Slide 5

Slide 5 text

Slide 6

Slide 6 text

ຊ೔ͷDBษڧձ ໷ʹҿΈձ͔Βฦ͖ͬͯͯటਲͨ͠ΤϯδχΞ̎ਓ͕ ରॲ͢Δɻɻɻ ݏͳࣄ݅ͩͬͨͶɾɾɾɾ => ༻๏༻ྔΛकͬͯਖ਼͘͠࢖͑ΔΑ͏ʹͳΓ͍ͨɻ ͳͬͯ΄͍͠ɻ 6

Slide 7

Slide 7 text

ຊ೔ͷDBษڧձ • ্ख͘ѻ͏ Λ଎౓ͷ؍఺Ͱߟ͑ͯ ଎౓Λग़͢ɺग़ͤΔΑ͏ͳ࢓૊ΈΛ࡞ΔͨΊʹ͸Ͳ͏͢Δ ͔ɺ ͱ͍͏؍఺ͰίϯςϯπΛ༻ҙ͠·ͨ͠ɻ 7

Slide 8

Slide 8 text

SQLνϡʔχϯά • SQLνϡʔχϯάͷجૅ ~ վΊͯৼΓฦΔExplainίϚϯυ ~ ύϥϝʔλνϡʔχϯά • PostgreSQL11ઃఆύϥϝʔλղମ৽ॻ • ॳΊͯͷMySQLνϡʔχϯά ~ σʔλϕʔε͸ා͘ͳ͍ʂ ~ 8

Slide 9

Slide 9 text

SQLνϡʔχϯάͷجૅ ~ վΊͯৼΓฦΔExplainίϚϯυ ~ 9

Slide 10

Slide 10 text

ࠓ೔ͷ໨ඪ • ΦϓςΟϚΠβ(ϓϥϯφ)ͷؾ࣋ͪΛ஌Δɻ ๻ͨͪ΋ΦϓςΟϚΠβʹͳΕΔ • SQLͷجຊͷ࢓૊ΈΛཧղͯ͠ "෗Δ೏͍ͷ͢Δ৔ॴ" Λ༧Ί஌ͬͯීஈͷ ίʔσΟϯά͔Β໾ཱ͍͚ͯͯΔΑ͏ʹɻ 10

Slide 11

Slide 11 text

͓͠ͳ͕͖ 1. ࣗݾ঺հ 2. SQL࣮ߦͷ࢓૊Έ 3. ࣮ߦܭըͷऔಘํ๏ Explainจʹ͍ͭͯ 4. ·ͱΊ 11

Slide 12

Slide 12 text

1. ࣗݾ঺հ • ߴڮ Ұٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ WebΤϯδχΞ • ೔ຊPostgreSQLϢʔβʔձ தࠃࢧ෦௕ 12

Slide 13

Slide 13 text

גࣜձࣾΦϛΧϨ શࠃ35000݅ͷࠗ׆ύʔςΟΛ ܝࡌͨ͠ϙʔλϧαΠτɻ 30ສਓڧͷํʑʹ ͝ར༻௖͍͓ͯΓ·͢ɻ ࠗ׆ͱ͍͑͹ΦϛΧϨ 13

Slide 14

Slide 14 text

େ౎ձ੎ʹͦʔ͍ͩ͞Μͷઆ໌ͯ͠΋.. 14

Slide 15

Slide 15 text

גࣜձࣾΦϛΧϨͷจԽ(Ϛωʔδϟʔ໨ઢ) https://speakerdeck.com/soudai/proper-problem 15

Slide 16

Slide 16 text

גࣜձࣾΦϛΧϨͷจԽ(ϓϨΠϠʔ໨ઢ) https://speakerdeck.com/takahashiikki/phpconference-sendai2019-omicale 16

Slide 17

Slide 17 text

2. SQL࣮ߦͷ࢓૊Έ 17

Slide 18

Slide 18 text

ͦ΋ͦ΋SQLจ͸ͲͷΑ͏ʹॲཧ͞ΕΔͷ͔ 18

Slide 19

Slide 19 text

SQLจͷॲཧ͞ΕΔྲྀΕ 19

Slide 20

Slide 20 text

SQL͕஗͍࣌ͷΩϞ͸Ͳ͔͜ • ࣮ߦܭըͷબఆ͕ѱ͍ • ΤΫθΩϡʔλͷ࣮ߦ͕஗͍ 20

Slide 21

Slide 21 text

SQL͕஗͍࣌ͷΩϞ͸Ͳ͔͜ • ࣮ߦܭըͷબఆ͕ѱ͍ ← ͜Ε • ΤΫθΩϡʔλͷ࣮ߦ͕஗͍ 21

Slide 22

Slide 22 text

࣮ߦܭը͸Ͳ͏΍ܾͬͯ·Δͷ͔ • ͦ΋ͦ΋ϓϥϯφ͸SQLͷσʔλΛ஌Βͳ͍ɻ • ϓϥϯφ͸ ౷ܭ৘ใ ΛಡΜͰ ʰ͜ͷςʔϒϧ͸͜ͷΑ͏ͳσʔλ͕ೖͬͯΔ͔Β ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷଎ͩΖ͏ʱ ͱ࣮ߦܭըΛܾΊΔɻ 22

Slide 23

Slide 23 text

ߴ଎ԽͷΩϞ • ౷ܭ৘ใɺͪΌΜͱߋ৽͞ΕͯΔΑͶʁ • ࣮ߦܭըΛݟͯ஗ͦ͏ͳ΋ͷແ͍͔ʁ 23

Slide 24

Slide 24 text

SQL͕஗͍࣌ͷΩϞ͸Ͳ͔͜ • ࣮ߦܭըͷબఆ͕ѱ͍ • ΤΫθΩϡʔλͷ࣮ߦ͕஗͍ ← ͜Ε 24

Slide 25

Slide 25 text

SQLͷ࣮ߦ • ڞ༗όοϑΝΛ௨ͯ͠ߦΘΕΔɻ • ώʔϓϑΝΠϧʢHDD্ͷ࣮ϑΝΠϧʣͷ σʔλΛಡΉͱֻ͕͔࣌ؒΔͷͰ σʔλΛಡΈࠐΉλΠϛϯάͰ ώʔϓϑΝΠϧ͔ΒΧʔωϧΩϟογϡ(OSͷϝϞϦ)ɺ ΧʔωϧΩϟογϡ͔Βڞ༗όοϑΝ΁ σʔλΛಡΈࠐΈɺҎ߱ͦͷσʔλΛ࢖͏ɻ 25

Slide 26

Slide 26 text

ߴ଎ԽͷΩϞ • ڞ༗όοϑΝʹσʔλ͕ͷͬͯΔ͔ɻ • ڞ༗όοϑΝ͔Βσʔλ͕ಡΈऔΕ͍ͯΔ͔ɻ ࠓճ͋·Γࣗ෼ͷൃදͰ͸৮Εͳ͍ɻ ઃఆͱ͔ͷ࿩͸Ҏ߱ͷൃදͰग़ͯ͘Δ͔΋ɻ 26

Slide 27

Slide 27 text

2.SQL࣮ߦͷ࢓૊Έ ·ͱΊ • SQL͸େ͖͘෼͚ͯύʔαʔɾϦϥΠλɾ ϓϥϯφɾΤΫθΩϡʔλͱ͍͏໾ׂ͕͋Δɻ • ϓϥϯφ͸౷ܭ৘ใΛݩʹ࣮ߦܭըΛ࡞Γɺ ΤΫθΩϡʔλ͕࣮ߦ͢Δɻ • ౷ܭ৘ใ͕ਖ਼͍͔͠Ͳ͏͔ɺ Ωϟογϡͨ͠ڞ༗όοϑΝ্͔Β σʔλΛಡΈऔΕ͍ͯΔ͔Ͳ͏͔͕ ߴ଎ͷΩϞͱͳΔɻ 27

Slide 28

Slide 28 text

3.࣮ߦܭըͷऔಘํ๏ Explainจʹ͍ͭͯ 28

Slide 29

Slide 29 text

࣮ߦܭըͷݟํ ߴ଎ԽͷΩϞ͸ ࣮ߦܭըΛݟͯ஗ͦ͏ͳ΋ͷແ͍͔ʁ ͱ ݟ౰Λ෇͚ͯΈΔॴͩͬͨɻ ࣮ߦܭըΛݟΔ => ExplainจΛ࣮ߦ͢Δ͜ͱͰ֬ೝ͕ग़དྷΔɻ 29

Slide 30

Slide 30 text

Explainจ EXPLAIN SELECT * FROM companies JOIN reviews ON (companies.id = reviews.company_id) WHERE companies.created_at < '2019-01-30 22:10:00'; ------------------------------------------------------- Nested Loop (cost=0.28..145.39 rows=24 width=1387) -> Seq Scan on reviews (cost=0.00..2.24 rows=24 width=558) -> Index Scan using c_pkey on companies (cost=0.28..5.96 rows=1 width=829) Index Cond: (id = reviews.company_id) Filter: (created_at < '2019-01-30 22:10:00+00'::timestamp with time zone) 30

Slide 31

Slide 31 text

ݴͬͯ΋ "࣮ߦʰܭըʱ " 31

Slide 32

Slide 32 text

ܭըΛվળ͍ͯ͘͠ʹ͸ɾɾ ʰܭըʱͯ͠ʰ࣮ߦʱͯ͠ॳΊͯʰվળʱʹਐΉɻ • EXPLAIN (ANALYZE) SQLΛ࣮ࡍʹ࣮ߦͯ͠ܭըͱ݁ՌΛ྆ํදࣔ͢Δɻ • EXPLAIN (ANALYZE, BUFFERS) ANALYZEͱڞʹࢦఆͯ͠ͲΕ͚ͩڞ༗όοϑΝ͔Β σʔλΛऔಘ͔ͨ͠Λදࣔ͢Δɻ 32

Slide 33

Slide 33 text

Explainจ EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM companies JOIN reviews ON (companies.id = reviews.company_id) WHERE companies.created_at < '2019-01-30 22:10:00'; ------------------------------------------------------- Nested Loop (cost=0.28..145.39 rows=24 width=1387) (actual time=1.049..1.095 rows=24 loops=1) Buffers: shared hit=72 read=2 -> Seq Scan on reviews (cost=0.00..2.24 rows=24 width=558) (actual time=0.008..0.012 rows=24 loops=1) Buffers: shared hit=2 -> Index Scan using c_pkey on companies (cost=0.28..5.96 rows=1 width=829) (actual time=0.044..0.044 rows=1 loops=24) Index Cond: (id = reviews.company_id) Filter: (created_at < '2019-01-30 22:10:00+00'::timestamp with time zone) Buffers: shared hit=70 read=2 Planning time: 0.152 ms Execution time: 1.125 ms 33

Slide 34

Slide 34 text

Explainจͷݟํ 34

Slide 35

Slide 35 text

Explainจͷݟํ 35

Slide 36

Slide 36 text

Explainจͷݟํ 36

Slide 37

Slide 37 text

ओͳॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index Scan εΩϟϯ Hash Join ݁߹ Index Only Scan εΩϟϯ Marge Join ݁߹ Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ Sort ιʔτ Aggregate άϧʔϐϯάܥ 37

Slide 38

Slide 38 text

ओͳॲཧϊʔυ 38

Slide 39

Slide 39 text

ओͳॲཧϊʔυʹ͍ͭͯ • εΩϟϯܥϊʔυ΍݁߹ܥϊʔυ͸ෳ਺छྨ͋Δɻ ݁Ռ͸ಉ͚ͩ͡ͲΞϧΰϦζϜͳͲ͕ҟͳΔɻ • ΞϧΰϦζϜͷҧ͍ => ॲཧ଎౓ͷҧ͍ͱߟ͑ͯ ࢼߦࡨޡΛߦ͍ͬͯ͘ɻ 39

Slide 40

Slide 40 text

ओͳεΩϟϯॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Index Scan εΩϟϯ Index Only Scan εΩϟϯ Bitmap Index Scan εΩϟϯ 40

Slide 41

Slide 41 text

εΩϟϯॲཧϊʔυͷҧ͍ Seq Scan • ςʔϒϧͷશ݅Λॱ൪ʹݺͼग़͍ͯ͘͠ɻ Index Scan • IndexͰର৅σʔλʹ͋ͨΓΛ෇͚ͨޙͰର৅ͷσʔλΛݺ ͼग़͍ͯ͘͠ɻ 41

Slide 42

Slide 42 text

εΩϟϯॲཧϊʔυͷҧ͍ Index Only Scan • IndexͷσʔλͷΈΛݺͼग़͍ͯ͘͠ɻ Bitmap Index Scan • IndexΛ࢖ͬͯ৚݅ʹ߹க͢Δ 1 or 0 ͷϏοτϚοϓΛੜ੒͠ ͦͷ಺1ͷ΋ͷ͚ͩΛॱ൪ʹݺͼग़͍ͯ͘͠ɻ 42

Slide 43

Slide 43 text

֤ॲཧͷ௕ॴͱτϨʔυΦϑ Seq Scan • IndexΛ࢖͏·Ͱ΋ແ͍গ਺ͷϨίʔυͷ৔߹͸଎͍ɻ 9.6Ͱ͸ɺύϥϨϧΫΤϦͷαϙʔτ Index Scan • IndexͰे෼ʹߜΓࠐΊΔ৔߹͸଎͍ɻ ͨͩ͠ ҋӢʹIndexΛషΔͱ ߏஙʹίετֻ͕͔Δɻ 10.0͔ΒɺύϥϨϧΫΤϦͷαϙʔτ 43

Slide 44

Slide 44 text

֤ॲཧͷ௕ॴͱτϨʔυΦϑ Index Only Scan • Index͕షΒΕͨσʔλͷΈ͕ඞཁͳ৔߹͸଎͍ɻ 10.0͔ΒɺύϥϨϧΫΤϦͷαϙʔτ Bitmap Index Scan • 2஋ͷ಺ɺยํͷΈΛൺֱ͢Ε͹͍͍͔Β଎͍ɻ ͨͩ͠ɺBitmapΛߏங͢Δॳظίετֻ͕͔Δɻ 44

Slide 45

Slide 45 text

ओͳ݁߹ॲཧϊʔυ ॲཧϊʔυ ෼ྨ Nested Loop ݁߹ Hash Join ݁߹ Marge Join ݁߹ 45

Slide 46

Slide 46 text

݁߹ϊʔυͷҧ͍ Nested Loop Join • ۦಈද(JOINݩ)Λ1ߦຖʹ಺෦ද (JOINઌ)ͷ શϨίʔυͱ෇͖߹Θͤͯ֘౰ͷ΋ ͷΛ݁߹͍ͯ͘͠ɻ 46

Slide 47

Slide 47 text

Hash Join • ಺෦දͷ݁߹Ωʔͷ ϋογϡϦετΛ࡞੒ͯ͠ ͦͷޙɺۦಈදͱ݁߹Λߦ͏ɻ 47

Slide 48

Slide 48 text

݁߹ϊʔυͷҧ͍ Merge Join • ۦಈදͱ಺෦දΛ݁߹ΩʔͰ ιʔτͨ͠ޙͰ ॱ൪ʹ෇͖߹Θͤͯ݁߹͍ͯ͘͠ɻ 48

Slide 49

Slide 49 text

֤ۦಈͷ௕ॴͱτϨʔυΦϑ Nested Loop Join • େ͖͍ςʔϒϧͱେ͖͍ςʔϒϧ Λ݁߹͢Δͱ n × m ճॲཧΛ͠ͳ͚Ε͹͍͚ͳ͍ͷͰ஗͍ɻ • ۦಈද͕খ͘͞಺෦දͷ݁߹ΩʔʹIndex͕͋Δ৔߹଎͍ɻ • 9.6͔ΒύϥϨϧΫΤϦͷαϙʔτ 49

Slide 50

Slide 50 text

֤ۦಈͷ௕ॴͱτϨʔυΦϑ Hash Join • ϋογϡϦετ͕ϝϞϦʹऩ·Δ৔߹͸ඇৗʹ଎͍ɻ • ༧ΊϋογϡϦετΛ࡞੒͢Δඞཁ͕͋ΔͷͰ ͦͷ෼ॳظίετֻ͕͔Δɻ • 9.6͔ΒύϥϨϧΫΤϦͷαϙʔτ(11.0ͰߋʹڧԽ) 50

Slide 51

Slide 51 text

֤ۦಈͷ௕ॴͱτϨʔυΦϑ Merge Join • େ͖͍ςʔϒϧ × େ͖͍ςʔϒϧͰ΋ߴ଎Ͱ݁߹ग़དྷΔɻ • ͨͩ͠ࣄલʹͲͪΒͷςʔϒϧ΋ιʔτ͢Δඞཁ͕͋ΔͷͰ ॳظίετֻ͕͔Δɻ • 10.0͔ΒύϥϨϧΫΤϦͷαϙʔτ 51

Slide 52

Slide 52 text

νϡʔχϯάͷΩϞ • ֤ํࣜͷτϨʔυΦϑϙΠϯτΛҙࣝͯ͠ ࢼߦࡨޡΛ͍ͯ͘͠ࣄͰௐ੔Λ͍ͯ͘͠ɻ • ςʔϒϧ͕େ͖͍ͷʹର৅ߦ͕গͳ͍͔ΒIndexషͬͨํ ͕ྑ͍͔΋ʁ • ෆཁͳΧϥϜ·ͰऔͬͯΔ͔ΒΧϥϜݮΒͨ͠Β IndexOnlyScanͰ͍͚Δʁ 52

Slide 53

Slide 53 text

4. ·ͱΊ 53

Slide 54

Slide 54 text

Explainจͱओཁͳॲཧϊʔυʹ͍ͭͯ ࿩Λ͠·ͨ͠ 54

Slide 55

Slide 55 text

΋ͬͱ࣮ફతͳSQLνϡʔχϯά͕͍ͨ͠Μ͡Ό 55

Slide 56

Slide 56 text

͓͢͢Ί [վగ৽൛]಺෦ߏ଄͔ΒֶͿ PostgreSQL ઃܭɾӡ༻ܭըͷమଇ https://amzn.to/2DLrrZo ୈ15ষ ࣮ߦܭըͷऔಘɾղੳ ΛಡΉͷ͕͓͢͢Ίɻ 56

Slide 57

Slide 57 text

͓͢͢Ί PostgreSQL Conference Japan 2018 ͷ νϡʔτϦΞϧτϥοΫ [T3] SQL νϡʔχϯάͷجૅ https://www.slideshare.net/ HarukaTakatsuka/t3-pg- sqltuningbasic20181122 https://www.youtube.com/watch? v=Bc0kIGHqjFk 57

Slide 58

Slide 58 text

ฐࣾ MySQLͳΜΑͳ͊ɻɻ 58

Slide 59

Slide 59 text

࣮ࡍͷख๏ PostgreSQL Conference Japan 2018 MySQL͔ΒPostgreSQL΁ͷҠߦͱ DBϦϑΝΫλϦϯά https://speakerdeck.com/takahashiikki/ postgresqljapan2018 59

Slide 60

Slide 60 text

·ͱΊ • Explain Λ࣮ߦ͢Δͱϓϥϯφ͕ߟ͑ͨ ࣮ߦܭըΛݟΔ͜ͱ͕ग़དྷΔɻ • Explain (ANALYZE) Λ͢ΔࣄͰ࣮ߦܭըٴͼ ࣮ࡍʹ࣮ߦͨ͠ࡍʹ͔͔ͬͨ࣌ؒΛݟΔࣄ͕ग़དྷΔɻ • ࢼߦࡨޡΛ͍͖ͯ͠ͳ͕Β SQLνϡʔχϯάΛ͍͖ͯ͠· ͠ΐ͏ɻ 60

Slide 61

Slide 61 text

೉ͦ͠͏ɻ 61

Slide 62

Slide 62 text

໰୊ͱͷ޲͖߹͍ํ • ໰୊Λ100఺ຬ఺Ͱղܾ͢Δඞཁ͸ͳ͍ɻ • ೉͍࣌͠͸ੋඇ͜͏͍͏ίϛϡχςΟʔΛ׆༻ͯ͠΄͍͠ɻ • தࠃ஍ํDBษڧձ͸͍ͭ΋͋ͳͨͷͦ͹ʹɻɻ 62

Slide 63

Slide 63 text

ίϛϡχςΟʔͷ׆༻ PostgreSQLͷSlackΛ ੋඇ׆༻͍ͯͩ͘͠͞ɻ ॳ৺ऀ͔ΒPostgreSQLͷ ίϛολʔ·Ͱ৭Μͳํ͕ډΔɻ 63

Slide 64

Slide 64 text

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ 64

Slide 65

Slide 65 text

ࢀߟࢿྉ • https://speakerdeck.com/soudai/web-service-monitoring • https://speakerdeck.com/soudai/shi-xing-ji-hua-falsehua • https://www.slideshare.net/HarukaTakatsuka/t3-pg- sqltuningbasic20181122 • https://www.postgresql.jp/document/10/html/planner- optimizer.html 65