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

 ページネーションから考える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

438799c739f93d00abc30fa2da33c543?s=128

TwitterID:@rukiadia

September 25, 2020
Tweet

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. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