MySQL INDEX+EXPLAIN入門

.Z42- */%&9ʴ&91-"*/ೖ໳ גࣜձࣾΠϯϑΟχοτϧʔϓ PUB ࡞੒ɿɹ࠷ऴߋ৽ɿ

͜ͷεϥΠυͷ಺༰ .Z42-*OOP%#Λલఏͱͨ͠*/%&9ͱ&91-"*/ͷॳڃฤ

໨࣍ */%&9Λ࢖͓͏ */%&9ͱ͸Կ͔ */%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ */%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ &91-"*/Λ࢖͓͏ &91-"*/ͱ͸Կ͔ &91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ &91-"*/ग़ྗ݁Ռ͔ΒͰ͖Δ42-νϡʔχϯά

*/%&9ͱ͸Կ͔ ςʔϒϧʹ͓͚ΔຊͷࡧҾͷΑ͏ͳ΋ͷ ˞ࡧҾɿຊͷதͷޠ۟΍ࣄ߲ͳͲΛҰఆͷॱংʢ͍͋͏͓͑ॱ౳ʣʹฒ΂ɺͦͷޠ۟΍ࣄ ߲ͷ͋ΔϖʔδΛࣔͨ͠ද ࡧҾΛ࢖Θͣʹ໨తͷϖʔδΛϖʔδ໨͔Βॱ൪ʹ୳͢ ʢΠϯσοΫεΛ࢖༻͠ͳ͍4&-&$5ʣ ˠ஗͍ ࡧҾΛ࢖ͬͯ໨తͷϖʔδΛ୳͢ ʢΠϯσοΫεΛ࢖༻ͨ͠4&-&$5ʣ ˠૣ͍
˞ͨͩ͠୯७ͳݕࡧͷ৔߹ɺ ϨίʔυҎԼͳΒΠϯσοΫε͕ͳ͍ํ͕଎͍͜ͱ΋

