扩展高分数据库

 

我为网络游戏提供了一个简单的高分服务，并且它比预期更受欢迎。 高分是一个web服务，它使用带有简单表的MYSQL后端，如下所示。 每个高分记录都作为一行存储在此表中。 问题是，对于大于140k行的行，我发现某些关键查询的速度变慢，以致于很快无法处理请求。

主表看起来像这样：

  • ID是每个得分记录的唯一键
  • 游戏是提交分数的游戏的ID号码（目前，总是等于“1”，不过即将支持更多游戏）
  • name是该玩家提交的显示名称
  • playerId是给定用户的唯一ID
  • 分数是一个数字分数表示，例如42,035
  • 时间是提交时间
  • 排名是一个很大的整数，用于对给定游戏的分数提交进行唯一排序。 人们通常会得到一定的分数，所以在这种情况下，领带被谁首先提交打破。 因此该字段的值大致等于“分数* 100000000 +（MAX_TIME - 时间）” 
    • +----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int(11)       | NO   | PRI | NULL    | auto_increment |
| game     | int(11)       | YES  | MUL | NULL    |                |
| name     | varchar(100)  | YES  |     | NULL    |                |
| playerId | varchar(50)   | YES  |     | NULL    |                |
| score    | int(11)       | YES  |     | NULL    |                |
| time     | datetime      | YES  |     | NULL    |                |
| rank     | decimal(50,0) | YES  | MUL | NULL    |                |
+----------+---------------+------+-----+---------+----------------+

    索引看起来像这样： 

    +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| pozscores |          0 | PRIMARY  |            1 | id          | A         |      138296 |     NULL | NULL   |      | BTREE      |         |
| pozscores |          0 | game     |            1 | game        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pozscores |          0 | game     |            2 | rank        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pozscores |          1 | rank     |            1 | rank        | A         |      138296 |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

    当用户请求高分时，他们通常从“按等级降序列表排序”中的任意点请求大约75个高分。 这些请求通常用于“全天候”或仅用于过去7天的分数。

    典型的查询如下所示： "SELECT * FROM scoretable WHERE game=1 AND time>? ORDER BY rank DESC LIMIT 0, 75;" 并在0.00秒内运行。

    但是，如果您向列表末尾请求"SELECT * FROM scoretable WHERE game=1 AND time>? ORDER BY rank DESC LIMIT 10000, 75;" 并在0.06秒内运行。

    "SELECT * FROM scoretable WHERE game=1 AND time>? ORDER BY rank DESC LIMIT 100000, 75;" 并在0.58秒内运行。

    看起来这将很快开始，因为每天提交数千个新的分数太长！

    此外，还有两种其他类型的查询，用于通过排序列表中的id查找特定的玩家。 他们看起来像这样：

    "SELECT * FROM scoretable WHERE game=1 AND time>? AND playerId=? ORDER BY rank DESC LIMIT 1"

    其次是a

    "SELECT count(id) as count FROM scoretable WHERE game=1 AND time>? AND rank>[rank returned from above]"

    我的问题是：可以做些什么来使这个可扩展的系统？ 我可以很快看到行数增长到几百万。 我希望选择一些聪明的指数会有所帮助，但改善只是微乎其微。

    更新：这是一个解释行： 

    mysql> explain SELECT * FROM scoretable WHERE game=1 AND time>0 ORDER BY rank DESC LIMIT 100000, 75;
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | scoretable| range | game          | game | 5       | NULL | 138478 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-------------+

    找到解决方案

    我已经解决了这个问题，这要归功于这个线程的一些指针。 做一个聚集索引正是我所需要的，所以我将表转换为在MySQL中使用InnoDB，它支持聚簇索引。 接下来，我删除了id字段，并将主键设置为（游戏ASC，rank DESC）。 现在，无论我使用什么偏移量，所有查询都运行得非常快。 解释显示没有额外的排序正在完成，它看起来很容易处理所有的流量。

    看到如何没有接受者，我会给它一个镜头。 我来自SQL Server背景，但适用相同的想法。

    一些一般性观察：

  • ID列几乎没有意义，除非有其他表/查询没有告诉我们，否则不应该参与任何索引。 事实上，它甚至不需要在最后的查询中。 你可以做COUNT（*）。
  • 您的聚集索引应该针对您最常见的查询。 因此，游戏ASC，时间DESC和等级DESC上的聚集索引效果良好。 按照时间排序DESC对于像这样的历史表格来说通常是一个好主意，通常你会对最近的东西感兴趣。 你也可以尝试一个单独的索引，排名其他方向，但我不知道这将是多少好处。
  • 你确定你需要SELECT *吗？ 如果您可以选择较少的列，则可以创建包含SELECT和WHERE所需的所有列的索引。

    • 100万行真的不是那么多。 我创建了一个拥有1,000,000行样本数据的表，即使只有一个索引（游戏ASC，时间DESC和级别DESC），所有查询的运行时间都不到1秒。

    （我不确定的唯一部分是playerId，查询表现得非常好，以至于playerId似乎不是必需的，也许你可以在聚集索引的末尾添加它。）

    链接地址: http://www.djcxy.com/p/50453.html

    上一篇: Scaling a High Score Database

    下一篇: Combining Facebook likes of URL and Facebook page