Share
Embed
Transcript
27 slides
-
ষͷ֓ཁ • MySQLΛ࠷େݶʹ׆༻͢ΔͨΊʹ – MySQLͷઃܭΛཧղ͢Δࣄ͕ॏཁ • MySQLαʔόΞʔΩςΫνϟΛ၆ᛌతʹଊ͑Δ – ετϨʔδΤϯδϯ͝ͱͷҧ͍Կ͔?
– ͦ͏ͨ͠ҧ͍͕ͳͥॏཁͳͷ͔? • MySQLྺ࢙తͳഎܠͱϕϯνϚʔΫʹ͍ͭͯ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 2 / 27 -
.Z42-ͷཧΞʔΩςΫνϟ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 3 / 27
ଓ / εϨουॲཧ ΫΤϦ Ωϟογϡ ύʔαʔ ΦϓςΟϚΠβ ΫϥΠΞϯτ ετϨʔδΤϯδϯ MySQLಛ༗ͷͷͰͳ͍αʔϏεؚΉ ଓॲཧɺೝূɺηΩϡϦςΟ MySQLͷத෦ ΫΤϦղੳɺੳɺ࠷దԽɺΩϟογϡɺ ΈࠐΈؔ(ɺ࣌ؒɺࢉज़ԋࢉɺ ҉߸Խ) ͷίʔυؚ͕·ΕΔ ετΞυɺτϦΨɺϏϡʔ͜ͷϨϕϧ ʹଐ͍ͯ͠Δ MySQLʹ֨ೲ͞ΕΔͯ͢ͷσʔλ อଘɺऔಘΛड͚࣋ͭ αʔόετϨʔδΤϯδϯAPIΛ௨͡ ͯΓͱΓΛ͢Δ ϨΠϠ1 ϨΠϠ2 ϨΠϠ3 -
ฒߦੑͷ੍ޚ • αʔόϨϕϧͱετϨʔδΤϯδϯϨϕϧͷೋͭͰ ੍ޚ͢Δඞཁ͋Γ – ฒߦੑͷ੍ޚॏཁͳςʔϚ͕ͩɺৄࡉऔΓ্͛ͳ͍ • ϩοΫ – ಡΈऔΓϩοΫ(ڞ༗ϩοΫ)
– ॻ͖ࠐΈϩοΫ(ഉଞϩοΫ) • ϩοΫͷཻ – ςʔϒϧϩοΫ • ͬͱΦʔόʔϔου͕͍ – ߦϩοΫ • ฒߦੑ͕࠷ߴ͘ɺΦʔόʔϔου࠷ߴ͍ • αʔόͰͳ͘ετϨʔδΤϯδϯͰ࣮͞Ε͍ͯΔ – InnoDBXtraDBͳͲͷετϨʔδΤϯδϯͰར༻Մೳ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 4 / 27 ಡΈऔΓྃ·ͰσʔλΛมߋ͠ͳ͍ͰͶ ϑϥάΛཱͯΔ Ϧιʔεͷಉ࣌ߋ৽Λ͙ͨΊͷϩοΫ -
τϥϯβΫγϣϯ"$*% • ΞτϛοΫੑ(Atomicity) – All or nothing
– τϥϯβΫγϣϯશମͷॲཧ͕ͯྃ͢͢Δ͔/͠ͳ͍͔ • Ұ؏ੑ (Consistency) – Ұ؏ͨ͠ঢ়ଶ͔ΒҰ؏ͨ͠ঢ়ଶͷભҠͱͳΔ͜ͱ • τϥϯβΫγϣϯ్தͰΫϥογϡͯ͠ɺDBͷมߋ͕ແ͍͜ͱ • ੑ(Isolation) – τϥϯβΫγϣϯ్த݁ՌଞτϥϯβΫγϣϯ͔Βݟ͑ͳ͍͜ͱ • Ӭଓੑ(Durability) – ίϛοτ͞ΕͨτϥϯβΫγϣϯͷมߋ֬ఆ͞ΕΔ͜ͱ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 5 / 27 -
Ϩϕϧ • ੑͷϨϕϧΛઃఆͰ͖Δ – Ϩϕϧ͕͍΄Ͳฒߦੑ͕ߴ͘ͳΓΦʔόʔϔου͕ݮΔ • READ UNCOMMITTED –
ίϛοτ͞Ε͍ͯͳ͍τϥϯβΫγϣϯͷ݁Ռ͕ଞτϥϯβ Ϋγϣϯ͔Βݟ͑Δ • READ COMMITTED (ଟ͘ͷDBͷσϑΥϧτ) – ৗʹ࠷৽ͷίϛοτࡁΈσʔλΛಡΈऔΔ • REPEATABLE READ (MySQLσϑΥϧτ) – τϥϯβΫγϣϯ࣮ߦதɺಡΈऔΓରͷσʔλ͕ ଞτϥϯβΫγϣϯʹΑͬͯมߋ͞Εͳ͍ • SERIALIZABLE – ϊϯϦϐʔλϒϧϦʔυɺϑΝϯτϜϦʔυͷ͕ ൃੜ͠ͳ͍ – σʔλͷ҆ఆੑ֬อ͞ΕΔ͕ɺฒߦੑԼ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 6 / 27 Ϩϕϧߴ Ϩϕϧ -
3&"%$0..*55&%ͷ • ϊϯϦϐʔλϒϧϦʔυ / ϑΝϯτϜϦʔυ͕ൃੜ • ϊϯϦϐʔλϒϧϦʔυͱ? 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@
TIMࣾ 7 / 27 Transaction A Start; 1. A ͞ΜͷߴΛSELECT (100ԁ) 3. A͞ΜͷߴΛ200ԁʹ͢Δ 5. COMMIT; Transaction B Start; 2. A ͞ΜͷߴΛSELECT (100ԁ) 4. A͞ΜͷߴΛSELECT (100ԁ) 6. A͞ΜͷߴΛSELECT (200ԁ!!) 7. COMMIT; ৗʹίϛοτࡁΈͷ࠷৽σʔλΛ ಡΈऔΔͨΊൃੜ -
3&1&"5"#-&3&"%ͷ • ϑΝϯτϜϦʔυ͕ൃੜ – ˞ MySQLͰʢผͷΈʹΑΓʣϑΝϯτϜϦʔυൃੜ͠ͳ͍ • ϑΝϯτϜϦʔυͱ?
2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 8 / 27 Transaction A Start; 1. Select * from users; (A͞Μ) 3. UsersςʔϒϧʹB͞ΜՃ 5. COMMIT; Transaction B Start; 2. Select * from users; (A͞Μ) 4. Select * from users; (A͞Μ) 6. Select * from users; (A͞ΜɺB͞Μ) ͖ͬ͞·Ͱ͍ͳ͔ͬͨB͞Μ͕ ಥવݱΕͨ -
"/4*42-Ϩϕϧ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 9 / 27
Ϩϕϧ μʔςΟʔ Ϧʔυ ϊϯϦϐʔλ ϒϧϦʔυ ϑΝϯτϜ Ϧʔυ ಡΈऔΓϩο Ϋ READ UNCOMMITTED YES YES YES NO READ COMMITTED NO YES YES NO REPEATABLE READ NO NO YES NO SERIARIZABLE NO NO NO YES -
σουϩοΫ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 10 / 27
A͞Μͷ༬ۚ B͞Μͷ༬ۚ Transaction A Transaction B ߋ৽͢ΔͨΊϩοΫ ղআͪ ߋ৽͢ΔͨΊϩοΫ ղআͪ Transaction A/B A, B͞Μͷ༬ۚใΛߋ৽͍ͨ͠ -
ςʔϒϧΛߋ৽͢Δͱ ϥϯμϜI/O͕ൃੜ τϥϯβΫγϣϯϩά 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 11 /
27 ͜͜ͰΫϥογϡͯ͠ɺτϥϯβΫγϣϯϩά͔Β෮چՄೳ • τϥϯβΫγϣϯޮԽʹཱͭ • τϥϯβΫγϣϯϩάΛͬͨϩάઌߦॻ͖ࠐΈ 1. σΟεΫ্ͷςʔϒϧΛߋ৽͢ΔͷͰͳ͘ɺϝϞϦ্ͷ ίϐʔΛมߋ͢Δ • ඇৗʹߴͳॻ͖ࠐΈ͕Մೳ 2. ετϨʔδΤϯδϯมߋهΛτϥϯβΫγϣϯϩάʹॻ͖ ࠐΉ • σΟεΫ্ͷσʔλͳͷͰӬଓੑ͋Γ • γʔέϯγϟϧͳI/O͕༻͞ΕΔ (ൺֱతߴʹߋ৽͞ΕΔ) 3. λΠϛϯάΛݟܭΒͬͯɺσΟεΫ্ͷςʔϒϧΛߋ৽ มߋΛσΟεΫʹ2ճॻ͖ࠐΉ͜ͱʹͳΔ -
.Z42-ͷτϥϯβΫγϣϯ • σϑΥϧτAUTO COMMIT – τϥϯβΫγϣϯΛ໌ࣔ͠ͳ͍ݶΓɺͦΕͧΕผͷ τϥϯβΫγϣϯͱ࣮ͯ͠ߦ͞ΕΔ •
ෳετϨʔδΤϯδϯͷซ༻ – τϥϯβΫγϣϯαʔόϨϕϧͰཧͤͣɺ࣮ ετϨʔδΤϯδϯʹ͞Ε͍ͯΔ – ෳΤϯδϯؒͰ1ͭͷτϥϯβΫγϣϯΛுΔࣄෆՄೳ • ҉తͳϩοΫͱ໌ࣔతͳϩοΫ – InnoDB2૬ͷϩοΫϓϩτίϧΛར༻ • τϥϯβΫγϣϯத͍ͭͰϩοΫऔಘՄೳ͕ͩɺCOMMIT, ROLLBACK͢Δ·Ͱղ๏͞Εͳ͍ • ϩοΫͯ͢ͷϩοΫΛಉ࣌ʹղ์͢Δ – ໌ࣔతͳϩοΫ ( SELECT … FOR UPDATE) 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 12 / 27 -
Ϛϧνόʔδϣϯͷฒߦੑ੍ޚ .7$$ • MySQLͷετϨʔδΤϯδϯͷ΄ͱΜͲߦϩοΫΛ Θͳ͍ • ߦϩοΫͱMVCCΛΈ߹Θͤͯ༻͍ͯ͠Δ –
MVCCOracle, PostgreSQL ͳͲͰ࣮͞Ε͍ͯΔ • MVCCREPEATABLE READ, READ COMMITTEDʹͷΈରԠ • MySQL REPEATABLE READͰϑΝϯτϜϦʔυ͕ൃੜ͠ͳ ͍ͷɺMVCCͷ͓͔͛ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 13 / 27 -
.7$$ͱ • MVCCΛ͏ͱɺෳτϥϯβΫγϣϯ͕ಉ͡ςʔϒϧ ͷҟͳΔσʔλΛಉ࣌ʹࢀরͰ͖Δ • InnoDBͰɺߦ͝ͱʹӅΕͨೋͭͷΛ֨ೲ – ߦ͕࡞͞ΕͨλΠϛϯά
– ߦ͕ظݶΕ(আ͞ΕͨʣλΠϛϯά • ্هʹೖΕΔɺ֤Πϕϯτ͕ൃੜͨ࣌͠ͷγεςϜ όʔδϣϯ൪߸ – τϥϯβΫγϣϯ͕։࢝͞ΕΔͨͼʹ൪߸ΠϯΫϦϝϯτ͞ ΕΔ – ΫΤϦͦΕͧΕ֤ߦͷόʔδϣϯ൪߸ΛτϥϯβΫγϣϯͷ όʔδϣϯͱൺֱ͢Δඞཁ͕͋Δ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 14 / 27 -
.7$$ͱr*OTFSU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 15 / 27
ߦA ߦB ߦC -‐ 1 1 1 Ճόʔδϣϯ আόʔδϣϯ -‐ -‐ ߦD 2 -‐ τϥϯβΫγϣϯA (version: 2) ͕σʔλD ΛՃ Trn A InnoDBݱࡏͷγεςϜ όʔδϣϯ൪߸(2) Λ৽͍͠ߦʹه͢Δ -
.7$$ͱr6QEBUF࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 16 / 27
ߦA ߦB ߦC -‐ 1 1 1 Ճόʔδϣϯ আόʔδϣϯ -‐ -‐ ߦD2 3 -‐ τϥϯβΫγϣϯA (version: 3) ͕σʔλD Λमਖ਼ ʢσʔλΛߦD͔ΒߦD2 ʹߋ৽) Trn A ৽͍͠ߦͷόʔδϣϯʹରԠ͢Δ γεςϜόʔδϣϯ൪߸Λͬͯɺ ߦͷ৽͍͠ίϐʔΛॻ͖ग़͢ ݹ͍ߦͷআόʔδϣϯͱͯ͠γ εςϜόʔδϣϯ൪߸Λॻ͖ग़͢ ߦD 2 3 -
.7$$ͱr4FMFDU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 17 /
27 ߦA ߦB ߦC -‐ 1 1 1 Ճόʔδϣϯ আόʔδϣϯ -‐ -‐ ߦD2 3 -‐ τϥϯβΫγϣϯA (version: 4) ͕SELECT * from table; Trn A 1. τϥϯβΫγϣϯͱಉ͔͡ɺͦΕҎ લͷόʔδϣϯߦΛݕࡧ͠ͳ͚Εͳ Βͳ͍ 2. আόʔδϣϯະఆٛͰ͋Δ͔ɺ τϥϯβΫγϣϯͷόʔδϣϯΑΓ ৽͍͠όʔδϣϯͰແ͚ΕͳΒͳ͍ ্ه2ͭΛݟͨͨ͠σʔλΛฦ͢ ߦD 2 3 σʔλɺA, B, C, D2 ͕ͱΕΔ -
.7$$ͱr4FMFDU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 18 /
27 ߦA ߦB ߦC -‐ 1 1 1 Ճόʔδϣϯ আόʔδϣϯ -‐ -‐ ߦD2 3 -‐ τϥϯβΫγϣϯA (version: 2) ͕SELECT * from table; Aͷ్தͰτϥϯβΫγϣϯB(version:3 )͕σʔλߋ৽, COMMIT Trn A ߦD 2 3 σʔλɺA, B, C, D ͕ͱΕΔ Trn B ߦDΛߦD2ʹߋ৽ 1. τϥϯβΫγϣϯͱಉ͔͡ɺͦΕҎલͷ όʔδϣϯߦΛݕࡧ͠ͳ͚ΕͳΒͳ͍ 2. আόʔδϣϯະఆٛͰ͋Δ͔ɺτϥϯ βΫγϣϯͷόʔδϣϯΑΓ৽͍͠όʔ δϣϯͰແ͚ΕͳΒͳ͍ ϊϯϦϐʔλϒϧϦʔυ͕ൃੜ͠ͳ͍!! -
.7$$ͱr4FMFDU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 19 /
27 ߦA ߦB ߦC -‐ 1 1 1 Ճόʔδϣϯ আόʔδϣϯ -‐ -‐ τϥϯβΫγϣϯA (version: 2) ͕SELECT * from table; Aͷ్தͰτϥϯβΫγϣϯB(version:3 )͕σʔλInsert, COMMIT Trn A ߦD 3 -‐ σʔλɺA, B, C ͕ͱΕΔ Trn B ߦDΛInsert 1. τϥϯβΫγϣϯͱಉ͔͡ɺͦΕҎલͷ όʔδϣϯߦΛݕࡧ͠ͳ͚ΕͳΒͳ͍ 2. আόʔδϣϯະఆٛͰ͋Δ͔ɺτϥϯ βΫγϣϯͷόʔδϣϯΑΓ৽͍͠όʔ δϣϯͰແ͚ΕͳΒͳ͍ ϑΝϯτϜϦʔυ͕ൃੜ͠ͳ͍!! -
.Z42-ͷετϨʔδΤϯδϯ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 20 / 27
• ֤σʔλϕʔεΛͦͷσʔλσΟϨΫτϦͷαϒσΟϨ ΫτϦͱͯ͠ϑΝΠϧγεςϜʹ֨ೲ • ςʔϒϧ࡞ͷࡍɺ.frm֦ுࢠ͕͍ͭͨϑΝΠϧʹςʔ ϒϧఆٛΛอଘ • ςʔϒϧʹؔ͢ΔใɺSHOW TABLE STATUSͰ֬ೝ -
*OOP%#Τϯδϯ • ྺ࢙লུ • InnoDB֓ཁ – MVCCΛ͍ߴ͍ฒߦੑΛ࣮ݱɺϨϕϧશ࣮ͯ – σϑΥϧτͰREPEATABLE
READϨϕϧ • ϑΝϯτϜϦʔυΛ્ࢭ͢ΔωΫετΩʔϩοΫΛద༻ – ςʔϒϧΫϥελԽΠϯσοΫεʹج͍ͮͯߏங͞ΕΔ • ΫϥελԽΠϯσοΫε5ষʹͯͰͯ͘Δ • ΠϯσοΫεʹσʔλ͕͍ͬͭͨ͘ͷ – ηΧϯμϦΠϯσοΫεʹओΩʔؚ͕·ΕΔͷͰɺओΩʔ͕େ ͖͍߹ΠϯσοΫε͕େ͖͘ͳΔ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 22 / 27 -
.Z*4".Τϯδϯ • શจΠϯσοΫεɺѹॖɺGISؔΛఏڙ • τϥϯβΫγϣϯߦϨϕϧͰαϙʔτ͍ͯ͠ͳ͍ • ΫϥογϡηʔϑͰͳ͍ • χονͳχʔζΛຬͨͨ͢Ίͷػೳ͕৭ʑ͋ΔΒ͍͠ •
σʔλετϨʔδ͕ίϯύΫτɺ͔ͭγϯϓϧͰ Φʔόʔϔου͕গͳ͍ – ༻్ʹΑͬͯ༏ΕͨύϑΥʔϚϯεΛୡͰ͖Δ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 23 / 27 -
ͦͷଞͷΈࠐΈΤϯδϯ • Archive • Blackhole • CSV
• Federated • Memory • Merge • NDB Cluster ͳͲͳͲ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 24 / 27 ݸਓతͳײ BlackholeͬͯΔਓ͍ͨΒ͖͖͍ͨɺҰମԿʹ͏ͷ͔? -
ςʔϒϧͷม • ALTER TABLEͰςʔϒϧม – > ALTER TABLE mytable ENGINE
= InnoDB; – ͜ͷίϚϯυ͕͔͔࣌ؒΔՄೳੑ͕͋Δ – ετϨʔδΤϯδϯݻ༗ͷػೳফ͑Δ • InnoDB -‐> MyISAM -‐> InnoDBͱม͢Δͱɺ࠷ॳʹఆٛ͞Ε͍ͯͨ ֎෦Ωʔͯ͢ফ͑Δ • MysqldumpͰม – μϯϓͯ͠ɺCREATE TABLEΛௐઅ͢Δ • CREATE SELECTͰม – > CREATE TABLE innodb_table LIKE myisam_table; – > ALTER TABLE innodb_table ENGINE=InnoDB; – > INSERT INTO innodb_table SELECT * from myisam_table; 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ 26 / 27