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. 2.

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

    ʹʂ 4 ֶੜͷํͱ͔DBʹ͍ͭͯ͋·Γৄ͘͠ແ͍ํΛର৅ʹͨ͠ جૅతͳॴͷ͓࿩Ͱ͢ɻ
  2. 4.

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

    4 ͦͷଞʹ΋ αΠτͷදࣔ଎౓͕3ඵΛ௒͑Δͱ 3ਓʹ1ਓ͕ݟΔͷΛࣙΊͯ͠·͏ɻ ͔ͤͬ͘ྑ͍΋ͷΛ࡞ͬͯ΋ DB͕ࢮ͵=ച্্͕͛ΒΕͳ͍ɻ
  3. 10.

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

    = 'ߴڮ'; 4 ߏจͷҙຯ ʰސ٬৘ใʱ ͱ͍͏ςʔϒϧ͔Β ʰ໊લ͕ 'ߴڮ'ʱʹ߹க͍ͯ͠ΔσʔλΛର৅ʹ ʰϝʔϧΞυϨεɺ஀ੜ೔ɺੑผʱͷσʔλΛ͍ͩ͘͞ɻ
  4. 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)
  5. 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)
  6. 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)
  7. 26.

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

    + ʮon customersʯ ͱॻ͔Ε͍ͯΔࣄ͔Β customers ςʔϒϧͷத਎Λ ॱ൪ʹશͯݟ͍ͯͬͨ ͱ͍͏ࣄ͕෼͔Δɻ
  8. 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)
  9. 30.

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

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

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

    name ʹ customemrs_name_idxͱ͍͏໊લͷ ΠϯσοΫεΛ࡞Δ
  11. 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)
  12. 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)