ϖʔδωʔγϣϯ͔Βߟ͑Δ SQLύϑΥʔϚϯε 2020/09/25 ίωώτϚϧγΣΦϯϥΠϯ https://connehito.connpass.com/event/187729/

ࣗݾ঺հ w ࢁాɹ঵ʢ΍·ͩɹ͠ΐ͏ʣ w ʹίωώτגࣜձࣾʹೖࣾ w ϑϩϯτΤϯυΤϯδχΞͱͯ͠8FCۀքʹ ೖΓɺࠓ͸8FCΞϓϦέʔγϣϯΤϯδχΞ ͱͯ͠ख޿͘΍͓ͬͯΓ·͢ɻ

ϖʔδωʔγϣϯ͸Ԟ͕ਂ͍

ϖʔδωʔγϣϯͱ͸ʁ • ϖʔδϟʔͱ΋ݺ͹Εɺݕࡧ݁ՌͷҰཡը໘ͳͲͰ࢖ΘΕΔɻ • ը໘ͷঢ়ଶ͕ଟ͍ɻ • ෳࡶɺ΋͘͠͸ॏ͍SQLʹૺ۰͠΍͍͢ɻ • ↑ࠓճ͸ͪ͜Βʹয఺Λ౰ͯ·͢ɻ

Ͳ͏͍ͬͨSQL͕ ࣮ߦ͞Ε͏ΔͩΖ͏͔ɾɾʁ

ݱ࣮ʹ͍ۙσʔλͰߟ͑ͯΈ·͠ΐ͏ • OracleͷैۀһαϯϓϧσʔλϕʔεΛ࢖͍·͢ɻ • ߹ܭ400ສϨίʔυ͔Β੒Δɺݱ࣮Ͱ͋Γͦ͏ͳσʔλͰ͢ɻ • https://dev.mysql.com/doc/employee/en/employees- introduction.html • ※ݸਓͰར༻͢Δ෼ʹ͸ϥΠηϯεతʹ໰୊ͳ͠ɻ

ςʔϒϧߏ੒

ࠓճ࢖͏ςʔϒϧ • employeesʢैۀһʣ • ஀ੜ೔ • ϑΝʔετωʔϜ • ϥετωʔϜ • ੑผ • ޏ༻೔

࣮ߦ؀ڥʹ͍ͭͯ • ࠓճ͸MySQLެࣜͷDockerΠϝʔδΛ࢖͍·͢ɻόʔδϣϯ͸5.7Ͱ ࢼ͍ͯ͠·͢ɻ • ੑೳ͸CPUs: 2ɺMemory: 2.00GBɺSwap: 1GB
 ʢ͓ͦΒ͘ɺDocker Desktopͷॳظઃఆʣ • σʔλͷ౤ೖํ๏͸ϦϙδτϦͷREADMEΛࢀরͷ͜ͱɻ • https://github.com/datacharmer/test_db

ͪ͜ΒͷσʔλϕʔεΛ࢖ͬͨ ैۀһ؅ཧը໘͕͋Δͱͨ͠Β

Ͳ͏͍͏SQL͕࣮ߦ͞Ε͏Δ͔ʁ • ৚݅બ୒ͰϑΟϧλϦϯά͍ͨ͠ɻ • ੑผͰɺ໾৬͝ͱʹɺ෦ॺ͝ͱʹɺೖࣾظؒͰ • ৚݅Ͱιʔτ͍ͨ͠ɻ • ೖࣾͨ͠ॱɺ΋͘͠͸ݹ͍ॱɻ೥ྸ͕௿͍ॱɺߴ͍ॱɻ • ໊લͰLIKEݕࡧ͍ͨ͠ɻ…etc

ࠓճߟ͑Δέʔε ʮೖࣾ೔͕1990-01-01Ҏ߱ͷࣾһΛ ೖࣾॱʹ߱ॱιʔτ͢Δʯ

