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

2019-02-02 に開催された 第26回中国地方DB勉強会でのスライドです。

SQLチューニングの基礎として Explain文のご説明をさせて頂きました。

# 概要

SQLのチューニングにあたって、そもそも遅いQueryとは何か、なぜ遅くなるのか。
遅いクエリを分析するのに役立つExplainコマンドについて改めて振り返ってみたいと思います。

Transcript

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

5. View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

17. 2. SQL࣮ߦͷ࢓૊Έ
    17
    

    View Slide

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

    View Slide

19. SQLจͷॲཧ͞ΕΔྲྀΕ
    19
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

30. 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
    

    View Slide

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

    View Slide

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

    View Slide

33. 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
    

    View Slide

34. Explainจͷݟํ
    34
    

    View Slide

35. Explainจͷݟํ
    35
    

    View Slide

36. Explainจͷݟํ
    36
    

    View Slide

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

    View Slide

38. ओͳॲཧϊʔυ
    38
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

53. 4. ·ͱΊ
    53
    

    View Slide

54. Explainจͱओཁͳॲཧϊʔυʹ͍ͭͯ
    ࿩Λ͠·ͨ͠
    54
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

58. ฐࣾ MySQLͳΜΑͳ͊ɻɻ
    58
    

    View Slide

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

    View Slide

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

    View Slide

61. ೉ͦ͠͏ɻ
    61
    

    View Slide

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

    View Slide

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

    View Slide

64. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠
    64
    

    View Slide

65. ࢀߟࢿྉ
    • 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
    

    View Slide