时间戳

首页 > 解决方案 > 在每列都必须支持排序的排行榜表中存储数据的最佳方式是什么？

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

问题描述

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

目前的直播版本使用了我不喜欢的方法，但我想不出更好的解决方案。由于ORDER BY需要对每个统计信息都可用，我有一个包含 150 多列的大表，每列都是统计信息之一，现在这个表有大约 20 万行。被索引的列工作正常，但我用完了索引，因为 MySQL 总共只允许 64 个，并且没有索引的列加载速度非常慢，即使我正在查看数据的前 50 行。

我考虑过按类别拆分统计数据，并有 6 个左右的表格，其中的列更少，但这意味着如果有人想查看武器 x（武​​器类别）和玩过的游戏（主要统计数据），我必须将表格连接在一起类别）同时。

除了支持每个统计数据的排行榜外，还需要支持按比率创建的排行榜，最常见的是击杀/死亡、击杀/游戏、胜利/游戏。在我的实时版本中，这是受支持的，但它真的很慢，当我试图查看每场武器 x 击杀数（武器 x / 玩过的游戏数）的排行榜时，加载前 50 个结果需要几秒钟，这是什么时候两列都在同一个表中。

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

除了每个统计数据都有一列的表格之外，我还考虑过一个表格，其中一列称为stat_id一列count，但是当我尝试实现它时，我意识到如果平均每个玩家有 50 个统计数据并且有超过 20 万行已经，那么新表将有超过一百万行，每个查询都需要 JOIN 以找出 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"];

标签： mysqlsqldatabaseleaderboard

解决方案

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

你有没有研究过 MariaDB 列式存储？

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

如果你不能这样做，另一种解决方案是限制结果足以将排序（在 MySQL 之外）推送到客户端应用程序。

推荐阅读