*/%&9ͱ͸Կ͔ 4&-&$5଎౓ൺֱ 0 500 1,000 1,500 2,000 2,500 1 101
201 300 ϛϦඵ ສϨίʔυ ৚߲݅໨΁ͷΠϯσοΫε༗Γ ແ͠Ͱ4&-&$5ʹ͔͔Δ࣌ؒ ͷҧ͍Λௐ΂ͯΈΔ 4&-&$5 '30.UFTU@UCM8)&3& DPM@ ˞ճຖͷฏۉ஋ ΠϯσοΫεΛ࢖༻͢Δͱ 4&-&$5ʹ͔͔Δ͕࣌ؒ݅ ਺ʹൺྫͤͣҰఆ ΠϯσοΫεΛ࢖༻͠ͳ͍ͱ 4&-&$5ʹ͔͔Δ͕࣌ؒ݅ ਺ʹൺྫͯ͠૿Ճ͢Δ ແ͠ ༗Γ

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 8)&3&۟ͦͷ̍ ɺ͸ΠϯσοΫε͕࢖༻Ͱ͖ͳ͍ 8)&3&۟ͷશͯͷ"/%ʹ͔͔͍ͬͯͳ͍ΠϯσοΫε͸࢖༻͞Εͳ͍ ᐆດݕࡧ࣌ɺఆ਺จࣈྻʴલํҰகҎ֎Ͱ͸ΠϯσοΫε͕࢖༻Ͱ͖ͳ͍ 8)&3&DPM@JOEFY@"/%DPM@JOEFY@03DPM@JOEFY@"/%DPM@JOEFY@ ɹɹɹɹ◦DPM@JOEFY@ ɹɹɹɹ×DPM@JOEFY@ ɹɹɹɹ×DPM@JOEFY@ ɹɹɹɹ◦8)&3&DPM@JOEFY@-*,&จࣈྻ
ɹɹɹɹ×8)&3&DPM@JOEFY@-*,&จࣈྻ ɹɹɹɹ×8)&3&DPM@JOEFY@-*,&จࣈྻ ɹɹɹɹ×8)&3&DPM@JOEFY@-*,&DPM@ ɹɹɹɹ×8)&3&DPM@JOEFY@ ɹɹɹɹ◦8)&3&DPM@JOEFY@*/ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@JOEFY@ DPM@JOEFY@ */%&9 DPM@JOEFY@ DPM@JOEFY@ */%&9 DPM@JOEFY@

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 8)&3&۟ͦͷ̎ ෳ߹ΠϯσοΫε ෳ਺ͷΧϥϜʹର͢ΔΠϯσοΫε ઌ಄ͷΠϯσοΫε͔Βॱʹ෦෼ΠϯσοΫεͱͯ͠࢖༻Ͱ͖Δ ˞΄ͱΜͲͷ৔߹.Z42-ଆͰద੾ʹฒͼସ͑ͯ͘ΕΔ͕ɺҰԠઌ಄ΠϯσοΫε͔Βॱʹॻ͘͜ͱ 8)&3&DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@ ɹɹɹɹ◦DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@
8)&3&DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@ ɹɹɹɹ◦DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ 8)&3&DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@@@ */%&9 DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 8)&3&۟ͦͷ̏ લͷΩʔ͕ൣғݕࡧͷ৔߹ɺͦΕҎ߱ͷΩʔ͕࢖༻͞Εͳ͍͜ͱ͕͋Δ ΠϯσοΫεϚʔδ ΠϯσοΫεΛ݁߹ͯ͠ར༻͢Δ ૝ఆ௨ΓʹΠϯσοΫεϚʔδͱͯ͠ར༻͞Εͳ͍Մೳੑ͕͋Δ 8)&3&DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@ ɹɹɹɹ◦DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@
DPM@JOEFY@QBSU@ ˣΠϯσοΫε͕࢖༻͞ΕΔΑ͏ʹॻ͖׵͑Δˣ 8)&3&DPM@JOEFY@QBSU@*/ "/%DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@ ɹɹɹɹ◦DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@JOEFY@ DPM@JOEFY@ */%&9 DPM@JOEFY@

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 8)&3&۟ͦͷ̐ ෳ߹ΠϯσοΫεͱΠϯσοΫεϚʔδͷҧ͍ ࢖༻ՄೳͱͳΔΠϯσοΫε ෳ߹ΠϯσοΫεʢDPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ʣ ΠϯσοΫεϚʔδʢDPM@JOEFY@ DPM@JOEFY@ͷ୯ମΠϯσοΫεʣ ◦DPM@JOEFY@QBSU@ ◦DPM@JOEFY@QBSU@
DPM@JOEFY@QBSU@ ◦DPM@JOEFY@QBSU@ ◦DPM@JOEFY@QBSU@ ◦DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@@ */%&9 DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@JOEFY@ DPM@JOEFY@ */%&9 DPM@JOEFY@

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 8)&3&۟ͦͷ̑ ࢖༻ՄೳͱͳΔ৚݅ͷҰྫ ෳ߹ΠϯσοΫε ΠϯσοΫεϚʔδ ◦8)&3&DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@ ×8)&3&DPM@JOEFY@QBSU@03DPM@JOEFY@QBSU@ ◦8)&3&DPM@JOEFY@QBSU@03DPM@JOEFY@QBSU@"/%DPM@JOEFY@QBSU@ ◦8)&3&DPM@JOEFY@"/%DPM@JOEFY@ ◦8)&3&DPM@JOEFY@03DPM@JOEFY@
×8)&3&DPM@JOEFY@03DPM@JOEFY@"/%DPM@JOEFY@

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 4&-&$5଎౓ൺֱ 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8
0.9 1 101 201 300 ϛϦඵ ສϨίʔυ ৚߲݅໨͕ෳ߹ΠϯσοΫε͔Πϯ σοΫεϚʔδ͔Ͱ4&-&$5ʹ͔ ͔Δ࣌ؒͷҧ͍Λௐ΂ͯΈΔ 4&-&$5 '30.UFTU@UCM8)&3&DPM@ "/%DPM@b` ˞ճຖͷฏۉ஋ ෳ߹ɿ Ϛʔδɿ ෳ߹ΠϯσοΫεͱΠϯσοΫε ϚʔδͰ͸ΠϯσοΫεϚʔδͷ ํ͕஗͍ ෳ߹ Ϛʔδ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@@ */%&9 DPM@ DPM@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@ DPM@JOEFY@ */%&9 DPM@ ֦େ

