DMSを使った異種DB移行で学んだPostgreSQLの面白い所/chugokudb27-postgresql-is-interesting

by Takahashi Ikki

Slide 1

Slide 1 text

2019-05-18 ୈ27ճ தࠃ஍ํDBษڧձ DMSΛ࢖ͬͨҟछDBҠߦͰֶΜͩ PostgreSQLͷ͓΋͠Ζ͍ॴ ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํࢧ෦௕ ߴڮ Ұٍ 1

Slide 2

Slide 2 text

͓͠ͳ͕͖ 1. ࣗݾ঺հ 2. DMSΛ؆୯ʹ঺հ 3. DMSΛ࢖ͬͨDBϦϑΝΫλϦϯά 4. PostgreSQLͷ͓΋͠Ζ͍ॴ 5. ·ͱΊ 2

Slide 3

Slide 3 text

஫ҙࣄ߲ • εϥΠυ͸ެ։͠·͢ɻ • ࣭໰ͱ͔͝ࢦఠͱ͔ ॾʑͷϑΟʔυόοΫ͕͍͟͝·ͨ͠Β #ChugokuDB ΁ ͓ئ͍க͠·͢ɻ 3

Slide 4

Slide 4 text

1. ࣗݾ঺հ • ߴڮ Ұٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ όοΫΤϯυΤϯδχΞ • ೔ຊPostgreSQLϢʔβʔձ தࠃࢧ෦௕ 4

Slide 5

Slide 5 text

2019/02/24 ύύʹͳΓ·ͨ͠ʂ 5

Slide 6

Slide 6 text

גࣜձࣾΦϛΧϨ શࠃ30,000݅ͷࠗ׆ύʔςΟΛ ܝࡌͨ͠ϙʔλϧαΠτ 35ສਓڧͷձһͷํʑʹ͝ར༻௖͍ ͍ͯ·͢ɻ \\ࠗ׆ͱݴ͑͹ΦϛΧϨʂ// ग़ձ͍ΛٻΊ͍ͯΔํͷ ҰॿͱͳΕΕ͹ʂʂ 6

Slide 7

Slide 7 text

؇࿨ٳ୊ 7

Slide 8

Slide 8 text

ࠓ೔ͷ͓࿩ • ΦϛΧϨʹͯDBϦϑΝΫλϦϯάΛ΍ͬͯΔɻ • ͦΕʹ͸DMSͱ͍͏AWSͷαʔϏεΛ׆༻͍ͯ͠Δɻ • DMSͬͯԿʁ • ͲΜͳ෩ʹ΍ͬͯΔͷʁ • MySQL͔͠΍ͬͯແ͔ͬͨ๻͕PostgreSQLΛ৮ͬͯΈͯ ͜Μͳࣄग़དྷΔͷ͔ɺͱࢥͬͨࣄΛ঺հ ʮ໘ന͍ͱ͜Ζʢখฒײʣʯ 8

Slide 9

Slide 9 text

ͪΐͬͱখ࿩ 9

Slide 10

Slide 10 text

ͳͥɺDBϦϑΝΫλϦϯάΛ΍Δͷ͔ 10

Slide 11

Slide 11 text

ΦϛΧϨʹ͍ͭͯ • αʔϏε։͔࢝Β໿8೥͕ܦա͍ͯ͠Δɻ • ͜͜1೥Ͱ ։ൃਓһ͕ࣾһ1໊ + ֎஫ ͷମ੍͔Β ։ൃࣾһ9໊ ͷମ੍ʹมֵɻ • ৭Μͳେਓͷࣄ৘͔Β͍ΘΏΔٕज़తෛ࠴ͱ ݺ͹ΕΔ΋ͷ͕ͦΕͳΓʹ͋Δɻ 11

Slide 12

Slide 12 text

ྫ͑͹ɾɾ ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ 12

Slide 13

Slide 13 text

ྫ͑͹ɾɾ ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ ʮ͜ͷςʔϒϧԿʹ࢖ͬͯΔ͔୭͔஌ͬͯΔʁʯ 13

Slide 14

Slide 14 text

ྫ͑͹ɾɾ ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ ʮ͜ͷςʔϒϧԿʹ࢖ͬͯΔ͔୭͔஌ͬͯΔʁʯ ʮʮʮ͍΍͊ɻɻɻ ! ʁʯʯʯ 14

