Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up
for free
DataBase Refactoring
soudai sone
May 16, 2017
10
9.8k
DataBase Refactoring
■オープンセミナー岡山の登壇資料です。
http://okayama.open-seminar.org/
■Twitterまとめ
https://togetter.com/li/1109418
soudai sone
May 16, 2017
Tweet
Share
More Decks by soudai sone
See All by soudai sone
soudai
0
1
soudai
1
550
soudai
8
3.5k
soudai
1
350
soudai
0
600
soudai
1
410
soudai
13
12k
soudai
5
3.7k
soudai
2
700
Featured
See All Featured
brianwarren
82
4.8k
holman
448
140k
danielanewman
3
560
sachag
267
17k
wjessup
340
16k
lara
17
2.9k
frogandcode
129
20k
aarron
258
36k
sachag
446
36k
lara
590
61k
destraynor
146
19k
bryan
32
3.5k
Transcript
σʔλϕʔε ϦϑΝΫλϦϯά Φʔϓϯηϛφʔ!Ԭࢁ
What is it? ࠓΈͳ͞Μʹ࣋ͬͯؼͬͯ΄͍͜͠ͱ
What is it? σʔλϕʔεͷण໋ ΞϓϦέʔγϣϯΑΓ͍
What is it? ͦΜͳ͍͖߹͍ʹͳΔσʔλϕʔε ͷେͳࣄΛ͓͑͠·͢
What is it? σʔλϕʔεͷϦϑΝΫλϦϯάͯ͠·͔͢ʁ
What is it? ෆదͳσʔλϕʔε͕ ίʔυαʔϏεͷΛࢭΊΔ
What is it?
What is it? վળ͢ΔͨΊͷ࠷ॳͷҰาΛ͓͑͠·͢
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹσʔλϕʔεͷෆ٢ͳष͍ ̏ɹઓ͏ͨΊͷࣄલ४උ ̐ɹϦϑΝΫλϦϯά ̑ɹ·ͱΊ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹσʔλϕʔεͷෆ٢ͳष͍ ̏ɹઓ͏ͨΊͷࣄલ४උ ̐ɹϦϑΝΫλϦϯά ̑ɹ·ͱΊ
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
ೖࣾͰ͢ʂ
Mackerel
Mackerel
ͯͳؒΛ୳ͯ͠·͢ curl -sIL mackerel.io | grep engineer
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹσʔλϕʔεͷෆ٢ͳष͍ ̏ɹઓ͏ͨΊͷࣄલ४උ ̐ɹϦϑΝΫλϦϯά ̑ɹ·ͱΊ
σʔλϕʔεͷෆ٢ͳष͍ σʔλϕʔεͷෆ٢ͳष͍
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻ Ϩίʔυͷଐੑʹ߹Θͤͯͷҙຯ͕มΘΔΑ͏ͳΧϥϜ
ձһͩͱೖձɺελοϑͩͱೖࣾͱ͢ΔͳͲ
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻ ςʔϒϧόʔδϣϯ
༷มߋͷ܁Γฦ͠ͷɺੜ·ΕΔ͜ͱ͕͋Δ
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻ ඇਖ਼نԽͷɺσʔλͷ߹ੑ͕औΕͳ͘ͳΔ
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻ ྫ͑NFNP
NFNP NFNPʜ ຊʹඞཁͳ߹ෳͷΤϯςΟςΟΛͭʹͯ͠ΔՄ ೳੑ͕͋Δ
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻ ෆཁͳσʔλআ͢Δʢཧআ͠ͳ͍ʣ
ຊʹඞཁͳ߹ύʔςʔγϣϯγϟʔσΟϯά
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻ ཧ*%ͳͲσʔλͷதͷใʹΑͬͯҙຯ͕มΘΔͷ
͔Β࢝·Δ*%ཧऀɺ͔Β࢝·Δ*%ϢʔβͳͲ
σʔλϕʔεͷෆ٢ͳष͍ wෳͷతʹΘΕΔΧϥϜ wෳͷతʹΘΕΔςʔϒϧ wͳσʔλ wΧϥϜͷଟ͗͢Δςʔϒϧ wߦͷଟ͗͢Δςʔϒϧ wʮεϚʔτʯΧϥϜ wมߋͷڪා σʔλϕʔεϦϑΝΫλϦϯά͔ΒҾ༻ σʔλϕʔεͷมߋσʔλͷߋ৽ʹΑͬͯɺΞϓϦέʔ
γϣϯ͕յΕΔͷͰʁͱͳΓखΛ͚Εͳ͍ঢ়ଶ
σʔλϕʔεͷෆ٢ͳष͍ σʔλϕʔεͷෆ٢ͳष͍
σʔλϕʔεͷෆ٢ͳष͍ σʔλϕʔεͷෆ٢ͳष͍ ˣ ٕज़తෛ࠴
σʔλϕʔεͷෆ٢ͳष͍ νʔζͳͷ͔ͬͨڇೕͳͷ͔
σʔλϕʔεͷෆ٢ͳष͍ νʔζͳͷ͔ͬͨڇೕͳͷ͔ ˣ ຊʹվम͕ඞཁ͔Ͳ͏͔
σʔλϕʔεͷෆ٢ͳष͍ ᄿ֮Λຏ͘
σʔλϕʔεͷෆ٢ͳष͍ ৭ΜͳઃܭΛݟΔ
σʔλϕʔεͷෆ٢ͳष͍
σʔλϕʔεͷෆ٢ͳष͍ ͖ͩ͢ͱ͔Εɺ ҆৺ͯ͋͠ͱಓΛਐΉ͚ͩ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹσʔλϕʔεͷෆ٢ͳष͍ ̏ɹઓ͏ͨΊͷࣄલ४උ ̐ɹϦϑΝΫλϦϯά ̑ɹ·ͱΊ
ઓ͏ͨΊͷࣄલ४උ ઓ͏ʹϦϑΝΫλϦϯά
ઓ͏ͨΊͷࣄલ४උ σʔλϕʔεͷಛ
ઓ͏ͨΊͷࣄલ४උ σʔλϕʔεͷಛ w σʔλৗʹมԽ͍ͯ͠Δ w Өڹൣғ͕͍ w %#ΛࢭΊΔ͜ͱ͕͍͠
ઓ͏ͨΊͷࣄલ४උ ϦϑΝΫλϦϯάʹඞཁͳϞϊ
ઓ͏ͨΊͷࣄલ४උ
ઓ͏ͨΊͷࣄલ४උ
ઓ͏ͨΊͷࣄલ४උ σʔλϕʔεͷಛ w σʔλৗʹมԽ͍ͯ͠Δ w Өڹൣғ͕͍ w %#ΛࢭΊΔ͜ͱ͕͍͠ ϞχλϦϯάͯ͠ঢ়ଶΛΔ มԽͷӨڹΛϦΞϧλΠϜʹΔ
ઓ͏ͨΊͷࣄલ४උ σʔλϕʔεͷಛ w σʔλৗʹมԽ͍ͯ͠Δ w Өڹൣғ͕͍ w %#ΛࢭΊΔ͜ͱ͕͍͠ Өڹ͕͍͔Βͦ͜ςετΛॻ͘ γφϦΦςετେ
ؔ࿈͢ΔΞϓϦέʔγϣϯͷશ͕ͯର
ઓ͏ͨΊͷࣄલ४උ σʔλϕʔεͷಛ w σʔλৗʹมԽ͍ͯ͠Δ w Өڹൣғ͕͍ w %#ΛࢭΊΔ͜ͱ͕͍͠
ઓ͏ͨΊͷࣄલ४උ αʔϏεఀࢭͷน w αʔϏεࢭΊΕͳ͍ w ࢭΊͨ࣌ͷӨڹൣғ͕Θ͔Βͳ͍ w ແఀࢭͷΈΛ࡞Δͷ͍͠
ઓ͏ͨΊͷࣄલ४උ αʔϏεఀࢭͷน
ઓ͏ͨΊͷࣄલ४උ αʔϏεఀࢭͷน ˣ ࣏ͱٕज़ͷ྆ํͰղܾ͢Δ
ઓ͏ͨΊͷࣄલ४උ αʔϏεఀࢭͷน w αʔϏεࢭΊΕͳ͍ w ࢭΊͨ࣌ͷӨڹൣғ͕Θ͔Βͳ͍ w ແఀࢭͷΈΛ࡞Δͷ͍͠
ઓ͏ͨΊͷࣄલ४උ αʔϏεఀࢭͷน w αʔϏεࢭΊΕͳ͍ w ࢭΊͨ࣌ͷӨڹൣғ͕Θ͔Βͳ͍ w ແఀࢭͷΈΛ࡞Δͷ͍͠
ઓ͏ͨΊͷࣄલ४උ αʔϏεఀࢭͷน w αʔϏεࢭΊΕͳ͍ w ࢭΊͨ࣌ͷӨڹൣғ͕Θ͔Βͳ͍ w ແఀࢭͷΈΛ࡞Δͷ͍͠
ઓ͏ͨΊͷࣄલ४උ σʔλϕʔεϦϑΝΫλϦϯά ͍͘͢͝εύϯʹͳΔ
ઓ͏ͨΊͷࣄલ४උ ࢝ΊΔલʹඞཁͳࣄ w ରΛબఆ͠ɺҠߦظؒΛܾΊΔ w ςετͱϞχλϦϯάΛἧ͑Δ w ϦϦʔεઓུΛܾΊΔ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹσʔλϕʔεͷෆ٢ͳष͍ ̏ɹઓ͏ͨΊͷࣄલ४උ ̐ɹϦϑΝΫλϦϯά ̑ɹ·ͱΊ
ϦϑΝΫλϦϯά σʔλϕʔεϦϑΝΫλϦϯά
ϦϑΝΫλϦϯά
ϦϑΝΫλϦϯά σʔλϕʔεϦϑΝΫλϦϯάͷྨ w ߏ ςʔϒϧ7JFXͷఆٛ w σʔλ࣭ σʔλͷ
w ࢀর߹ੑ ςʔϒϧͷؔ࿈ੑ w ϝιου ετΞυϓϩγδϟ w มߋ ςʔϒϧΧϥϜͷՃ w ΞʔΩςΫνϟ ΞϓϦͱͷΠϯλʔϑΣΠε
None
ϦϑΝΫλϦϯά ៉ྷͳϦϑΝΫλϦϯά w ࢀরͱߋ৽ͷಡΈࠐΈઌΛ͚Δ w ߋ৽ͷϘτϧωοΫΛ͚Δ w σʔλઃܭΛݟ͢ w దʹ*/%&9Λ׆༻͢Δ
ϦϑΝΫλϦϯά ҋͷਂ͍ϦϑΝΫλϦϯά w +0*/Λඇਖ਼نԽͰิ͏ w ֎෦Ωʔ੍Λ֎͢ w ͱΓ͋͑ͣΩϟογϡʹಥͬࠐΉ w ۃͷεέʔϧΞοϓ
ϦϑΝΫλϦϯά ϦϑΝΫλϦϯάख๏
ϦϑΝΫλϦϯά ϦϑΝΫλϦϯάख๏ w %#ϚΠάϨʔγϣϯπʔϧΛ͏ w %#ͷػೳΛར༻͢Δ w खಈͰ42-Λ࣮ߦ͢Δ
ϦϑΝΫλϦϯά ϦϑΝΫλϦϯάख๏ w %#ϚΠάϨʔγϣϯπʔϧΛ͏ w %#ͷػೳΛར༻͢Δ w खಈͰ42-Λ࣮ߦ͢Δ
ϦϑΝΫλϦϯά ϦϑΝΫλϦϯάख๏ w %#ϚΠάϨʔγϣϯπʔϧΛ͏ w %#ͷػೳΛར༻͢Δ w खಈͰ42-Λ࣮ߦ͢Δ
ϦϑΝΫλϦϯά ϦϑΝΫλϦϯάख๏ w %#ϚΠάϨʔγϣϯπʔϧΛ͏ w %#ͷػೳΛར༻͢Δ w खಈͰ42-Λ࣮ߦ͢Δ
ϦϑΝΫλϦϯά ϦϑΝΫλϦϯάͷίπ
ϦϑΝΫλϦϯά ҠߦલʹͤΔࣄ
ϦϑΝΫλϦϯά Ҡߦதաڈͷঢ়ଶΛอ࣋͢Δࣄ
ϦϑΝΫλϦϯά σʔλϕʔεϦϑΝΫλϦϯάͷྨ w ߏ ςʔϒϧ7JFXͷఆٛ w σʔλ࣭ σʔλͷ
w ࢀর߹ੑ ςʔϒϧͷؔ࿈ੑ w ϝιου ετΞυϓϩγδϟ w มߋ ςʔϒϧΧϥϜͷՃ w ΞʔΩςΫνϟ ΞϓϦͱͷΠϯλʔϑΣΠε
JE ໊લ ྸ ીࠜɹେ ીࠜɹኽָ
ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ
JE ໊લ ྸ ીࠜɹେ ીࠜɹኽָ
ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ຖՃྸͷ61%"5&͕ඞཁͳͷͰ ੜ݄ʹมߋ͍ͨ͠
JE ໊લ ྸ ીࠜɹେ ીࠜɹኽָ
ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ੜ݄͔ΒྸΛܭࢉͤ͞Δ Έ͕ඞཁ
JE ໊લ ྸ ીࠜɹେ ીࠜɹኽָ
ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ྸ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ
JE ໊લ ྸ ીࠜɹେ ીࠜɹኽָ
ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ྸ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ԾྻΛར༻͢Δ
JE ໊લ ྸ ીࠜɹେ ીࠜɹኽָ
ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ JE ໊લ ੜ݄ ྸ ીࠜɹେ ીࠜɹኽָ ીࠜɹᗅָ ીࠜɹࡣָ ෆཁʹͳͬͨΒ আ͢Δ
ϦϑΝΫλϦϯά খ͞ͳมߋΛ͍εύϯͰ܁Γฦ͢
ϦϑΝΫλϦϯά খ͞ͳมߋΛ͍εύϯͰ܁Γฦ͢ ˣ %#Өڹൣғ͕͍ͷͰ Γ͚Λ͘͢͢͠Δ͜ͱ͕େࣄ
ϦϑΝΫλϦϯά େ͖ͳσʔλͷϦϑΝΫλϦϯά
ϦϑΝΫλϦϯά େ͖ͳσʔλͷϦϑΝΫλϦϯάྫ w େ͖ͳςʔϒϧͷมߋ w Ϩίʔυͷߋ৽͕ܹ͍͠ςʔϒϧ w %#ͷόʔδϣϯΞοϓ
มߋલ ΞϓϦ
ෳ มߋલ มߋޙ ΞϓϦ
มߋલ มߋޙ ΞϓϦ
มߋલ มߋޙ ΞϓϦ
มߋޙ ΞϓϦ
ϦϑΝΫλϦϯά %#ͷػೳΛ͏·͘׆༻͢Δ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹσʔλϕʔεͷෆ٢ͳष͍ ̏ɹઓ͏ͨΊͷࣄલ४උ ̐ɹDBϦϑΝΫλϦϯά ̑ɹ·ͱΊ
·ͱΊ σʔλϕʔεੜ͖
·ͱΊ σʔλϕʔεੜ͖ ↓ ఆظతͳϝϯςφϯεඞਢ
·ͱΊ খ͍͞͏ͪʹ͢
·ͱΊ খ͍͞͏ͪʹ͢ ↓ DBͷͱڞʹංେԽ͢Δ
·ͱΊ πʔϧDBͷػೳΛ׆༻͢Δ
·ͱΊ πʔϧDBͷػೳΛ׆༻͢Δ ↓ DBϚΠάϨʔγϣϯԾྻͳͲ ઓ͏ͨΊͷಓ۩ʑਐԽ͍ͯ͠Δ
·ͱΊ
·ͱΊ
·ͱΊ Ұ࡞ͬͨ%#ফͤͳ͍
·ͱΊ Ұ࡞ͬͨ%#ফͤͳ͍ ˣ ઃܭ͕େࣄ
·ͱΊ पғͷܦݧஊ͔ΒֶͿ
·ͱΊ पғͷܦݧஊ͔ΒֶͿ ˣ ੵۃతʹίϛϡχςΟΛར༻͢Δ
ࢀߟࢿྉ ɾQPTUHSFTRMKQ4MBDL νϟοτϧʔϜ IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN ɾNZTRMDBTVBM4MBDL νϟοτϧʔϜ IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN
·ͱΊ lखΛಈ͔ͨ͠ਓ͚͕ͩੈքΛม͑Δz :BTVIJSP0OJTIJ
͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