mysql - 痛苦地缓慢 self 加入

Question

我有一个名为“match_players”的关系表，其中包含大约 20000 行。有一个名为“hero_id”的列代表玩家在比赛中选择的英雄。所有字段都已编入索引。

我需要建立另一个表，包含两个英雄的所有组合，统计他们在同一个团队中的次数，他们是敌人的次数等。

我当前的查询在 8 核 SSD 服务器上大约需要 90 秒。我想这与 MySQL 在对结果进行分组之前在内部构建这个包含所有组合的庞大表有关。

是否有另一种方法来收集行值的组合，也许 MySQL 只是简单地扫描表，并在发现新组合时记录下来？感谢任何输入。

表“match_players”:

match_id | team | position | player_id | hero_id |

56427859 |    1 |        1 |    546107 |      17 |
56427859 |    1 |        2 |    469333 |      81 |
56427859 |    1 |        3 |    227526 |      60 |
56427859 |    1 |        4 |    193739 |      32 |
56427860 |    0 |        0 |    473923 |      11 |
56427860 |    0 |        1 |    292764 |      93 |
56427860 |    0 |        2 |    138018 |      26 |
56427860 |    0 |        3 |    326510 |      96 |

etc...

查询:

SELECT mp1.hero_id, mp2.hero_id
FROM match_players mp1
INNER JOIN match_players mp2
ON mp1.hero_id < mp2.hero_id
WHERE mp1.team = mp2.team
GROUP BY mp1.hero_id, mp2.hero_id

解释:

id | select_type | table | type | possible_keys      | key        | key_len | ref                        |  rows | Extra

 1 |      SIMPLE |   mp1 |  ALL | faction_id,hero_id |       NULL |    NULL |                       NULL | 34060 | Using temporary; Using filesort
 1 |      SIMPLE |   mp2 |  ref | faction_id,hero_id | faction_id |       1 | beta_dota_2.mp1.faction_id |  3499 | Using where

更新:

因为我只需要一起比赛的英雄，所以我将查询更新为以下内容，速度快得多。我认为它会在十分之几秒内完成。

SELECT mp1.hero_id, mp2.hero_id
FROM match_players mp1
INNER JOIN match_players mp2
ON mp1.hero_id < mp2.hero_id
WHERE mp1.team = mp2.team AND mp1.match_id = mp2.match_id
GROUP BY mp1.hero_id, mp2.hero_id

不过，我仍然想知道，如果我想要一个所有英雄组合的列表，无论是否在同一场比赛中，最好的方法是什么？对于任何超过几千行的表，我的原始查询显然是不可行的。

Answer 1

对于这个查询:

SELECT mp1.hero_id, mp2.hero_id
FROM match_players mp1 INNER JOIN
     match_players mp2
     ON mp1.hero_id < mp2.hero_id AND
        mp1.team = mp2.team
GROUP BY mp1.hero_id, mp2.hero_id;

您需要一个关于match_players(team, hero_id) 的复合索引。这是一个起点。

我想，性能问题可能是因为一场比赛中有很多玩家选择了同一个“英雄”。如果这是可能的，那么对于计数，您需要 count(distinct match_id) 而不是 count(*)。更重要的是，这会对性能产生重大影响——具体取决于团队的规模。

您可能想运行此查询以了解这种情况发生的频率:

select cnt, count(*)
from (select match_id, hero_id, count(*) as cnt
      from match_players
      group by match_id, hero_id
     ) mh
group by cnt
order by cnt desc;