ҎԼͷ৔߹03%&3#:۟ʹΠϯσοΫε͸࢖༻Ͱ͖ͳ͍ ࿈ଓ͠ͳ͍Ωʔ ˞࿈ଓ͍ͯ͠Ε͹໰୊ͳ͍ 03%&3#:DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ɼDPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ */%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ
03%&3#:ͦͷ̍ 03%&3#:DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ ɹɹɹɹ◦DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@@@ */%&9 DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@

"4$ͱ%&4$͕ࠞࡏ͍ͯ͠Δ ෳ਺ͷΩʔ */%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 03%&3#:ͦͷ̎ 03%&3#:DPM@JOEFY@ DPM@JOEFY@ ɹɹɹɹ×DPM@JOEFY@ ɹɹɹɹ×DPM@JOEFY@ Ωʔ໊ छผ
ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@JOEFY@ DPM@JOEFY@ */%&9 DPM@JOEFY@ 03%&3#:DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@%&4$ ɹɹɹɹ×DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@@@ */%&9 DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 03%&3#:ͦͷ̏ 8)&3&۟Ͱ࢖༻͞Ε͍ͯΔ΋ͷͱผͷΩʔ 03%&3#:۟ͱ(3061#:۟Ͱ࢖༻͍ͯ͠Δ߲໨͕ҟͳΔ 8)&3&DPM@JOEFY@ 03%&3#:DPM@JOEFY@ ɹɹɹɹ˚DPM@JOEFY@ ɹɹɹɹ˚DPM@JOEFY@ ˞ͲͪΒ͔ยํͷΈ࢖༻Մೳ Ωʔ໊
छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@JOEFY@ DPM@JOEFY@ */%&9 DPM@JOEFY@ (3061#:DPM@JOEFY@ 03%&3#:DPM@JOEFY@ ɹɹɹɹ◦DPM@JOEFY@ ɹɹɹɹ×DPM@JOEFY@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@JOEFY@ DPM@JOEFY@ */%&9 DPM@JOEFY@

)"7*/(۟͸ΠϯσοΫε͕࢖༻͞Εͳ͍ ࢖༻ͯ͠ཉ͍͠ΠϯσοΫε͕࢖༻͞Εͳ͍৔߹͸'03$&*/%&9Λ࢖ͬ ͯΠϯσοΫεΛࢦఆ͢Δ͜ͱ͕Ͱ͖Δ ૝ఆͨ͠ΠϯσοΫεΛ࢖༻͢Δ42-ʹͳ͍ͬͯΔ͔Λ&91-"*/Ͱ֬ೝ ͢Δ ˞Ϩίʔυ਺΍σʔλͷ಺༰ʹΑΓ࢖༻͞ΕΔΠϯσοΫε͕มΘΔՄೳੑ͕͋ΔͷͰɺ࣮ࡍ ʹ͍ۙσʔλΛ࢖༻͢Δ͜ͱ͕ॏཁ */%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ ͦͷଞ

