mysql - 复杂查询优化提高速度

Question

我有以下查询想要优化:

SELECT 
    *, @rownum := @rownum + 1 AS rank
FROM (
        SELECT
                SUM(a.id = 1) as KILLS,
                SUM(a.id = 2) as DEATHS,
                SUM(a.id = 3) as WINS,
                tb1.totalPlaytime,
                p.playerName

        FROM
        (
                SELECT
                        player_id,
                        SUM(pg.timeEnded - pg.timeStarted) as totalPlaytime
                FROM playergame pg
                INNER JOIN player p
                        ON pg.player_id = p.id
                WHERE pg.game_id IN(1, 2, 3) 
                GROUP BY
                        p.id
                ORDER BY
                        p.playerName ASC
        ) tb1
        INNER JOIN playeraction pa
                ON pa.player_id = tb1.player_id
        INNER JOIN action a
                ON pa.action_id = a.id
        INNER JOIN player p
                ON pa.player_id = p.id      
        GROUP BY
                p.id
        ORDER BY
            KILLS DESC) tb2
 WHERE tb2.playerName LIKE "%"

不知何故，我感觉这不适合 mysql。我在不同的表中保留了很多操作，以获得良好的统计方法，但这会减慢一切。 (也许是大数据？)

这是我的模型

现在我尝试执行以下操作:

在 View 中组合联接

我将许多联接组合成一个 View 。这没有给我带来任何改进。

为表格建立索引

我对常用的键建立了索引，这确实加快了速度，但我无法使整个结果集低于 0.613 秒。

从操作表开始并使用左联接

这给了我一种稍微不同的方法，但连接仍然很慢(第一个示例仍然是最快的)

索引:

欢迎任何提示、技巧、补充、改进

Answer 1

我删除了之前的答案，因为它是错误的并且没有帮助，在这里我只是总结我们在评论中的对话以及我自己的附加评论

有多种方法可以加快查询速度。

1. 确保您没有进行任何多余的查询。
2. 尽可能少地进行连接。
3. 如果可能的话，在多列上建立索引。
4. 如果需要/可能的话，使索引聚集 http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

关于您在问题中编写的查询:

1. 删除ORDER BY在内部查询中

2. 删除INNER JOIN在内部查询中并替换 GROUP BY p.id通过GROUP BY player_id

3. 关于索引在哪里有意义、哪里没有意义的几句话。

   在您的情况下，在 gameid 上建立索引是没有意义的在 table 上playergame因为这可能会返回大量行。这就是您对最内部查询所能做的全部事情。

   如果您知道您对表的期望，即它们可能面临的数据量，则还可以对联接进行一些优化。您可能会认为这是一个问题，您是否正在为 FPS 的 MMO 游戏构建数据库。 MMO 每款游戏将拥有数百万用户，而 FPS 则只有少数。此外，不同类型的游戏可能有不同的 Action 。这意味着您可以尝试通过使索引更精确来优化查询。如果您能够在 action 的内部联接中定义那gameid IN (...)然后在元组 (gameid, id) 上创建索引可能有帮助。

   通配车 WHERE条款。您可以尝试在playername上创建索引但只有当您在搜索字符串末尾使用通配符时，它才会起作用，对于一开始的情况，您需要一个单独的索引，并希望查询优化器足够聪明，可以在每次进行查询时在它们之间切换.

   请记住，索引越多意味着插入和删除速度越慢，因此只保留尽可能少的索引。

4. 另一件事是稍微重新设计结构。您仍然可以保持数据库规范化，但也许拥有一个包含某些游戏摘要的表格会很有用。您可能有一个包含昨天之前发生的比赛摘要的表，并且您的查询只会汇总今天的数据，然后根据需要连接两个表。然后，您可以通过在时间戳上创建索引或按天分区表来优化它。一切都取决于您期望的负载。

这个话题相当深奥，所以一切都取决于数据背后的故事。