君も Arel おじさんになって ActiveRecord のクエリを高速化しよう / Let's become Uncle Arel

Transcript

1. ܅΋ Arel ͓͡͞Μʹ ͳͬͯ ActiveRecord ͷΫΤϦΛߴ଎Խ͠Α͏ ©morygonzalez Fukuoka.rb #66
2. ࣗݾ঺հ • Kaizen Platform ͱ͍͏ SaaS ͷձࣾʹࡏ੶͍ͯ͠·͢ • Ϩʔϧζྺ5೥͘Β͍ • ͓ͬ͞Μ͚ͩͲ ActiveRecord ʢORMʣͳ͍ͱΫΤϦॻ͚ͳ͍ΏͱΓϓϩάϥϚʔͰ͢… ©morygonzalez Fukuoka.rb #66
3. ΁ʔࣾͷ DB ͷεΩʔϚɺΊͬͪΌෳࡶͰ͢ ©morygonzalez Fukuoka.rb #66
4. Kaizen Platform Ͱ΍ͬͯΔ͜ͱ • ͓٬͞ΜͷαΠτʹ JS ೖΕͯ΋Β͏ • A/B ςετ͢Δ • ܭଌ͢Δ • BigQuery ʹϩάஷΊΔ • όονͰϩάΛूܭͯ͠ MySQL ʹಥͬࠐΉ • A/B ςετͷ݁ՌΛදࣔ͢Δ • etc. ©morygonzalez Fukuoka.rb #66
5. ݫ͍͠… ©morygonzalez Fukuoka.rb #66
6. Ϣʔβʔͷ֓೦͕ෳࡶ • Ϣʔβʔ • ૊৫ • νʔϜ • ΤʔδΣϯτ • etc. ©morygonzalez Fukuoka.rb #66
7. Ϣʔβʔͷݕࡧػೳ͕ΊͬͪΌ஗͍… • PM ʮϢʔβʔ໊͔૊৫໊͔νʔϜ໊Ͱݕࡧͯ͠Ϣʔβʔͷ updated_at ΧϥϜͰ sort ͯ͠Αʯ • Θͨ͠ʮྃղͰ͢ʯ • Tech Lead ఼ʮεϩʔΫΤϦʹͳͬͯΔΜͰ͚͢Ͳ…ʯ ©morygonzalez Fukuoka.rb #66
8. MySQL ͷಛੑ • WHERE ۟Ͱ࢖͏ΧϥϜͱ ORDER BY Ͱ sort ͢Δͱ͖ʹ࢖ ͏ΧϥϜ͕ҟͳΔͱ sort ࣌ʹΠϯσοΫε͕࢖ΘΕͳ͍ ©morygonzalez Fukuoka.rb #66
9. MySQL ͷಛੑ ͜͏͍͏ͷ͸× CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` tinyint(1) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; ALTER TABLE users ADD INDEX index_users_on_username(username); ALTER TABLE users ADD INDEX index_users_on_created_at(created_at); SELECT * FROM users WHERE username = 'foo' ORDER BY created_at; ※͜ͷลద౰ʹॻ͍ͨͷͰؒҧͬͯͨΒ͢Έ·ͤΜ… ©morygonzalez Fukuoka.rb #66
10. MySQL ͷಛੑ ͜͏͍͏ͷ͸◦ CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` tinyint(1) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; ALTER TABLE users ADD INDEX index_users_on_username_and_created_at(username, created_at); SELECT * FROM users WHERE username = 'foo' ORDER BY created_at; ※͜ͷลద౰ʹॻ͍ͨͷͰؒҧͬͯͨΒ͢Έ·ͤΜ… ©morygonzalez Fukuoka.rb #66
11. Ͳ͏΍ͬͯղܾ͢Δ͔ʁ 1.ΫΤϦΛ2ճʹ෼͚Δʂʂʂɺʂ 2.select ͢ΔΧϥϜΛݮΒ͢ ©morygonzalez Fukuoka.rb #66
12. ͦͷൃ૝͸ͳ͔ͬͨΘ… ©morygonzalez Fukuoka.rb #66
13. 1. ΫΤϦΛ2ճʹ෼͚Δ • ର৅ͷϢʔβʔΛݕࡧ͢ΔΫΤϦΛ౤͛ͯ user id ҰཡΛऔ Δ • ↑Ͱऔಘͨ͠ id Λ where ۟ʹೖΕͯݕࡧ͠ɺ updated_at ΧϥϜͰ sort ͢Δ • ೋͭͷΫΤϦͱ΋ΠϯσοΫεޮ͍ͯര଎Ͱ͢ ©morygonzalez Fukuoka.rb #66
14. 2. select ͢ΔΧϥϜΛݮΒ͢ • ActiveRecord ͸ෆඞཁͳΧϥϜ·Ͱશ෦ select ͠·͢ • ͍ͭ͜Λ΍Ίͯ͋͛Δ͚ͩͰ 1000ms ͘Β͍͔͔ͬͯͨΫΤ Ϧ͕ 500ms ͘Β͍ʹͳΓ·͢ ©morygonzalez Fukuoka.rb #66
15. 2. select ͢ΔΧϥϜΛݮΒ͢ User. includes(:public_organization). references(:public_organization). where(...) SELECT `users`.`id` AS t0_r0, `users`.`username` AS t0_r1, `users`.`email` AS t0_r2, ... FROM users WHERE ... Έ͍ͨͳඇਓؒతͳΫΤϦ͕ੜ੒͞ΕΔ… ©morygonzalez Fukuoka.rb #66
16. 2. select ͢ΔΧϥϜΛݮΒ͢ User. select( [@users[:id], @users[:username], @users[:allow_public_profile]] ). where(...) SELECT users.id, users.username, users.allow_public_profile FROM users WHERE ... ৗࣝతͳΫΤϦʹͳΓ·͢ɻ ©morygonzalez Fukuoka.rb #66
17. ORM Ͳͬ΀ΓͷΏͱΓϨΠϧβʔͩͱϝιουνΣʔϯͯ͠Ұจ ͰΫΤϦΛॻ͍ͯ͠·͍͕ͪ User.includes(:comments). where('users.name like ?', 'foo%'). where.not(foo: 'bar'). where('comments.body like ?', '%bar%') ©morygonzalez Fukuoka.rb #66
18. ແཧ͠ͳͯ͘΋͍͍ΜͩΑ… ©morygonzalez Fukuoka.rb #66
19. MySQL ͷؾ࣋ͪʹͳΖ ͏ʂʂɺʂ ©morygonzalez Fukuoka.rb #66
20. ORM Λ࢖Θͣʹ SQL Λॻ͘ͱ͖ʹΈ͍ͨʹαϒΫΤϦʹͨ͠ ΓɺΫΤϦΛ෼ׂͨ͠Γͯ͠ΠϯσοΫε͕࢖͑ΔΑ͏ͳΫΤϦΛ ࡉ͔͘෼͚ͯ౤͛ͯσʔλΛऔಘ͠·͠ΐ͏ ©morygonzalez Fukuoka.rb #66
21. ԾʹϝιουνΣʔϯͯ͠ෳࡶͳΫΤϦ͕ҰߦͰॻ͚ͯ΋ɺΠϯσ οΫε͕ޮ͔ͣʹ஗͔ͬͨΓ MySQL ʹෛՙΛ͔͚͍ͯͨΒҙຯ ͕͋Γ·ͤΜɻ ©morygonzalez Fukuoka.rb #66
22. Further Reading • ArelͰ৭ΜͳSQLΛ૊ΈཱͯͯΈΔ - ryopeko ͷԿ͔ • ׽(Φτί)ͷίϯϐϡʔλಓ: Using filesort ©morygonzalez Fukuoka.rb #66
23. ·ͱΊ • Rails Ͱ ActiveRecord ͹͔Γ࢖ͬͯΔͱΫΤϦͷνϡʔ χϯά͕͓Ζ͔ͦʹͳΔ • SQL ॻ͍ͯ Arel ʹͯ͠Կͱ͔͠Α͏ʂʂʂɺʂ ©morygonzalez Fukuoka.rb #66