Slide 15

Slide 15 text

ྫ͑͹ɾɾ ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ ʮ͜ͷςʔϒϧԿʹ࢖ͬͯΔ͔୭͔஌ͬͯΔʁʯ ʮʮʮ͍΍͊ɻɻɻ ! ʁʯʯʯ ୭΋஌Βͳ͍ςʔϒϧͷڪා... 15

Slide 16

Slide 16 text

gender ΧϥϜ ૝ఆ ࣮ࡍ ܕ int ೖΔσʔλ 0, 1, 2 σʔλͷҙຯ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 16

Slide 17

Slide 17 text

gender ΧϥϜ ૝ఆ ࣮ࡍ ܕ int ೖΔσʔλ 0, 1, 2 σʔλͷҙຯ 0: ະఆٛ, 1: உੑ, 2: ঁੑ PostgreSQLʮ!!cannot convert int!!ʯ 17

Slide 18

Slide 18 text

gender ΧϥϜ ૝ఆ ࣮ࡍ ܕ int varchar ೖΔσʔλ 0, 1, 2 0, 1, 2, உੑ, ঁੑ σʔλͷҙຯ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 0: ະఆٛ, 1: உੑ, 2: ঁੑ 18

Slide 19

Slide 19 text

ݱঢ় • ҋRDBΞϯνύλʔϯ͕ͲΜͲΜ૿͍͑ͯ͘ɻ • ͱ͸͍͑ݶΒΕͨϦιʔεΛશͯϦϑΝΫλϦϯάʹ ׂ͘ࣄ͸ग़དྷͳ͍ɻ • ͳΔ΂͘ແఀࢭͰ͔ͭԁ׈ʹ։ൃΛਐΊΔͨΊʹ ฐࣾCTOͦʔ͍ͩ(@soudai1025)͞Μ͕ ؤுͬͯߟ͑ͨɻ 19

Slide 20

Slide 20 text

2. DMS͕ͲΜͳ΋ͷ͔؆୯ʹ͝঺հ 20

Slide 21

Slide 21 text

AWS Database Migration ωοτϫʔΫӽ͠ʹ σʔλҠߦ͕ग़དྷΔαʔϏε ಄จࣈΛऔͬͯɺDMSͱݺ͹ΕΔɻ Oracle→Oracle͸΋ͪΖΜͷࣄ SQL Server→Amazon Auroraͳ ͲͷҟछDBؒͷҠߦ΋αϙʔτ͞ ΕΔɻ 21

Slide 22

Slide 22 text

AWS Database Migration Serviceͷ͝঺հ • AWS DMS ͷߏ੒ཁૉ • ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ • λεΫͷৼΔ෣͍ʹ͍ͭͯ • λεΫͷઃఆํ๏ʹ͍ͭͯ 22

Slide 23

Slide 23 text

AWS DMS ͷߏ੒ཁૉ • ιʔεΤϯυϙΠϯτ • σʔλͷίϐʔݩɻEC2onDB, RDS, ΦϯϓϨDB, S3 ͳͲɻ • λʔήοτΤϯυϙΠϯτ • σʔλͷίϐʔઌɻEC2onDB, RDS, ΦϯϓϨDB, S3, DynamoDB ͳͲɻ • ϨϓϦέʔγϣϯΠϯελϯε • ίϐʔݩ͔Βίϐʔઌ΁σʔλͷϨϓϦέʔγϣϯΛߦ͏Πϯελϯεɻ • λεΫ • ͲͷςʔϒϧΛͲͷΑ͏ʹҠߦ͢Δ͔ΛఆΊͨϧʔϧɻ େମ͜Ε͚͓͚֮ͩ͑ͯ͹׬શʹཧղग़དྷΔ 23

Slide 24

Slide 24 text

ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ • ΠϯελϯεΫϥε͸ T2, C4, R4ͷ̏छྨΛαϙʔτ. • T2: ։ൃɾݕূ༻ • C4: େྔͷԋࢉΛߦ͏৔߹ʹ໾ཱͭɻ (AWSతʹ͸ ҟछDBҠߦ͸͜ΕΛ͢͢Ί͍ͯΔ) • R4: ϝϞϦ૿ՃΠϯελϯεɻ (ҰճͷτϥϯβΫγϣϯ͕େ͖͍࣌͸͜Ε.) • ϚϧνAZʹରԠ͍ͯͯ͠ɺϑΣΠϧΦʔόʔΛαϙʔτ. • ࣮ࡍɺDMSͱ͓͕͔͔ͯۚ͠Δͷ͸͜ͷΠϯελϯεར༻ྉͱετϨʔδ͚ͩɻ ʢ※ॾઆʣ 24

