ΠϯσοΫεࠨ͔Βӈ • ΠϯσοΫεͷΩʔࠨ͔Β͔͑͠ͳ͍ • LIKEͰ%͕લʹ͋Δͱ͑ͳ͍ • ࠨ͕ෆఆ͔ͩΒ • ࠨ͔Βݟ͍ͯ͘ͳΒɺ్த·ͰͰOK • σϞ • select * from users where name like 'ాத%' limit 1\G • select * from users where name like '%ాத' limit 1\G
ෳ߹ΠϯσοΫε • ෳͷΧϥϜΛ·ͱΊͯΠϯσοΫεΛுΔ • ΠϯσοΫεͷҰ෦͚ͩΛ͏͜ͱͰ͖Δ • ͜͜Ͱࠨ͔Βӈ͕ग़ͯ͘Δ • σϞ • select count(*) from users where blood_type = 'A' and name like 'ాத%'\G • select count(*) from users where blood_type = 'A'\G
NOTͷѻ͍ • Ұக͍ͤͯ͘͞ͷ͕جຊʹͳΔͨΊΠϯσοΫε͕ޮ ͔ͳ͍ • NOTͰ͖Δ͚ͩॻ͔ͳ͍Α͏ʹ • σϞ • select * from users where name not like 'Ճ౻%' limit 1\G • select * from users where name like 'Ճ౻%' limit 1\G • select * from users where name != 'Ճ౻' limit 1\G • ͜ΕMySQL 5.6Ҏ߱ͩͱ͍͚Δ
NULLͷѻ͍ • ࣮ΠϯσοΫε͕ޮ͘ • IS NULL • IS NOT NULL • ͜ͷڍಈMySQL͚ͩͬΆ͍ • σϞ • select count(*) from users where phone_num is null\G • select count(*) from slow_users where phone_num is null\G • select count(*) from users where phone_num is not null\G • select count(*) from slow_users where phone_num is not null\G
ιʔτ • ݕࡧ͚ͩͰͳ͘ιʔτʹΠϯσοΫε͕͑Δ • Կग़͖ͯͨΑ͏ʹB+ͷϦʔϑιʔτ͞ΕͯΔ • ෳ߹ΠϯσοΫε1൪ͰߜΓࠐΜͰ2൪Ͱιʔτ Ͱ͖Δ • σϞ • select * from users order by order_num limit 1\G • select * from slow_users order by order_num limit 1\G • select * from users where blood_type = 'B' order by birthday limit 1\G • select * from slow_users where blood_type = 'B' order by birthday limit 1\G
REPEATABLE READҙ • ৽ଔ1ͷ࣌ʹɺ͜ΕΛཧղ͍ͯ͠ͳͯ͘DBͷόά ͩͱࢥͬͯ͠·ͬͨ... • σϞ • A: begin; • B: begin; • A: select * from users where id = 123456; • B: select * from users where id = 123456 for update; -- ͜ͷ࣌ͰഉଞϩοΫ • B: update users set order_num = order_num + 1 where id = 123456; • B: select * from users where id = 123456; • A: select * from users where id = 123456; -- ·ͩίϛοτͯ͠ͳ͍͔ΒมΘͬͯͳ͍ • B: commit; • A: select * from users where id = 123456; -- ίϛοτޙ͚ͩͲมΘͬͯͳ͍ • A: select * from users where id = 123456 for update; -- B͕ίϛοτͨ͠ͰऔΕΔ
mihyaeru21
bengo4com
yokawasa
cyberarchitect
soracom
kawajiritakayuki
nasa_desu
picardparis
weaverryan
miu_crescent
gawa
takatsunobuhiro
ytaisei
bryan
mza
roundedbygravity
rmw
addyosmani
bluesmoon
lynnandtonic
eileencodes
tenderlove
geeforr
pauljervisheath