YAPC::Tokyo 2019 Log Friendly DB Design

ϩάʹ΍͍͞͠%#ઃܭ @fist0 2019.01.26 YAPC::Tokyo 2019

2019.01.26 :"1$5PLZP 2 ࣗݾ঺հ )/;͌͢ͱ +PCαʔόαΠυΤϯδχΞ *%pTUpTUP ೥͸ͣͬͱHPMBOHॻ͍ͯͨ ೥͸4DBMBॻ͘ػӡ 4DBMB೥ੜ

2019.01.26 :"1$5PLZP 3 ࢲͱ1FSM ͸͡Ίͯͷ:"1$͕:"1$"TJB5PLZP ৽ଔͰೖࣾͨ͠ͷ͕1FSMͷձࣾ ৽ଔݚमͰ1FSM ഑ଐ͞Εͯ࠷ॳͷϓϩμΫτ΋1FSM

2019.01.26 :"1$5PLZP αʔϏεӡ༻ͯ͠·͔͢ʁ

2019.01.26 :"1$5PLZP ϩάݟͯ·͔͢ʁ

2019.01.26 :"1$5PLZP 6 ӡ༻ͱϩά ʁʁʁʮϩάʹ͸αʔϏεͷେ੾ͳ͜ͱ͕͢΂ͯ٧·ͬͯΔΜͩΑʯ ʁʁʁʮͰ΋ɺ΄ͱΜͲͷਓ͸ͦΕʹؾ͔ͮͳ͍Μͩʜʯ ਆ༷ɺ෹༷ɺϩά༷

2019.01.26 :"1$5PLZP ใԸँಙ

2019.01.26 :"1$5PLZP ϩά

2019.01.26 :"1$5PLZP 9 ϩά ͍Ζ͍Ζ͋Δ ΞΫηεϩά )551αʔό FYOHJOY BQBDIF ͷϩά
ΞϓϦϩά 8FCΞϓϦέʔγϣϯ FY1FSM 1)1 3VCZ ͷϩά %#ϩά %# FY.Z42- 1PTUHSF42- ͷϩά

2019.01.26 :"1$5PLZP 10 ͳͥඞཁ͔ʁ αʔϏεʹԿ͕ى͖ͨͷ͔஌ΔͨΊ w σʔλ෼ੳ w ໰͍߹Θͤͷࣄ࣮֬ೝ w
ো֐ͷݪҼڀ໌ w ো֐ͷࣗಈݕ஌

2019.01.26 :"1$5PLZP 11 ϩάͷཁ݅ ੔߹ੑ ΞΫηεϩάʹ͸ϩά͕͋Δ͚ͲΞϓϦϩάʹϩά͕ͳ͍ͱ͔ ໢ཏੑ 8FCΞϓϦ಺ͷಛघͳ৚݅෼ذͰΞϓϦϩά͕࿙Εͯͨͱ͔ ௥੻༰қੑ ΞΫηεϩάͱΞϓϦϩάΛඥ෇͚Δखஈ͕ͳ͘೔࣌Ͱඥ෇͚Δ͔͠ͳ͍ͱ͔
ௐࠪͷͨΊʹΞΫηεϩάΛߴස౓Ͱݟʹߦ͔ͳ͍ͱ͍͚ͳ͍ͱ͔

2019.01.26 :"1$5PLZP 'JO5FDI

2019.01.26 :"1$5PLZP 13 ͱ͋ΔԾ૝௨՟ΞϓϦͷ։ൃ Ϣʔβͷࢿ࢈͸αʔόͰ؅ཧ͢Δߏ੒ %#ʹอଘ͞ΕΔσʔλͷ੔߹ੑ͕࠷༏ઌ αʔϏε͕ࢭ·ΔҎ্ʹσʔλͷෆ੔߹͕ා͍ Ճ͑ͯɺۚ༥γεςϜͱͯ͠ূ੻ΛٻΊΒΕΔ ϩά͸ͱͯ΋େࣄ