Slide 25

Slide 25 text

λεΫͷৼΔ෣͍ͷछྨʹ͍ͭͯ λεΫͷৼΔ෣͍͸ओʹ3λΠϓ͋Δɻ • શϩʔυ+ܧଓతͳϨϓϦέʔγϣϯ(CDC). • ΦϛΧϨͰར༻͍ͯ͠ΔλΠϓɻ • طଘͷσʔλΛϑϧϩʔυͨ͠ޙ(PostgreSQLͩͱCOPYจͰߦ͏) ͦͷޙ͸τϥϯβΫγϣϯϩά͔ΒܧଓతʹϨϓϦέʔγϣϯ • ܧଓతͳϨϓϦέʔγϣϯ(CDC)ͷΈ • શϩʔυͷΈ 25

Slide 26

Slide 26 text

λεΫͷઃఆํ๏ʹ͍ͭͯ • ϨϓϦέʔγϣϯΛߦ͏ςʔϒϧͷࢦఆํ๏͸̎छྨ • userɿ จࣈྻҰகͰ user ͷΈΛର৅ͱ͢Δɻ • user_%ɿ ਖ਼نදݱͰ user_xxx ʹ߹க͢Δ શͯͷςʔϒϧΛର৅ͱ͢Δɻ 26

Slide 27

Slide 27 text

λεΫͷઃఆํ๏ʹ͍ͭͯ • ·ͨɺ֤ςʔϒϧʹରͯ͠ҠߦϧʔϧΛઃఆग़དྷΔɻ • ྫ͑͹Ҡߦޙͷςʔϒϧ໊/ΧϥϜ໊Λมߋͨ͠Γ ର৅ͷσʔλΛWHERE۟ͷΑ͏ʹϧʔϧͰ ߜΓࠐΜͩΓग़དྷΔɻ • ଞʹ΋ςʔϒϧͷҠߦઌͷεΩʔϚ໊ͷࢦఆͳͲɻ 27

Slide 28

Slide 28 text

ͦͷଞDMSͷओͳ׆༻ࣄྫ • ΦϯϓϨͷDB͔ΒAWSͷRDS΁σʔλΛҠߦ͢Δɻ • ͔ͷߴڮগ೥͸ɾɾɾ Ҏલਂ໷ϑϧόοΫΞοϓΛऔͬͯAWSʹ సૹͯ͠෮ݩ͢Δ࡞ۀΛ͍ͯͨ͠ • Lambda+RDS͸ಉ࣌઀ଓͷ໰୊͔Β ਪ঑͞Ε͍ͯͳ͍ͷͰ RDS → DynamoDB ʹܧଓతʹϨϓϦέʔγϣϯΛͯ͠ Lambdaઐ༻ͷDynamoDBΛ࡞Δɻ 28

Slide 29

Slide 29 text

ͦͷଞDMSͷओͳ׆༻ࣄྫ • લʑճͷதࠃ஍ํDBษڧձͰ PostgreSQLͷ όʔδϣϯΞοϓΛDMSͰ ؤுΔΈ͍ͨͳൃදΛͨ͠ • https://speakerdeck.com/ takahashiikki/chugokudb-25- session1 29

Slide 30

Slide 30 text

2. DMS͕ͲΜͳ΋ͷ͔؆୯ʹ͝঺հ ·ͱΊ • DMS͸ωοτϫʔΫӽ͠ʹϨϓϦέʔγϣϯΛ࣮ݱ͢ΔαʔϏε. • σʔλͷίϐʔݩͱίϐʔઌΛࢦఆͯ͠ ҠߦϧʔϧΛ࡞੒͢ΔࣄͰσʔλͷҠߦ͸ શͯDMSͷ੹຿Ͱ࣮ݱͯ͘͠ΕΔɻ • DMS୯ମͱͯ͠͸ ϨϓϦέʔγϣϯΠϯελϯεͷӡ༻අ͕͔͔Δఔ౓Ͱ ҆ՁͰਐΊΔࣄ͕ग़དྷΔɻ 30

