mysql - 限制连接以避免查询运行时间过长

Question

我有以下查询，该查询在表中查找双向连接，该表通过与其自身的联接保持单向连接:

    SELECT tbl1.to, LEAST( tbl2.counter, tbl1.counter ) AS minCounter
    FROM connections tbl1
       INNER JOIN connections tbl2 ON tbl2.from = tbl1.to
       WHERE tbl1.from =<user_id>
             AND tbl2.to =<user_id>
       ORDER BY LEAST( tbl2.counter, tbl1.counter ) DESC 
       LIMIT 15

这表现得相当好，但有时当有大量 from 连接时，它会变得太慢。我想要做出的权衡是降低其准确性，并对他实际用于查询中联接的 from 连接数量添加限制。这可能吗？

Answer 1

5 秒的 1.5B 行连接听起来还不错。但此查询速度缓慢的原因之一是它会在没有 limit 子句的情况下返回大量行。这是因为您还指定了排序顺序。排序和添加限制将导致首先对完整结果进行排序，以便确定应返回哪 15 行。

如果您愿意牺牲准确性来获得更好的性能，请尝试删除order by，或者在限制结果后进行排序。

SELECT * FROM 
   (SELECT tbl1.to, LEAST( tbl2.counter, tbl1.counter ) AS minCounter
    FROM connections tbl1
       INNER JOIN connections tbl2 ON tbl2.from = tbl1.to
       WHERE tbl1.from =<user_id>
             AND tbl2.to =<user_id>
       LIMIT 15)
ORDER BY minCounter DESC 

或者，您可以尝试先将记录数限制为合理的数量(本例中为 500 条)，然后再次进行排序和限制。这样，对于具有最多 500 个结果的用户，结果将 100% 准确，而对于具有更多结果的用户，结果将不太准确。

SELECT * FROM 
   (SELECT tbl1.to, LEAST( tbl2.counter, tbl1.counter ) AS minCounter
    FROM connections tbl1
       INNER JOIN connections tbl2 ON tbl2.from = tbl1.to
       WHERE tbl1.from =<user_id>
             AND tbl2.to =<user_id>
       LIMIT 500)
ORDER BY minCounter DESC 
LIMIT 15