2019-01-20 の 岡山モバイルアプリ開発もくもく会 での発表資料です。
ΞϓϦͷ࣮ʹཱͭPostgreSQL2019-01-20 ୈ30ճϞόΠϧΞϓϦ։ൃ͘͘ձ
View Slide
͓͠ͳ͕͖1.͋͏ͱΈʔ2.PostgreSQLͷྻܕ3.·ͱΊ
1. ͋͏ͱΈʔ
1. ͋͏ͱΈʔ• ߴڮ Ұٍ• ΦϛΧϨ WebΤϯδχΞ• ຊPostgreSQLϢʔβʔձதࠃࢧ෦
גࣜձࣾΦϛΧϨશࠃ35,000݅ͷࠗ׆ύʔςΟΛܝࡌͨ͠ϙʔλϧαΠτ30ສਓڧͷձһͷํʑʹ͝ར༻͍͍ͯ·͢ɻࠗ׆ͱݴ͑ΦϛΧϨʂ\\͍͍݁ࠗͧʂʂ//
2. PostgreSQLͷྻܕ
ׂͱ͋Γ͕ͪͳΞϓϦͷཁ݅ಥવͰ͕͢ɺօ͞Μ͜ΕɺͲ͏࣮ͬͯ͠·͢ʁ
λάׂ࣮ͱਏ͗͢• ొ؆୯ɻ• ฤूͱݕࡧ͕ࠈɻ
1ΧϥϜͷςΩετܕʹcsv۠ΓͰೖΕΔߋ৽ਏ͍ɻݕࡧਏ͍ɻ (δΣΠΥʔΫ)tag1, tag2, tag3,... ͬͯΔɻtagΛ3͔ͭ͠αϙʔτ͠ͳ͍ʁ
ͦ͜ͰྻܕͰ͢ΑɻCREATE TABLE blogs (title varchar(64),content text,tags varchar(16)[])
ྻܕͷྫINSERT INTO blogs(title, content, tags)VALUES('͘͘ձ', '໌͘͘ձͰ͢', '{ "ios", "android" , "PostgreSQL" }')title | contents | tags------------+----------------------+--------------------------͘͘ձ | ໌͘͘ձͰ͢ | {ios,android,PostgreSQL}(1 row)
ྻܕͷϝϦοτ• ΠϯσοΫε͕ޮ͘ɻ• ݕࡧΛ͢Δҝͷԋࢉࢠϝιουͷαϙʔτ͕๛ɻ• औಘޙ֤ݴޠͷྻͱͯ͠༻Ͱ͖Δ ( Djangoͱ͔ͦ͏. )
ྻܕͷݕࡧʹ͍ͭͯ'ios' λά͕͋ΔͷΛݕࡧ.SELECT * FROM blogs WHERE 'ios' = ANY(tags);title | contents | tags------------+----------------------+--------------------------͘͘ձ | ໌͘͘ձͰ͢ | {ios,android,PostgreSQL}(1 row)ios λά ͕͍ͭͯΔ ͔ͭ android λά͕͍ͭͯΔΈ͍ͨͳݕࡧͰ͖Δɻ
ྻܕͷऔಘʹ͍ͭͯSELECT tags[1] AS tag FROM blogs;tag----------ios(1 row)ͦͷ΄͔ʹtags[2:3] : ྻͷ 2ʙ3൪Λऔಘtags[2:] : ྻͷ 2൪Ҏ߱Λऔಘtags[:2] : ྻͷ 2൪ҎԼΛऔಘ※ 0͔Β։࢝͠ͳ͍ࣄʹதࢭ
ͦͷଞʹָʹཁ݅ΛຬͨͤΔSELECT unnest(tags), count(*)FROM blogsGROUP BY unnest(tags)ORDER BY count(*) DESC;unnest | count------------+-------database | 2ios | 1PostgreSQL | 1android | 1(4 rows)
ྻܕͷσϝϦοτ• ֎෦Ωʔ੍͕షΕͳ͍ɻ=> ͷ߹ੑΛอূ͢Δͷ͕͍͠ɻ=> λάݕࡧͳͲͷϑϦʔςΩετͷσʔλΛอ࣋͢Δͷʹద͍ͯ͠Δɻ
3. ·ͱΊ
3. ·ͱΊࠓճɺPostgreSQLͷྻܕͷհΛ͠·ͨ͠ɻͱͯڧྗͳΈͰ͕ͪ͢ΖΜϝϦοτɾσϝϦοτ͕͋Γɺඞͣۜ͠ͷؙͱ͍͏Θ͚Ͱͳ͍Ͱ͢ɻͳͷͰɺunnest() array_agg() Λͬͯߦ <=> ྻؒΛ૬ޓม͢ΔͳͲɺϝϦοτɾσϝϦοτΛڗडͰ͖ΔΑ͏ͳܗͰ͍ͬͯ͘ͷ͕ྑ͍͔ͱࢥ͍·͢ʂ
4. ࠂதࠃํDBษڧձ2019-02-02 () ୈ26ճ தࠃํDBษڧձ in Ԭࢁhttps://dbstudychugoku.connpass.com/event/112468/SQLνϡʔχϯά(EXPLAIN)ͷํ๏ύϥϝʔλνϡʔχϯάΛऔΓ্͛·͢ɻઈࢍLTൃදऀΛืूதͰ͢ʂʂ
͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