mysql - rails : Performance issue with joining of records

Question

我对 ActiveRecord 和 MySQL 进行了以下设置:

1. 用户通过成员资格拥有许多组
2. Group 通过成员(member)资格拥有许多用户

schema.rb 中还描述了group_id 和user_id 的索引:

add_index "memberships", ["group_id", "user_id"], name: "uugj_index", using: :btree

3 种不同的查询:

User.where(id: Membership.uniq.pluck(:user_id))

(3.8ms) SELECT DISTINCT memberships.user_id FROM membershipsUser Load (11.0ms) SELECT users.* FROM users WHERE users.id IN (1, 2...)

User.where(id: Membership.uniq.select(:user_id))

User Load (15.2ms) SELECT users.* FROM users WHERE users.id IN (SELECT DISTINCT memberships.user_id FROM memberships)

User.uniq.joins(:memberships)

User Load (135.1ms) SELECT DISTINCT users.* FROM users INNER JOIN memberships ON memberships.user_id = users.id

执行此操作的最佳方法是什么？为什么使用 join 的查询要慢得多？

Answer 1

第一个查询很糟糕，因为它将所有用户 ID 吸取到一个 Ruby 数组中，然后将它们发送回数据库。如果你有很多用户，那就是一个巨大的数组和大量的带宽，再加上 2 次到数据库的往返而不是一次。此外，数据库无法有效处理如此庞大的数组。

第二种和第三种方法都是高效的数据库驱动解决方案(一种是子查询，一种是连接)，但是你需要有合适的索引。您需要 user_id 上的memberships 表的索引。

add_index :memberships, :user_id

您已有的索引仅在您想要查找属于特定 组的所有用户时才有用。

更新:

如果您的 users 表中有很多列和数据，则第三个查询中的 DISTINCT users.* 会相当慢，因为 MySQL 必须比较大量数据以确保唯一性。

要清楚:这不是 JOIN 固有的缓慢，而是 DISTINCT 的缓慢。例如:这是一种避免 DISTINCT 并仍然使用 JOIN 的方法:

SELECT users.* FROM users
INNER JOIN (SELECT DISTINCT memberships.user_id FROM memberships) AS user_ids
ON user_ids.user_id = users.id;

鉴于所有这些，在这种情况下，我相信第二个查询将是最适合您的方法。如果添加上述索引，第二个查询应该比原始结果中报告的更快。如果您在添加索引后还没有这样做，请重试第二种方法。

虽然第一个查询本身存在一些缓慢的问题，但从您的评论来看，很明显它仍然比第三个查询快(至少对于您的特定数据集而言)。这些方法的权衡将取决于您的特定数据集，即您拥有多少用户以及您拥有多少成员(member)资格。一般来说，我认为第一种方法仍然是最差的，即使它最终更快。

另外，请注意我推荐的索引是专门为您在问题中列出的三个查询而设计的。如果您对这些表有其他类型的查询，您可能会更好地使用额外的索引，或者可能是多列索引，正如@tata 在他/她的回答中提到的那样。