Slide 31

Slide 31 text

3. DMSΛ࢖ͬͨDBϦϑΝΫλϦϯά 31

Slide 32

Slide 32 text

ϦϑΝΫλϦϯάख๏ 32

Slide 33

Slide 33 text

ݱঢ়ͷΞʔΩςΫνϟ ෳ਺ͷαʔϏε͕ӡ༻͞Ε͓ͯΓ୯ҰͷMySQLͷhogeεΩʔϚ͔ΒσʔλΛࢀর͍ͯ͠Δɻ 33

Slide 34

Slide 34 text

WebͷϦϑΝΫλϦϯά APIαʔϏεΛ৽͘͠࡞Γɺ ֤ػೳΛݸผͷAPIͱͯ͠࡞੒ ྫ͑͹ • ΦϛΧϨձһొ࿥/ࢀরAPI • ࠗ׆ύʔςΟΛݕࡧ͢Δػೳ • ࠗ׆ύʔςΟͷ༧໿͢ΔAPI 34

Slide 35

Slide 35 text

DBͷϦϑΝΫλϦϯά • ϦϑΝΫλϦϯάͨ͠ޙͷDBΛ༻ҙ͠ DMSΛͰϨϓϦέʔγϣϯ͢Δɻ • ϨϓϦέʔγϣϯ͞ΕͨλΠϛϯάͰ DBͷτϦΨʔΛൃಈͤ͞Δ • AuroraͷҠߦઌΛPostgreSQLʹͨ͠ͷ ͸ PostgreSQLͷํ͕τϦΨʔ͕ॊೈͳҝ • τϦΨʔ಺ͰچσʔλΛՃ޻͠ ϦϑΝΫλϦϯάͨ͠ςʔϒϧͷ ܗࣜʹ߹͏Α͏ʹొ࿥͢Δɻ 35

Slide 36

Slide 36 text

ϦϑΝΫλϦϯά • Web͸গͮͭ͠APIܦ༝Ͱ ࢀরɾॻࠐ͢ΔΑ͏ʹมߋΛ ਐΊΔɻ (WebͷϦϑΝΫλϦϯά) • API͸ॻࠐ͸MySQLଆ΁ߦ͍ɺ ಡࠐ͸PostgreSQLଆ͔Βߦ͏ɻ (DBͷϦϑΝΫλϦϯά) • WebଆΛશͯAPIܦ༝ʹग़དྷͨΒ APIͷॻࠐ΋PostgreSQLଆ΁มߋ͢ Δɻ 36

Slide 37

Slide 37 text

Slide 38

Slide 38 text

͜ͷϦϑΝΫλϦϯάํ๏ͷϝϦοτ • ߋ৽API͸MySQL΁ॻࠐΛ͢ΔͷͰগͮͭ͠APIܦ༝ʹͯ͠΋ طଘαʔϏεͷςʔϒϧͷࢀরՕॴʹӨڹ͕ແ͍ɻ • ҰՕॴAPIܦ༝ʹͯ͠ΈΔ → ໰୊͕͋Ε͹ௐ੔͢Δɻˠ ... ͳͲͷΑ͏ʹ ΧφϦΞϦϦʔε ܗࣜͰ গͮͭ͠վળΛਐΊ͍͚ͯΔɻ • σʔλͷಡࠐ͸PostgreSQL͔Βߦ͏ͷͰ ॻࠐAPIΛݺͿଆ͸ͲͪΒʹॻ͖ࠐΜͰ͍Δ͔Λ ؾʹ͠ͳͯ͘΋ྑ͍ɻ 38

Slide 39

Slide 39 text

