php - 3rd NF 中数据库中点的计算

Question

我有一个数据库，用于存储射击游戏的结果。我将它们放入 3NF 以允许系统扩展。所以它看起来像这样:

Player
-------------------
GameId integer
PlayerId integer
TeamId integer

Hits
-------------------
GameId integer 
FromId integer
ToId integer
Hits integer

所以基本上每个游戏都有一个 ID，每个玩家和团队都有它的 ID(他们的名字存储在其他数据库中)

现在我想计算每个玩家的分数。我需要每场比赛的分数，但更重要的是每个球员的总分数。分数基本上是:每击中对手 3 分，每击中一名队员 -2 分，每击中 -2 分。
仅计算团队命中数就需要使用 3 个表进行 JOIN，我担心生产环境中的性能。 (每个游戏有约 8 名玩家 -> PlayerDB-Size 为 8n，HitsDB-Size 为 (8-1)^2*n)

最后:我需要计算每场比赛每个玩家的得分并将其相加，因为每场比赛的最低得分应该为零。最后获得每个玩家的排名(玩家 x 的总积分排名第二等)

我觉得我迷失在过于复杂的查询中，这会在某些时候降低数据库的性能。

有人可以评判这个设计吗？也许可以给我一些指示，让我从哪里开始进一步研究？我考虑过将 TeamHits 和每场比赛的得分存储在玩家数据库中(对它们进行求和的得分，用于统计目的的 teamHits)，但这当然会破坏标准化。

PS:我正在使用 PHP 5 和 MYSQL。我还考虑过从数据库中获取每个游戏，用 PHP 计算分数(当我展示游戏时我已经这样做了)并将其写回(最好将游戏放入数据库，但也当参数点变化)

编辑:避免子选择的想法是:

SELECT p.*, SUM(h.Hits) AS TeamHits, SUM(h2.Hits) as Hits
FROM player p
LEFT JOIN
  (hits h
    INNER JOIN player p2
    ON h.GameId=p2.GameId AND h.ToId=p2.PlayerId
  )
ON p.GameId=p2.GameId AND h.FromId=p.PlayerId AND p.TeamId=p2.TeamId
GROUP BY p.PlayerId, p.GameId
LEFT JOIN hits h2
ON h2.GameId=p.GameId AND h2.FromId=p.PlayerId

但这当然行不通。是否可以将分组与连接结合起来，还是必须使用子查询？
我最好的是:

SELECT p.PlayerId, SUM((-2-3)*IFNULL(th.TeamHits, 0) + (3)*IFNULL(h.Hits, 0) + (-2)*IFNULL(ht.HitsTaken, 0)) AS Points
FROM player p
LEFT JOIN
    (SELECT p.GameId, p.PlayerId, SUM(h.Hits) AS TeamHits
    FROM player p
    INNER JOIN hits h
    ON h.GameId=p.GameId AND p.PlayerId=h.FromId
    INNER JOIN player p2
    ON p.GameId=p2.GameId AND p2.PlayerId=h.ToId AND p.TeamId=p2.TeamId
    GROUP BY p.PlayerId, p.GameId) th
ON p.GameId=th.GameId AND p.PlayerId=th.PlayerId
LEFT JOIN
    (SELECT p.GameId, p.PlayerId, SUM(h.Hits) AS Hits
    FROM player p
    INNER JOIN hits h
    ON h.GameId=p.GameId AND p.PlayerId=h.FromId
    GROUP BY p.PlayerId, p.GameId) h
ON p.GameId=h.GameId AND p.PlayerId=h.PlayerId
LEFT JOIN
    (SELECT p.GameId, p.PlayerId, SUM(h.Hits) AS HitsTaken
    FROM player p
    INNER JOIN hits h
    ON h.GameId=p.GameId AND p.PlayerId=h.ToId
    INNER JOIN player p2
    ON p.GameId=p2.GameId AND p2.PlayerId=h.FromId AND p.TeamId!=p2.TeamId
    GROUP BY p.PlayerId, p.GameId) ht
ON p.GameId=ht.GameId AND p.PlayerId=ht.PlayerId
GROUP BY p.PlayerId

fiddle :http://sqlfiddle.com/#!9/dc0cb/4

当前问题:对于一个大约有 10,000 场比赛的数据库，计算所有玩家的分数大约需要 18 秒。这是无法使用的，所以我需要改进这个...

Answer 1

连接并不那么昂贵，子查询才是。只要您可以避免子查询，您就不会遇到太糟糕的情况。

请记住，现在已经为这些东西构建了数据库。只需确保您在正确的字段上有正确的索引，以便对其进行优化。像 teamID 和 GameID 和playerID 一样应该是索引。

只需在 phpmyadmin 中运行它，看看执行需要多少毫秒。如果它需要超过 50 个，则它是一个繁重的查询，但通常很难做到这一点...我曾经成功地进行了一个非常繁重的查询，从不同的表和 View 中连接了 100.000 多行，并且仍然在 5 毫秒内完成了。 .

我们所说的每小时请求数量是多少？一天200个玩家？一天20万玩家？请求多久发生一次？每个玩家每秒 10 次？一分钟一次？您的数据库负载如何？

我认为所有这些参数都很低，所以你还不应该担心这个优化。让您的游戏启动并运行，清理可以获得真正 yield 的​​ PHP 代码，并远离复杂的子查询或 View 。

只要你的表进行了连接和并集，它的速度就相当快了。如果您必须执行子查询，请查看是否没有其他方法，即使用链接表将某些结果链接到某些其他表，以便您可以执行联接而不是子查询。