优化慢排名查询(Optimize slow ranking query)

我需要优化是永远走排名查询（本身作品的查询，但我知道，这太可怕了，我刚刚凭借良好的多项纪录尝试过了，它提供了超时）。

我会简单介绍一下模型。我有3个表：球员，球队和player_team。我的球员，可以属于一个团队。明显，因为它的声音，球员都存储在队伍的选手表和团队。在我的应用程序，每个玩家都可以随时切换团队，以及日志已被编程和维持。但是，一个球员被认为只属于一个团队在给定时间。玩家的目前的团队是最后一个他的加盟。

球员和球队的结构是不相关的，我想。我在每个ID列PK。在player_team我有：

id          (PK)
player_id   (FK -> player.id)
team_id     (FK -> team.id)

现在，每个团队都为每个已加入玩家的一个点。所以，现在，我想要得到的前N个队的名次与玩家的最大数量。

我最初的想法是让第一次从player_team目前的球员（也就是一个创纪录的顶部，每个球员，这个记录必须是玩家当前的团队）。我没能找到一个简单的方法来做到这一点（尝试GROUP BY player_team.player_id HAVING player_team.id = MAX（player_team.id），但是这并没有削减它。

我尝试了一些没有工作querys的，但设法得到这个工作。

SELECT 
    COUNT(*) AS total,
    pt.team_id,
    p.facebook_uid AS owner_uid, 
    t.color 
FROM 
    player_team pt 
JOIN player p ON (p.id = pt.player_id)  
JOIN team t ON (t.id = pt.team_id) 
WHERE 
    pt.id IN (
        SELECT max(J.id) 
        FROM player_team J 
        GROUP BY J.player_id
    )  

GROUP BY 
    pt.team_id 
ORDER BY 
    total DESC 
LIMIT 50

正如我所说的，它的工作原理，但看起来很糟糕，执行得很差，所以我敢肯定，必须有一个更好的方式去。任何人有优化任何想法？

我使用MySQL，顺便说一句。

提前致谢

添加解释。（对不起，不知道如何将它正确地格式化）

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     t   ALL     PRIMARY     NULL    NULL    NULL    5000    Using temporary; Using filesort
1   PRIMARY     pt  ref     FKplayer_pt77082,FKplayer_pt265938,new_index    FKplayer_pt77082    4   t.id    30  Using where
1   PRIMARY     p   eq_ref  PRIMARY     PRIMARY     4   pt.player_id    1
2   DEPENDENT SUBQUERY  J   index   NULL    new_index   8   NULL    150000  Using index

Answer 1:

尝试这个：

SELECT  t.*, cnt
FROM    (
        SELECT  team_id, COUNT(*) AS cnt
        FROM    (
                SELECT  player_id, MAX(id) AS mid
                FROM    player_team
                GROUP BY
                        player_id
                ) q
        JOIN    player_team pt
        ON      pt.id = q.mid
        GROUP BY
                team_id
        ) q2
JOIN    team t
ON      t.id = q2.team_id
ORDER BY
        cnt DESC
LIMIT 50

创建一个索引player_team (player_id, id)这个工作速度快（按照这个顺序）。

Answer 2:

它是杀害其子查询-如果你添加一个current场上player_team表，在那里你给它的值= 1，如果它是最新的，并且0，如果是老，你可以通过只是在做这简化了很多：

SELECT 
    COUNT(*) AS total,
    pt.team_id,
    p.facebook_uid AS owner_uid, 
    t.color 
FROM 
    player_team pt 
JOIN player p ON (p.id = pt.player_id)  
JOIN team t ON (t.id = pt.team_id) 
WHERE 
    player_team.current = 1 
GROUP BY 
    pt.team_id 
ORDER BY 
    total DESC 
LIMIT 50

有在多个条目player_team表，其中区分哪一个的唯一途径是“当前”的记录同样的关系是通过比较两个（或更多）行，我认为是不好的做法。我已经在之前这种情况下，你需要做的，使其工作真正杀死性能的解决办法。这是迄今为止最好能够看到这行做一个简单的查找（在这种情况下，目前where current=1 ） -或者通过移动历史数据到一个完全不同的表（根据您的情况，这可能是矫枉过正）。

Answer 3:

我有时会发现在MySQL更复杂的查询需要被分成两片。

第一片会拉需要到一个临时表中的数据和所述第二片将是一个试图操纵创建数据集中的查询。这样做肯定导致显著的性能增益。

Answer 4:

这将让目前的团队由大小排序的颜色：

  SELECT team_id, COUNT(player_id) c AS total, t.color 
    FROM player_team pt JOIN teams t ON t.team_id=pt.team_id  
    GROUP BY pt.team_id WHERE current=1
    ORDER BY pt.c DESC
    LIMIT 50;

但是你还没有给出哪个球员应该被认为是球队的老板的条件。您当前的查询任意一个显示播放器owner_id因为分组的，不是因为球员是实际所有者。如果您player_team表包含一个“所有者”栏，你可以加入上面查询到业主的查询。就像是：

SELECT o.facebook_uid, a.team_id, a.color, a.c
FROM player_teams pt1 
  JOIN players o ON (pt1.player_id=o.player_id AND o.owner=1)
  JOIN (...above query...) a
    ON a.team_id=pt1.team_id;

Answer 5:

你可以添加一列“last_playteam_id”给玩家表，每一个球员改变了他的团队与来自player_team表的PK时间更新。

然后，你可以这样做：

SELECT 
    COUNT(*) AS total,
    pt.team_id,
    p.facebook_uid AS owner_uid, 
    t.color 
FROM 
    player_team pt 
JOIN player p ON (p.id = pt.player_id)  and p.last_playteam_id = pt.id
JOIN team t ON (t.id = pt.team_id) 
GROUP BY 
    pt.team_id 
ORDER BY 
    total DESC 
LIMIT 50

这可能是最快的，因为你没有老player_team行更新到当前= 0。

您还可以添加，而不是列“last_team_id”，并保持它的当前团队那里，你得到最快的结果对于上面的查询，但它可能是与其他查询帮助较小。

文章来源: Optimize slow ranking query