͜ͷϦϑΝΫλϦϯάํ๏ͷσϝϦοτ • ϩʔΧϧͷ։ൃ؀ڥΛ࡞੒͢Δࣄ͕ग़དྷͳ͍ɻ ※ ຊ౰ਏ͍ɻ͍͍ײ͡ͷແ͍Ͱ͔͢Ͷɾɾʁ • DMS͕ࢭ·ͬͨ࣌ʹࢀরAPI͕ίέΔɻ ※ τϦΨʔΤϥʔ౳ͰDMS͕ࢭ·ΔͱαʔϏε͕ࢭ·Δɻ • DMS͕஗Ԇͨ࣌͠ͷߟྀ͕೉͍͠ɻ ※ ࠓͷॴɺக໋ই͸ͳ͍ɻ(ই͕ແ͍ͱ͸ݴͬͯͳ͍) • ίϯϑϦΫτ͢Δࣄ͕ݒ೦͞ΕΔͷͰϦʔυϨϓϦΧʹ·ͰτϦΨʔΛషΕͳ͍͕ ϦʔυϨϓϦΧ͕Ϛελʔʹঢ֨͢ΔͱαʔϏε͕ࢭ·Δɻ • ͦͷଞ৭Μͳҋ͸࠙਌ձͰʂ Suddenly Kigenzen ͱ͔ God Unknown Datetime ͱ͔ɻ 39

Slide 40

Slide 40 text

3. DMSΛ࢖ͬͨDBϦϑΝΫλϦϯά ·ͱΊ • AWS DMSͰ MySQL͔ΒPostgreSQLʹ ϨϓϦέʔγϣϯ͍ͯ͠Δɻ • DMS͕Ҡߦͨ͠λΠϛϯάͰઃఆͨ͠τϦΨʔ͕ൃಈͯ͠ ϦϑΝΫλϦϯά͞ΕͨςʔϒϧʹॻࠐΛ͢Δɻ • ߋ৽ɾࢀরΛAPIܦ༝ʹ͢ΔࣄͰطଘͷ։ൃΛਐΊͭͭ ϦϑΝΫλϦϯάͨ͠DBʹࢀরΛ੾Γସ͍͚͍͑ͯͯΔɻ • ϩʔΧϧͷ؀ڥߏங͕೉͍͠ͷͱɺDMS͕ࢭ·ͬͨ࣌ʹ ࢀরAPI͕ίέΔͷͰߟྀ͕೉͍͠ɻ 40

Slide 41

Slide 41 text

4. PostgreSQLͷ͓΋͠Ζ͍ॴ 41

Slide 42

Slide 42 text

JPUGதࠃࢧ෦௕ͷδϯΫε • தࠃࢧ෦௕͸ۀ຿ͰPostgtrSQLΛ࢖͑ͳ͍ • લ৬͸ SQLServerɾMySQL ͩͬͨɻ • ۤઅ5೥ɾɾPostgreSQLΛॳΊͯۀ຿Ͱ৮ͬͨࣗ෼͕ײͨ͡ PostgreSQLͷ໘ന͍ॴɺ໾ཱͬͨॴΛ͝঺հ ※ APIΛDjangoͰॻ͍ͯΔͷͰ DjangoͷDatasetͷίϯςΩετ͕ଟগؚ·Ε·͢ɻ ※ ΞϓϦέʔγϣϯΤϯδχΞ͔Βͷࢹ఺͕ ଟ෼ʹؚ·Ε·͢ɻ 42

Slide 43

Slide 43 text

PostgreSQLΛ࢖༻͢Δ͚ͩͰ ಘΒΕΔ࢓૊Έ 43

Slide 44

Slide 44 text

PostgreSQLΛ࢖༻͢Δ͚ͩͰಘΒΕΔ࢓૊Έ JOINͷΞϧΰϦζϜ MySQLͱPostgreSQLͰ࠾༻͞Ε͍ͯΔ ݁߹ॲཧͷΞϧΰϦζϜʹҧ͍͕͋ΔҟͳΔɻ MySQLͰ͸ Nested Loop JOINͷΈ͚ͩͲɺ PostgreSQLͰ͸ Nested Loop JOINɺHash JOINɺMerge JOINͷ ࡾछྨ͕αϙʔτ͞Ε͍ͯΔɻ 44

Slide 45

Slide 45 text

݁߹ϊʔυͷҧ͍ Nested Loop Join • ۦಈද(JOINݩ)Λ1ߦຖʹ಺෦ද (JOINઌ)ͷ શϨίʔυͱ෇͖߹Θͤͯ֘౰ ͷ΋ͷΛ݁߹͍ͯ͘͠ɻ 45

Slide 46

Slide 46 text

݁߹ϊʔυͷҧ͍ Hash Join • ಺෦දͷ݁߹Ωʔͷ ϋογϡϦετΛ࡞੒ͯ͠ ͦͷޙɺۦಈදͱ݁߹Λߦ͏ɻ 46

Slide 47

Slide 47 text

