実践ハイパフォーマンスMySQL読書会第1回資料

ୈճ࣮ફϋΠύϑΥʔϚϯε.Z42-ಡॻձ ୈҰষʮ.Z42-ͷΞʔΩςΫνϟͱྺ࢙ʯ ੢ඌ৻༞ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺

ষͷ֓ཁ •  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

ฒߦੑͷ੍ޚ •  αʔόϨϕϧͱετϨʔδΤϯδϯϨϕϧͷೋͭͰ ੍ޚ͢Δඞཁ͋Γ –  ฒߦੑͷ੍ޚ͸ॏཁͳςʔϚ͕ͩɺৄࡉ͸औΓ্͛ͳ͍ •  ϩοΫ –  ಡΈऔΓϩοΫ(ڞ༗ϩοΫ)
–  ॻ͖ࠐΈϩοΫ(ഉଞϩοΫ) •  ϩοΫͷཻ౓ –  ςʔϒϧϩοΫ •  ΋ͬͱ΋Φʔόʔϔου͕௿͍ –  ߦϩοΫ •  ฒߦੑ͕࠷΋ߴ͘ɺΦʔόʔϔου΋࠷΋ߴ͍ •  αʔόͰͳ͘ετϨʔδΤϯδϯͰ࣮૷͞Ε͍ͯΔ –  InnoDB΍XtraDBͳͲͷετϨʔδΤϯδϯͰར༻Մೳ 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ͭͷτϥϯβΫγϣϯΛுΔࣄ͸ෆՄೳ •  ҉໧తͳϩοΫͱ໌ࣔతͳϩοΫ –  InnoDB͸2૬ͷϩοΫϓϩτίϧΛར༻ •  τϥϯβΫγϣϯத͸͍ͭͰ΋ϩοΫऔಘՄೳ͕ͩɺCOMMIT, ROLLBACK͢Δ·Ͱղ๏͞Εͳ͍ •  ϩοΫ͸͢΂ͯͷϩοΫΛಉ࣌ʹղ์͢Δ –  ໌ࣔతͳϩοΫ ( SELECT … FOR UPDATE౳) 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 12 / 27

Ϛϧνόʔδϣϯͷฒߦੑ੍ޚ .7$$ •  MySQLͷετϨʔδΤϯδϯͷ΄ͱΜͲ͸ߦϩοΫΛ ࢖Θͳ͍ •  ߦϩοΫͱMVCCΛ૊Έ߹Θͤͯ࢖༻͍ͯ͠Δ – 
MVCC͸Oracle, PostgreSQL ͳͲͰ΋࣮૷͞Ε͍ͯΔ •  MVCC͸REPEATABLE 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 ͕ͱΕΔ

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. ࡟আόʔδϣϯ͸ະఆٛͰ͋Δ͔ɺτϥϯ βΫγϣϯͷόʔδϣϯΑΓ΋৽͍͠όʔ δϣϯͰແ͚Ε͹ͳΒͳ͍ ϊϯϦϐʔλϒϧϦʔυ͕ൃੜ͠ͳ͍!!

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Ͱ֬ೝ

4)085"#-&45"564 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 21 / 27

*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࢖ͬͯΔਓ͍ͨΒ࿩͖͖͍ͨɺҰମԿʹ࢖͏ͷ͔?

ਖ਼͍͠ετϨʔδΤϯδϯͷબ୒ ʮInnoDBʹͳ͍ػೳ͕ඞཁͰ͋ΓɺInnoDBʹ͸Α͍୅ସࡦ ͕ແ͍ͱ͍͏৔߹Ͱͳ͍ݶΓɺInnoDBΛ࢖༻͢Δʯ •  ʮMyISAM͸InnoDBΑΓ΋ߴ଎Ͱ͋ΔʯΛӏವΈʹ͠ͳ͍ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺
25 / 27

ςʔϒϧͷม׵ •  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

.Z42-ͷྺ࢙ •  1.6 ࢀর 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 27
/ 27

実践ハイパフォーマンスMySQL読書会第1回資料

実践ハイパフォーマンスMySQL読書会第1回資料

S.Nishio

More Decks by S.Nishio

Other Decks in Education

Featured

Transcript

ୈճ࣮ફϋΠύϑΥʔϚϯε.Z42-ಡॻձ ୈҰষʮ.Z42-ͷΞʔΩςΫνϟͱྺ࢙ʯ ੢ඌ৻༞ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺

ষͷ֓ཁ •  MySQLΛ࠷େݶʹ׆༻͢ΔͨΊʹ –  MySQLͷઃܭΛཧղ͢Δࣄ͕ॏཁ •  MySQLαʔόΞʔΩςΫνϟΛ၆ᛌతʹଊ͑Δ –  ετϨʔδΤϯδϯ͝ͱͷҧ͍͸Կ͔?

.Z42-ͷ࿦ཧΞʔΩςΫνϟ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 3 / 27

ฒߦੑͷ੍ޚ •  αʔόϨϕϧͱετϨʔδΤϯδϯϨϕϧͷೋͭͰ ੍ޚ͢Δඞཁ͋Γ –  ฒߦੑͷ੍ޚ͸ॏཁͳςʔϚ͕ͩɺৄࡉ͸औΓ্͛ͳ͍ •  ϩοΫ –  ಡΈऔΓϩοΫ(ڞ༗ϩοΫ)

