mysql - 找不到在不降低性能的情况下将 2 个 SQL 查询减少到 1 个的方法

Question

我有以下 SQL 查询，它工作得很好:

SELECT COUNT(*), COUNT(DISTINCT `fk_match_id`) 
  FROM `pass` 
  WHERE `passer` IN ('48717','33305','49413','1640') 
  AND `receiver` IN ('48717','33305','49413','1640');

IN 子句中的数字是球员 ID，可以从数据库中名为球员的另一个表中获得。此表中的每一行都有一个玩家 ID、一个 team_id 和一个 match_id，它是匹配表的外键。

我想使用 match_id 自动获取那些玩家 ID。我可以这样做:

SELECT COUNT(*), COUNT(DISTINCT `fk_match_id`) 
  FROM `pass` 
  WHERE `passer` IN 
  (
    SELECT player_id 
      FROM `player` 
      WHERE `team_id` = someTeamID  
      AND `match_id` = someMatchID) 
      AND `receiver` IN 
      (
        SELECT player_id 
          FROM `player` 
          WHERE `team_id` = someTeamID  
          AND `match_id` = someMatchID
      )
  )

然而，显然使用子查询是出了名的慢，而且使用起来确实太慢了。即使使用 join，如下所示，也太慢了:

SELECT COUNT(*), COUNT(DISTINCT `fk_match_id`) 
  from `pass` st1 
  INNER JOIN `player` st2 
  ON (st1.passer = st2.player_id OR st1.receiver = st2.player_id);

那也太慢了。所以想知道是否有可能在 0.0 秒内有效地完成我在 2 个查询中可以做的事情(在一个查询中获取玩家 ID，然后运行第一个查询几乎不需要任何时间)仅在一个查询中，或者如果是完全不可能的。

如有任何帮助，我们将不胜感激。

非常感谢!

编辑::

相关表结构如下:

玩家:

通过:

我想计算历史上给定阵容中每位球员传给另一名球员的传球次数。我有一个比赛 ID 和一个团队 ID。我可以通过查询球员表来获取某支球队参加特定比赛的球员:

SELECT player_id 
  FROM `player` 
  WHERE `team_id` = someTeamID  
  AND `match_id` = someMatchID

这会返回如下内容:

1803,1930,13310,1764,58845,15157,51938,2160,18892,12002,4101,14668,80979,59013

然后我想查询 pass 表并返回那些 id 之一在 passer 和 receiver 列中的每一行。

Answer 1

(passer, receiver) 需要一个复合索引:

添加后，尝试JOIN:

SELECT COUNT(*), COUNT(DISTINCT fk_match_id) 
FROM pass
  INNER JOIN player AS p 
    ON pass.passer = p.player_id
  INNER JOIN player AS r 
    ON r.player_id = pass.passer  ;

如果您想要特定 (team_id, match_id) 组合的这些结果，请添加一个 (team_id, match_id, player_id) 索引，然后使用:

SELECT COUNT(*), COUNT(DISTINCT fk_match_id) 
FROM pass
  INNER JOIN player AS p 
    ON  p.team_id = someTeamID  
    AND p.match_id` = someMatchID
    AND p.player_id = pass.passer 
  INNER JOIN player AS r
    ON  r.team_id = someTeamID  
    AND r.match_id` = someMatchID
    AND r.player_id = pass.receiver ;