2019.01.26 :"1$5PLZP 14 ͱ͋ΔԾ૝௨՟ΞϓϦͷαʔόαΠυ αʔόαΠυ͸("&(PΛબ୒͕ͯͬͭ͠ΓΫϥ΢υ 4UBDL%SJWFSΛ࢖͑͹ϩάͷ੔߹ੑʹ͍ͭͯ͸͋·Γ৺഑ͳ͠ ৺഑ͳͷ͸໢ཏੑͱ௥੻༰қੑ

2019.01.26 :"1$5PLZP %#ઃܭ

2019.01.26 :"1$5PLZP 16 ߟ͑Δ͜ͱ ঢ়ଶΛ࣋ͬͨσʔλΛ%#ͰͲ͏ѻ͏͔ ঢ়ଶͷมԽΛϩάʹͲ͏ه࿥͢Δ͔ ྫɿϢʔβͷ೔ຊԁ࢒ߴɺຊਓ֬ೝεςʔλεɺϢʔβొ࿥৘ใ

2019.01.26 :"1$5PLZP 17 ঢ়ଶͷมԽͱϩά Α͋͘ΔྫɿΞΧ΢ϯτͷ৐ͬऔΓʢෆਖ਼ϩάΠϯʴ*%1BTTมߋʣ Ϣʔβ৘ใͷมߋཤྺΛௐࠪ͠ͳ͚Ε͹ͳΒͳ͍ Ϣʔβ৘ใͷมߋ͕Ͳ͔͜ͷϩάʹ࢒͍ͬͯͳ͚Ε͹ͳΒͳ͍

2019.01.26 :"1$5PLZP 18 ։ൃݱ৔ͷ%#ઃܭ΍͍͖ͬͯ

2019.01.26 :"1$5PLZP 19 Ϣʔβͷ೔ຊԁ࢒ߴ ೔ຊԁ࢒ߴ͸શϢʔβ͕ਓͭ࣋ͭ ࠷৽ͷԁ࢒ߴ͸Ϣʔβʹදࣔ͢Δ ೔ຊԁ࢒ߴ͸ೖۚɺग़ۚɺԾ૝௨՟ചങ͕ൃੜ͢ΔͱมԽ͢Δ

2019.01.26 :"1$5PLZP 20 Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶃ VTFS JE 1, VTFSOBNF
VTFS@CBMBODF VTFS@JE 1, BNPVOU VQEBUFE@BU Ϣʔβͷ࢒ߴͷཤྺʢ࢒ߴͷਪҠʣΛ؅ཧը໘ʹද͍ࣔͨ͠ͱ͖ ˠ࢒ߴมߋʹؔΘΔΤϯυϙΠϯτ΁ͷΞΫηεϩάΛࢀর͢Δʁ

2019.01.26 :"1$5PLZP 21 Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶄ VTFS JE 1, VTFSOBNF
VTFS@CBMBODF JE 1, VTFS@JE BNPVOU DSFBUFE@BU ߋ৽࣌͸มԽޙͷ৽͍͠࢒ߴΛ*/4&35ͯ͠ɺ࠷৽ͷ࢒ߴΛऔΔͱ͖͸ SELECT ~ WHERE user_id = hoge ORDER BY created_at LIMIT 1

2019.01.26 :"1$5PLZP 22 Ϣʔβͷ೔ຊԁ࢒ߴͷઃܭᶄ աڈͷ࢒ߴͷϨίʔυ͕େྔʹͰ͖ͯύϑΥʔϚϯε͸΍΍ݒ೦͕ͩ ؅ཧը໘ʹඞཁͳσʔλ͸อଘͰ͖͍ͯΔͷͰѱ͘͸ͳͦ͞͏ ଞ௨՟ͷ࢒ߴ΋VTFS@CBMBODFςʔϒϧͰ؅ཧ͢Δ͜ͱʹͳͬͨΒʁ

2019.01.26 :"1$5PLZP 23 Ϣʔβͷ࢒ߴ ࢒ߴ͸શϢʔβ͕௨՟ʹ͖ͭਓͭ࣋ͭ ௨՟ͷछผ͸ɺ+1:ɺ#5$ɺ&5)ͳͲ ࠷৽ͷ֤࢒ߴ͸Ϣʔβʹදࣔ͢Δ ࢒ߴ͸ೖۚɺग़ۚɺԾ૝௨՟ചങ͕ൃੜ͢ΔͱมԽ͢Δ

2019.01.26 :"1$5PLZP 24 Ϣʔβ࢒ߴͷઃܭᶃ VTFS JE 1, VTFSOBNF
VTFS@CBMBODF VTFS@JE 1, KQZ@BNPVOU CUD@BNPVOU VQEBUFE@BU ߋ৽࣌͸৽͍͠࢒ߴͰ61%"5& ௨՟ͷछผ͕૿͑Δͨͼʹ"-5&3จΛଧͭͷ͔ʁʢଧͪͨ͘ͳ͍'ʣ

2019.01.26 :"1$5PLZP 25 Ϣʔβ࢒ߴͷઃܭᶄ VTFS JE 1, VTFSOBNF
VTFS@CBMBODF VTFS@JE 1, DVSSFODZ@JE BNPVOU VQEBUFE@BU ೔ຊԁ࢒ߴͷͱ͖ͱಉ༷ʹɺ Ϣʔβͷ࢒ߴͷཤྺʢ࢒ߴͷਪҠʣΛ؅ཧը໘ʹද͍ࣔͨ͠

2019.01.26 :"1$5PLZP 26 Ϣʔβ࢒ߴͷઃܭᶅ VTFS JE 1, VTFSOBNF
VTFS@CBMBODF JE 1, VTFS@JE DVSSFODZ@JE BNPVOU DSFBUFE@BU ߋ৽࣌͸มԽޙͷ৽͍͠࢒ߴΛ*/4&35 Ͱ͸ɺϢʔβͷ࠷৽ͷ࢒ߴΛऔΔͱ͖͸ʜʁ

2019.01.26 :"1$5PLZP 27 Ϣʔβ࢒ߴͷઃܭᶅ Ϣʔβͷ࠷৽ͷ࢒ߴΛ௨՟ࢦఆͰऔಘ͍ͨ͠ͱ͖ SELECT ~ WHERE user_id =
hoge AND currency_id = jpy ORDER BY created_at LIMIT 1 Ϣʔβͷ࠷৽ͷ࢒ߴΛશ௨՟छผͰऔಘ͍ͨ͠ͱ͖ ˠΫΤϦͰ͍͘ͳΒαϒΫΤϦ஍ࠈ ˠ্ͷΫΤϦΛ௨՟छผ͝ͱʹ࣮ߦ

2019.01.26 :"1$5PLZP 28 Ϣʔβ࢒ߴͷઃܭᶆ VTFS JE 1, VTFSOBNF
VTFS@CBMBODF VTFS@JE 1, DVSSFODZ@JE 1, BNPVOU VQEBUFE@BU VTFS@CBMBODFʹ61%"5&ͷΫΤϦΛ࣮ߦ͢Δͱ͖ VTFS@CBMBODF@MPHʹ΄΅ಉ౳ͷϨίʔυΛ*/4&35͢Δ VTFS@CBMBODF@MPH JE 1, VTFS@JE DVSSFODZ@JE BNPVOU VQEBUFE@BU ෳ߹1,

2019.01.26 :"1$5PLZP 29 Ϣʔβ࢒ߴͷઃܭᶆ Ϣʔβͷ࠷৽ͷ࢒ߴΛ௨՟ࢦఆͰऔಘ͍ͨ͠ͱ͖ SELECT * FROM user_balance WHERE
user_id = hoge AND currency_id = jpy Ϣʔβͷ࠷৽ͷ࢒ߴΛશ௨՟छผͰऔಘ͍ͨ͠ͱ͖ SELECT * FROM user_balance WHERE user_id = hoge ؅ཧը໘ͰཤྺΛऔಘ͍ͨ͠ͱ͖ʢ௨՟ࢦఆʣ SELECT * FROM user_balance_log WHERE user_id = hoge AND currency_id = jpy

2019.01.26 :"1$5PLZP 30 ϩάςʔϒϧͷ෼཭ αʔϏεͰར༻͢ΔςʔϒϧͱϩάςʔϒϧΛ෼཭ͨ͠ σʔλมߋͷཤྺΛ֬ೝ͢ΔͨΊʹΞΫηεϩάࢀর͸ͭΒ͍ ΞΫηεϩά͸جຊͰ͔͍͠ݕࡧʹ΋͕͔͔࣌ؒΔ %#ϩάΛݟʹߦ͘ͷ΋ӡ༻తʹָͰ͸ͳ͍

2019.01.26 :"1$5PLZP 31 ϩάςʔϒϧͷಋೖํ਑ ϨίʔυΛߋ৽͢Δςʔϒϧ͸Ұ཯ϩάςʔϒϧΛ࡞Δํ਑ʹͨ͠ ঢ়ଶͷมԽΛؚΊσʔλͷมߋ͕શ෦ςʔϒϧʹ࢒Δ ˠશ෦࢒Δɻ໢ཏੑͷ޲্ ˠςʔϒϧʹ࢒ΔͷͰௐࠪ͠΍͍͢ɻ௥੻༰қੑͷ޲্

2019.01.26 :"1$5PLZP 32 ϩάςʔϒϧͷઃܭ

2019.01.26 :"1$5PLZP 33 ϝΠϯςʔϒϧͷΧϥϜ໊ʹॏෳ͠ͳ໋໊͍ IPHF JE 1, IPHF VQEBUFE@BU
IPHF@MPH MPH@JE 1, JE IPHF VQEBUFE@BU MPHHFE@BU ϩάςʔϒϧͷ໋໊نଇɺڞ௨ΧϥϜ໊͸౷ҰͰ͖ΔΑ͏ʹ

2019.01.26 :"1$5PLZP 34 Ϩίʔυ࡟আ΁ͷରԠ IPHF JE 1, IPHF VQEBUFE@BU
IPHF@MPH MPH@JE 1, JE IPHF VQEBUFE@BU MPHHFE@BU IPHFͷϨίʔυΛ࡟আͨ͠ͱ͖IPHF@MPHʹͲΜͳϨίʔυ͕ೖΕ͹Α͍͔

2019.01.26 :"1$5PLZP 35 Ϩίʔυ࡟আϑϥάͷ௥Ճ IPHF JE 1, IPHF VQEBUFE@BU
JT@EFMFUFE IPHF@MPH MPH@JE 1, JE IPHF VQEBUFE@BU MPHHFE@BU JT@EFMFUF@MPH ෺ཧ࡟আ૝ఆ͕ͩɺສ͕Ұͷ࿦ཧ࡟আʹ΋ରԠͰ͖ΔΑ͏ʹϑϥάΛ໋໊

2019.01.26 :"1$5PLZP 36 ϩάςʔϒϧͷ࠷ऴઃܭ IPHF JE 1, IPHF VQEBUFE@BU
IPHF@MPH MPH@JE 1, JE IPHF VQEBUFE@BU MPHHFE@BU JT@EFMFUF@MPH

2019.01.26 :"1$5PLZP 37 ࠷ऴઃܭͷײ૝ ϝϦοτ ΍Γ͍ͨ͜ͱʹରͯ͠ΫΤϦ͕ૉ௚ʹͳΔ มߋཤྺʹ͍ͭͯߟ͑ͳͯ͘Α͍ ෺ཧ࡟আ࿦ཧ࡟আͰ೰·ͳͯ͘Α͍ σϝϦοτ ࡞੒ߋ৽࡟আܥΫΤϦͰ࣮ߦΫΤϦ͕Ұͭ૿͑ΔʢཁτϥϯβΫγϣϯʣ
ϩάςʔϒϧʹϨίʔυ͕ͲΜͲΜཷ·͍ͬͯ͘

2019.01.26 :"1$5PLZP 38 ϩάςʔϒϧංେԽ໰୊ ϩάςʔϒϧʹϨίʔυ͕ͲΜͲΜཷ·͍ͬͯ͘ ˠ%#Λຊ൪༻ͱϩάςʔϒϧ༻Ͱ෼͚Δʁ ˠύʔςΟγϣχϯάͯ͠ఆظతʹຊ൪%#͔ΒऔΓग़͢ʁ

2019.01.26 :"1$5PLZP 39 ϩάςʔϒϧ༻ʹ%#෼ׂ͢Δ͔ʁ ӡ༻Λߟ͑ͨΒ%#෼ׂͨ͘͠ͳΔ ͔͠͠ϩάςʔϒϧʹ͸τϥϯβΫγϣϯඞਢ ˠ෼ׂ͢Δͱ෼ࢄτϥϯβΫγϣϯ໰୊͕ݦࡏԽ 4QBOOFSͷಉҰϓϩδΣΫτͰΠϯελϯε෼͚Δͷ͕Αͦ͞͏ʁ .Z42-ͳΒૉ௚ʹύʔςΟγϣχϯά͢Δͷ͕Αͦ͞͏

2019.01.26 :"1$5PLZP 40 %BUBTUPSF΋ซ༻͍ͯ͘͠ελΠϧ ֎෦"1*͔ΒԾ૝௨՟Ձ֨Λऔಘͯ͠อଘ͢Δ औಘස౓͸ۃΊͯ୹͍ ʙඵʹճ ͷͰϨίʔυ਺΋๲େ .Z42-ͳΒ%#෼ׂ͢ΔPS%BUBTUPSF͕Αͦ͞͏

2019.01.26 :"1$5PLZP 41 ࠓ೔఻͔͑ͨͬͨ͜ͱ

2019.01.26 :"1$5PLZP 42 ࠓ೔఻͔͑ͨͬͨ͜ͱ ϩάςʔϒϧΛ༻ҙ͢ΔઃܭͲ͏ʁ ϩάʹ΍ͯ͘͞͠ӡ༻ͷ؍఺͔ΒݟΔͱྑ͍ͱߟ͍͑ͯΔ ݱঢ়ɺ͜ΕҎ্ͷઃܭΛࢥ͍͔ͭͳ͔ͬͨ ͜ͷ৔ΛआΓͯઃܭϨϏϡʔ͕͍ͨ͠ Έͳ͞·ͷ͝ҙݟΛ͓଴͍ͪͯ͠·͢

2019.01.26 :"1$5PLZP 5IBOLT

YAPC::Tokyo 2019 Log Friendly DB Design

YAPC::Tokyo 2019 Log Friendly DB Design

Other Decks in Programming

Featured

Transcript