RDB anti-pattern that Java engineer wants to know

by soudai sone

Slide 1

Slide 1 text

JavaΤϯδχΞʹ஌ͬͯ΄͍͠ RDBΞϯνύλʔϯ ++6($$$4QSJOH

Slide 2

Slide 2 text

What is it? ࠓ೔Έͳ͞Μʹ࣋ͬͯؼͬͯ΄͍͜͠ͱ

Slide 3

Slide 3 text

What is it? σʔλϕʔεͷण໋͸ ΞϓϦέʔγϣϯΑΓ΋௕͍

Slide 4

Slide 4 text

What is it? ͦΜͳ௕͍෇͖߹͍ʹͳΔσʔλϕʔε ͷେ੾ͳࣄΛ͓఻͑͠·͢

Slide 5

Slide 5 text

What is it? ର৅ͷσʔλϕʔειϑτ΢ΣΞ

Slide 6

Slide 6 text

What is it? PostgreSQL 9.6ͱMySQL 5.7(InnoDB)ʹݶΔ ଞͷRDBͷ࿩͸͠·ͤΜ

Slide 7

Slide 7 text

What is it? RDBΞϯνύλʔϯ

Slide 8

Slide 8 text

What is it? RDBΞϯνύλʔϯ ↓ ޙʑʹۤ͠ΈΛੜΉ

Slide 9

Slide 9 text

What is it? RDBΞϯνύλʔϯΛ஌Δ

Slide 10

Slide 10 text

What is it? RDBΞϯνύλʔϯΛ஌Δ ↓ ಉ͡աͪΛ܁Γฦ͞ͳ͍

Slide 11

Slide 11 text

What is it? ࠓ೔͸RDBͷࣦഊྫΛ͝঺հ͠·͢

Slide 12

Slide 12 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹσʔλϕʔεͷ໎ٶ ̏ɹڧ͗͢Δ੍໿ ̐ɹ؂ࢹ͞Εͳ͍σʔλϕʔε ̑ɹ·ͱΊ

Slide 13

Slide 13 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹσʔλϕʔεͷ໎ٶ ̏ɹڧ͗͢Δ੍໿ ̐ɹ؂ࢹ͞Εͳ͍σʔλϕʔε ̑ɹ·ͱΊ

Slide 14

Slide 14 text

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Mackerel

Slide 17

Slide 17 text

Mackerel

Slide 18

Slide 18 text

͸ͯͳ͸஥ؒΛ୳ͯ͠·͢ curl -sIL mackerel.io | grep engineer

Slide 19

Slide 19 text

Slide 20

Slide 20 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹσʔλϕʔεͷ໎ٶ ̏ɹڧ͗͢Δ੍໿ ̐ɹ؂ࢹ͞Εͳ͍σʔλϕʔε ̑ɹ·ͱΊ

Slide 21

Slide 21 text

No content

Slide 22

Slide 22 text

σʔλϕʔεͷ໎ٶ 4PGUXBSF%FTJHOΛಡΜͰ͘Εʂʂ Ҏ্οʂʂʂʂ

Slide 23

Slide 23 text

σʔλϕʔεͷ໎ٶ σʔλϕʔεͷઃܭͷ࿩

Slide 24

Slide 24 text

σʔλϕʔεͷ໎ٶ demo=# SELECT delete_falg AS delete_flag FROM users GROUP BY delete_flag delete_flag ------------- 1 2 0 9 99 NULL (6 ߦ)

Slide 25

Slide 25 text

σʔλϕʔεͷ໎ٶ ■࢓༷ॻ_20170513(1).xls 0: ະ࡟আ 1: ࡟আࡁΈ 2: ؅ཧऀʹΑΔڧ੍࡟আ 9: ຣফ 99: Α͘Θ͔Βͳ͍ NULL: όάͰೖΔ

Slide 26

Slide 26 text

σʔλϕʔεͷ໎ٶ NFNP NFNP NFNP

Slide 27

Slide 27 text

σʔλϕʔεͷ໎ٶ NFNP NFNP NFNP NFNPͰ͸ͳ͘NFNPͷͱ͜Ζ͕ྺ࢙Λײͤ͡͞Δ

Slide 28

Slide 28 text

σʔλϕʔεͷ໎ٶ σʔλϕʔεͷ໎ٶͷ໰୊

Slide 29

Slide 29 text

σʔλϕʔεͷ໎ٶ w ෆద੾ͳ໊લͰ͸σʔλϕʔεͷςʔϒϧͷؔ࿈ੑ ΍ҙਤ͕ཧղͰ͖ͳ͍ w ϦϨʔγϣφϧϞσϧΛج͍ͮͨઃܭ͍ͯ͠ͳ͍ͱ طଘͷศརͳπʔϧΛར༻ग़དྷͳ͍ w อଘ͞Εͨσʔλ͕ਖ਼͍͔͠Ͳ͏͔͕൑அग़དྷͳ͍ w ͲͷΑ͏ͳσʔλΛอଘ͠ɺͲͷΑ͏ͳσʔλΛ  औΓग़ͤ͹͍͍͔Θ͔Βͳ͍

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

σʔλϕʔεͷ໎ٶ ࿦ཧ*%

Slide 35

Slide 35 text

σʔλϕʔεͷ໎ٶ ࿦ཧ*% ˣ ձһ*%Λྫʹ͠·͢

Slide 36

Slide 36 text

σʔλϕʔεͷ໎ٶ

Slide 37

Slide 37 text

σʔλϕʔεͷ໎ٶ ͔Β࢝·Δͷ͸ҰൠϢʔβ ͔Β࢝·Δͷ͸؅ཧϢʔβ

Slide 38

Slide 38 text

σʔλϕʔεͷ໎ٶ Ϣʔβͷ౎ಓ෎ݝΛද͢

Slide 39

Slide 39 text

σʔλϕʔεͷ໎ٶ ຊ౰ͷձһ*%

Slide 40

Slide 40 text

σʔλϕʔεͷ໎ٶ σʔλʹϩδοΫΛຒΊࠐΉͳͬʂ

Slide 41

Slide 41 text

σʔλϕʔεͷ໎ٶ σʔλʹϩδοΫΛຒΊࠐΉͳͬʂ ˣ σʔλΛσʔλͱͯ͠ಡΈऔΕͳ͍

Slide 42

Slide 42 text

σʔλϕʔεͷ໎ٶ ॲํᝦ w ΧϥϜΛਖ਼໊͘͠લΛ͚ͭΔ w σʔλͷ੹຿Λ෼͚Δ w มԽʹ߹ΘͤͯϦϑΝΫλϦϯά͢Δ

Slide 43

Slide 43 text

σʔλϕʔεͷ໎ٶ ॲํᝦ w ΧϥϜΛਖ਼໊͘͠લΛ͚ͭΔ w σʔλͷ੹຿Λ෼͚Δ w มԽʹ߹ΘͤͯϦϑΝΫλϦϯά͢Δ

Slide 44

Slide 44 text

σʔλϕʔεͷ໎ٶ ϦϑΝΫλϦϯάͷྫ

Slide 45

Slide 45 text

σʔλϕʔεͷ໎ٶ JE ໊લ ೥ྸ ીࠜɹ૖େ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ݱঢ়

Slide 46

Slide 46 text

σʔλϕʔεͷ໎ٶ ձһJE ໊લ ೥ྸ ݖݶJE ౎ಓ෎ݝJE ીࠜɹ૖େ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ཧ૝

Slide 47

Slide 47 text

σʔλϕʔεͷ໎ٶ JE ձһJE ໊લ ೥ྸ ݖݶJE ౎ಓ෎ݝJE /6-- ીࠜɹ૖େ /6-- /6-- /6-- ીࠜɹኽָ /6-- /6-- /6-- ીࠜɹᗅָ /6-- /6-- /6-- ીࠜɹࡣָ /6-- /6-- ର৅ͷΧϥϜΛ௥Ճ

Slide 48

Slide 48 text

σʔλϕʔεͷ໎ٶ JE ձһJE ໊લ ೥ྸ ݖݶJE ౎ಓ෎ݝJE ીࠜɹ૖େ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ର৅ͷΧϥϜΛ௥Ճ

Slide 49

Slide 49 text

σʔλϕʔεͷ໎ٶ Ͳ͏΍ͬͯ࿦ཧ*%Λ ৽͍͠ΧϥϜʹೖΕΔ͔

Slide 50

Slide 50 text

σʔλϕʔεͷ໎ٶ ϦϑΝΫλϦϯάख๏ͷྫ w */4&35΍61%"5&ͷ࣌ʹΞϓϦ͕ೖΕΔ w */4&35΍61%"5&ͷ࣌ʹτϦΨʔΛ࢖͏ w Ծ૝ྻ΍7JFXΛ࢖͏

Slide 51

Slide 51 text

σʔλϕʔεͷ໎ٶ ձһJE ໊લ ೥ྸ ݖݶJE ౎ಓ෎ݝJE ીࠜɹ૖େ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ཧ૝ͷ7JFXΛ࡞Δ

Slide 52

Slide 52 text

σʔλϕʔεͷ໎ٶ αʔϏεͷੑ࣭ʹ߹Θͤͯ ख๏ΛબͿ

Slide 53

Slide 53 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹσʔλϕʔεͷ໎ٶ ̏ɹڧ͗͢Δ੍໿ ̐ɹ؂ࢹ͞Εͳ͍σʔλϕʔε ̑ɹ·ͱΊ

Slide 54

Slide 54 text

ڧ͗͢Δ੍໿ σʔλϕʔε͸ αʔϏεʹ༩͑ΔӨڹൣғ͕޿͍

Slide 55

Slide 55 text

ڧ͗͢Δ੍໿ ༩͑ΔӨڹൣғͷྫ w %#Λఀࢭ͢ΔͱαʔϏε͕ࢭ·Δ w มߋ͢Δͱෳ਺αʔϏεͷվम͕ඞཁ w σʔλ͸ৗʹ஝ੵ͞Ε͍ͯ͘

Slide 56

Slide 56 text

ڧ͗͢Δ੍໿ ࣌ؒܦաͰσʔλ͕੒௕͢Δ

Slide 57

Slide 57 text

ڧ͗͢Δ੍໿ ͭ·Γมߋ͕େม

Slide 58

Slide 58 text

ڧ͗͢Δ੍໿ ͭ·Γมߋ͕େม ˣ ͕࣌ؒܦͯ͹ܦͭ΄Ͳঢ়گ͕ѱԽ

Slide 59

Slide 59 text

ڧ͗͢Δ੍໿ ͔ͩΒͦ͜ਖ਼͍͠ઃܭʹҭͯΔ΂͖

Slide 60

Slide 60 text

ڧ͗͢Δ੍໿ ͦͷͨΊʹ3%#ͷػೳΛ׆༻͢Δ

Slide 61

Slide 61 text

ڧ͗͢Δ੍໿ ͦͷͨΊʹ3%#ͷػೳΛ׆༻͢Δ ˣ ͔ͦ͠͠Ε͕ཪ໨ʹग़Δ͜ͱ΋

Slide 62

Slide 62 text

ڧ͗͢Δ੍໿ 3%#ͷػೳʹґଘ͗͢͠Δͱ ͦΕ͸ڧ͗͢Δ੍໿ʹͳΔ

Slide 63

Slide 63 text

ڧ͗͢Δ੍໿ ڧ͗͢Δ੍໿ͷྫ w τϦΨʔ͕ଟ͘ͷ5"#-&ʹӨڹΛ༩͑Δ w ετΞυ͕ϩδοΫΛ࣋ͪ͗͢Δ w ϚςϏϡʔ΍%#-JOLͷଟ༻͗͢͠Δ

Slide 64

Slide 64 text

ڧ͗͢Δ੍໿ 3%#ͷػೳ͸ͱͯ΋ศར

Slide 65

Slide 65 text

ڧ͗͢Δ੍໿ 3%#ͷػೳ͸ͱͯ΋ศར ˣ ͔ۜ͠͠ͷ஄ؙͰ͸ͳ͍

Slide 66

Slide 66 text

ڧ͗͢Δ੍໿ ઃܭͷόϥϯεײ͕֮େࣄ

Slide 67

Slide 67 text

ڧ͗͢Δ੍໿ ॲํᝦ w ͦͷػೳΛར༻͢ΔظؒΛݕ౼͢Δ w ͦͷػೳͷӨڹൣғΛཧղ͢Δ w ຊ౰ʹ࠷దͳΞϓϩʔν͔໰͍௚͢

Slide 68

Slide 68 text

ڧ͗͢Δ੍໿ ॲํᝦ w ͦͷػೳΛར༻͢ΔظؒΛݕ౼͢Δ w ͦͷػೳͷӨڹൣғΛཧղ͢Δ w ຊ౰ʹ࠷దͳΞϓϩʔν͔໰͍௚͢

Slide 69

Slide 69 text

ڧ͗͢Δ੍໿ ਓ͸աڈͷ੒ޭମݧͷ όΠΞε͕͔͔Δ

Slide 70

Slide 70 text

ڧ͗͢Δ੍໿ ਓ͸աڈͷࣦഊମݧ΋ όΠΞε͕͔͔Δ

Slide 71

Slide 71 text

ڧ͗͢Δ੍໿ ઃܭͷόϥϯεײ͕֮େࣄ

Slide 72

Slide 72 text

ڧ͗͢Δ੍໿ ઃܭͷόϥϯεײ͕֮େࣄ ˣ ଟ͘ͷઃܭΛݟͯཆ͏͜ͱ͕େࣄ

Slide 73

Slide 73 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹσʔλϕʔεͷ໎ٶ ̏ɹڧ͗͢Δ੍໿ ̐ɹ؂ࢹ͞Εͳ͍σʔλϕʔε ̑ɹ·ͱΊ

Slide 74

Slide 74 text

؂ࢹ͞Εͳ͍σʔλϕʔε ؂ࢹͱϞχλϦϯά

Slide 75

Slide 75 text

؂ࢹ͞Εͳ͍σʔλϕʔε

Slide 76

Slide 76 text

؂ࢹ͞Εͳ͍σʔλϕʔε

Slide 77

Slide 77 text

؂ࢹ͞Εͳ͍σʔλϕʔε ͳΜͰςετॻ͘ͷʹ ϞχλϦϯά͠ͳ͍ΜͰ͔͢ʁ

Slide 78

Slide 78 text

؂ࢹ͞Εͳ͍σʔλϕʔε ؂ࢹ͸ࡾஈ֊͋Δ

Slide 79

Slide 79 text

؂ࢹ͞Εͳ͍σʔλϕʔε w͍ͪૣ͘ো֐ൃੜΛ֬ೝ͢Δ w෮چͷͨΊͷݪҼΛ֬ೝ͢Δ wঢ়گͷมԽΛ࣌ܥྻͰ؂ࢹ͢Δ͜ͱͰ Ϧιʔε؅ཧ΍ো֐ͷ༧ஹͷ೺Ѳ͢Δ

Slide 80

Slide 80 text

؂ࢹ͞Εͳ͍σʔλϕʔε w͍ͪૣ͘ো֐ൃੜΛ֬ೝ͢Δ w෮چͷͨΊͷݪҼΛ֬ೝ͢Δ wঢ়گͷมԽΛ࣌ܥྻͰ؂ࢹ͢Δ͜ͱͰ Ϧιʔε؅ཧ΍ো֐ͷ༧ஹͷ೺Ѳ͢Δ ࢮ׆؂ࢹ

Slide 81

Slide 81 text

؂ࢹ͞Εͳ͍σʔλϕʔε w͍ͪૣ͘ো֐ൃੜΛ֬ೝ͢Δ w෮چͷͨΊͷݪҼΛ֬ೝ͢Δ wঢ়گͷมԽΛ࣌ܥྻͰ؂ࢹ͢Δ͜ͱͰ Ϧιʔε؅ཧ΍ো֐ͷ༧ஹͷ೺Ѳ͢Δ νΣοΫ؂ࢹ

Slide 82

Slide 82 text

؂ࢹ͞Εͳ͍σʔλϕʔε w͍ͪૣ͘ো֐ൃੜΛ֬ೝ͢Δ w෮چͷͨΊͷݪҼΛ֬ೝ͢Δ wঢ়گͷมԽΛ࣌ܥྻͰ؂ࢹ͢Δ͜ͱͰ Ϧιʔε؅ཧ΍ো֐ͷ༧ஹͷ೺Ѳ͢Δ ϞχλϦϯά؂ࢹ

Slide 83

Slide 83 text

؂ࢹ͞Εͳ͍σʔλϕʔε

Slide 84

Slide 84 text

؂ࢹ͞Εͳ͍σʔλϕʔε

Slide 85

Slide 85 text

؂ࢹ͞Εͳ͍σʔλϕʔε Πϯϑϥ͸ੜ͖෺

Slide 86

Slide 86 text

؂ࢹ͞Εͳ͍σʔλϕʔε Πϯϑϥ͸ੜ͖෺ ˣ ೔ʑͷ੒௕ه࿥ΛݟΔ

Slide 87

Slide 87 text

؂ࢹ͞Εͳ͍σʔλϕʔε աڈΛ஌Δ͜ͱͰ ࠓͷ৔ॴ͕Θ͔Δ

Slide 88

Slide 88 text

؂ࢹ͞Εͳ͍σʔλϕʔε ࠓͱաڈͷࠩ෼

Slide 89

Slide 89 text

؂ࢹ͞Εͳ͍σʔλϕʔε ࠓͱաڈͷࠩ෼ ˣ ະདྷΛ஌ΔͨΊͷࡐྉ

Slide 90

Slide 90 text

؂ࢹ͞Εͳ͍σʔλϕʔε ؂ࢹ͢΂͖߲໨ w ౷ܭ৘ใ΍04ϦιʔεͳͲ3%#ͷঢ়ଶ w εϩʔΫΤϦϩά΍ΤϥʔϩάͳͲ w ෳ਺ͷ%#ΛॏͶͨൺֱ

Slide 91

Slide 91 text

؂ࢹ͞Εͳ͍σʔλϕʔε ϞχλϦϯά͸վળͷҰา໨

Slide 92

Slide 92 text

؂ࢹ͞Εͳ͍σʔλϕʔε ॲํᝦ w ద੾ʹϞχλϦϯάΛ͢Δ w ϞχλϦϯάπʔϧΛ׆༻͢Δ w վળͷͨΊͷҰา໨Λ౿Έग़͢

Slide 93

Slide 93 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹσʔλϕʔεͷ໎ٶ ̏ɹڧ͗͢Δ੍໿ ̐ɹ؂ࢹ͞Εͳ͍σʔλϕʔε ̑ɹ·ͱΊ

Slide 94

Slide 94 text

·ͱΊ ۪ऀ͸ܦݧʹֶͿ ݡऀ͸աڈʹֶͿ

Slide 95

Slide 95 text

·ͱΊ ΞϯνύλʔϯΛ஌Δ

Slide 96

Slide 96 text

·ͱΊ

Slide 97

Slide 97 text

·ͱΊ SQLΞϯνύλʔϯ͸ݫબ͞Εࣦͨഊू

Slide 98

Slide 98 text

·ͱΊ SQLΞϯνύλʔϯ͸ݫબ͞Εࣦͨഊू ↓ DBͷϊ΢ϋ΢͕٧·໊ͬͨஶ

Slide 99

Slide 99 text

·ͱΊ Ұ౓࡞ͬͨ%#͸ফͤͳ͍

Slide 100

Slide 100 text

·ͱΊ Ұ౓࡞ͬͨ%#͸ফͤͳ͍ ˣ ઃܭ͕େࣄ

Slide 101

Slide 101 text

·ͱΊ σʔλϕʔεͷࢮ͸αʔϏεͷࢮ

Slide 102

Slide 102 text

·ͱΊ σʔλϕʔεͷࢮ͸αʔϏεͷࢮ ˣ ղܾͰ͖Δਓ͸ӳ༤

Slide 103

Slide 103 text

·ͱΊ %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ

Slide 104

Slide 104 text

·ͱΊ

Slide 105

Slide 105 text

·ͱΊ αʔϏε΍νʔϜΛकΔ

Slide 106

Slide 106 text

·ͱΊ αʔϏε΍νʔϜΛकΔ ˣ ͦͷͨΊʹֶͿ

Slide 107

Slide 107 text

·ͱΊ पғͷܦݧஊ͔ΒֶͿ

Slide 108

Slide 108 text

·ͱΊ पғͷܦݧஊ͔ΒֶͿ ˣ ੵۃతʹίϛϡχςΟΛར༻͢Δ

Slide 109

Slide 109 text

ࢀߟࢿྉ ɾQPTUHSFTRMKQ4MBDL νϟοτϧʔϜ IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN ɾNZTRMDBTVBM4MBDL νϟοτϧʔϜ IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN