mysql - SQL 查询连接表和having 子句

Question

我正在用 SQL 编写一个查询，以获取职业生涯中击出超过 500 个本垒打的球员在 35 岁赛季的本垒打总数。

SELECT b.playerID, b.yearID, b.HR
FROM batting b
JOIN master m ON m.playerID = b.playerID
WHERE b.yearID - m.birthYear = '35'
HAVING SUM(b.HR) > 500

该查询在执行时超时。我已经成功创建了一个查询，以返回球员在特定年龄赛季的本垒打总数。我还成功创建了一个查询来返回 500 本垒打俱乐部中的球员。

当我尝试将它们组合起来时，有些东西会超时，但我无法确定原因。

这是一个运行良好的查询:

SELECT b.playerID, b.yearID, b.HR
FROM batting b
JOIN MASTER M ON b.playerID = m.playerID
WHERE b.yearID - m.birthYear = 35 AND b.yearID = 2015
ORDER BY b.HR DESC

现在，如果我能只返回那些职业生涯打出 500 个本垒打的球员就好了。 2015 年全垒打击球手 HR 总数仅为 500 个。

Answer 1

最可能的解释是优化器选择的执行计划效率不高。

我们没有看到这些表上有哪些索引可用。

该查询中最突出的一件事是:

WHERE b.yearID - m.birthYear = '35'

MySQL将从master中获取具有给定player_id的每一行，并将其与来自batting中具有相同player_id的每一行进行匹配(由于相等连接谓词)

  ON m.playerID = b.playerID

然后 MySQL 必须获取该组组合行，然后计算这个表达式

    b.yearID - m.birthYear

然后取出结果并将其与“35”进行比较。

假设 playerID 列在 master 表中是唯一的

我们希望看到以某种形式编写的查询谓词可以利用 batting 上的索引，该索引的前导列为 (playerID,yearID)。

 SELECT b.playerid
      , b.yearid
      , SUM(b.hr) AS hr
   FROM master m
   JOIN batting b
     ON b.playerid = m.playerid
    AND b.yearid   = m.birthyear + 35
  GROUP BY b.playerid, b.yearid
HAVING SUM(b.hr) > 500
 ORDER BY SUM(b.hr) DESC

要获取每个玩家返回的行，您将需要一个 GROUP BY 子句。为了获得总本垒打，您需要在 SELECT 列表中使用 SUM() 聚合。

为了获得最佳查询性能，您需要一个覆盖索引

... ON batting (playerid, yearid, hr)

如果 playerid 在 master 表上不唯一，则查询将无法保证您期望的 SUM(b.hr) 值，该值可能是预期的两倍、三倍等等。

使用 EXPLAIN 查看执行计划。

还要注意可能对执行计划产生负面影响的隐式数据类型转换。我们假设两个表中的 playerid 列的数据类型匹配，并且 yearid 和 birthyear 列的数据类型是数字。

编辑

我原来的答案集中在您的查询“超时”的原因上，而我错过了您想要实现的结果的规范:

返回职业生涯心率总计超过 500 的玩家，并返回特定年份总心率每个玩家的年份。

(我将搁置讨论适当确定玩家年满 35 岁的“年份”，并使用原始查询中的标准。)

一种方法是使用条件聚合。使用当条件为 TRUE 时返回 HR 的表达式，否则返回 0 或 NULL。然后将该表达式包装在 SELECT 列表中的 SUM 聚合中。

如果我们想要仅返回职业生涯心率总计超过 500 且在指定年份击球中至少有一行的球员...

 SELECT b.playerid
      , MAX(IF(b.yearid = m.birthyear + 35,b.yearid,NULL)) AS yearid
      , SUM(IF(b.yearid = m.birthyear + 35, b.hr, 0)) AS year_hr
   FROM master m
   JOIN batting b
     ON b.playerid = m.playerid
  GROUP BY b.playerid
HAVING SUM(b.hr) > 500
   AND MAX(IF(b.yearid = m.birthyear + 35,b.yearid,NULL)) IS NOT NULL
 ORDER BY ... 

要返回职业 HR 总计超过 500 的每个球员的行，即使指定 yearid 的 batting 中没有行，我们也可以调整查询省略 HAVING 子句中的第二个条件，并在 SELECT 列表中使用表达式 m.birthyear + 35

 SELECT b.playerid
      , MAX(m.birthyear + 35) AS yearid
      , SUM(IF(b.yearid = m.birthyear + 35, b.hr, 0)) AS year_hr
   FROM master m
   JOIN batting b
     ON b.playerid = m.playerid
  GROUP BY b.playerid
HAVING SUM(b.hr) > 500
 ORDER BY ... 

请注意，职业生涯 HR 总计恰好为 500 的玩家将被排除在外。