mysql - 发现连接( friend 、 friend 的 friend )的查询太慢

Question

如果两个用户是 friend ，我在“ friend ”表中有一个条目，如下所示:

   table: friends
   ------------------------
   uid1           uid2
   332            333

现在假设有一个新用户(uid=100)加入。我的应用程序找到她附近的用户，然后尝试向她显示他们的联系(Facebook friend 、 friend 的 friend 等)。我为此目的使用此查询:

           SELECT (fr.id IS NOT NULL)+(f2.id IS NOT NULL)*2+(fr.id IS NULL AND f2.id IS NULL)*3 AS connection
           FROM users u
           LEFT JOIN friends AS fr ON ((fr.uid1='100' AND fr.uid2=u.uid) OR (fr.uid1=u.uid AND fr.uid2='100'))
           LEFT JOIN (friends AS f1, friends AS f2) ON fr.id IS NULL AND
           (
            (f1.uid1='100' AND f1.uid2=f2.uid1 AND f2.uid2=u.uid) OR
            (f1.uid2='100' AND f1.uid1=f2.uid1 AND f2.uid2=u.uid) OR
            (f1.uid2='100' AND f1.uid1=f2.uid2 AND f2.uid1=u.uid) OR
            (f1.uid1='100' AND f1.uid2=f2.uid2 AND f2.uid1=u.uid)
           ) 
           WHERE [location near her] 

此查询将查找新用户附近的人，对于每个人，如果与新用户是 friend ，则“连接”值将为 1；如果他们有共同 friend ，则为 2；否则为 3。

查询过去工作正常，但现在我的用户多了一点(10,000)，它变得非常慢(需要 10 秒才能找到新用户附近 10 个人的连接类型)。我的查询或数据库方案中是否有什么完全错误的事情？我知道定义索引会让事情变得更快，但我仍然认为对于这样简单的查询花费 10 秒是非常长的。如果有任何建议，我将不胜感激。

Answer 1

我在 sqlfiddle 上工作来解决你的问题。这就是我的建议:

• 请勿使用OR状况left join ，这是一个性能 killer ，最好进行 2 次或更多连接，而不是使用 OR
• 请勿使用fr.id IS NULL第二名left join (性能 killer )，而是在选择部分使用它:(fr.id IS NOT NULL)+(fr.id IS NULL AND f2.id IS NOT NULL)*2
• 使用CASE WHEN [cond] THEN [type_connection] ELSE 3 END显示 1/2/3 连接类型(更易读)

这是我建议的代码(例如:uid=1 的新用户):

SELECT
    u.uid
    ,CASE
        WHEN directFriend.frUid IS NOT NULL THEN 1
        WHEN commonFriend.frUid IS NOT NULL THEN 2
        ELSE 3
    END as connection
FROM
    users u
    LEFT JOIN(
            SELECT 
                u.uid as frUid
            FROM 
                users u
                LEFT JOIN friends as fr1
                    ON fr1.uid1='1' 
                    AND fr1.uid2=u.uid
                LEFT JOIN friends as fr2
                    ON fr2.uid1=u.uid 
                    AND fr2.uid2='1'
            WHERE
                fr1.uid2 IS NOT NULL 
                OR fr2.uid1 IS NOT NULL) as directFriend
        ON directFriend.frUid = u.uid
    LEFT JOIN(
            SELECT 
                CASE 
                    WHEN common1.uid2 IS NOT NULL THEN common1.uid2
                    WHEN common2.uid1 IS NOT NULL THEN common2.uid1
                END as frUid
            FROM 
                users u
                LEFT JOIN friends as fr1
                    ON fr1.uid1='1' 
                    and fr1.uid2=u.uid
                LEFT JOIN friends as fr2
                    ON fr2.uid2='1' 
                    and fr2.uid1=u.uid 
                LEFT JOIN friends as common1
                    ON common1.uid1 = fr1.uid2
                    AND common1.uid1 = u.uid
                LEFT JOIN friends as common2
                    ON common2.uid2 = fr2.uid1
                    AND common2.uid2 = u.uid
            WHERE
                common1.uid2 IS NOT NULL 
                OR common2.uid1 IS NOT NULL) as commonFriend
    ON commonFriend.frUid = u.uid

Here is the sqlfiddle demo used

希望有帮助:)