ActiveRecord 中 joins、includes、preload、eager_load 的区别

/

joins

joins 的使用场合

过滤结果,而不是访问关联表的记录。

Post.joins(:comments).where(comments: {user_id: 1}).map { |post| post.title }
=> SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."user_id" = ?  [["user_id", 1]]

joins 能阻止 N + 1 查询吗?

不。joins 不加载关联表的记录到内存,如果访问关联表的记录将触发 N + 1 查询。

Post.joins(:comments).where(comments: {user_id: 1}).map { |post| post.comments.size }
=> SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."user_id" = ?  [["user_id", 1]]
   SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 1]]
   SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 2]]
   SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 3]]
   SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 4]]

includes

includes 能阻止 N + 1 查询吗?

能。includes 加载当前表的记录以及作为 includes 方法参数的表的记录。

Post.includes(:comments).map { |post| post.title }
=> SELECT "posts".* FROM "posts"
   SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3, 4, 5)

includes 总是生成两次查询吗?

不。includes 默认生成两次查询(就像上面)。而当附加有 where 或者 order 等条件时,则使用 LEFT OUTER JOIN 生成单次查询。

Post.includes(:comments).where(comments: {user_id: 1}).map { |post| post.title }
=> SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."content" AS t0_r2, "posts"."user_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."content" AS t1_r1, "comments"."user_id" AS t1_r2, "comments"."post_id" AS t1_r3, "comments"."created_at" AS t1_r4, "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."user_id" = ?  [["user_id", 1]]

单次查询还是两次查询更快?

具体情况具体分析。两次查询通过附加 references 可以强制使用 LEFT OUTER JOIN 生成单次查询。

Post.includes(:comments).references(:comments).map { |post| post.title }
=> SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."content" AS t0_r2, "posts"."user_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."content" AS t1_r1, "comments"."user_id" AS t1_r2, "comments"."post_id" AS t1_r3, "comments"."created_at" AS t1_r4, "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

在已附加 references 的关联中进行条件查询会怎样?

与不附加 references 的条件查询其实是一样的。

Post.includes(:comments).references(:comments).where(comments: {user_id: 1}).map { |post| post.title }
=> SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."content" AS t0_r2, "posts"."user_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."content" AS t1_r1, "comments"."user_id" AS t1_r2, "comments"."post_id" AS t1_r3, "comments"."created_at" AS t1_r4, "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."user_id" = ?  [["user_id", 1]]

preload

preload 生成两次查询,跟 includes 的默认机制相同。

Post.preload(:comments).map { |post| post.title }
=> SELECT "posts".* FROM "posts"
   SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3, 4, 5)

混合 preload 和 joins

Post.joins(:comments).preload(:comments).map { |post| post.title }
=> SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"
   SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3, 4)

eager_load

eager_load 通过使用 LEFT OUTER JOIN 生成单次查询,跟 includes + references 相同。

Post.eager_load(:comments).map { |post| post.title }
=> SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."content" AS t0_r2, "posts"."user_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."content" AS t1_r1, "comments"."user_id" AS t1_r2, "comments"."post_id" AS t1_r3, "comments"."created_at" AS t1_r4, "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

混合 eager_load 和 joins

Post.joins(:comments).eager_load(:comments).map { |post| post.comments.size }
=> SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."content" AS t0_r2, "posts"."user_id" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."content" AS t1_r1, "comments"."user_id" AS t1_r2, "comments"."post_id" AS t1_r3, "comments"."created_at" AS t1_r4, "comments"."updated_at" AS t1_r5 FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"

参考:

  1. Making sense of ActiveRecord joins, includes, preload, and eager_load
  2. preload, eager_load, includes, references, and joins in Rails

Comments