ActiveRecord N+1 count queries #shibuyarb

2015/07/15 shibuya.rb LT

https://flic.kr/p/3L83sZ by LASZLO ILYES
CC BY 2.0 https://creativecommons.org/licenses/by/2.0/

https://flic.kr/p/5nz5Gr by Tor-Sven Berge
CC BY-ND 2.0 https://creativecommons.org/licenses/by-nd/2.0/

Transcript

1. Ac#veRecord*N+1*count*queries 2015/07/15'Shibuya.rb @k0kubun
2. I'm$@k0kubun DevInfra)at)Cookpad
3. Let's&think&about N+1$count$queries
4. How$do$you$defeat$this? User.all.each do |user| user.tweets.count end # SELECT `users`.* FROM `users` # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 1 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 2 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 3 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 4 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 5
5. OK,$cache$it$:) User.all.each do |user| user.tweets_count end class Tweet < ActiveRecord::Base belongs_to :user, counter_cache: true end # SELECT `users`.* FROM `users` ͦΕ͕!Rails!way
6. OK...?
7. ʊਓਓਓਓਓਓਓਓਓਓʊ ʼɹALTER&TABLE&usersɹʻ ʉY^Y^Y^Y^Y^Y^Y^Y^Yʉ ΑͬΆͲݫ͘͠ͳ͍ͱ΍Γͨ͘ͳ͍
8. ແݶʹ૿͑ଓ͚ΔΧϥϜ CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tweets_count` int(11) NOT NULL DEFAULT '0', `replies_count` int(11) NOT NULL DEFAULT '0', `favorites_count` int(11) NOT NULL DEFAULT '0', `retweets_count` int(11) NOT NULL DEFAULT '0', `followers_count` int(11) NOT NULL DEFAULT '0', `following_count` int(11) NOT NULL DEFAULT '0', -- …
9. ͦͯ͠๚ΕΔഁ୼ ਨΕྲྀ͞ΕΔN+1ΫΤϦ
10. ͦΜͳ೰ΈΛղܾ͢Δ ࠷ߴͷGemΛ࡞Γ·ͨ͠
11. ac#verecord)precount N+1$count$query$killer$for$Ac3veRecord
12. ͜ΕΛͳ User.all.each do |user| user.tweets.count end # SELECT `users`.* FROM `users` # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 1 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 2 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 3 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 4 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 5
13. ͜͏͡Ό User.all.precount(:tweets).each do |user| user.tweets.count end # SELECT `users`.* FROM `users` # SELECT COUNT(`tweets`.`user_id`), `tweets`.`user_id` FROM # `tweets` WHERE `tweets`.`user_id` IN (1, 2, 3, 4, 5) # GROUP BY `tweets`.`user_id`
14. JOIN΋؆୯ User.all.eager_count(:tweets).each do |user| user.tweets.count end # SELECT `users`.`id` AS t0_r0, `users`.`created_at` AS t0_r3, # `users`.`updated_at` AS t0_r4, COUNT(`tweets`.`id`) AS t1_r0 # FROM `users` LEFT OUTER JOIN `tweets` # ON `tweets`.`user_id` = `users`.`id` GROUP BY users.id
15. ͦͯ͠଎͍ counter_cacheͳΜ͍ͯΒͳ͔ͬͨΜ΍
16. Just%write gem$"ac$verecord*precount"