݁߹ϊʔυͷҧ͍ Merge Join • ۦಈදͱ಺෦දΛ݁߹ΩʔͰ ιʔτͨ͠ޙͰ ॱ൪ʹ෇͖߹Θͤͯ݁߹͍ͯ͠ ͘ɻ 47

Slide 48

Slide 48 text

JOINʹΑΔϝϦοτ • JOIN͕σʔλ͕খ͍͞΋ͷಉ࢜ʹݶΒͳ͍ɻ Ͳͷ৔߹Ͱ΋࠷దͳJOINΞϧΰϦζϜ͕ PostgreSQLଆͷ੹຿Ͱબ୒͞ΕΔɻ ͦΕͳΓͷ଎౓࠷దԽ͕ݟࠐΊΔɻ 48

Slide 49

Slide 49 text

PostgreSQLΛ࢖༻͢Δ͚ͩͰಘΒΕΔ࢓૊Έ ύϥϨϧΫΤϦ ΫΤϦ໰͍߹ΘͤΛ ෳ਺CPUʹͯฒྻͰूܭͯ݁͠ՌΛฦ͢࢓૊Έɻ 49

Slide 50

Slide 50 text

ύϥϨϧΫΤϦͷαϙʔτ PostgreSQLͷόʔδϣϯΛ্͛Δ͚ͩͰαϙʔτ͕૿͑Δɻ ʢόʔδϣϯΞοϓ͚ͩͰ଎͘ͳΔʣ • Seq Scan(9.6͔Β) • Index Scan(10.0͔Β) • ֤JOIN ʢNLJͱHash JOIN͸9.6͔ΒɺMerge JOIN ͸10.0͔Βʣ • UNIONͱ͔ 50

Slide 51

Slide 51 text

ύϥϨϧΫΤϦͷϝϦοτ ฒྻͰ࣮ߦ͞ΕΔͷͰCPUϘτϧωοΫͷ ΫΤϦ໰͍߹Θͤͷ଎౓վળ͕ظ଴͞ΕΔ. ྫ͑͹ ΠϯσοΫε͕షΓ੾Εͳ͍ɺ ෳࡶͳݕࡧʹ͓͍ͯ΋ͦΕͳΓͷ ύϑΥʔϚϯε͕ग़Δ ʢҰൠʹ ̎ʙ̐ഒͱ͔ݴΘΕΔʣ 51

Slide 52

Slide 52 text

PostgreSQLͷαϙʔτߏจ (MySQLൺ) 52

Slide 53

Slide 53 text

PostgreSQLͷαϙʔτߏจ (MySQLൺ) Returning۟ PostgreSQLͷಠ֦ࣗுͰɺINSERTɾUPDATEɾ DELETEͷ݁ՌΛฦ͢ࣄ͕ग़དྷΔػೳ. ྫ͑͹ DELETE ~ WHERE ~; ͳͲͷෳ਺݅DELETE΍ INSERT INTO ~ SELECT~ ͷෳ਺݅INSERT΋ αϙʔτ͍ͯ͠Δɻ 53

Slide 54

Slide 54 text

Returning۟ ྫ͑͹ɺ (id͸serialܕ (auto increment) ) INSERT INTO users (email, password) VALUES ('[email protected]', 'password') RETURNING id, email, password; ͱॻ͘ͱ ૠೖ݁Ռ͕ฦͬͯ͘Δɻ MySQLͷLAST_INSER_ID()ΑΓ΋ڧྗ. 54

Slide 55

Slide 55 text

Կ͕خ͍͔͠ • ΋͘͠͸ϦΧόϦͷ࣌ʹҎԼͷΑ͏ʹ༧Ίର৅ΛνΣο Ϋग़དྷͨΓͨ͠. BEGIN; -- ↓ ෳࡶͳ৚݅Ͱ࡟আΛ͢Δ. DELETE FROM party WHERE date < '2019-02-25' RETURNING id, date; COMMIT; 55

Slide 56

Slide 56 text