*/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ ͦͷ̍ جຊతʹબ୒ੑͷߴ͍߲໨͕ΠϯσοΫεͩͱޮՌ͕ߴ͍ બ୒ੑ͕ߴ͍ˠͦͷ߲໨ͰߜΓࠐΜͩ݁Ռ݅਺͕গͳ͍ બ୒ੑ͕௿͍ˠͦͷ߲໨ͰߜΓࠐΜͩ݁Ռ݅਺͕ଟ͍ ˞ੑผɺ0/0''ϑϥάͱͯ͠࢖༻͢Δ߲໨ͳͲ ˞΄ͱΜͲϑϥάཱ͕͍ͬͯͳ͍߲໨ͷɺϑϥάཱ͕͍ͬͯΔ΋ͷΛநग़͢Δ৔߹ͳͲ͸ ɹબ୒ੑ͕௿ͯ͘΋݁Ռ݅਺͕গͳ͘ͳΔͨΊΠϯσοΫεʹ͢ΔͱޮՌ͕ߴ͍ ෳ߹ΠϯσοΫε͸ઌ಄͔Βॱʹ෦෼ΠϯσοΫεͱͯ͠΋࢖༻Ͱ͖ΔͷͰɺ ୯ಠͰ࢖ΘΕΔ͜ͱ΋͋ΔΧϥϜ͸ΠϯσοΫεͷ࠷ॳͷྻʹࢦఆ͢Δ
ෳ߹ΠϯσοΫε͸બ୒ੑͷߴ͍΋ͷΛઌ಄ʹ͢Δ

*/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ ͦͷ̎ ΠϯσοΫεαΠζ͕૿͑Δ͜ͱʹΑΔӨڹ ݕࡧ଎౓͕௿Լ */4&35ɺ61%"5&ɺ%&-&5&࣌ΠϯσοΫεΛ࡞Γ௚͢଎౓͕௿Լ ΠϯσοΫε৘ใΛ֨ೲ͢ΔͨΊ%#༰ྔ͕૿Ճ ϝϞϦʹऩ·ΔαΠζΛ௒͑ΔͱɺσΟεΫ͔ΒͷಡΈࠐΈ͕ൃੜͯ͠଎౓͕େ ෯௿Լ

*/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ ΠϯσοΫεαΠζൺֱ 0 60 120 180 240 1 101 201
300 MB ສϨίʔυ 0 1 1(3ͷෳ߹) 3 σʔλαΠζ ΠϯσοΫεͷ༗Γແ͠ɺݸ਺ͰΠϯ σοΫεαΠζͷҧ͍Λௐ΂ͯΈΔ ̌ɿແ͠ ̍ɿ ̍ʢ̏ͷෳ߹ʣɿ ̏ɿ ΠϯσοΫε͕ଟ͍ఔ%#αΠζ૿Ճ ෳ਺ͷ୯ମΠϯσοΫεΑΓ΋ಉ߲໨ ͷෳ߹ΠϯσοΫεͷํ͕%#αΠζগ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@@@ */%&9 DPM@ DPM@ DPM@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@ DPM@JOEFY@ */%&9 DPM@ DPM@JOEFY@ */%&9 DPM@ Ωʔ໊ छผ ϑΟʔϧυ DPM@JOEFY@ */%&9 DPM@

*/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ */4&35଎౓ൺֱ 0 1 2 3 4 1 101 201
300 ϛϦඵ ສϨίʔυ 0 1 1(3ͷෳ߹) 3 ΠϯσοΫεͷ༗Γແ͠ɺݸ਺Ͱ */4&35ʹ͔͔Δ࣌ؒͷҧ͍Λௐ ΂ͯΈΔ */4&35*/50UFTU@UCM DPM@ DPM@ DPM@ DPM@ 7"-6&4 SBOE SBOE SBOE SBOE ˞ ճຖͷฏۉ஋ ΠϯσοΫε͕ଟ͍ํ͕ΑΓ࣌ ͕͔͔ؒΔ ෳ਺ͷ୯ମΠϯσοΫεΑΓ΋ ಉ߲໨ͷෳ߹ΠϯσοΫεͷํ ͕଎͍

*/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ ͦͷ̏ ΠϯσοΫεαΠζΛݮΒ͢ʹ͸ ෆཁͳΠϯσοΫεΛ࡞੒͠ͳ͍ ΠϯσοΫεͷΩʔ௕Λ୹͘͢Δ ɹখ͍͞αΠζͷσʔλܕΛ࢖༻͢Δɺ௕͍จࣈྻͳͲΛΠϯσοΫεʹ͠ͳ͍ σʔλࣗମΛݮΒ͢

