在每一列都必须支持排序的排行榜表格中存储数据的最佳方式是什么？

Question

我目前正在使用一个外部API为一个游戏重建一个排行榜，其中游戏中的每个属性都可以被视为一个单独的排行榜。有超过150+的统计数据，只有少数玩家拥有所有的统计数据，大多数人可能会有大约50-100个。这些数据包括使用不同的武器，玩的时间，杀死的次数等等。

目前的实时版本使用了一种我不喜欢的方法，但我想不出更好的解决方案。因为每个统计数据都需要ORDER BY可用，所以我有一个包含150+列的大型表，每个列都是一个统计数据，这个表现在大约有200,000行。有索引的列可以正常工作，但是我耗尽了索引，因为MySQL总共只允许64个，并且没有索引的列加载起来非常慢，即使是在查看数据的前50行时也是如此。

我考虑过按类别划分统计数据，并在6个左右的表中包含较少的列，但这意味着如果有人想要同时查看武器x(武器类别)和游戏(主要统计类别)，我必须将表连接在一起。

除了支持每个统计数据的排行榜之外，还需要支持由比率创建的排行榜，最常见的是杀死/死亡，杀死/游戏，赢/游戏。在我的实时版本中，这是支持的，但它真的很慢，当我试图查看每个游戏(武器x/玩的游戏)的武器x杀伤数排行榜时，它需要几秒钟来加载前50个结果，这是当两个列在同一个表中时。

当添加球员统计数据时，包含他们的统计数据的旧行被标记为历史数据，因此排行榜查询可以只考虑实时数据，这确实提高了我的性能，因为我不必使用MAX(column)来为球员找到正确的值。但我确实希望以某种方式将这些历史数据保留在表中，以便人们可以查看历史排行榜。

除了为每个统计数据创建一个列的表之外，我还考虑了一个包含一个名为stat_id的列和一个名为count的列的表，但是当我尝试实现它时，我意识到如果每个玩家平均有50个统计数据，并且已经有超过20万行，那么新表将有超过一百万行，每个查询都需要一个连接来找出stat_id是什么。

我可能已经回答了我自己的问题，一个为每个统计数据都有一个列的表确实是最好的选择，但我也认为肯定有一些我没有意识到的事情，或者是另一种可能提高性能的方法，特别是在处理为球员计算排行榜位置的问题时，目前每个列需要50秒-2分钟。

这是“实时排行榜”的当前排行榜查询(不包括历史数据)，其中$columnsToSelect是列名的字符串列表，有时是列的比率，$this->params是用户可以用来定制结果集的参数对象。少于50场比赛的玩家不会存储在排行榜表格中，查询也是按Id排序的，这样就可以找到玩家的确切位置，并且可以找到他们所在的页面。

$sql = "SELECT s.`id`, `player`, $columnsToSelect, `lastupdated`;
        FROM stats_table as s
        WHERE $orderColumn <> 0
        AND `historical` = 0
        AND `banned` = 0 " . (
            $this->params["mingames"] > 50 ? (
                "AND `gamesplayed` >= " . $this->params["mingames"] 
            ) : ""
        );

$sql .= " AND ($orderColumn, s.id) <= (?, ?)
              ORDER BY $orderColumn DESC, s.`id` DESC
              LIMIT " . $this->params["rows"];

Answer 1

MySQL不是对每一列进行排序的最佳数据库解决方案。

你有没有看过MariaDB专栏商店？

https://www.percona.com/blog/2017/01/30/mariadb-columnstore/

如果您不能做到这一点，另一个解决方案是对结果进行足够的限制，以将排序(在MySQL之外)推送到客户端应用程序。