Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

TwitterID:@rukiadia

September 25, 2020
Tweet

More Decks by TwitterID:@rukiadia

Other Decks in Programming

Transcript

  1. ࣮ߦ؀ڥʹ͍ͭͯ • ࠓճ͸MySQLެࣜͷDockerΠϝʔδΛ࢖͍·͢ɻόʔδϣϯ͸5.7Ͱ ࢼ͍ͯ͠·͢ɻ • ੑೳ͸CPUs: 2ɺMemory: 2.00GBɺSwap: 1GB
 ʢ͓ͦΒ͘ɺDocker

    Desktopͷॳظઃఆʣ • σʔλͷ౤ೖํ๏͸ϦϙδτϦͷREADMEΛࢀরͷ͜ͱɻ • https://github.com/datacharmer/test_db
  2. ·ͣ͸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) ʙఔ౓ͷ͕ࠩೝΊΒΕ·͢ɻ ઌ಄ͷߦΛऔಘ͚ͨͩ͠Ͱ͕͢ɺ ͳ͔ͳ͔ͷ͕ࠩग़·ͨ͠Ͷɻ
  3. ΠϯσοΫεΛར༻ͯ͠ΈΑ͏ —- hire_dateʹΠϯσοΫεΛுΔ ALTER TABLE employees ADD INDEX hire_date_index(hire_date); —-

    ΠϯσοΫεͷ֬ೝ EXPLAIN SELECT * FROM employees ORDER BY hire_date desc LIMIT 200; ࡞੒௚ޙ͸ΠϯσοΫεΛࢀরͰ͖ͳ͍ɻ ಺෦ʹΠϯσοΫε͕อ࣋͞Ε͍ͯͳ͍ͷͰɻ
  4. ΠϯσοΫε༗ແͷࠩΛݟͯΈΑ͏ 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ʯΛ࢖༻͠ɺ ΠϯσοΫεແ͠ঢ়ଶͷ݁ՌΛද͍ࣔͯ͠·͢ɻ
  5. ΠϯσοΫε͕ ࢖ΘΕͳ͍৔߹ 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ͷશจݕࡧػೳʹೖͬͨ೔ຊޠύʔ αʔ͕࢖༻ՄೳͳͷͰɺͦͪΒ΋ީิʹೖΔ͔΋͠Εͳ͍ɻ —- ଞʹ΋৭ʑͳύλʔϯ͕͋Γ·͢ɻ • ΠϯσοΫεΛ࢖ͬͯ ͍Δͭ΋ΓͰ΋ɺ࣮͸ ςʔϒϧεΩϟϯ͕࣮ߦ ͞Εͯ͠·͏έʔεɻ
  6. ࢀߟࢿྉ • 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
  7. ·ͱΊ • ΠϯσοΫε͸ۜͷ஄ؙͰ͸ͳ͍ɻ • SQLͷ࠷దԽΛ͢Δ࣌͸ɺެࣜͷυΩϡϝϯτΛ࠷ॳʹݟΔ΂͖ɻ • MySQLͷ৔߹: https://dev.mysql.com/doc/refman/5.6/ja/optimize- overview.html •

    Oracleͷैۀһαϯϓϧσʔλϕʔε͸࣮ݧ΍࿅शʹ࠷దͰͨ͠ɻ • յͯ͠΋୭ʹ΋໎࿭Λ͔͚ͳ͍ͷͰ҆৺ɻ • ݸਓͰ࢖͏෼ʹ͸໰୊ͳ͍͸͚ͣͩͲɺϥΠηϯε͸ҰԠݟ͓ͯ͘ͱ͍͍ɻ