Share
Embed
SQLチューニング_理論と改善の実例_/pgcon19j_t4
# SQLチューニング 理論と改善の実例
- 2019-11-15 開催のPostgreSQL Conference Japan 2019 の発表スライドです
Takahashi Ikki
October 14, 2019
Transcript
60 slides
-
1. ࣗݾհ • ߴڮ Ұٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ WebΞϓϦέʔγϣϯΤϯδχΞ
• ຊPostgreSQLϢʔβʔձ தࠃࢧ෦ 4 -
࣮ߦܭըͲͷΑ͏ʹܾ·Δͷ͔ • લఏͱͯ͠ ϓϥϯφ σʔλϕʔεʹͲΜͳ͕ೖͬͯΔ͔ ΛΒͳ͍ɻ • ϓϥϯφ σʔλϕʔε͕ཧ͍ͯ͠Δ ౷ܭใ
Λݟͯ ʮ͜ͷςʔϒϧʹ͜Μͳσʔλ͕ೖͬͯΔʹ͋Δ͔Β ɹ͜ͷΑ͏ʹΞΫηε͢Δͷ͕࠷ͩΖ͏ʯ ͱ࣮ߦܭըΛܾΊΔɻ 9 -
2. SQL͕࣮ߦ͞ΕΔΈ • SQLͷ࣮ߦʹ͓͍ͯ ύʔαʔɾϦϥΠλɾϓϥϯφɾΤΫθΩϡʔλ ͱ͍͏ׂ͕͋Δɻ • ϓϥϯφ౷ܭใΛݩʹ࣮ߦܭըΛ࡞Γ ΤΫθΩϡʔλ͕࣮ߦܭը௨ΓʹSQLΛ࣮ߦ͢Δ •
࣮ߦܭըΛಡΜͰ͘ͳΓͦ͏ͳՕॴΛݟ͍ͯ͘ࣄ͕ SQLνϡʔχϯάͷ֩ͱͳΔɻ 11 -
࣮ߦܭըͷྫ EXPLAIN SELECT * FROM sales INNER JOIN customers ON
sales.customer_id = customers.id WHERE sales.customer_id = 15; QUERY PLAN ---------------------------------------------------------------------- Nested Loop (cost=0.42..502666.88 rows=59 width=147) -> Index Scan using customers_pkey on customers (cost=0.42..8.44 rows=1 width=110) Index Cond: (id = 15) -> Seq Scan on sales (cost=0.00..502657.85 rows=59 width=37) Filter: (customer_id = 15) 14 -
࣮ߦܭըͷྫ EXPLAIN ANALYZE SELECT * FROM sales INNER JOIN customers
ON sales.customer_id = customers.id WHERE sales.customer_id = 15; QUERY PLAN ----------------------------------------------------------------------------------------- Nested Loop (cost=0.42..502666.88 rows=59 width=147) (actual time=9.838..2883.704 rows=60 loops=1) -> Index Scan using customers_pkey on customers (cost=0.42..8.44 rows=1 width=110) (actual time=1.205..1.221 rows=1 loops=1) Index Cond: (id = 15) -> Seq Scan on sales (cost=0.00..502657.85 rows=59 width=37) (actual time=8.603..2881.422 rows=60 loops=1) Filter: (customer_id = 15) Rows Removed by Filter: 23999940 Planning Time: 3.930 ms Execution Time: 2885.418 ms 16 -
࣮ߦܭըͷྫ EXPLAIN ANALYZE SELECT * FROM sales INNER JOIN customers
ON sales.customer_id = customers.id WHERE sales.customer_id = 15; QUERY PLAN ----------------------------------------------------------------------------------------- Nested Loop (cost=0.42..502666.88 rows=59 width=147) ʲ(actual time=9.838..2883.704 rows=60 loops=1)ʳ -> Index Scan using customers_pkey on customers (cost=0.42..8.44 rows=1 width=110) ʲ(actual time=1.205..1.221 rows=1 loops=1)ʳ Index Cond: (id = 15) -> Seq Scan on sales (cost=0.00..502657.85 rows=59 width=37) ʲ(actual time=8.603..2881.422 rows=60 loops=1)ʳ Filter: (customer_id = 15) Rows Removed by Filter: 23999940 ʲPlanning Time: 3.930 msʳ ʲExecution Time: 2885.418 msʳ 17 -
ओͳॲཧϊʔυ ॲཧϊʔυ ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index
Scan εΩϟϯ Hash Join ݁߹ Index Only Scan εΩϟϯ Marge Join ݁߹ Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ Sort ιʔτ Aggregate άϧʔϐϯάܥ 21 -
ओͳॲཧϊʔυ ॲཧϊʔυ ྨ Seq Scan εΩϟϯ Nested Loop ݁߹ Index
Scan εΩϟϯ Hash Join ݁߹ Index Only Scan εΩϟϯ Marge Join ݁߹ Bitmap Index Scan εΩϟϯ Limit Ϧϛοτ Sort ιʔτ Aggregate άϧʔϐϯάܥ • εΩϟϯܥϊʔυ݁߹ܥϊʔυෳछྨ͋Δɻ ݁Ռಉ͚ͩ͡ͲΞϧΰϦζϜ͕ҟͳΔɻ 22 -
ύϥϨϧॲཧʹ͍ͭͯ • Parallel ͱ͍͏Ωʔϫʔυ͕ॲཧ ϊʔυͷલʹͭ͘ɻ • ύϥϨϧΫΤϦ ͱ͍͏PostgreSQLͷ ػೳɻ •
9.6 ʙ αϙʔτ͕ঃʑʹ૿͖͑ͯͯ ͍Δɻ 23 -
ओͳεΩϟϯܥॲཧϊʔυ ॲཧϊʔυ ྨ Seq Scan εΩϟϯ Index Scan εΩϟϯ Index
Only Scan εΩϟϯ Bitmap Index Scan εΩϟϯ 24 -
εΩϟϯॲཧϊʔυͷҧ͍ Seq Scan • ςʔϒϧͷશ݅Λॱ൪ʹݺͼग़͍ͯ͘͠ɻ • IndexΛ͏·Ͱແ͍গͷϨίʔυͷ߹͍͕ɺϨίʔυ͕૿͑ΔʹͭΕ ͯ͘ͳΔՄೳੑ͕͋Δɻ Index Scan
• IndexͰରσʔλʹ͋ͨΓΛ͚ͨޙͰରͷσʔλΛݺͼग़͍ͯ͘͠ɻ • IndexͰेʹߜΓࠐΊΔ߹͍ɻͨͩ͠ɺҋӢʹIndexΛషΔͱΠϯσοΫ εɾςʔϒϧͷߏஙʹίετֻ͕͔Δɻ 25 -
εΩϟϯॲཧϊʔυͷҧ͍ Index Only Scan • IndexͷσʔλͷΈΛݺͼग़͍ͯ͘͠ɻ • Indexͷσʔλ͔͠ඞཁ͡Όͳ͍߹͍ɻ Bitmap Index
Scan • IndexΛͬͯ݅ʹ߹க͢ΔߦΛϏοτϚοϓͱͯ͠ϝϞϦʹੜ͠ɺͦͷ ϏοτONͷͷ͚ͩΛॱ൪ʹݺͼग़͍ͯ͘͠ɻ • ϏοτԋࢉʹԠ༻ग़དྷΔ͠ɺ2ͷɺยํͷΈΛൺֱ͢Ε͍͍͔Β͍ɻ ͨͩ͠ɺBitmapΛߏங͢Δॳظίετֻ͕͔Δɻ 26 -
݁߹ϊʔυͷҧ͍ Nested Loop Join • ۦಈද(JOINݩ)Λ1ߦຖʹ ෦ද(JOINઌ)ͷશϨίʔυͱ ಥ͖߹Θͤͯ֘ͷͷΛ݁߹͍ͯ͠ ͘ɻ •
େ͖͍ςʔϒϧͱେ͖͍ςʔϒϧΛ݁߹ ͢Δͱn × m ճॲཧΛ͠ͳ͚Ε͍͚ͳ ͍ͷͰ͍ɻ • ۦಈද͕খ͘͞෦දͷ݁߹ΩʔʹIndex ͕͋Δ߹͍ɻ 28 -
݁߹ϊʔυͷҧ͍ Hash Join • ෦දͷ݁߹Ωʔͷ ϋογϡϦετΛ࡞ͯ͠ ͦͷޙɺۦಈදͱ݁߹Λߦ͏ɻ • ϋογϡϦετ͕ϝϞϦʹऩ·Δ ߹ඇৗʹ͍ɻ
• ༧ΊϋογϡϦετΛ࡞͢Δඞཁ ͕͋ΔͷͰͦͷॳظίετֻ͕͔ Δɻ 29 -
݁߹ϊʔυͷҧ͍ Merge Join • ۦಈදͱ෦දΛ݁߹ΩʔͰ ιʔτͨ͠ޙͰ ॱ൪ʹ͖߹Θͤͯ݁߹͍ͯ͘͠ɻ • େ͖͍ςʔϒϧ ×
େ͖͍ςʔϒϧͰ ߴͰ݁߹ग़དྷΔɻ • ͨͩ͠ࣄલʹͲͪΒͷςʔϒϧ ιʔτ͢Δඞཁ͕͋ΔͷͰ ॳظίετֻ͕͔Δɻ 30 -
վળͷ࣮ྫ(Indexߏஙલ) EXPLAIN ANALYZE SELECT * FROM customers WHERE name =
'johnson51'; QUERY PLAN ----------------------------------------------------------------------- Seq Scan on customers (cost=0.00..12089.00 rows=3 width=110) (actual time=1.066..60.822 rows=2 loops=1) Filter: ((name)::text = 'johnson51'::text) Rows Removed by Filter: 399998 Planning Time: 0.649 ms Execution Time: 60.905 ms 35 -
Index࡞ͷޭཁҼ • customersςʔϒϧͷσʔλྔαʔϏεͷͱڞʹ૿͑ ͍ͯ͘ɻ => Seq Scanͷ͕αʔϏεͷʹ࿈Ε͘ͳΓͦ͏ɻ • Ϣʔβʔ໊ϢχʔΫͱ͍͏റΓΛαʔϏεͰઃ͚͍ͯΔɻ =>
σʔλͷภΓ͕গͳ͍ͱஅग़དྷΔɻ ʮԿނIndexΛషΔ͔ ΊͬͪΌେࣄ!! ʯ 36 -
Indexͷఆٛ CREATE INDEX customers_name_idx ON customers (name); • customers ςʔϒϧͷ
name ΧϥϜʹ customers_name_idx ͱ͍͏໊લʢศ্ٓʣͷ IndexΛఆٛ͢Δ 37 -
վળͷ࣮ྫ(Indexߏஙޙ) EXPLAIN ANALYZE SELECT * FROM customers WHERE name =
'johnson51'; QUERY PLAN ---------------------------------------------------------------------------------- Bitmap Heap Scan on customers (cost=4.45..16.30 rows=3 width=110) (actual time=0.084..0.120 rows=2 loops=1) Recheck Cond: ((name)::text = 'johnson51'::text) Heap Blocks: exact=2 -> Bitmap Index Scan on customers_name_idx (cost=0.00..4.45 rows=3 width=0) (actual time=0.056..0.065 rows=2 loops=1) Index Cond: ((name)::text = 'johnson51'::text) Planning Time: 0.345 ms Execution Time: 0.226 ms 38 -
վળͷલޙͷൺֱ ߲ Indexߏஙલ Indexߏஙޙ εΩϟϯํ๏ Seq Scan Bitmap Heap Scan
/ Bitmap Index Scan ࣮ߦ࣌ؒ 60.905 ms 0.226 ms 39 -
PostgrteSQLͰͷIndexͷαϙʔτͷ͝հ • ࣜʹର͢ΔIndexͷఆٛ ྫɿ LOWER(user_name) ͱ͍͏ࣜʹରͯ͠IndexΛఆٛ • ෦తʹIndexΛఆٛ ྫɿ delete_flag=0ͷ݅ͷͱͰ
ɹɹ email ʹ Unique IndexΛఆٛ 41 -
Viewͷྫ • ʮ2019ͷ20உੑͷച্σʔλΛऔಘ͢Δʯ SELECT * FROM sales WHERE created_at BETWEEN
'2019-01-01' AND '2019-12-31' AND gender = 1 AND age BETWEEN 20 AND 29; ্هΛ male_sales_twenty_2019 ͱ͍͏Viewͱͯ͠ఆٛ͢ΔࣄͰ SELECT * FROM male_sales_twenty_2019; ͜ͷΑ͏ʹγϯϓϧͳSQLͱͯ͠ද͢ࣄ͕Ͱ͖Δɻ 44 -
Viewʹ͍ͭͯ • male_sales_twenty_2019 σʔλͷ࣮ମΛ࣋ͨͳ͍ɻ • View ʹରͯ͠ SELECTจΛ࣮ߦ͢Δͱ ৗʹSQL͍߹Θ͕࣮ͤߦ͞ΕΔɻ ↓
͜͜Ͱ Materialized View 45 -
Materialized View ʹ͍ͭͯ • Materialized View SQLͷ݁ՌΛอ࣋͢Δɻ • Materialized View
ʹࢀরͷΫΤϦΛྲྀ͢ͱ อ͍࣋ͯ͠ΔSQLͷ݁ՌΛฦ٫͢Δɻ • ௨ৗͷ View ͱҟͳΓৗʹSQL͕ൃߦ͞ΕΔࣄ͕ͳ͘ͳΔɻ • ·ͨɺอ࣋ใʹରͯ͠IndexΛఆٛ͢ΔࣄՄೳɻ 46 -
Materialized View ʹ͍ͭͯ • REFRESH Λ͢ΔࣄͰอ͍࣋ͯ͠ΔSQLͷ݁ՌΛ ࠶ߋ৽͢Δ͜ͱ͕ग़དྷΔɻ • ݱঢ়Ͱ ࠩߋ৽ରԠ͍ͯ͠ͳͯ͘
ϦϑϨογϡͷλΠϛϯάͰSQL͕ϑϧͰ࠶࣮ߦ͞ΕΔɻ ※ ViewͱҟͳΓ ࢀরͷʹSQL͕ൃੜ͢Δ͜ͱͳ͍͕ ϦϑϨογϡ͢Δ·Ͱ࠷৽ͷσʔλ͕ө͞Εͳ͍ɻ 47 -
վળલͷSQL • ʹରͯ͠ಛूΛΉɻ • feature ςʔϒϧͷதʹ ݕࡧจࣈྻ ͕֨ೲ͞Ε͍ͯͯ ͦΕΛ༻ͯ͠σʔλΛߜΓࠐΈΛߦ͏ɻ •
Πϝʔδͱͯ͠ /item/feature/1 ͷURLΛ։͘ͱ ݕࡧจࣈྻΛݕࡧAPIʹPostͯ݁͠ՌΛऔಘ͠ ͦͷಛूҰཡ͕දࣔ͞ΕΔɻ 48 -
վળલͷSQL • ྫ͑ɺ id = 1 ʢ PostgreSQL ʣͷಛूͷσʔλΛऔಘ͢Δʹ ҎԼͷΑ͏ͳSQL͕ྲྀΕΔ.
SELECT * FROM available_item INNER JOIN item_detail ON available_item.item_id = item_detail.item_id WHERE ( available_item.item_name LIKE '%PostgreSQL%' OR item_detail.article LIKE '%PostgreSQL%' OR item_detail.requirement_text LIKE '%PostgreSQL%' ); ※ available_item ɿ ར༻ՄೳͳϦετ ※ item_detail : ͷৄࡉใ 49 -
վળલͷ࣮ߦܭը (cost, actual_time ল͍ͨ) QUERY PLAN -------------------------------------------------------------------------------- Merge Join Merge
Cond: (available_item.item_id = item_detail.item_id) Join Filter: ( ((available_item.title)::text ~~ '%PostgreSQL%'::text) OR (item_detail.article ~~ '%PostgreSQL%'::text) OR ((item_detail.requirement_man_text)::text ~~ '%PostgreSQL%'::text) ) Rows Removed by Join Filter: 121547 -> Index Scan using available_item_item_id_key on available_item -> Index Scan using item_detail_item_id_key on item_detail Planning time: 13.394 ms Execution time: 6635.537 ms 50 -
SQLͰٻΊΒΕΔཁ݅ • LIKE͕தؒҰகɺarticleશจݕࡧͳͷͰ Index͕࡞Γʹ͍͘ • ࡏݿྉۚঢ়ଶৗʹ࠷৽Λදࣔ͢Δඞཁ͕͋Δ => HTMLΩϟογϡͷ࣮ͱ૬ੑ͕ѱ͍ • ͱ͍͑ɺ໊આ໌͕มΘΔࣄ͋·Γແ͍ͷͰ
ʮରʯ͕มΘΔࣄͦ͜·Ͱଟ͘ͳ͍ɻ ͜͜Ͱ ಛूͷର ͷϚςϏϡʔΛ࡞ͬͯߴԽɻ 51 -
ϚςϏϡʔͷ࣮ CREATE MATERIALIZED VIEW view_feature_available_item AS WITH target_item AS (
SELECT available_item.item_id, available_item.title, item_detail.requirement_text, item_detail.article FROM available_item INNER JOIN item_detail ON available_item.item_id = item_detail.item_id ) SELECT target_item.item_id , feature.id AS feature_id FROM feature INNER JOIN target_item ON ( target_item.title LIKE CONCAT('%', display_condition_query ,'%') OR target_item.requirement_text LIKE CONCAT('%', display_condition_query ,'%') OR target_item.article LIKE CONCAT('%', display_condition_query ,'%') ); CREATE INDEX view_feature_available_item_item_id_key ON view_feature_available_item (item_id); CREATE INDEX view_feature_available_item_feature_id_key ON view_feature_available_item (feature_id) 52 -
վળޙͷSQL EXPLAIN ANALYZE SELECT * FROM available_item INNER JOIN view_feature_available_item
ON available_item.item_id = view_feature_available_item.item_id WHERE view_feature_available_item.feature_id = 1; • feature.id = 1 ͕ PostgreSQL ͷಛू. 53 -
վળޙͷ࣮ߦܭը (cost, actual_time ল͍ͨ) QUERY PLAN ----------------------------------------------------------------------------------------- Nested Loop ->
Hash Join Hash Cond: (available_item.item_id = view_feature_available_item.item_id) -> Seq Scan on available_item -> Hash Buckets: 16384 Batches: 1 Memory Usage: 592kB -> Bitmap Heap Scan on view_feature_available_item Recheck Cond: (feature_id = 1) Heap Blocks: exact=628 -> Bitmap Index Scan on view_feature_available_item_feature_id_key Index Cond: (feature_id = 1) -> Index Scan using item_detail_item_id_key on item_detail Index Cond: (item_id = available_item.item_id) Planning time: 9.376 ms Execution time: 3424.800 ms 54 -
ࢀߟจݙ • Explaining EXPLAIN ୈ̎ճ • PostgreSQLΫΤϦ࣮ߦͷجૅࣝ ʙExplainΛಡΈղ͜͏ʙ • ࠷͔ͭ࠷ʹΞΫηε͢ΔʮDBߴԽٕज़ʯʢલฤʣ
• PostgreSQL Conference Japan 2018 ʲT3ʳSQL νϡʔχϯ άͷجૅ • PostgreSQL 11.5 จॻ 59