·ͣ͸SQLΛࢼͯ͠ΈΑ͏ SELECT count(*) FROM employees; - 300,024݅ SELECT count(*) FROM employees WHERE hire_date >= ‘1990-01-01'; - 135,227݅ SELECT * FROM employees LIMIT 200; - 200 rows in set (0.00 sec) ɹ SELECT * FROM employees WHERE hire_date >= ‘1990-01-01’ ORDER BY hire_date desc LIMIT 200; - 200 rows in set (0.15 sec) ʙఔ౓ͷ͕ࠩೝΊΒΕ·͢ɻ ઌ಄ͷߦΛऔಘ͚ͨͩ͠Ͱ͕͢ɺ ͳ͔ͳ͔ͷ͕ࠩग़·ͨ͠Ͷɻ

ࠓճߟ͑Δέʔε ෳࡶͳSQLͰ͸ͳ͍ͷͰ ଧͯΔख͸ݶΒΕͦ͏

ΠϯσοΫεΛݕ౼ͯ͠ΈΑ͏ • ର৅͸ɺemployeeςʔϒϧͷhire_dateΧϥϜ • ※ࠓճ৮ΕΔΠϯσοΫε͸ʮB-treeΠϯσοΫεʯͷ͜ͱΛࢦͯ͠ ͍Δͱߟ͍͑ͯͩ͘͞ɻΠϯσοΫε͸ෳ਺ͷछྨ͕͋ΔͷͰɺ೦ͷ ҝʹ͜͜Ͱݴٴ͓͖ͯ͠·͢ɻ

ΧʔσΟφϦςΟΛҙࣝ͠Α͏ • ΠϯσοΫεΛ࣮ߦ͢Δલʹҙࣝ͢Δ΂͖ࢦ਑ • ಛఆͷྻͷ஋͕Ͳͷ͙Β͍ͷछྨΛ͔࣋ͭΛදΘ֓͢೦Ͱ͢ɻ • employee.gender͸”M(Male)”ͱ”F(Female)”ͷ2छྨͳͷͰɺ͜ͷ৔߹͸ ΧʔσΟφϦςΟ͕̎ͱͳΓ·͢ • ໨҆͸ʮಛఆͷΩʔ஋Λࢦఆͨ͠ͱ͖ʹɺશମͷϨίʔυ਺ͷ5ˋఔ౓ʹߜ ΓࠐΊΔ͚ͩͷΧʔσΟφϦςΟ͕͋Δ͜ͱʯ • ग़యɿʰୡਓʹֶͿDBઃܭ పఈࢦೆॻʱ

ΠϯσοΫεΛར༻ͯ͠ΈΑ͏ —- hire_dateʹΠϯσοΫεΛுΔ ALTER TABLE employees ADD INDEX hire_date_index(hire_date); —- ΠϯσοΫεͷ֬ೝ EXPLAIN SELECT * FROM employees ORDER BY hire_date desc LIMIT 200; ࡞੒௚ޙ͸ΠϯσοΫεΛࢀরͰ͖ͳ͍ɻ ಺෦ʹΠϯσοΫε͕อ࣋͞Ε͍ͯͳ͍ͷͰɻ