&91-"*/ͱ͸Կ͔ ʮ&91-"*/4&-&$5ʙʯ౳4&-&$5จͷ಄ʹʮ&91-"*/ʯΛ͚ͭΔͱɺ .:42-ΦϓςΟϚΠβ͕ΫΤϦͷ࣮ߦܭըΛදࣔͯ͘͠ΕΔ ˞%&-&5&ɺ61%"5&Ͱ࢖༻͍ͨ͠৔߹͸4&-&$5ʹॻ͖׵͑Δ &91-"*/4&-&$5 '30.UFTU@UCM 8)&3&DPM@JOEFY@ "/%DPM@JOEFY@ "/%DPM@JOEFY@ JE
TFMFDU@UZQF UBCMF UZQF QPTTJCMF@LFZT LFZ LFZ@MFO SFG SPXT &YUSB 4*.1-& UFTU@UCM SFG DPM@JOEFY@ DPM@JOEFY@ DPM@JOEFY@ DPOTU 6TJOHXIFSF

&91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̍ TFMFDU@UZQF ɹ4FMFDUͷछྨɹ UBCMF ɹࢀর͢Δςʔϒϧ QPTTJCMF@LFZT ɹ࢖༻ՄೳͳΠϯσοΫεϦετ LFZ ɹ.Z42-͕࣮ࡍʹ࢖༻Λܾఆͨ͠ΠϯσοΫε
ॏཁ JE TFMFDU@UZQF UBCMF UZQF QPTTJCMF@LFZT LFZ LFZ@MFO SFG SPXT &YUSB 4*.1-& UFTU@UCM SFG DPM@JOEFY@ DPM@JOEFY@ DPM@JOEFY@ DPOTU 6TJOHXIFSF

&91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̎ LFZ@MFO ɹΠϯσοΫεͷΩʔ௕ SFG ɹݕࡧ৚݅ͰɺLFZͱൺֱ͞Ε͍ͯΔΧϥϜ·ͨ͸ఆ਺ SPXT ɹݕࡧ͞ΕΔਪఆϨίʔυ਺ ɹಉ݁͡ՌͱͳΔ42-ͳΒ͕͜͜গͳ͍΄Ͳྑ͍ ॏཁ
JE TFMFDU@UZQF UBCMF UZQF QPTTJCMF@LFZT LFZ LFZ@MFO SFG SPXT &YUSB 4*.1-& UFTU@UCM SFG DPM@JOEFY@ DPM@JOEFY@ DPM@JOEFY@ DPOTU 6TJOHXIFSF

