$30 off During Our Annual Pro Sale. View Details »

SQLチューニング 理論と実践/osc19ni

SQLチューニング 理論と実践/osc19ni

# 概要

2019年10月05日に開催される OSC2019 新潟 の発表スライドです。

https://ospn.connpass.com/event/145108/

株式会社オミカレ ( https://party-calendar.net/ ) で実際に取り組んだ高速化手法を元に、 そもそもSQLをチューニングする 理論と実践とはなにか?という所で、発表しました。

初学者の方が対象で PostgreSQLを元に
- SQLはどういう風に実行されるのか
- Explainの処理結果の見方
- それを元にして高速化するにはどうするか

を離しました、

Takahashi Ikki

October 05, 2019
Tweet

More Decks by Takahashi Ikki

Other Decks in Programming

Transcript

  1. SQLνϡʔχϯά
    ཧ࿦ͱ࣮ફ
    2019-10-05
    OSC 2019 ৽ׁ

    View Slide

  2. ஫ҙࣄ߲
    4 εϥΠυ͸ެ։͍ͯ͠·͢ɻ
    4 ࣭໰ͱ͔͕͋Ε͹ ೔ຊPostgreSQLϢʔβձ ϒʔεͰʂ
    4 ௚઀ͩͱஏ͔͍ͣ͠ɻɻͬͯํ͸ #osc19ni ʹʂ
    4 ֶੜͷํͱ͔DBʹ͍ͭͯ͋·Γৄ͘͠ແ͍ํΛର৅ʹͨ͠
    جૅతͳॴͷ͓࿩Ͱ͢ɻ

    View Slide

  3. ಥવͰ͕͢ԿͷάϥϑͰ͠ΐ͏ʁ

    View Slide

  4. Կͷάϥϑʁ
    4 Ҏલ ฐࣾͷӡӦαΠτʹͯ
    TV๒ʹΑΓDB΁ͷΞΫηε͕
    ٸ૿ͨ࣌͠ͷάϥϑ
    4 ͜ͷؒαΠτ͸΄΅ΞΫηεग़དྷͳ͔ͬ
    ͨɻ
    ཧ༝͸DBɻ
    4 ͦͷଞʹ΋
    αΠτͷදࣔ଎౓͕3ඵΛ௒͑Δͱ
    3ਓʹ1ਓ͕ݟΔͷΛࣙΊͯ͠·͏ɻ
    ͔ͤͬ͘ྑ͍΋ͷΛ࡞ͬͯ΋
    DB͕ࢮ͵=ച্্͕͛ΒΕͳ͍ɻ

    View Slide

  5. ࠓ೔ͷ࿩ͷ໨ඪ
    4 σʔλϕʔεͷSQLͷԠ౴଎౓͕஗͍৔߹ʹ
    Ͳ͕͜஗͍ͷ͔ௐࠪ͢Δํ๏ͱ
    վળํ๏ͷجຊతͳॴΛ஌ͬͯ΋Β͏ɻ

    View Slide

  6. ͓͠ͳ͕͖
    4 ࣗݾ঺հ
    4 SQL࣮ߦͷ࢓૊Έ
    4 SQLνϡʔχϯάͷجૅ
    4 ·ͱΊ

    View Slide

  7. 1.ࣗݾ঺հ
    4 ߴڮɹҰٍ
    4 Ԭࢁࡏॅ ʢ৽ׁདྷΔͷॳʂʣ
    4 גࣜձࣾΦϛΧϨ WebΤϯδχΞ
    4 ೔ຊPostgreSQLϢʔβʔձ
    தࠃ஍ํ ࢧ෦௕

    View Slide

  8. ̎ɽSQL࣮ߦͷ࢓૊Έ

    View Slide

  9. SQLͱ͸
    DBʹ͓͍ͯɺσʔλͷૢ࡞΍ఆٛΛߦ͏ͨΊͷ
    σʔλϕʔεݴޠʢ໰͍߹ΘͤݴޠʣͷҰͭɻ
    By. ʰ https://ja.wikipedia.org/wiki/SQL ʱ

    View Slide

  10. SQLͱ͸

    SELECT ϝʔϧΞυϨε, ஀ੜ೔, ੑผ
    FROM ސ٬৘ใ
    WHERE ໊લ = 'ߴڮ';
    4 ߏจͷҙຯ
    ʰސ٬৘ใʱ ͱ͍͏ςʔϒϧ͔Β
    ʰ໊લ͕ 'ߴڮ'ʱʹ߹க͍ͯ͠ΔσʔλΛର৅ʹ
    ʰϝʔϧΞυϨεɺ஀ੜ೔ɺੑผʱͷσʔλΛ͍ͩ͘͞ɻ

    View Slide

  11. SQLจ͕ॲཧ͞Ε͍ͯ͘ྲྀΕ

    View Slide

  12. SQLจ͕ॲཧ͞Ε͍ͯ͘ྲྀΕ
    ౉͞ΕͨSQL͕
    Ұ൪ߴ଎ʹ࣮ߦ͞ΕΔͰ͋Ζ͏࣮ߦܭըΛ
    ϓϥϯφ͕ܾΊͯɺ
    ͦͷ࣮ߦܭըʹԊͬͯSQLΛ࣮ߦͯ݁͠ՌΛฦ͢

    View Slide

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

    View Slide

  14. SQLνϡʔχϯάͷجૅ
    4 ΑΓʮείΞ͕ग़Δʯ࣮ߦܭըΛ
    ϓϥϯφ͕૊ΈཱͯΔࣄ͕ग़དྷΔΑ͏ʹௐ੔͢Δɻ
    => ͦͷҝʹ͸ ࣮ࡍʹϓϥϯφ͕
    ͲΜͳ࣮ߦܭըΛཱ͔ͯͨݟͯνϡʔχϯά͢Δඞཁ͕͋Δ

    View Slide

  15. ̏ɽSQLνϡʔχϯάͷجૅ

    View Slide

  16. SQLνϡʔχϯάͷجૅ
    લষʹͯɺϓϥϯφཱ͕࣮ͯͨߦܭըΛ஌Γ
    ͦͷ࣮ߦܭըΛΑΓ଎౓ͷग़Δ࣮ߦܭըʹ͢ΔΑ͏ʹ
    վળ͢Δͷ͕SQL଎౓νϡʔχϯάͷجૅɺͱ͍͏࿩Λͨ͠

    View Slide

  17. ࣮ߦܭըͷݟํ
    4 EXPLAIN ͱ͍͏ΩʔϫʔυΛSQLจͷઌ಄ʹ෇͚Δɻ
    4 ࣮ߦܭը͕ʮਓʹ෼͔ΔΑ͏ͳܗͰʯฦͬͯ͘Δɻ
    ྫɿ
    EXPLAIN
    SELECT ϝʔϧΞυϨε
    FROM ސ٬৘ใ
    WHERE ໊લ = 'ߴڮ';

    View Slide

  18. ͱ͸͍͑ɺݴͬͯ΋ܭը

    View Slide

  19. ࣮ߦܭըͷݟํ
    4 EXPLAIN ANALYZE ͱ͍͏ΩʔϫʔυΛ
    SQLจͷઌ಄ʹ෇͚Δɻ
    4 ࣮ߦܭըͱ࣮ߦʹ͔͔͕ͬͨ࣌ؒฦͬͯ͘Δɻ
    ྫɿ
    EXPLAIN ANALYZE
    SELECT ϝʔϧΞυϨε
    FROM ސ٬৘ใ
    WHERE ໊લ = 'ߴڮ';

    View Slide

  20. σϞ Part1

    View Slide

  21. ࣮ߦܭըͷྫ
    osc_demo=# EXPLAIN ANALYZE SELECT * FROM customers WHERE name = 'percy.wolff';
    QUERY PLAN
    ----------------------------------------------------------------------
    Gather (cost=1000.00..10172.63 rows=3 width=110)
    (actual time=170.646..174.786 rows=1 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    -> Parallel Seq Scan on customers (cost=0.00..9172.33 rows=1 width=110)
    (actual time=134.860..136.997 rows=0 loops=3)
    Filter: ((name)::text = 'percy.wolff'::text)
    Rows Removed by Filter: 133333
    Planning Time: 2.530 ms
    Execution Time: 174.869 ms
    (8 rows)

    View Slide

  22. ࣮ߦܭըͷݟํ
    ಛʹॏཁͳՕॴ͸ೋ఺
    Gather (cost=1000.00..10172.63 rows=3 width=110)
    (actual time=170.646..174.786 rows=1 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    -> Parallel Seq Scan on customers (cost=0.00..9172.33 rows=1 width=110)
    (actual time=134.860..136.997 rows=0 loops=3)
    Filter: ((name)::text = 'percy.wolff'::text)
    Rows Removed by Filter: 133333
    Planning Time: 2.530 ms
    Execution Time: 174.869 ms
    (8 rows)

    View Slide

  23. ࣮ߦܭըͷݟํ
    ಛʹॏཁͳՕॴ͸ೋ఺
    Gather (cost=1000.00..10172.63 rows=3 width=110)
    (actual time=170.646..174.786 rows=1 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    -> ʲParallel Seq Scan on customersʳ (cost=0.00..9172.33 rows=1 width=110)
    (actual time=134.860..136.997 rows=0 loops=3)
    Filter: ((name)::text = 'percy.wolff'::text)
    Rows Removed by Filter: 133333
    Planning Time: 2.530 ms
    Execution Time: ʲ174.869 msʳ
    (8 rows)

    View Slide

  24. ࣮ߦܭըͷݟํ
    4 Parallel Seq Scan on customers
    4 Execution Time: 174.869 ms

    View Slide

  25. ࣮ߦܭըͷݟํ
    Parallel Seq Scan on customers
    4 ʮParallelʯ + ʮSeq Scanʯ + ʮon customersʯ

    View Slide

  26. Seq Scan ͱ͸
    4 ೔ຊޠͩͱʮॱ࣍ݕࡧʯ
    4 ߟ͑ํ͸ඇৗʹγϯϓϧͰDBʹ֨ೲ͞Εͨ
    ʮ͢΂ͯͷσʔλΛॱʹݕࡧ͢Δʯݕࡧํ๏
    ʮSeq Scanʯ + ʮon customersʯ ͱॻ͔Ε͍ͯΔࣄ͔Β
    customers ςʔϒϧͷத਎Λ ॱ൪ʹશͯݟ͍ͯͬͨ
    ͱ͍͏ࣄ͕෼͔Δɻ

    View Slide

  27. Parallel ͱ͸
    4 PostgreSQLʹ͋ΔػೳͷҰͭɻ
    4 σʔλϕʔεͷ໰͍߹ΘͤΛෳ਺ͷϫʔΧʔʹΑͬͯ
    ฒྻʹݕࡧ͢Δػೳɻ
    ʮParallelʯ ͱॻ͔Ε͍ͯΔࣄ͔Β
    ͜ͷSQL͸ ςʔϒϧͷσʔλΛฒྻʹݕࡧͨ͠ࣄ͕෼͔Δɻ

    View Slide

  28. ࣮ߦܭըͷݟํ
    Execution Time: 174.869 ms
    4 ࣮ߦܭըʹԊͬͯSQLΛ࣮ߦ͔͔ͯͬͨ࣌ؒ͠ɻ

    View Slide

  29. Ҏ্Λ౿·͑ͯɺ࠶౓ ࣮ߦܭըΛݟͯΈΑ͏ɻ
    ----------------------------------------------------
    Gather (cost=1000.00..10172.63 rows=3 width=110)
    (actual time=170.646..174.786 rows=1 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    -> Parallel Seq Scan on customers (cost=0.00..9172.33 rows=1 width=110)
    (actual time=134.860..136.997 rows=0 loops=3)
    Filter: ((name)::text = 'percy.wolff'::text)
    Rows Removed by Filter: 133333
    Planning Time: 2.530 ms
    Execution Time: 174.869 ms
    (8 rows)

    View Slide

  30. ࣮ߦܭը͔ΒνϡʔχϯάϙΠϯτΛ୳͢
    4 ஗ͦ͏ͳॴແ͍͔ɻ
    4 Seq Scan ʹண໨
    => ސ٬σʔλ͸αʔϏε͕੒௕͢Ε͹͢Δ΄Ͳ૿͑Δɻ
    => Seq Scan͸σʔλΛશͯݕࡧ͢Δݕࡧํ๏ɻ
    => αʔϏεͷ੒௕ʹ൐͍ɺ͜ͷSQL͸ͲΜͲΜ஗͘ͳΔɻ
    => ͜͜ʹߴ଎Խͷ؊͕͋Γͦ͏ɻ

    View Slide

  31. σϞ Part2

    View Slide

  32. IndexͰߴ଎Խ͍ͯ͘͠
    CREATE INDEX customers_name_idx ON customers (name);
    4 customers ͷ name ʹ customemrs_name_idxͱ͍͏໊લͷ
    ΠϯσοΫεΛ࡞Δ

    View Slide

  33. 4 ࣮ߦ݁Ռ
    -------------------------------------------------------
    Bitmap Heap Scan on customers
    (cost=4.45..16.30 rows=3 width=110)
    (actual time=2.301..2.319 rows=1 loops=1)
    Recheck Cond: ((name)::text = 'percy.wolff'::text)
    Heap Blocks: exact=1
    -> Bitmap Index Scan on customers_name_idx
    (cost=0.00..4.45 rows=3 width=0)
    (actual time=2.275..2.284 rows=1 loops=1)
    Index Cond: ((name)::text = 'percy.wolff'::text)
    Planning Time: 6.774 ms
    Execution Time: 2.918 ms
    (7 rows)

    View Slide

  34. Indexߏஙલͱߏஙޙ
    ߲໨ Indexߏஙલ Indexߏஙޙ
    εΩϟϯํ๏ Parallel Seq
    Scan
    Bitmap Heap Scan
    ࣮ߦ࣌ؒ 174.869 ms 2.918 ms

    View Slide

  35. -------------------------------------------------------
    Bitmap Heap Scan on customers
    (cost=4.45..16.30 rows=3 width=110)
    (actual time=2.301..2.319 rows=1 loops=1)
    Recheck Cond: ((name)::text = 'percy.wolff'::text)
    Heap Blocks: exact=1
    -> Bitmap Index Scan on customers_name_idx
    (cost=0.00..4.45 rows=3 width=0)
    (actual time=2.275..2.284 rows=1 loops=1)
    Index Cond: ((name)::text = 'percy.wolff'::text)
    Planning Time: 6.774 ms
    Execution Time: 2.918 ms
    (7 rows)

    View Slide

  36. ̐ɽ·ͱΊ

    View Slide

  37. ̐ɽ·ͱΊ
    4 ࠓճ͸SQLͷ࣮ߦͷ࢓૊Έͱ࣮ߦܭըͷݟํɺ
    ߴ଎Խʹ͋ͨͬͯߟ͑Δॴͱ࣮ࡍͷߴ଎Խख๏ʹ͍ͭͯ
    ͓࿩ͤͯ͞௖͖·ͨ͠ɻ
    4 ࠓճͷ࿩͸ඇৗʹ؆୯ͳσϞͰIndex͕͙͢ʹޮՌ͕͋Δ
    Α͏ͳܗͰͨ͠ɻ
    ࣮຿Ͱ͍͏ͱ׳Ε͍ͯͳ͍ͱ୯७ʹߴ଎Խग़དྷΔΑ͏ͳ
    IndexΛషΔͷ͸೉͔ͬͨ͠Γ͠·͢ɻ

    View Slide

  38. ̐ɽ·ͱΊ
    4 ͱ͸͍͑ɺࢼߦࡨޡ͸ඇৗʹେࣄͰ͢ɻ
    4 ಛघͳ৔߹Λআ͍ͯɺIndex͸࡞ͬͯμϝͩͬͨΒݩʹ໭ͤ
    ͹ྑ͍ͷͰ͢ɻ
    4 جૅΛԡ͑ͯ͞ΑΓ༗ޮͳIndexΛ࡞ΔΑ͏ʹͳΓͭͭɺ
    ࣌ʹ͸ΞάϨογϒʹSQLͱ޲͖߹͍͖ͬͯ·͠ΐ͏ʂ

    View Slide

  39. ͝ਗ਼੅͋Γ͕ͱ͏͍͟͝
    ·ͨ͠ɻ

    View Slide