τϥϯβΫγϣϯ"$*% •  ΞτϛοΫੑ(Atomicity) –  All or nothing

෼཭Ϩϕϧ •  ෼཭ੑͷϨϕϧΛઃఆͰ͖Δ –  Ϩϕϧ͕௿͍΄Ͳฒߦੑ͕ߴ͘ͳΓΦʔόʔϔου͕ݮΔ •  READ UNCOMMITTED –

3&"%$0..*55&%ͷ໰୊ •  ϊϯϦϐʔλϒϧϦʔυ / ϑΝϯτϜϦʔυ͕ൃੜ •  ϊϯϦϐʔλϒϧϦʔυͱ͸? 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@

3&1&"5"#-&3&"%ͷ໰୊ •  ϑΝϯτϜϦʔυ͕ൃੜ –  ˞ MySQLͰ͸ʢผͷ࢓૊ΈʹΑΓʣϑΝϯτϜϦʔυ͸ൃੜ͠ͳ͍ •  ϑΝϯτϜϦʔυͱ͸?

"/4*42-෼཭Ϩϕϧ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 9 / 27

σουϩοΫ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 10 / 27

ςʔϒϧΛ௚઀ߋ৽͢Δͱ ϥϯμϜI/O͕ൃੜ τϥϯβΫγϣϯϩά 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 11 /

.Z42-ͷτϥϯβΫγϣϯ •  σϑΥϧτ͸AUTO COMMIT –  τϥϯβΫγϣϯΛ໌ࣔ͠ͳ͍ݶΓɺͦΕͧΕผͷ τϥϯβΫγϣϯͱ࣮ͯ͠ߦ͞ΕΔ •

Ϛϧνόʔδϣϯͷฒߦੑ੍ޚ .7$$ •  MySQLͷετϨʔδΤϯδϯͷ΄ͱΜͲ͸ߦϩοΫΛ ࢖Θͳ͍ •  ߦϩοΫͱMVCCΛ૊Έ߹Θͤͯ࢖༻͍ͯ͠Δ –

.7$$ͱ͸ •  MVCCΛ࢖͏ͱɺෳ਺τϥϯβΫγϣϯ͕ಉ͡ςʔϒϧ ಺ͷҟͳΔσʔλΛಉ࣌ʹࢀরͰ͖Δ •  InnoDBͰ͸ɺߦ͝ͱʹӅΕͨೋͭͷ஋Λ֨ೲ –  ߦ͕࡞੒͞ΕͨλΠϛϯά

.7$$ͱ͸r*OTFSU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 15 / 27

.7$$ͱ͸r6QEBUF࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 16 / 27

.7$$ͱ͸r4FMFDU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 17 /

.7$$ͱ͸r4FMFDU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 18 /

.7$$ͱ͸r4FMFDU࣌ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 19 /

.Z42-ͷετϨʔδΤϯδϯ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 20 / 27

4)085"#-&45"564 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 21 / 27

*OOP%#Τϯδϯ •  ྺ࢙͸লུ •  InnoDB֓ཁ –  MVCCΛ࢖͍ߴ͍ฒߦੑΛ࣮ݱɺ෼཭Ϩϕϧ΋શ࣮ͯ૷ –  σϑΥϧτͰ͸REPEATABLE

.Z*4".Τϯδϯ •  શจΠϯσοΫεɺѹॖɺGISؔ਺౳Λఏڙ •  τϥϯβΫγϣϯ͸ߦϨϕϧͰ͸αϙʔτ͍ͯ͠ͳ͍ •  ΫϥογϡηʔϑͰ͸ͳ͍ •  χονͳχʔζΛຬͨͨ͢Ίͷػೳ͕৭ʑ͋ΔΒ͍͠ •

ͦͷଞͷ૊ΈࠐΈΤϯδϯ •  Archive •  Blackhole •  CSV

ਖ਼͍͠ετϨʔδΤϯδϯͷબ୒ ʮInnoDBʹͳ͍ػೳ͕ඞཁͰ͋ΓɺInnoDBʹ͸Α͍୅ସࡦ ͕ແ͍ͱ͍͏৔߹Ͱͳ͍ݶΓɺInnoDBΛ࢖༻͢Δʯ •  ʮMyISAM͸InnoDBΑΓ΋ߴ଎Ͱ͋ΔʯΛӏವΈʹ͠ͳ͍ 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺

ςʔϒϧͷม׵ •  ALTER TABLEͰςʔϒϧม׵ –  > ALTER TABLE mytable ENGINE

.Z42-ͷྺ࢙ •  1.6 ࢀর 2014/05/22 ࣮ફϋΠύϑΥʔϚϯεMySQLಡॻձ@ TIMࣾ಺ 27

実践ハイパフォーマンスMySQL読書会 第1回資料

実践ハイパフォーマンスMySQL読書会 第1回資料

More Decks by S.Nishio

Other Decks in Education

Featured

Transcript

実践ハイパフォーマンスMySQL読書会第1回資料

実践ハイパフォーマンスMySQL読書会第1回資料