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
2. ஫ҙࣄ߲ • εϥΠυ͸ެ։͍ͯ͠·͢ɻ • ࣭໰ͱ͔͝ࢦఠͱ͔ॾʑͷϑΟʔυόοΫ͕͍͟͝·ͨ͠Β #ChugokuDB ΁ ͓ئ͍க͠·͢ɻ • தࠃ஍ํDBษڧձ͸ ໺࣍ ηογϣϯதͰ΋ؾܰʹ࣭໰ͯ͠ྑ͍ͷͰ ෼͔Βͳ͍ॴ͸ηογϣϯதʹݴͬͯ௖͍ͯ΋ߏ͍·ͤΜɻ • PostgreSQLʹׂͱ࠷దԽ͞Εͨ࿩ʹͳͬͪΌ͍ͬͯ·͢ɻ • ࣮ફతͳSQLνϡʔχϯάํ๏͸ग़ͯ͜ͳ͍Ͱ͢ɺ͢Έ·ͤΜm(_ _)m 2
3. ஫ҙࣄ߲ μϝθολΠʂ 3
4. ຊ೔ͷDBษڧձ ࡢࠓɺRDSͱ͔Λར༻͢ΔࣄʹΑͬͯ ͙͢σϓϩΠग़དྷΔ͠ DBΛߏங͢Δϋʔυϧ͸Լ͕ͬͨɻ ͱ͸ݴ͏΋ͷͷ DB͕٧·ͬͯɾɾɾͱ͔كʹΑ͋͘Δɻ σʔλϕʔε͸ϘτϧωοΫʹͳΓ͕ͪɻ 4
6. ຊ೔ͷDBษڧձ ໷ʹҿΈձ͔Βฦ͖ͬͯͯటਲͨ͠ΤϯδχΞ̎ਓ͕ ରॲ͢Δɻɻɻ ݏͳࣄ݅ͩͬͨͶɾɾɾɾ => ༻๏༻ྔΛकͬͯਖ਼͘͠࢖͑ΔΑ͏ʹͳΓ͍ͨɻ ͳͬͯ΄͍͠ɻ 6
7. ຊ೔ͷDBษڧձ • ্ख͘ѻ͏ Λ଎౓ͷ؍఺Ͱߟ͑ͯ ଎౓Λग़͢ɺग़ͤΔΑ͏ͳ࢓૊ΈΛ࡞ΔͨΊʹ͸Ͳ͏͢Δ ͔ɺ ͱ͍͏؍఺ͰίϯςϯπΛ༻ҙ͠·ͨ͠ɻ 7
8. SQLνϡʔχϯά • SQLνϡʔχϯάͷجૅ ~ վΊͯৼΓฦΔExplainίϚϯυ ~ ύϥϝʔλνϡʔχϯά • PostgreSQL11ઃఆύϥϝʔλղମ৽ॻ • ॳΊͯͷMySQLνϡʔχϯά ~ σʔλϕʔε͸ා͘ͳ͍ʂ ~ 8
9. SQLνϡʔχϯάͷجૅ ~ վΊͯৼΓฦΔExplainίϚϯυ ~ 9
10. ࠓ೔ͷ໨ඪ • ΦϓςΟϚΠβ(ϓϥϯφ)ͷؾ࣋ͪΛ஌Δɻ ๻ͨͪ΋ΦϓςΟϚΠβʹͳΕΔ • SQLͷجຊͷ࢓૊ΈΛཧղͯ͠ "෗Δ೏͍ͷ͢Δ৔ॴ" Λ༧Ί஌ͬͯීஈͷ ίʔσΟϯά͔Β໾ཱ͍͚ͯͯΔΑ͏ʹɻ 10
11. ͓͠ͳ͕͖ 1. ࣗݾ঺հ 2. SQL࣮ߦͷ࢓૊Έ 3. ࣮ߦܭըͷऔಘํ๏ Explainจʹ͍ͭͯ 4. ·ͱΊ 11
12. 1. ࣗݾ঺հ • ߴڮ Ұٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ WebΤϯδχΞ • ೔ຊPostgreSQLϢʔβʔձ தࠃࢧ෦௕ 12
13. גࣜձࣾΦϛΧϨ શࠃ35000݅ͷࠗ׆ύʔςΟΛ ܝࡌͨ͠ϙʔλϧαΠτɻ 30ສਓڧͷํʑʹ ͝ར༻௖͍͓ͯΓ·͢ɻ ࠗ׆ͱ͍͑͹ΦϛΧϨ 13
14. େ౎ձ੎ʹͦʔ͍ͩ͞Μͷઆ໌ͯ͠΋.. 14
15. גࣜձࣾΦϛΧϨͷจԽ(Ϛωʔδϟʔ໨ઢ) https://speakerdeck.com/soudai/proper-problem 15
16. גࣜձࣾΦϛΧϨͷจԽ(ϓϨΠϠʔ໨ઢ) https://speakerdeck.com/takahashiikki/phpconference-sendai2019-omicale 16
17. 2. SQL࣮ߦͷ࢓૊Έ 17
18. ͦ΋ͦ΋SQLจ͸ͲͷΑ͏ʹॲཧ͞ΕΔͷ͔ 18
19. SQLจͷॲཧ͞ΕΔྲྀΕ 19
20. SQL͕஗͍࣌ͷΩϞ͸Ͳ͔͜ • ࣮ߦܭըͷબఆ͕ѱ͍ • ΤΫθΩϡʔλͷ࣮ߦ͕஗͍ 20
21. SQL͕஗͍࣌ͷΩϞ͸Ͳ͔͜ • ࣮ߦܭըͷબఆ͕ѱ͍ ← ͜Ε • ΤΫθΩϡʔλͷ࣮ߦ͕஗͍ 21
22. ࣮ߦܭը͸Ͳ͏΍ܾͬͯ·Δͷ͔ • ͦ΋ͦ΋ϓϥϯφ͸SQLͷσʔλΛ஌Βͳ͍ɻ • ϓϥϯφ͸ ౷ܭ৘ใ ΛಡΜͰ ʰ͜ͷςʔϒϧ͸͜ͷΑ͏ͳσʔλ͕ೖͬͯΔ͔Β ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷଎ͩΖ͏ʱ ͱ࣮ߦܭըΛܾΊΔɻ 22
23. ߴ଎ԽͷΩϞ • ౷ܭ৘ใɺͪΌΜͱߋ৽͞ΕͯΔΑͶʁ • ࣮ߦܭըΛݟͯ஗ͦ͏ͳ΋ͷແ͍͔ʁ 23
24. SQL͕஗͍࣌ͷΩϞ͸Ͳ͔͜ • ࣮ߦܭըͷબఆ͕ѱ͍ • ΤΫθΩϡʔλͷ࣮ߦ͕஗͍ ← ͜Ε 24
25. SQLͷ࣮ߦ • ڞ༗όοϑΝΛ௨ͯ͠ߦΘΕΔɻ • ώʔϓϑΝΠϧʢHDD্ͷ࣮ϑΝΠϧʣͷ σʔλΛಡΉͱֻ͕͔࣌ؒΔͷͰ σʔλΛಡΈࠐΉλΠϛϯάͰ ώʔϓϑΝΠϧ͔ΒΧʔωϧΩϟογϡ(OSͷϝϞϦ)ɺ ΧʔωϧΩϟογϡ͔Βڞ༗όοϑΝ΁ σʔλΛಡΈࠐΈɺҎ߱ͦͷσʔλΛ࢖͏ɻ 25
26. ߴ଎ԽͷΩϞ • ڞ༗όοϑΝʹσʔλ͕ͷͬͯΔ͔ɻ • ڞ༗όοϑΝ͔Βσʔλ͕ಡΈऔΕ͍ͯΔ͔ɻ ࠓճ͋·Γࣗ෼ͷൃදͰ͸৮Εͳ͍ɻ ઃఆͱ͔ͷ࿩͸Ҏ߱ͷൃදͰग़ͯ͘Δ͔΋ɻ 26
27. 2.SQL࣮ߦͷ࢓૊Έ ·ͱΊ • SQL͸େ͖͘෼͚ͯύʔαʔɾϦϥΠλɾ ϓϥϯφɾΤΫθΩϡʔλͱ͍͏໾ׂ͕͋Δɻ • ϓϥϯφ͸౷ܭ৘ใΛݩʹ࣮ߦܭըΛ࡞Γɺ ΤΫθΩϡʔλ͕࣮ߦ͢Δɻ • ౷ܭ৘ใ͕ਖ਼͍͔͠Ͳ͏͔ɺ Ωϟογϡͨ͠ڞ༗όοϑΝ্͔Β σʔλΛಡΈऔΕ͍ͯΔ͔Ͳ͏͔͕ ߴ଎ͷΩϞͱͳΔɻ 27
28. 3.࣮ߦܭըͷऔಘํ๏ Explainจʹ͍ͭͯ 28
29. ࣮ߦܭըͷݟํ ߴ଎ԽͷΩϞ͸ ࣮ߦܭըΛݟͯ஗ͦ͏ͳ΋ͷແ͍͔ʁ ͱ ݟ౰Λ෇͚ͯΈΔॴͩͬͨɻ ࣮ߦܭըΛݟΔ => ExplainจΛ࣮ߦ͢Δ͜ͱͰ֬ೝ͕ग़དྷΔɻ 29
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
31. ݴͬͯ΋ "࣮ߦʰܭըʱ " 31
32. ܭըΛվળ͍ͯ͘͠ʹ͸ɾɾ ʰܭըʱͯ͠ʰ࣮ߦʱͯ͠ॳΊͯʰվળʱʹਐΉɻ • EXPLAIN (ANALYZE) SQLΛ࣮ࡍʹ࣮ߦͯ͠ܭըͱ݁ՌΛ྆ํදࣔ͢Δɻ • EXPLAIN (ANALYZE, BUFFERS) ANALYZEͱڞʹࢦఆͯ͠ͲΕ͚ͩڞ༗όοϑΝ͔Β σʔλΛऔಘ͔ͨ͠Λදࣔ͢Δɻ 32
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
34. Explainจͷݟํ 34
35. Explainจͷݟํ 35
36. Explainจͷݟํ 36
37. ओͳॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index Scan εΩϟϯ Hash Join ݁߹ Index Only Scan εΩϟϯ Marge Join ݁߹ Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ Sort ιʔτ Aggregate άϧʔϐϯάܥ 37
38. ओͳॲཧϊʔυ 38
39. ओͳॲཧϊʔυʹ͍ͭͯ • εΩϟϯܥϊʔυ΍݁߹ܥϊʔυ͸ෳ਺छྨ͋Δɻ ݁Ռ͸ಉ͚ͩ͡ͲΞϧΰϦζϜͳͲ͕ҟͳΔɻ • ΞϧΰϦζϜͷҧ͍ => ॲཧ଎౓ͷҧ͍ͱߟ͑ͯ ࢼߦࡨޡΛߦ͍ͬͯ͘ɻ 39
40. ओͳεΩϟϯॲཧϊʔυ ॲཧϊʔυ ෼ྨ Seq Scan εΩϟϯ Index Scan εΩϟϯ Index Only Scan εΩϟϯ Bitmap Index Scan εΩϟϯ 40
41. εΩϟϯॲཧϊʔυͷҧ͍ Seq Scan • ςʔϒϧͷશ݅Λॱ൪ʹݺͼग़͍ͯ͘͠ɻ Index Scan • IndexͰର৅σʔλʹ͋ͨΓΛ෇͚ͨޙͰର৅ͷσʔλΛݺ ͼग़͍ͯ͘͠ɻ 41
42. εΩϟϯॲཧϊʔυͷҧ͍ Index Only Scan • IndexͷσʔλͷΈΛݺͼग़͍ͯ͘͠ɻ Bitmap Index Scan • IndexΛ࢖ͬͯ৚݅ʹ߹க͢Δ 1 or 0 ͷϏοτϚοϓΛੜ੒͠ ͦͷ಺1ͷ΋ͷ͚ͩΛॱ൪ʹݺͼग़͍ͯ͘͠ɻ 42
43. ֤ॲཧͷ௕ॴͱτϨʔυΦϑ Seq Scan • IndexΛ࢖͏·Ͱ΋ແ͍গ਺ͷϨίʔυͷ৔߹͸଎͍ɻ 9.6Ͱ͸ɺύϥϨϧΫΤϦͷαϙʔτ Index Scan • IndexͰे෼ʹߜΓࠐΊΔ৔߹͸଎͍ɻ ͨͩ͠ ҋӢʹIndexΛషΔͱ ߏஙʹίετֻ͕͔Δɻ 10.0͔ΒɺύϥϨϧΫΤϦͷαϙʔτ 43
44. ֤ॲཧͷ௕ॴͱτϨʔυΦϑ Index Only Scan • Index͕షΒΕͨσʔλͷΈ͕ඞཁͳ৔߹͸଎͍ɻ 10.0͔ΒɺύϥϨϧΫΤϦͷαϙʔτ Bitmap Index Scan • 2஋ͷ಺ɺยํͷΈΛൺֱ͢Ε͹͍͍͔Β଎͍ɻ ͨͩ͠ɺBitmapΛߏங͢Δॳظίετֻ͕͔Δɻ 44
45. ओͳ݁߹ॲཧϊʔυ ॲཧϊʔυ ෼ྨ Nested Loop ݁߹ Hash Join ݁߹ Marge Join ݁߹ 45
46. ݁߹ϊʔυͷҧ͍ Nested Loop Join • ۦಈද(JOINݩ)Λ1ߦຖʹ಺෦ද (JOINઌ)ͷ શϨίʔυͱ෇͖߹Θͤͯ֘౰ͷ΋ ͷΛ݁߹͍ͯ͘͠ɻ 46
47. Hash Join • ಺෦දͷ݁߹Ωʔͷ ϋογϡϦετΛ࡞੒ͯ͠ ͦͷޙɺۦಈදͱ݁߹Λߦ͏ɻ 47
48. ݁߹ϊʔυͷҧ͍ Merge Join • ۦಈදͱ಺෦දΛ݁߹ΩʔͰ ιʔτͨ͠ޙͰ ॱ൪ʹ෇͖߹Θͤͯ݁߹͍ͯ͘͠ɻ 48
49. ֤ۦಈͷ௕ॴͱτϨʔυΦϑ Nested Loop Join • େ͖͍ςʔϒϧͱେ͖͍ςʔϒϧ Λ݁߹͢Δͱ n × m ճॲཧΛ͠ͳ͚Ε͹͍͚ͳ͍ͷͰ஗͍ɻ • ۦಈද͕খ͘͞಺෦දͷ݁߹ΩʔʹIndex͕͋Δ৔߹଎͍ɻ • 9.6͔ΒύϥϨϧΫΤϦͷαϙʔτ 49
50. ֤ۦಈͷ௕ॴͱτϨʔυΦϑ Hash Join • ϋογϡϦετ͕ϝϞϦʹऩ·Δ৔߹͸ඇৗʹ଎͍ɻ • ༧ΊϋογϡϦετΛ࡞੒͢Δඞཁ͕͋ΔͷͰ ͦͷ෼ॳظίετֻ͕͔Δɻ • 9.6͔ΒύϥϨϧΫΤϦͷαϙʔτ(11.0ͰߋʹڧԽ) 50
51. ֤ۦಈͷ௕ॴͱτϨʔυΦϑ Merge Join • େ͖͍ςʔϒϧ × େ͖͍ςʔϒϧͰ΋ߴ଎Ͱ݁߹ग़དྷΔɻ • ͨͩ͠ࣄલʹͲͪΒͷςʔϒϧ΋ιʔτ͢Δඞཁ͕͋ΔͷͰ ॳظίετֻ͕͔Δɻ • 10.0͔ΒύϥϨϧΫΤϦͷαϙʔτ 51
52. νϡʔχϯάͷΩϞ • ֤ํࣜͷτϨʔυΦϑϙΠϯτΛҙࣝͯ͠ ࢼߦࡨޡΛ͍ͯ͘͠ࣄͰௐ੔Λ͍ͯ͘͠ɻ • ςʔϒϧ͕େ͖͍ͷʹର৅ߦ͕গͳ͍͔ΒIndexషͬͨํ ͕ྑ͍͔΋ʁ • ෆཁͳΧϥϜ·ͰऔͬͯΔ͔ΒΧϥϜݮΒͨ͠Β IndexOnlyScanͰ͍͚Δʁ 52
53. 4. ·ͱΊ 53
54. Explainจͱओཁͳॲཧϊʔυʹ͍ͭͯ ࿩Λ͠·ͨ͠ 54
55. ΋ͬͱ࣮ફతͳSQLνϡʔχϯά͕͍ͨ͠Μ͡Ό 55
56. ͓͢͢Ί [վగ৽൛]಺෦ߏ଄͔ΒֶͿ PostgreSQL ઃܭɾӡ༻ܭըͷమଇ https://amzn.to/2DLrrZo ୈ15ষ ࣮ߦܭըͷऔಘɾղੳ ΛಡΉͷ͕͓͢͢Ίɻ 56
57. ͓͢͢Ί PostgreSQL Conference Japan 2018 ͷ νϡʔτϦΞϧτϥοΫ [T3] SQL νϡʔχϯάͷجૅ https://www.slideshare.net/ HarukaTakatsuka/t3-pg- sqltuningbasic20181122 https://www.youtube.com/watch? v=Bc0kIGHqjFk 57
58. ฐࣾ MySQLͳΜΑͳ͊ɻɻ 58
59. ࣮ࡍͷख๏ PostgreSQL Conference Japan 2018 MySQL͔ΒPostgreSQL΁ͷҠߦͱ DBϦϑΝΫλϦϯά https://speakerdeck.com/takahashiikki/ postgresqljapan2018 59
60. ·ͱΊ • Explain Λ࣮ߦ͢Δͱϓϥϯφ͕ߟ͑ͨ ࣮ߦܭըΛݟΔ͜ͱ͕ग़དྷΔɻ • Explain (ANALYZE) Λ͢ΔࣄͰ࣮ߦܭըٴͼ ࣮ࡍʹ࣮ߦͨ͠ࡍʹ͔͔ͬͨ࣌ؒΛݟΔࣄ͕ग़དྷΔɻ • ࢼߦࡨޡΛ͍͖ͯ͠ͳ͕Β SQLνϡʔχϯάΛ͍͖ͯ͠· ͠ΐ͏ɻ 60
61. ೉ͦ͠͏ɻ 61
62. ໰୊ͱͷ޲͖߹͍ํ • ໰୊Λ100఺ຬ఺Ͱղܾ͢Δඞཁ͸ͳ͍ɻ • ೉͍࣌͠͸ੋඇ͜͏͍͏ίϛϡχςΟʔΛ׆༻ͯ͠΄͍͠ɻ • தࠃ஍ํDBษڧձ͸͍ͭ΋͋ͳͨͷͦ͹ʹɻɻ 62
63. ίϛϡχςΟʔͷ׆༻ PostgreSQLͷSlackΛ ੋඇ׆༻͍ͯͩ͘͠͞ɻ ॳ৺ऀ͔ΒPostgreSQLͷ ίϛολʔ·Ͱ৭Μͳํ͕ډΔɻ 63
64. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ 64
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