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

# 概要

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

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

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

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

を離しました、

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