mysql - 如何优化 2700 万行表上的联接

Question

我有一个名为 follow 的表，它有 2 列 user1_id、user2_id其中 user1 跟随 user2。如果 user1 关注 user2 并且 user2 关注 user1 他们是 friend 。我必须找到 friend 并将它们存储在表中，但我希望在方便的时间完成此任务，因为我有 2700 万行。我试过了

create temporary table friends as (
select f1.* 
from follow f1 inner join follow f2
on f1.user1_id = f2.user2_id and f1.user2_id = f2.user1_id)

和

create temporary table friends as (
select user1_id, user2_id
from follow
where (user2_id, user1_id) in (select * from follow))

但是他们花费了太多时间。有什么可以提高这个 Action 的性能吗？您能为这个示例建议一个更好的查询吗？

Answer 1

我建议两种方法。第一个使用 group by:

create table friends as
      select least(user1_id, user2_id) as user1_id, greatest(user1_id, user2_id) as user2_id
      from follow
      group by least(user1_id, user2_id), greatest(user1_id, user2_id)
      having count(*) > 1;

这会产生group by的成本，虽然很昂贵，但可能是一个不错的选择。

另一种方法是在 follow(user1_id, user2_id) 上创建索引并执行以下操作:

create table friends as
    select user1_id, user2_id
    from follow f
    where user1_id < user2_id and
          exists (select 1
                  from follow f2
                  where f2.user1_id = f.user2_id and f2.user2_id = f.user1_id
                 );

这会导致对表中的许多记录进行索引查找，但这可能是最好的选择。