Rails eager_group 修复 n+1 聚合函数问题

flyerhzm · 2015年06月29日 · 最后由 lijunwei 回复于 2021年12月19日 · 6840 次阅读
本帖已被管理员设置为精华贴

英文版 更有趣些

主要是来解决 includes 和 counter_cache 无法解决的一些 n+1 查询,比如

SELECT "posts".* FROM "posts";
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 1 AND "comments"."status" = 'approved'
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 2 AND "comments"."status" = 'approved'
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 3 AND "comments"."status" = 'approved'

=>

SELECT "posts".* FROM "posts";
SELECT COUNT(*) AS count_all, post_id AS post_id FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3) AND "comments"."status" = 'approved' GROUP BY post_id;

或者是

SELECT "posts".* FROM "posts";
SELECT AVG("comments"."rating") AS avg_id FROM "comments" WHERE "comments"."post_id" = 1;
SELECT AVG("comments"."rating") AS avg_id FROM "comments" WHERE "comments"."post_id" = 2;
SELECT AVG("comments"."rating") AS avg_id FROM "comments" WHERE "comments"."post_id" = 3;

=>

SELECT "posts".* FROM "posts";
SELECT AVG("comments"."rating") AS average_comments_rating, post_id AS post_id FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3) GROUP BY post_id;

使用起来会很简单

class Post < ActiveRecord::Base
  has_many :comments

  define_eager_group :comments_average_rating, :comments, :average, :rating
  define_eager_group :approved_comments_count, :comments, :count, :*, -> { approved }
end

class Comment < ActiveRecord::Base
  belongs_to :post

  scope :approved, -> { where(status: 'approved') }
end

用 define_eager_group 来定义 aggregate sql functions,然后就可以用 eager_group 来查询

posts = Post.all.eager_group(:comments_average_rating, :approved_comments_count)
posts.each do |post|
  post.comments_average_rating
  post.approved_comments_count
end

做了简单的性能测试,可以快非常多,我的例子是 10 倍。

项目地址在 (https://github.com/xinminlabs/eager_group)

@hooopo

  1. counter_cache_with_conditions,我之前也考虑过加字段手动维护,但是一旦条件变复杂了,数据很容易发生偏差,另外 counter_cache_with_conditions 只能支持 COUNT,其它聚合函数就无能为力了。
  2. view 也是不错的选择,但是结果会变慢,而且在 rails 中维护 db view 是非常麻烦的。materialized view 就更不合适了,需要定时刷新,数据的实时性被破坏了。

#2 楼 @flyerhzm

  1. 同意
  2. 关于定时刷新问题其实没有想象那么严重,上面提到按照你数据变更的频繁程度,自己选择是实时、延迟、还是定时刷新。实际项目中,其实写入没有那么频繁,比如像 RubyChina 这样的应用,我只要保证有回帖和发帖实时刷新就可以,性能完全不是问题。当然,数据量大到一定程度的话,实时刷新代价太大,可以考虑延迟和定时刷新,这样只会有部分数据发生微小偏差,但可以保证最终数据的一致性。

  3. 还是实际中使用的问题,您的需求显然是为 eager group 专门设计的,但我只要稍微改变一点需求,eager group 立马变得不可用,而且这个改动还是非常合理的:按照 approved_comments_count 倒序分页。Materialized View 方案就完全可以解决:

Materialized View + JOIN 方案:

posts = Post.joins(:post_agg_mv).order("approved_comments_count DESC").limit(10).all 
posts.each do |post|
  post.comments_average_rating #需要delegate一下
  post.approved_comments_count
end

Materialized View + include 方案:

// post_agg_mv.approved_comments_count 上面上可以加索引的
posts_mv = PostAggMv.include(:post).order("approved_comments_count DESC").limit(10).all
posts_mv.each do |post_mv|
  post = post_mv.post
  post.comments_average_rating #需要delegate一下
  post.approved_comments_count
end

@hooopo 这个需求的确是还不能支持,但是也不难,就像 includes + where/order 可以转成 JOIN,eager_group + where/order 也可以通过 JOIN 来完成。

#4 楼 @flyerhzm 能实现,但效率很差,需要全表 group,再 join,再 order & limit.

@hooopo 是的,不过如果实时性要求不高的话,与其用 materialized view,我更倾向用 redis 的 sorted set 来实现排序。

@flyerhzm 看了英文的 blog,好像有个 typo?

@hooopo 刷新的时候好像只能

REFRESH MATERIALIZED VIEW customers_with_revenue;

这样代价还是挺高的。

@flyerhzm 感觉这个做缓存还是挺不错的。缓存一些实时性不是很高的数据,然后又有可以直接使用 join 和其他表做关联的优点。

有篇文章介绍在 rails 中如何使用 ActiveRecord + PostgreSQL Materialized Views

感觉这个 gem 不错,可以制定 refresh triggers,指定更新某一行的 materialized_views materialized_views

手写 sql 吧,sql 有这么难写么。。。

https://github.com/magnusvk/counter_culture 这个可以指定条件,只是多态是个问题

试了一下,polymorphic 情况下的 has_many through 不行。

class Organization
  has_many :dynamic_field_adapters, as: :attached
  has_many :dynamic_fields, through: :dynamic_field_adapters


  define_eager_group :fields_count, :dynamic_fields, :count, :*
end

Organization.limit(2).eager_group(:fields_count).first.fields_count
# => nil
hooopo 一招秒杀 N+1 agg 问题 提及了此话题。 11月01日 03:33
ceclinux-github 一招秒杀 N+1 agg 问题 提及了此话题。 11月01日 14:34

文章顶部的 "英文版" 链接 404 了😵

需要 登录 后方可回复, 如果你还没有账号请 注册新账号