ΠϯσοΫε༗ແͷࠩΛݟͯΈΑ͏ SELECT * FROM employees IGNORE INDEX (hire_date_index) WHERE hire_date >= '1990-01-01' ORDER BY hire_date desc LIMIT 200; - 200 rows in set (0.15 sec) SELECT * FROM employees WHERE hire_date >= '1990-01-01' ORDER BY hire_date desc LIMIT 200; - 200 rows in set (0.01 sec) ໌֬ͳ͕ࠩग़·ͨ͠Ͷʂ ΠϯσοΫεΛҙਤతʹແࢹ͢Δʮ*(/03&*/%&9ʯΛ࢖༻͠ɺ ΠϯσοΫεແ͠ঢ়ଶͷ݁ՌΛද͍ࣔͯ͠·͢ɻ

ࠓճߟ͑Δέʔε ͳͥૣ͘ͳͬͨͷ͔ʁ

ORDER BYͷ࠷దԽ • ΠϯσοΫε͕͋Δ͓͔͛ͰɺιʔτॲཧΛεΩοϓͭͭ͠ORDER BY۟ΛຬͨͤΔɻ • https://dev.mysql.com/doc/refman/5.7/ja/order-by- optimization.html • ࠷దԽ͕ޮ͔ͳ͍৔߹΋͋ΔͷͰ஫ҙ͕ඞཁɻ • ྫɿෳ਺ͷΩʔʹରͯ͠ɺORDER BYΛ࢖༻͍ͯ͠Δ৔߹

ΠϯσοΫε͕ ࢖ΘΕͳ͍৔߹ SELECT * FROM employees WHERE retirement_date IS NULL; —- NULL͕ೖ͍ͬͯΔΧϥϜ͕ଟ͍ͱɺΠϯσοΫε͕ޮ͔ͳ͍৔߹΋ɻ —- ࡏ੶தͷࣾһͷߦʹ͸ʮ9999-01-01ʯͷΑ͏ͳ஋ΛೖΕ͓͖ͯɺ NULLΛආ͚Δ΍Γํ͕͋Δɻ SELECT * FROM employees WHERE first_name LIKE ‘%a%'; —- LIKEज़ޠΛ࢖͏৔߹ɺΠϯσοΫε͕ޮ͘ͷ͸લํҰகͷΈɻ —- MySQL5.7Ҏ߱Ͱ͋Ε͹ɺInnoDBͷશจݕࡧػೳʹೖͬͨ೔ຊޠύʔ αʔ͕࢖༻ՄೳͳͷͰɺͦͪΒ΋ީิʹೖΔ͔΋͠Εͳ͍ɻ —- ଞʹ΋৭ʑͳύλʔϯ͕͋Γ·͢ɻ • ΠϯσοΫεΛ࢖ͬͯ ͍Δͭ΋ΓͰ΋ɺ࣮͸ ςʔϒϧεΩϟϯ͕࣮ߦ ͞Εͯ͠·͏έʔεɻ

ͲͬͪͰ΋ྑ͍খൌ • গ͠લʹϖʔδωʔγϣϯΛ࡞ͬͨ࣌͸SQLͷνϡʔχϯά͕ٻΊΒ ΕΔγʔϯ͸ͳ͔ͬͨɻ • ςʔϒϧ΋গͳ͍͠ɺσʔλͷ૯਺΋2,000~3,000ఔ౓ɻ • Ή͠Ζɺը໘ଆͷReact࣮૷ʹ͕͔͔࣌ؒͬͨɻ

ࢀߟࢿྉ • MySQL 5.6 ϦϑΝϨϯεϚχϡΞϧ • https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html • https://dev.mysql.com/doc/refman/5.6/ja/optimize-overview.html • MySQL :: Employees Sample Database • https://dev.mysql.com/doc/employee/en/ • ୡਓʹֶͿSQLపఈࢦೆॻ ୈ2൛ ॳڃऀͰऴΘΓͨ͘ͳ͍͋ͳͨ΁ • https://www.shoeisha.co.jp/book/detail/9784798157825

·ͱΊ

·ͱΊ • ΠϯσοΫε͸ۜͷ஄ؙͰ͸ͳ͍ɻ • SQLͷ࠷దԽΛ͢Δ࣌͸ɺެࣜͷυΩϡϝϯτΛ࠷ॳʹݟΔ΂͖ɻ • MySQLͷ৔߹: https://dev.mysql.com/doc/refman/5.6/ja/optimize- overview.html • Oracleͷैۀһαϯϓϧσʔλϕʔε͸࣮ݧ΍࿅शʹ࠷దͰͨ͠ɻ • յͯ͠΋୭ʹ΋໎࿭Λ͔͚ͳ͍ͷͰ҆৺ɻ • ݸਓͰ࢖͏෼ʹ͸໰୊ͳ͍͸͚ͣͩͲɺϥΠηϯε͸ҰԠݟ͓ͯ͘ͱ͍͍ɻ

ϖʔδωʔγϣϯΛ ਂງΓͨ͠࿩ͷ͓ͦ͢෼͚͸ Ҏ্Ͱ͢ɻ

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