Link
Embed
Share
Beginning
This slide
Copy link URL
Copy link URL
Copy iframe embed code
Copy iframe embed code
Copy javascript embed code
Copy javascript embed code
Share
Tweet
Share
Tweet
Slide 1
Slide 1 text
SQLνϡʔχϯά ཧͱ࣮ફ 2019-10-05 OSC 2019 ৽ׁ
Slide 2
Slide 2 text
ҙࣄ߲ 4 εϥΠυެ։͍ͯ͠·͢ɻ 4 ࣭ͱ͔͕͋Ε ຊPostgreSQLϢʔβձ ϒʔεͰʂ 4 ͩͱஏ͔͍ͣ͠ɻɻͬͯํ #osc19ni ʹʂ 4 ֶੜͷํͱ͔DBʹ͍ͭͯ͋·Γৄ͘͠ແ͍ํΛରʹͨ͠ جૅతͳॴͷ͓Ͱ͢ɻ
Slide 3
Slide 3 text
ಥવͰ͕͢ԿͷάϥϑͰ͠ΐ͏ʁ
Slide 4
Slide 4 text
Կͷάϥϑʁ 4 Ҏલ ฐࣾͷӡӦαΠτʹͯ TV๒ʹΑΓDBͷΞΫηε͕ ٸ૿ͨ࣌͠ͷάϥϑ 4 ͜ͷؒαΠτ΄΅ΞΫηεग़དྷͳ͔ͬ ͨɻ ཧ༝DBɻ 4 ͦͷଞʹ αΠτͷද͕ࣔ3ඵΛ͑Δͱ 3ਓʹ1ਓ͕ݟΔͷΛࣙΊͯ͠·͏ɻ ͔ͤͬ͘ྑ͍ͷΛ࡞ͬͯ DB͕ࢮ͵=ച্্͕͛ΒΕͳ͍ɻ
Slide 5
Slide 5 text
ࠓͷͷඪ 4 σʔλϕʔεͷSQLͷԠ͕͍߹ʹ Ͳ͕͍͜ͷ͔ௐࠪ͢Δํ๏ͱ վળํ๏ͷجຊతͳॴΛͬͯΒ͏ɻ
Slide 6
Slide 6 text
͓͠ͳ͕͖ 4 ࣗݾհ 4 SQL࣮ߦͷΈ 4 SQLνϡʔχϯάͷجૅ 4 ·ͱΊ
Slide 7
Slide 7 text
1.ࣗݾհ 4 ߴڮɹҰٍ 4 Ԭࢁࡏॅ ʢ৽ׁདྷΔͷॳʂʣ 4 גࣜձࣾΦϛΧϨ WebΤϯδχΞ 4 ຊPostgreSQLϢʔβʔձ தࠃํ ࢧ෦
Slide 8
Slide 8 text
̎ɽSQL࣮ߦͷΈ
Slide 9
Slide 9 text
SQLͱ DBʹ͓͍ͯɺσʔλͷૢ࡞ఆٛΛߦ͏ͨΊͷ σʔλϕʔεݴޠʢ͍߹ΘͤݴޠʣͷҰͭɻ By. ʰ https://ja.wikipedia.org/wiki/SQL ʱ
Slide 10
Slide 10 text
SQLͱ ྫ SELECT ϝʔϧΞυϨε, ੜ, ੑผ FROM ސ٬ใ WHERE ໊લ = 'ߴڮ'; 4 ߏจͷҙຯ ʰސ٬ใʱ ͱ͍͏ςʔϒϧ͔Β ʰ໊લ͕ 'ߴڮ'ʱʹ߹க͍ͯ͠ΔσʔλΛରʹ ʰϝʔϧΞυϨεɺੜɺੑผʱͷσʔλΛ͍ͩ͘͞ɻ
Slide 11
Slide 11 text
SQLจ͕ॲཧ͞Ε͍ͯ͘ྲྀΕ
Slide 12
Slide 12 text
SQLจ͕ॲཧ͞Ε͍ͯ͘ྲྀΕ ͞ΕͨSQL͕ Ұ൪ߴʹ࣮ߦ͞ΕΔͰ͋Ζ͏࣮ߦܭըΛ ϓϥϯφ͕ܾΊͯɺ ͦͷ࣮ߦܭըʹԊͬͯSQLΛ࣮ߦͯ݁͠ՌΛฦ͢
Slide 13
Slide 13 text
࣮ߦܭըͲ͏ܾͬͯ·Δͷ͔ʁ 4 DBͰ ʮ͜ͷςʔϒϧʹ͜Μͳσʔλ͕ೖͬͯΔʯͱ͍ ͏౷ܭใΛ࣋ͬͯΔɻ 4 ϓϥϯφ ౷ܭใΛಡΜͰ ʰ͜ͷςʔϒϧ͜ͷΑ͏ͳσʔλ͕ೖͬͯΔ͔Β ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷ͩΖ͏ʱ ͱ࣮ߦܭըΛܾΊΔɻ
Slide 14
Slide 14 text
SQLνϡʔχϯάͷجૅ 4 ΑΓʮείΞ͕ग़Δʯ࣮ߦܭըΛ ϓϥϯφ͕ΈཱͯΔࣄ͕ग़དྷΔΑ͏ʹௐ͢Δɻ => ͦͷҝʹ ࣮ࡍʹϓϥϯφ͕ ͲΜͳ࣮ߦܭըΛཱ͔ͯͨݟͯνϡʔχϯά͢Δඞཁ͕͋Δ
Slide 15
Slide 15 text
̏ɽSQLνϡʔχϯάͷجૅ
Slide 16
Slide 16 text
SQLνϡʔχϯάͷجૅ લষʹͯɺϓϥϯφཱ͕࣮ͯͨߦܭըΛΓ ͦͷ࣮ߦܭըΛΑΓͷग़Δ࣮ߦܭըʹ͢ΔΑ͏ʹ վળ͢Δͷ͕SQLνϡʔχϯάͷجૅɺͱ͍͏Λͨ͠
Slide 17
Slide 17 text
࣮ߦܭըͷݟํ 4 EXPLAIN ͱ͍͏ΩʔϫʔυΛSQLจͷઌ಄ʹ͚Δɻ 4 ࣮ߦܭը͕ʮਓʹ͔ΔΑ͏ͳܗͰʯฦͬͯ͘Δɻ ྫɿ EXPLAIN SELECT ϝʔϧΞυϨε FROM ސ٬ใ WHERE ໊લ = 'ߴڮ';
Slide 18
Slide 18 text
ͱ͍͑ɺݴͬͯܭը
Slide 19
Slide 19 text
࣮ߦܭըͷݟํ 4 EXPLAIN ANALYZE ͱ͍͏ΩʔϫʔυΛ SQLจͷઌ಄ʹ͚Δɻ 4 ࣮ߦܭըͱ࣮ߦʹ͔͔͕ͬͨ࣌ؒฦͬͯ͘Δɻ ྫɿ EXPLAIN ANALYZE SELECT ϝʔϧΞυϨε FROM ސ٬ใ WHERE ໊લ = 'ߴڮ';
Slide 20
Slide 20 text
σϞ Part1
Slide 21
Slide 21 text
࣮ߦܭըͷྫ 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)
Slide 22
Slide 22 text
࣮ߦܭըͷݟํ ಛʹॏཁͳՕॴೋ 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)
Slide 23
Slide 23 text
࣮ߦܭըͷݟํ ಛʹॏཁͳՕॴೋ 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)
Slide 24
Slide 24 text
࣮ߦܭըͷݟํ 4 Parallel Seq Scan on customers 4 Execution Time: 174.869 ms
Slide 25
Slide 25 text
࣮ߦܭըͷݟํ Parallel Seq Scan on customers 4 ʮParallelʯ + ʮSeq Scanʯ + ʮon customersʯ
Slide 26
Slide 26 text
Seq Scan ͱ 4 ຊޠͩͱʮॱ࣍ݕࡧʯ 4 ߟ͑ํඇৗʹγϯϓϧͰDBʹ֨ೲ͞Εͨ ʮͯ͢ͷσʔλΛॱʹݕࡧ͢Δʯݕࡧํ๏ ʮSeq Scanʯ + ʮon customersʯ ͱॻ͔Ε͍ͯΔࣄ͔Β customers ςʔϒϧͷதΛ ॱ൪ʹશͯݟ͍ͯͬͨ ͱ͍͏ࣄ͕͔Δɻ
Slide 27
Slide 27 text
Parallel ͱ 4 PostgreSQLʹ͋ΔػೳͷҰͭɻ 4 σʔλϕʔεͷ͍߹ΘͤΛෳͷϫʔΧʔʹΑͬͯ ฒྻʹݕࡧ͢Δػೳɻ ʮParallelʯ ͱॻ͔Ε͍ͯΔࣄ͔Β ͜ͷSQL ςʔϒϧͷσʔλΛฒྻʹݕࡧͨ͠ࣄ͕͔Δɻ
Slide 28
Slide 28 text
࣮ߦܭըͷݟํ Execution Time: 174.869 ms 4 ࣮ߦܭըʹԊͬͯSQLΛ࣮ߦ͔͔ͯͬͨ࣌ؒ͠ɻ
Slide 29
Slide 29 text
Ҏ্Λ౿·͑ͯɺ࠶ ࣮ߦܭըΛݟͯΈΑ͏ɻ ---------------------------------------------------- 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)
Slide 30
Slide 30 text
࣮ߦܭը͔ΒνϡʔχϯάϙΠϯτΛ୳͢ 4 ͦ͏ͳॴແ͍͔ɻ 4 Seq Scan ʹண => ސ٬σʔλαʔϏε͕͢Ε͢Δ΄Ͳ૿͑Δɻ => Seq ScanσʔλΛશͯݕࡧ͢Δݕࡧํ๏ɻ => αʔϏεͷʹ͍ɺ͜ͷSQLͲΜͲΜ͘ͳΔɻ => ͜͜ʹߴԽͷ؊͕͋Γͦ͏ɻ
Slide 31
Slide 31 text
σϞ Part2
Slide 32
Slide 32 text
IndexͰߴԽ͍ͯ͘͠ CREATE INDEX customers_name_idx ON customers (name); 4 customers ͷ name ʹ customemrs_name_idxͱ͍͏໊લͷ ΠϯσοΫεΛ࡞Δ
Slide 33
Slide 33 text
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)
Slide 34
Slide 34 text
Indexߏஙલͱߏஙޙ ߲ Indexߏஙલ Indexߏஙޙ εΩϟϯํ๏ Parallel Seq Scan Bitmap Heap Scan ࣮ߦ࣌ؒ 174.869 ms 2.918 ms
Slide 35
Slide 35 text
------------------------------------------------------- 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)
Slide 36
Slide 36 text
̐ɽ·ͱΊ
Slide 37
Slide 37 text
̐ɽ·ͱΊ 4 ࠓճSQLͷ࣮ߦͷΈͱ࣮ߦܭըͷݟํɺ ߴԽʹ͋ͨͬͯߟ͑Δॴͱ࣮ࡍͷߴԽख๏ʹ͍ͭͯ ͓͖ͤͯ͞·ͨ͠ɻ 4 ࠓճͷඇৗʹ؆୯ͳσϞͰIndex͕͙͢ʹޮՌ͕͋Δ Α͏ͳܗͰͨ͠ɻ ࣮Ͱ͍͏ͱ׳Ε͍ͯͳ͍ͱ୯७ʹߴԽग़དྷΔΑ͏ͳ IndexΛషΔͷ͔ͬͨ͠Γ͠·͢ɻ
Slide 38
Slide 38 text
̐ɽ·ͱΊ 4 ͱ͍͑ɺࢼߦࡨޡඇৗʹେࣄͰ͢ɻ 4 ಛघͳ߹Λআ͍ͯɺIndex࡞ͬͯμϝͩͬͨΒݩʹͤ ྑ͍ͷͰ͢ɻ 4 جૅΛԡ͑ͯ͞ΑΓ༗ޮͳIndexΛ࡞ΔΑ͏ʹͳΓͭͭɺ ࣌ʹΞάϨογϒʹSQLͱ͖߹͍͖ͬͯ·͠ΐ͏ʂ
Slide 39
Slide 39 text
͝ਗ਼͋Γ͕ͱ͏͍͟͝ ·ͨ͠ɻ