Returning۟ͷϝϦοτ DjangoͰInsertର৅ͷ֤ΦϒδΣΫτΛ࡞ͬͨޙʹ PostgreSQLʹෳ਺݅ҰׅͰBulk Insert͢Δͱ ໭Γ஋ͷΦϒδΣΫτͷ഑ྻ͸શͯૠೖલͷΦϒδΣΫτʹ idϓϩύςΟͷ஋͕ηοτ͞Εͨঢ়ଶͰฦͬͯ͘ΔͷͰ SQLΛ౤͛Δճ਺͕100ճͱ͔1000ճͱ͔ͷ୯ҐͰݮͬͨɻ ݁Ռɺόον଎౓͕௒޲্ ͨ͠ɻ 56

Slide 57

Slide 57 text

parties = [ {'title': 'ύʔςΟʔ1', date: '2019೥06݄10೔'}, {'title': 'ύʔςΟʔ2', date: '2019೥06݄11೔'}, {'title': 'ύʔςΟʔ3', date: '2019೥06݄12೔'}, ] inserted_parties = Party.objects.bulk_create(parties) # ↓ ͜͏ͳΔ [ {'id': 1, 'title': 'ύʔςΟʔ1', date: '2019೥06݄10೔'}, {'id': 2, 'title': 'ύʔςΟʔ2', date: '2019೥06݄11೔'}, {'id': 3, 'title': 'ύʔςΟʔ3', date: '2019೥06݄12೔'}, ] 57

Slide 58

Slide 58 text

PostgreSQLͷαϙʔτߏจ (MySQLൺ) Materialized View ͦʔ͍ͩ͞Μ͕େ޷͖ͳౕɻ ςʔϒϧͷΑ͏ʹʮ݁ՌΛอ࣋ʯͨ͠Viewɻ 58

Slide 59

Slide 59 text

ྫ͑͹ҎԼͷΑ͏ʹఆٛ͢Δͱ ఆٛͨ͠λΠϛϯάͰͷσʔλ͕ active_users ʹ ֨ೲ͞ΕΔ. CREATE MATERIALIZED VIEW active_users AS SELECT id , name , gender FROM users WHERE delete_flag = 0; 59

Slide 60

Slide 60 text

ఆٛͨ͠ Materialized View͸௨ৗͷςʔϒϧͷΑ͏ʹ ΞΫηε͕ՄೳͱͳΔɻ -- SELECT͸΋ͪΖΜͷ͜ͱ SELECT * FROM active_users; -- JOIN΋Մೳ SELECT * FROM reserves INNER JOIN active_users ON reserves.user_id = active_users.id; 60

Slide 61

Slide 61 text

ͦͷଞʹ΋IndexΛషΕͨΓ͢Δ. CREATE MATERIALIZED VIEW active_users AS SELECT id , name , gender , created_at FROM users WHERE delete_flag = 0; CREATE INDEX active_users_idx1 ON active_users (created_at); 61

Slide 62

Slide 62 text

Materialized ViewͷϝϦοτ ݟͯ΋Βͬͯͷ௨Γ Ωϟογϡతͳ໾ׂΛߦ͏ͷͰ ύϑΥʔϚϯεͷ޲্͕ظ଴ग़དྷΔɻ ಛʹෳࡶͳΫΤϦूܭΛߦ͏৔߹ͱ͔͸༧Ί ϚςϏϡʔʹ͓ͯ͘͜͠ͱͰ ௨ৗͷϏϡʔͷaliasͱͯ͠ͷଆ໘ͷ Ըܙ΋ड͚Δ͜ͱ͕ग़དྷΔɻ 62

Slide 63

Slide 63 text

Materialized ViewͷσϝϦοτ ͱ͸͍͑ɺ͋͘·Ͱ ʮΩϟογϡʯ ͳͷͰ ϦΞϧλΠϜੑͷ͋Δ΋ͷ౳ʹ͸޲͔ͳ͍ɻ ߋ৽͢Δʹ͸ MATERIALIZED VIEW active_users; Λ͠ͳ͍ͱ͍͚ͳ͍͕ શσʔλΛߋ৽͢ΔͷͰ ॏ͍Query͸REFRESHͷ౓ʹྲྀΕͯ͠·͏ɻ ʢPostgreSQLʹ͸ϚςϏϡʔͷ෦෼ߋ৽ͷػೳ͸ͳ͍ʣ 63

Slide 64

Slide 64 text

ͦͷଞ • CUBEߏจʢখܭ͕औΕΔʣ • WITH۟ ʢαϒΫΤϦʹผ໊Λ͚ͭΔΈ͍ͨͳౕʢޠኮྗʣʣ 64

