Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

# 概要

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

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

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

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

を離しました、

F891bc57aad1b6bdbf344358e7fec3cc?s=128

Takahashi Ikki

October 05, 2019
Tweet

Transcript

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

  2. ஫ҙࣄ߲ 4 εϥΠυ͸ެ։͍ͯ͠·͢ɻ 4 ࣭໰ͱ͔͕͋Ε͹ ೔ຊPostgreSQLϢʔβձ ϒʔεͰʂ 4 ௚઀ͩͱஏ͔͍ͣ͠ɻɻͬͯํ͸ #osc19ni

    ʹʂ 4 ֶੜͷํͱ͔DBʹ͍ͭͯ͋·Γৄ͘͠ແ͍ํΛର৅ʹͨ͠ جૅతͳॴͷ͓࿩Ͱ͢ɻ
  3. ಥવͰ͕͢ԿͷάϥϑͰ͠ΐ͏ʁ

  4. Կͷάϥϑʁ 4 Ҏલ ฐࣾͷӡӦαΠτʹͯ TV๒ʹΑΓDB΁ͷΞΫηε͕ ٸ૿ͨ࣌͠ͷάϥϑ 4 ͜ͷؒαΠτ͸΄΅ΞΫηεग़དྷͳ͔ͬ ͨɻ ཧ༝͸DBɻ

    4 ͦͷଞʹ΋ αΠτͷදࣔ଎౓͕3ඵΛ௒͑Δͱ 3ਓʹ1ਓ͕ݟΔͷΛࣙΊͯ͠·͏ɻ ͔ͤͬ͘ྑ͍΋ͷΛ࡞ͬͯ΋ DB͕ࢮ͵=ച্্͕͛ΒΕͳ͍ɻ
  5. ࠓ೔ͷ࿩ͷ໨ඪ 4 σʔλϕʔεͷSQLͷԠ౴଎౓͕஗͍৔߹ʹ Ͳ͕͜஗͍ͷ͔ௐࠪ͢Δํ๏ͱ վળํ๏ͷجຊతͳॴΛ஌ͬͯ΋Β͏ɻ

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

  7. 1.ࣗݾ঺հ 4 ߴڮɹҰٍ 4 Ԭࢁࡏॅ ʢ৽ׁདྷΔͷॳʂʣ 4 גࣜձࣾΦϛΧϨ WebΤϯδχΞ 4

    ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํ ࢧ෦௕
  8. ̎ɽSQL࣮ߦͷ࢓૊Έ

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

  10. SQLͱ͸ ྫ SELECT ϝʔϧΞυϨε, ஀ੜ೔, ੑผ FROM ސ٬৘ใ WHERE ໊લ

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

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

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

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

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

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

  17. ࣮ߦܭըͷݟํ 4 EXPLAIN ͱ͍͏ΩʔϫʔυΛSQLจͷઌ಄ʹ෇͚Δɻ 4 ࣮ߦܭը͕ʮਓʹ෼͔ΔΑ͏ͳܗͰʯฦͬͯ͘Δɻ ྫɿ EXPLAIN SELECT ϝʔϧΞυϨε

    FROM ސ٬৘ใ WHERE ໊લ = 'ߴڮ';
  18. ͱ͸͍͑ɺݴͬͯ΋ܭը

  19. ࣮ߦܭըͷݟํ 4 EXPLAIN ANALYZE ͱ͍͏ΩʔϫʔυΛ SQLจͷઌ಄ʹ෇͚Δɻ 4 ࣮ߦܭըͱ࣮ߦʹ͔͔͕ͬͨ࣌ؒฦͬͯ͘Δɻ ྫɿ EXPLAIN

    ANALYZE SELECT ϝʔϧΞυϨε FROM ސ٬৘ใ WHERE ໊લ = 'ߴڮ';
  20. σϞ Part1

  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)
  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)
  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)
  24. ࣮ߦܭըͷݟํ 4 Parallel Seq Scan on customers 4 Execution Time:

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

    Scanʯ + ʮon customersʯ
  26. Seq Scan ͱ͸ 4 ೔ຊޠͩͱʮॱ࣍ݕࡧʯ 4 ߟ͑ํ͸ඇৗʹγϯϓϧͰDBʹ֨ೲ͞Εͨ ʮ͢΂ͯͷσʔλΛॱʹݕࡧ͢Δʯݕࡧํ๏ ʮSeq Scanʯ

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

    ςʔϒϧͷσʔλΛฒྻʹݕࡧͨ͠ࣄ͕෼͔Δɻ
  28. ࣮ߦܭըͷݟํ Execution Time: 174.869 ms 4 ࣮ߦܭըʹԊͬͯSQLΛ࣮ߦ͔͔ͯͬͨ࣌ؒ͠ɻ

  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)
  30. ࣮ߦܭը͔ΒνϡʔχϯάϙΠϯτΛ୳͢ 4 ஗ͦ͏ͳॴແ͍͔ɻ 4 Seq Scan ʹண໨ => ސ٬σʔλ͸αʔϏε͕੒௕͢Ε͹͢Δ΄Ͳ૿͑Δɻ =>

    Seq Scan͸σʔλΛશͯݕࡧ͢Δݕࡧํ๏ɻ => αʔϏεͷ੒௕ʹ൐͍ɺ͜ͷSQL͸ͲΜͲΜ஗͘ͳΔɻ => ͜͜ʹߴ଎Խͷ؊͕͋Γͦ͏ɻ
  31. σϞ Part2

  32. IndexͰߴ଎Խ͍ͯ͘͠ CREATE INDEX customers_name_idx ON customers (name); 4 customers ͷ

    name ʹ customemrs_name_idxͱ͍͏໊લͷ ΠϯσοΫεΛ࡞Δ
  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)
  34. Indexߏஙલͱߏஙޙ ߲໨ Indexߏஙલ Indexߏஙޙ εΩϟϯํ๏ Parallel Seq Scan Bitmap Heap

    Scan ࣮ߦ࣌ؒ 174.869 ms 2.918 ms
  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)
  36. ̐ɽ·ͱΊ

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

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

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