ページネーションから考えるSQLパフォーマンス

【ランサーズ社合同勉強会】コネヒトマルシェオンライン「事業を支えるWeb開発」vol.2
https://connehito.connpass.com/event/187729/

こちらのイベントで発表した資料です。

★参考URL

「MySQL :: Employees Sample Database」
・https://dev.mysql.com/doc/employee/en/employees-introduction.html
・https://dev.mysql.com/doc/employee/en/sakila-structure.html
・https://github.com/datacharmer/test_db

「MySQL :: MySQL 5.6 リファレンスマニュアル」
・https://dev.mysql.com/doc/refman/5.6/ja/optimize-overview.html
・https://dev.mysql.com/doc/refman/5.6/ja/order-by-optimization.html
・https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html

「達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ」
・https://www.shoeisha.co.jp/book/detail/9784798157825

Transcript

1. ϖʔδωʔγϣϯ͔Βߟ͑Δ SQLύϑΥʔϚϯε 2020/09/25 ίωώτϚϧγΣΦϯϥΠϯ https://connehito.connpass.com/event/187729/
2. ࣗݾ঺հ w ࢁాɹ঵ʢ΍·ͩɹ͠ΐ͏ʣ
   w ʹίωώτגࣜձࣾʹೖࣾ
   w ϑϩϯτΤϯυΤϯδχΞͱͯ͠8FCۀքʹ ೖΓɺࠓ͸8FCΞϓϦέʔγϣϯΤϯδχΞ ͱͯ͠ख޿͘΍͓ͬͯΓ·͢ɻ
3. ϖʔδωʔγϣϯ͸Ԟ͕ਂ͍
4. ϖʔδωʔγϣϯͱ͸ʁ • ϖʔδϟʔͱ΋ݺ͹Εɺݕࡧ݁ՌͷҰཡը໘ͳͲͰ࢖ΘΕΔɻ • ը໘ͷঢ়ଶ͕ଟ͍ɻ • ෳࡶɺ΋͘͠͸ॏ͍SQLʹૺ۰͠΍͍͢ɻ • ↑ࠓճ͸ͪ͜Βʹয఺Λ౰ͯ·͢ɻ
5. Ͳ͏͍ͬͨSQL͕ ࣮ߦ͞Ε͏ΔͩΖ͏͔ɾɾʁ
6. ݱ࣮ʹ͍ۙσʔλͰߟ͑ͯΈ·͠ΐ͏ • OracleͷैۀһαϯϓϧσʔλϕʔεΛ࢖͍·͢ɻ • ߹ܭ400ສϨίʔυ͔Β੒Δɺݱ࣮Ͱ͋Γͦ͏ͳσʔλͰ͢ɻ • https://dev.mysql.com/doc/employee/en/employees- introduction.html • ※ݸਓͰར༻͢Δ෼ʹ͸ϥΠηϯεతʹ໰୊ͳ͠ɻ
7. ςʔϒϧߏ੒
8. ࠓճ࢖͏ςʔϒϧ • employeesʢैۀһʣ • ஀ੜ೔ • ϑΝʔετωʔϜ • ϥετωʔϜ • ੑผ • ޏ༻೔
9. ࣮ߦ؀ڥʹ͍ͭͯ • ࠓճ͸MySQLެࣜͷDockerΠϝʔδΛ࢖͍·͢ɻόʔδϣϯ͸5.7Ͱ ࢼ͍ͯ͠·͢ɻ • ੑೳ͸CPUs: 2ɺMemory: 2.00GBɺSwap: 1GB
 ʢ͓ͦΒ͘ɺDocker Desktopͷॳظઃఆʣ • σʔλͷ౤ೖํ๏͸ϦϙδτϦͷREADMEΛࢀরͷ͜ͱɻ • https://github.com/datacharmer/test_db
10. ͪ͜ΒͷσʔλϕʔεΛ࢖ͬͨ ैۀһ؅ཧը໘͕͋Δͱͨ͠Β
11. Ͳ͏͍͏SQL͕࣮ߦ͞Ε͏Δ͔ʁ • ৚݅બ୒ͰϑΟϧλϦϯά͍ͨ͠ɻ • ੑผͰɺ໾৬͝ͱʹɺ෦ॺ͝ͱʹɺೖࣾظؒͰ • ৚݅Ͱιʔτ͍ͨ͠ɻ • ೖࣾͨ͠ॱɺ΋͘͠͸ݹ͍ॱɻ೥ྸ͕௿͍ॱɺߴ͍ॱɻ • ໊લͰLIKEݕࡧ͍ͨ͠ɻ…etc
12. ࠓճߟ͑Δέʔε ʮೖࣾ೔͕1990-01-01Ҏ߱ͷࣾһΛ ೖࣾॱʹ߱ॱιʔτ͢Δʯ
13. ·ͣ͸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) ʙఔ౓ͷ͕ࠩೝΊΒΕ·͢ɻ
    ઌ಄ͷߦΛऔಘ͚ͨͩ͠Ͱ͕͢ɺ
    ͳ͔ͳ͔ͷ͕ࠩग़·ͨ͠Ͷɻ
