MySql：获得排名繁重的查询优化-需要帮助

debugcn 发表于 Dev

abd_ashi

我的数据库中只有下表：

分数表

栏位... | .... Type ......... Null .. | ..Key .. | .. .........默认................. | ..额外
................... ................................................... ................................................... ........................
id ....................... | .... ....... int（20）... | ..NO ... | ..PRI .. | ..NULL ................... ......... auto_increment
scr ........................ int（20）。 .. | ..NO ... | ........... | ..... 0 ....................... ..... | ..........................
player_name .. | ..varchar（150）.. | ..NO ... | .......... | ..NULL ..................................... | .........................
位置.............. | .varchar（5）....... | ..NO ... | .......... | ..NULL ..................................... | .........................
DateUpdated .. | ..timestamp ...... | ..NO ... | .......... | CURRENT_TIMESTAMP | ..on-update

目前，我在id字段上有一个索引。

我正在处理100万条记录。每个玩家只会在数据库中有一个记录。

我想根据以下示例来检索特定玩家的全球排名：约翰，id：682：

排名.......... Id ....... SCR ...... DateUpdated
----------------------- --------------------------------------
15257 ... 53264 ....... 62 ........ 2013-3-10 16:45:37
15258 ... 3533 ......... 62 ....... 2013-3-10 16:45 ：37
15259 ... 7283 ......... 62 ........ 2013-3-13
16:45:37 15260 ... 386 .......... .61 ........ 2013-3-09 18:55:25
15261 ... 78252 ....... 61 ....... 2013-3-10 13:33： 21
15262 ... 682 ........... 61 ........ 2013-3-10 16:45:37 <==这是我们的播放器
15263 ... 9263 .. ....... 61 ....... 2013-3-10 16:45:37
15264 ... 7263 ......... 61 ....... 2013 -3-10 16:56:25
15265 ... 7826 ......... 60 ....... 2013-3-10
12:26:37 15266 ... 9276 ... ...... 60 ........ 2013-3-10 15:22:37 15267
... 932872 ..... 60 ....... 2013-3-13 11 ：45：37

该玩家出现在中间，上方有5位玩家，下方有5位玩家。请注意，排名由scr排序，然后由DateUpdated排序

这是我的查询，带来了这些结果：

 SELECT id,
  scr,
  player_name,
  location,
  dateupdated,
  rank
FROM
  (SELECT id,
    scr,
    player_name,
    location,
    dateupdated
  FROM scores
  WHERE id IN
    (SELECT id
    FROM
      (SELECT id
      FROM scores
      WHERE id IN
        (SELECT id
        FROM scores
        WHERE scr >=
          (SELECT scr FROM scores WHERE id = 1140188
          )
        AND id     != 1140188
        AND id NOT IN
          (SELECT id
          FROM scores
          WHERE scr IN
            (SELECT scr FROM scores WHERE id = 1140188
            )
          AND dateupdated >=
            (SELECT dateupdated FROM scores WHERE id = 1140188
            )
          )
        ORDER BY scr ASC,
          dateupdated ASC
        )
      ORDER BY scr,
        dateupdated ASC limit 0,
        5
      ) AS t
    UNION ALL
    SELECT id FROM
      (SELECT id FROM scores WHERE id = 1140188
      ) AS g
    UNION ALL
    SELECT id
    FROM
      (SELECT id
      FROM scores
      WHERE id IN
        (SELECT id
        FROM scores
        WHERE scr <=
          (SELECT scr FROM scores WHERE id = 1140188
          )
        AND id     != 1140188
        AND id NOT IN
          (SELECT id
          FROM scores
          WHERE scr IN
            (SELECT scr FROM scores WHERE id = 1140188
            )
          AND dateupdated <
            (SELECT dateupdated FROM scores WHERE id = 1140188
            )
          )
        ORDER BY scr ASC,
          dateupdated ASC
        )
      ORDER BY scr DESC,
        dateupdated ASC limit 0,
        5
      ) AS s
    )
  ORDER BY scr DESC,
    dateupdated ASC
  ) AS A
LEFT JOIN
  (SELECT l.id             AS id2,
    @curRow := @curRow + 1 AS Rank
  FROM scores l
  JOIN
    (SELECT @curRow := 0
    ) r
  ORDER BY scr DESC,
    dateupdated ASC
  ) AS B ON A.id = B.id2;

但是此查询在我的本地计算机上大约需要8秒钟，并且会消耗大量资源，因此在Web服务上实现此查询将导致灾难。

任何人都可以在这里提供任何提示，甚至欢迎使用全新的查询..

请帮忙！！！！！

斯坦尼斯拉夫

SELECT
all_ranked.*
FROM (select rank 
      from (SELECT l.id             AS id2,
            @curRow := @curRow + 1 AS Rank
            FROM scores l
            JOIN
                (SELECT @curRow := 0) r
                ORDER BY scr DESC, dateupdated ASC
            ) AS B) 
      where B.id=1234567) as rank_record, <--- just one record - value of rank
      (SELECT l.id             AS id2,
            @curRow := @curRow + 1 AS Rank
            FROM scores l
            JOIN
                (SELECT @curRow := 0) r
                ORDER BY scr DESC, dateupdated ASC
            ) AS all_ranked <--- all ranked users
where all_ranked.rank>=rank_record.rank-5 and all_ranked.rank>=rank_record.rank+5;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。