代码之家 › 专栏 › 技术社区 › Juan Pablo Califano

优化慢排名查询

ranking subquery optimization mysql

Juan Pablo Califano · 技术社区 · 16 年前

我需要优化一个查询,以获得一个永久的排名(查询本身是有效的,但我知道这很糟糕,我刚用大量记录尝试过,它给出了一个超时)。

我将简单地解释一下这个模型。我有三张桌子:球员,球队和球员队伍。我有球员,可以属于一个球队。听起来很明显,玩家被存储在玩家表中,团队中的团队。在我的应用程序中,每个玩家都可以在任何时候切换团队,并且必须保存一个日志。然而,一个球员在给定的时间被认为只属于一个球队。一个球员目前的球队是他最后一个加入的球队。

我认为球员和球队的结构是不相关的。我每个人都有一个ID列pk。在球员队伍中,我有:

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

现在,每个团队为加入的每个玩家分配一个点。所以,现在,我想得到一个排名前N个球队的球员数量最多。

我的第一个想法是首先从球员队伍中选出当前的球员(即每个球员都有一个最高记录;这个记录必须是球员的当前队伍)。我找不到一个简单的方法去做(尝试按球员组分组,球员组,球员组,球员组,id=max(球员组,id),但这并没有切断它。

我尝试了一些不起作用的查询,但成功地使其起作用。

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

5 回复 | 直到 16 年前

Quassnoi 16 年前

试试这个:

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) (按此顺序)以便快速工作。

Dal Hundal 16 年前

它是杀死它的子查询-如果您添加 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

在 球员队伍 同一关系的表,其中区分“当前”记录的唯一方法是比较两个(或更多)行,我认为这是不好的做法。我以前也遇到过这种情况,你必须做些变通,才能让它真正发挥作用,扼杀性能。通过执行简单的查找(在本例中, where current=1 )-或者将历史数据移动到一个完全不同的表中(取决于您的情况,这可能会造成过度杀伤力)。

John M 16 年前

我有时会发现MySQL中更复杂的查询需要分成两部分。

第一个部分将把所需的数据拉到临时表中,第二个部分是试图操作所创建数据集的查询。这样做肯定会带来显著的性能提升。

dnagirl 16 年前

这将使当前的团队按大小订购颜色:

  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;

但你还没有给出一个条件,在这个条件下,球员应该被视为球队的老板。您当前的查询是因为分组而任意地将一个玩家显示为所有者ID,而不是因为该玩家是实际的所有者。如果您的player_team表包含“owner”列,则可以将上述查询加入到所有者查询中。比如:

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;

ceteras 16 年前

您可以在player表中添加一个列“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

这可能是最快的,因为您不必将旧玩家的团队行更新为current=0。

您还可以添加一列“最后一个团队ID”,并将其保持在当前团队中,这样您就可以获得上述查询的最快结果,但对其他查询的帮助可能会更少。