14. ࠓճߟ͑Δέʔε ෳࡶͳSQLͰ͸ͳ͍ͷͰ ଧͯΔख͸ݶΒΕͦ͏
15. ΠϯσοΫεΛݕ౼ͯ͠ΈΑ͏ • ର৅͸ɺemployeeςʔϒϧͷhire_dateΧϥϜ • ※ࠓճ৮ΕΔΠϯσοΫε͸ʮB-treeΠϯσοΫεʯͷ͜ͱΛࢦͯ͠ ͍Δͱߟ͍͑ͯͩ͘͞ɻΠϯσοΫε͸ෳ਺ͷछྨ͕͋ΔͷͰɺ೦ͷ ҝʹ͜͜Ͱݴٴ͓͖ͯ͠·͢ɻ
16. ΧʔσΟφϦςΟΛҙࣝ͠Α͏ • ΠϯσοΫεΛ࣮ߦ͢Δલʹҙࣝ͢Δ΂͖ࢦ਑ • ಛఆͷྻͷ஋͕Ͳͷ͙Β͍ͷछྨΛ͔࣋ͭΛදΘ֓͢೦Ͱ͢ɻ • employee.gender͸”M(Male)”ͱ”F(Female)”ͷ2छྨͳͷͰɺ͜ͷ৔߹͸ ΧʔσΟφϦςΟ͕̎ͱͳΓ·͢ • ໨҆͸ʮಛఆͷΩʔ஋Λࢦఆͨ͠ͱ͖ʹɺશମͷϨίʔυ਺ͷ5ˋఔ౓ʹߜ ΓࠐΊΔ͚ͩͷΧʔσΟφϦςΟ͕͋Δ͜ͱʯ • ग़యɿʰୡਓʹֶͿDBઃܭ పఈࢦೆॻʱ
17. ΠϯσοΫεΛར༻ͯ͠ΈΑ͏ —- hire_dateʹΠϯσοΫεΛுΔ ALTER TABLE employees ADD INDEX hire_date_index(hire_date); —- ΠϯσοΫεͷ֬ೝ EXPLAIN SELECT * FROM employees ORDER BY hire_date desc LIMIT 200; ࡞੒௚ޙ͸ΠϯσοΫεΛࢀরͰ͖ͳ͍ɻ
    ಺෦ʹΠϯσοΫε͕อ࣋͞Ε͍ͯͳ͍ͷͰɻ
18. ΠϯσοΫε༗ແͷࠩΛݟͯΈΑ͏ 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ʯΛ࢖༻͠ɺ
    ΠϯσοΫεແ͠ঢ়ଶͷ݁ՌΛද͍ࣔͯ͠·͢ɻ
19. ࠓճߟ͑Δέʔε ͳͥૣ͘ͳͬͨͷ͔ʁ
20. ORDER BYͷ࠷దԽ • ΠϯσοΫε͕͋Δ͓͔͛ͰɺιʔτॲཧΛεΩοϓͭͭ͠ORDER BY۟ΛຬͨͤΔɻ • https://dev.mysql.com/doc/refman/5.7/ja/order-by- optimization.html • ࠷దԽ͕ޮ͔ͳ͍৔߹΋͋ΔͷͰ஫ҙ͕ඞཁɻ • ྫɿෳ਺ͷΩʔʹରͯ͠ɺORDER BYΛ࢖༻͍ͯ͠Δ৔߹
21. ΠϯσοΫε͕ ࢖ΘΕͳ͍৔߹ 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ͷશจݕࡧػೳʹೖͬͨ೔ຊޠύʔ αʔ͕࢖༻ՄೳͳͷͰɺͦͪΒ΋ީิʹೖΔ͔΋͠Εͳ͍ɻ —- ଞʹ΋৭ʑͳύλʔϯ͕͋Γ·͢ɻ • ΠϯσοΫεΛ࢖ͬͯ ͍Δͭ΋ΓͰ΋ɺ࣮͸ ςʔϒϧεΩϟϯ͕࣮ߦ ͞Εͯ͠·͏έʔεɻ
22. ͲͬͪͰ΋ྑ͍খൌ • গ͠લʹϖʔδωʔγϣϯΛ࡞ͬͨ࣌͸SQLͷνϡʔχϯά͕ٻΊΒ ΕΔγʔϯ͸ͳ͔ͬͨɻ • ςʔϒϧ΋গͳ͍͠ɺσʔλͷ૯਺΋2,000~3,000ఔ౓ɻ • Ή͠Ζɺը໘ଆͷReact࣮૷ʹ͕͔͔࣌ؒͬͨɻ
23. ࢀߟࢿྉ • 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
24. ·ͱΊ
25. ·ͱΊ • ΠϯσοΫε͸ۜͷ஄ؙͰ͸ͳ͍ɻ • SQLͷ࠷దԽΛ͢Δ࣌͸ɺެࣜͷυΩϡϝϯτΛ࠷ॳʹݟΔ΂͖ɻ • MySQLͷ৔߹: https://dev.mysql.com/doc/refman/5.6/ja/optimize- overview.html • Oracleͷैۀһαϯϓϧσʔλϕʔε͸࣮ݧ΍࿅शʹ࠷దͰͨ͠ɻ • յͯ͠΋୭ʹ΋໎࿭Λ͔͚ͳ͍ͷͰ҆৺ɻ • ݸਓͰ࢖͏෼ʹ͸໰୊ͳ͍͸͚ͣͩͲɺϥΠηϯε͸ҰԠݟ͓ͯ͘ͱ͍͍ɻ
26. ϖʔδωʔγϣϯΛ ਂງΓͨ͠࿩ͷ͓ͦ͢෼͚͸ Ҏ্Ͱ͢ɻ
27. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