Slide 65

Slide 65 text

4. PostgreSQLͷ͓΋͠Ζ͍ॴ ·ͱΊ • JOINαϙʔτͩͬͨΓύϥϨϧΫΤϦͳͲ όʔδϣϯΞοϓͨ͠ΓPostgreSQLΛ ࢖༻͢Δ͚ͩͰಘΒΕΔԸܙ͕͋ͬͨɻ • Materialized View͸ࠔͬͨ࣌ʹ ॿ͚ͯ͘ΕΔɻ·͞ʹຑༀɻɻɻ 65

Slide 66

Slide 66 text

͓ΘΓʹ σʔλϕʔεͷϦϑΝΫλϦϯάΛ ௕ظͰ΍͍ͬͯΔɻ ࠓճͷख๏͸σʔλϕʔεϦϑΝΫλϦϯάͱ͍͏ ໊ஶʹऔΓ্͛ΒΕ͍ͯΔख๏ͷ͋Δछɺݱ୅൛ʢͱ ࢥ͍ͬͯΔʣɻ 66

Slide 67

Slide 67 text

͓ΘΓʹ 1೥ऑ૸͖͚ͬͯͨͲ...ҋ͸ͨ͘͞Μ͋Δɻ ࠷ऴϩάΠϯ೔͕࣌ لݩલʹͳ͍ͬͯΔਓ΋͍ͨ͠ ύʔςΟʔ͕Կނ͔ਂ໷2͔࣌Β։࠵͞ΕΔΈ͍ͨͳ ͜ͱ΋͋Δɻɻ ͱ͸͍͑ɺϨδΣϯυίʔυ ͱ͍͏͔ ࠓ·Ͱͷച্Λࢧ͑ͯ͘Εͨେ੾ͳίʔυͰ͋Δ͜ͱ͸ ͪΌΜͱཧղ͍͔ͯ͠ͳ͍ͱ͍͚ͳ͍ͱࢥ͏ɻ ·͞ʹίʔυΛ଀ΜͰਓΛ଀·ͣɻ 67

Slide 68

Slide 68 text

͓ΘΓʹ ࠓճɺPostgreSQLʹͳͬͨࣄͰ ඇৗʹۀ຿Ͱ࢖͑ΔPostgreSQLͷ஌͕ࣝ૿͑ͨɻ ΠϯϑϥΤϯδχΞͱͯ͠ۀ຿ͯ͠ͳ͍ͷͰ ͋·ΓRDBͷػೳʹ໨Λ޲͚Δࣄ͕ແ͔͚ͬͨͲ RDBͷػೳΛ஌͍ͬͯΔࣄʹΑΓ ઃܭྗ͕େ෼ண͍ͨɻ ࠓճͷൃද͕Կ͔఻ΘΔ΋ͷ͕͋Ε͹͍͍ͳɻ 68

Slide 69

Slide 69 text

ΑΓྑ͍RDBϥΠϑΛʂ 69

Slide 70

Slide 70 text

ࠂ஌ 70

Slide 71

Slide 71 text

Πϕϯτࠂ஌ • ୈ28ճ தࠃ஍ํDBษڧձ in Ԭࢁ • ͪΐͬͱ࣌ظ͕ະఆɾɾ 71

Slide 72

Slide 72 text

Πϕϯτࠂ஌ • Φʔϓϯηϛφʔ2019@Ԭࢁ • 2019೥06݄29೔ (౔) • ࠓճͷςʔϚ͸ʮTechnology X Societyʯ ࠓ೥ͷΦʔϓϯηϛφʔԬࢁͰ͸ɺςΫϊϩδʔͰࣾձͱؔΘΓʹ͓ ͍ͯϢχʔΫͳൃ૝΍࢖໋Λ͓࣋ͪͷํͨͪͷ͓࿩Λ͓ಧ͚͠·͢ɻ ਃ͠ࠐΈ: https://okayama.open-seminar.org/ ʢΦϛΧϨ͸ΰʔϧυεϙϯαʔʂʣ 72

Slide 73

Slide 73 text

Πϕϯτࠂ஌ • JAWS-UG Ԭࢁ 2019 • 2019೥08݄17೔ (౔) https://jaws-ug-okayama.doorkeeper.jp/events/ 91487 73

Slide 74

Slide 74 text

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝· ͨ͠ʂʂ 74