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 ৽ׁ
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. ͝ਗ਼੅͋Γ͕ͱ͏͍͟͝ ·ͨ͠ɻ