&91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̏ UZQF ݁߹ܕ DPOTU ɹҰக͢ΔϨίʔυ͕࠷େͭ ɹ6/*26&·ͨ͸13*."3:,&:ͷΠϯσοΫεͷ౳Ձݕࡧ FR@SFG ɹ̍ɿ̍ͷ+0*/ ɹ6/*26&·ͨ͸13*."3:,&:ͷΠϯσοΫεΛ࢖༻
SFG ɹ̍ɿ̽ͷ+0*/ ɹ6/*26&·ͨ͸13*."3:,&:Ҏ֎ͷΠϯσοΫεΛ࢖༻ SFG@PS@OVMM ɹ/6--Λ࢖༻ͨ͠Ϩίʔυͷิ଍ݕࡧ΋௥Ճ࣮ߦ͞ΕΔ ॏཁ JE TFMFDU@UZQF UBCMF UZQF QPTTJCMF@LFZT LFZ LFZ@MFO SFG SPXT &YUSB 4*.1-& UFTU@UCM SFG DPM@JOEFY@ DPM@JOEFY@ DPM@JOEFY@ DPOTU 6TJOHXIFSF

&91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̐ *OEFY@NFSHF ɹΠϯσοΫεϚʔδ͕࢖༻͞ΕΔ VOJRVF@TVCRVFSZ ɹαϒΫΤϦͰ6/*26&·ͨ͸13*."3:,&:ͷΠϯσοΫεΛ࢖༻ JOEFY@TVCRVFSZ ɹαϒΫΤϦͰ6/*26&·ͨ͸13*."3:,&:Ҏ֎ͷΠϯσοΫεΛ࢖༻ SBOHF ɹΠϯσοΫεΛ࢖༻ͨ͠ൣғݕࡧ
JOEFY ɹϑϧΠϯσοΫεεΩϟϯ ɹΠϯσοΫεʹର͢Δ"--ͷͨΊ஗͍ "-- ɹϑϧςʔϒϧεΩϟϯ ɹΠϯσοΫεͷ௥ՃͳͲͰճආՄೳ ͜Ε͕ग़ͨΒ஫ҙ

&91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̑ &YUSB ɹͲͷΑ͏ʹΫΤϦ͕ղܾ͞ΕΔ͔ʹؔ͢Δ௥Ճ৘ใ ɹ਺͕ଟ͍ͷͰग़ͨΒ஫ҙͳ΋ͷͷΈهࡌ 6TJOHpMFTPSU ɹ03%&3#:ʹΠϯσοΫεΛ࢖༻Ͱ͖ͳ͍ 6TJOHUFNQPSBSZ ɹΫΤϦͷղܾʹςϯϙϥϦςʔϒϧͷ࡞੒͕ඞཁ ɹ%*45*/$5ͷ࢖༻࣌΍03%&3#:ʹΠϯσοΫεΛ࢖༻Ͱ͖ͳ͍৔߹ɺ03%&3#:ͱ
(3061#:͕ࣜҟͳΔ৔߹ͳͲ ͜Ε͕ग़ͨΒ஫ҙ ॏཁ JE TFMFDU@UZQF UBCMF UZQF QPTTJCMF@LFZT LFZ LFZ@MFO SFG SPXT &YUSB 4*.1-& UFTU@UCM SFG DPM@JOEFY@ DPM@JOEFY@ DPM@JOEFY@ DPOTU 6TJOHXIFSF

&91-"*/ग़ྗ݁Ռ͔ΒͰ͖Δ42-νϡʔχϯά 42-Λνϡʔχϯά͢Δʹ͸UZQF LFZ SPXT FYUSB͕ॏཁ UZQF ɹJOEFY "--ͷ৔߹͸νϡʔχϯάͰ͖ͳ͍͔ݕ౼͢Δ LFZ ɹ૝ఆ௨ΓͷΠϯσοΫε͕࢖༻͞Ε͍ͯΔ͔֬ೝ͢Δ
SPXT ɹΑΓ݅਺ΛߜΓࠐΉ͜ͱ͸Մೳ͔ݕ౼͢Δ &YUSB ɹVTJOHpMFTPSU VTJOHUFNQPSBSZͷ৔߹͸νϡʔχϯάͰ͖ͳ͍͔ݕ౼͢Δ JE TFMFDU@UZQF UBCMF UZQF QPTTJCMF@LFZT LFZ LFZ@MFO SFG SPXT &YUSB 4*.1-& UFTU@UCM SFG DPM@JOEFY@ DPM@JOEFY@ DPM@JOEFY@ DPOTU 6TJOHXIFSF

ࢀߟࢿྉ .Z42-ϦϑΝϨϯεϚχϡΞϧ࠷దԽ4&-&$5εςʔτϝ ϯτ͓ΑͼͦͷଞͷΫΤϦͷ࠷దԽ IUUQEFWNZTRMDPNEPDSFGNBOKBRVFSZTQFFEIUNM .Z42-1SBDUJDF8JLJ IUUQXXXNZTRMQSBDUJDFXJLJDPN

ͦͷଞ σʔλ࡞੒ʹ࢖༻ͨ͠ϚγϯͷεϖοΫ .Z42- $16()[*OUFM$PSF%VP 3".(#

MySQL INDEX+EXPLAIN入門

MySQL INDEX+EXPLAIN入門

Infiniteloop

More Decks by Infiniteloop

Other Decks in Programming

Featured

Transcript

.Z42- /%&9ʴ&91-"/ೖ໳ גࣜձࣾΠϯϑΟχοτϧʔϓ PUB ࡞੒ɿɹ࠷ऴߋ৽ɿ

͜ͷεϥΠυͷ಺༰ .Z42-OOP%#Λલఏͱͨ͠/%&9ͱ&91-"*/ͷॳڃฤ

໨࣍ /%&9Λ࢖͓͏ /%&9ͱ͸Կ͔ /%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ /%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ &91-"/Λ࢖͓͏ &91-"/ͱ͸Կ͔ &91-"/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ &91-"/ग़ྗ݁Ռ͔ΒͰ͖Δ42-νϡʔχϯά

*/%&9ͱ͸Կ͔ 4&-&$5଎౓ൺֱ 0 500 1,000 1,500 2,000 2,500 1 101

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 8)&3&۟ͦͷ̐ ෳ߹ΠϯσοΫεͱΠϯσοΫεϚʔδͷҧ͍ ࢖༻ՄೳͱͳΔΠϯσοΫε ෳ߹ΠϯσοΫεʢDPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ʣ ΠϯσοΫεϚʔδʢDPM@JOEFY@ DPM@JOEFY@ͷ୯ମΠϯσοΫεʣ ◦DPM@JOEFY@QBSU@ ◦DPM@JOEFY@QBSU@

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 4&-&$5଎౓ൺֱ 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8

ҎԼͷ৔߹03%&3#:۟ʹΠϯσοΫε͸࢖༻Ͱ͖ͳ͍ ࿈ଓ͠ͳ͍Ωʔ ˞࿈ଓ͍ͯ͠Ε͹໰୊ͳ͍ 03%&3#:DPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ ɹɹɹɹ×DPM@JOEFY@QBSU@ɼDPM@JOEFY@QBSU@ DPM@JOEFY@QBSU@ */%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ

"4$ͱ%&4$͕ࠞࡏ͍ͯ͠Δ ෳ਺ͷΩʔ */%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 03%&3#:ͦͷ̎ 03%&3#:DPM@JOEFY@ DPM@JOEFY@ ɹɹɹɹ×DPM@JOEFY@ ɹɹɹɹ×DPM@JOEFY@ Ωʔ໊ छผ

*/%&9Λ࢖༻͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ 03%&3#:ͦͷ̏ 8)&3&۟Ͱ࢖༻͞Ε͍ͯΔ΋ͷͱผͷΩʔ 03%&3#:۟ͱ(3061#:۟Ͱ࢖༻͍ͯ͠Δ߲໨͕ҟͳΔ 8)&3&DPM@JOEFY@ 03%&3#:DPM@JOEFY@ ɹɹɹɹ˚DPM@JOEFY@ ɹɹɹɹ˚DPM@JOEFY@ ˞ͲͪΒ͔ยํͷΈ࢖༻Մೳ Ωʔ໊

*/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ ΠϯσοΫεαΠζൺֱ 0 60 120 180 240 1 101 201

/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ /4&35଎౓ൺֱ 0 1 2 3 4 1 101 201

*/%&9Λ࡞੒͢Δ࣌ʹؾΛ͚ͭΔ͜ͱ ͦͷ̏ ΠϯσοΫεαΠζΛݮΒ͢ʹ͸ ෆཁͳΠϯσοΫεΛ࡞੒͠ͳ͍ ΠϯσοΫεͷΩʔ௕Λ୹͘͢Δ ɹখ͍͞αΠζͷσʔλܕΛ࢖༻͢Δɺ௕͍จࣈྻͳͲΛΠϯσοΫεʹ͠ͳ͍ σʔλࣗମΛݮΒ͢

&91-"/ͱ͸Կ͔ ʮ&91-"/4&-&$5ʙʯ౳4&-&$5จͷ಄ʹʮ&91-"/ʯΛ͚ͭΔͱɺ .:42-ΦϓςΟϚΠβ͕ΫΤϦͷ࣮ߦܭըΛදࣔͯ͘͠ΕΔ ˞%&-&5&ɺ61%"5&Ͱ࢖༻͍ͨ͠৔߹͸4&-&$5ʹॻ͖׵͑Δ &91-"/4&-&$5 '30.UFTU@UCM 8)&3&DPM@JOEFY@ "/%DPM@JOEFY@ "/%DPM@JOEFY@ JE

&91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̍ TFMFDU@UZQF ɹ4FMFDUͷछྨɹ UBCMF ɹࢀর͢Δςʔϒϧ QPTTJCMF@LFZT ɹ࢖༻ՄೳͳΠϯσοΫεϦετ LFZ ɹ.Z42-͕࣮ࡍʹ࢖༻Λܾఆͨ͠ΠϯσοΫε

&91-"*/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̎ LFZ@MFO ɹΠϯσοΫεͷΩʔ௕ SFG ɹݕࡧ৚݅ͰɺLFZͱൺֱ͞Ε͍ͯΔΧϥϜ·ͨ͸ఆ਺ SPXT ɹݕࡧ͞ΕΔਪఆϨίʔυ਺ ɹಉ݁͡ՌͱͳΔ42-ͳΒ͕͜͜গͳ͍΄Ͳྑ͍ ॏཁ

&91-"/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̏ UZQF ݁߹ܕ DPOTU ɹҰக͢ΔϨίʔυ͕࠷େͭ ɹ6/26&·ͨ͸13."3:,&:ͷΠϯσοΫεͷ౳Ձݕࡧ FR@SFG ɹ̍ɿ̍ͷ+0/ ɹ6/26&·ͨ͸13."3:,&:ͷΠϯσοΫεΛ࢖༻

&91-"/Ͱग़ྗ͞ΕΔ֤߲໨ʹ͍ͭͯ ͦͷ̐ OEFY@NFSHF ɹΠϯσοΫεϚʔδ͕࢖༻͞ΕΔ VOJRVF@TVCRVFSZ ɹαϒΫΤϦͰ6/26&·ͨ͸13."3:,&:ͷΠϯσοΫεΛ࢖༻ JOEFY@TVCRVFSZ ɹαϒΫΤϦͰ6/26&·ͨ͸13."3:,&:Ҏ֎ͷΠϯσοΫεΛ࢖༻ SBOHF ɹΠϯσοΫεΛ࢖༻ͨ͠ൣғݕࡧ

&91-"*/ग़ྗ݁Ռ͔ΒͰ͖Δ42-νϡʔχϯά 42-Λνϡʔχϯά͢Δʹ͸UZQF LFZ SPXT FYUSB͕ॏཁ UZQF ɹJOEFY "--ͷ৔߹͸νϡʔχϯάͰ͖ͳ͍͔ݕ౼͢Δ LFZ ɹ૝ఆ௨ΓͷΠϯσοΫε͕࢖༻͞Ε͍ͯΔ͔֬ೝ͢Δ

ࢀߟࢿྉ .Z42-ϦϑΝϨϯεϚχϡΞϧ࠷దԽ4&-&$5εςʔτϝ ϯτ͓ΑͼͦͷଞͷΫΤϦͷ࠷దԽ IUUQEFWNZTRMDPNEPDSFGNBOKBRVFSZTQFFEIUNM .Z42-1SBDUJDF8JLJ IUUQXXXNZTRMQSBDUJDFXJLJDPN

ͦͷଞ σʔλ࡞੒ʹ࢖༻ͨ͠ϚγϯͷεϖοΫ .Z42- $16()[*OUFM$PSF%VP 3".(#