MySQL 多子查询与整个查询

Question

我想知道哪种从 MySQL 数据库获取数据的方式具有更好的性能特征。

在一个主查询中使用子查询:

SELECT
(SELECT SUM(`number`) FROM `table`) as `number_sum`,
(SELECT MAX(`number`) FROM `table`) as `number_max`,
(SELECT MIN(`number`) FROM `table`) as `number_min`

或者，3 个不同的 SELECT 语句检索相同的数据。

提前致谢!

Answer 1

由于这三个聚合来自具有相同 WHERE 条件的同一个表，因此您不需要子查询。所有三个聚合都对同一行分组进行操作(未指定 GROUP BY，因此整个表的一行)，因此它们都可以存在于 SELECT 列表中直接。

SELECT
  SUM(number) AS number_sum,
  MAX(number) AS number_max,
  MIN(number) AS number_min
FROM `table`

如果任何聚合需要基于不同的条件，您将在 WHERE 子句中进行过滤，那么您将需要为不同的条件使用子选择，或者进行笛卡尔连接。此子选择和以下 LEFT JOIN 方法对于仅返回一行的聚合在性能方面应该是等效的:

SELECT
  /* Unique filtering condition - must be done in a subselect */
  (SELECT SUM(number) FROM `table` WHERE `somecolumn` = `somevalue`) AS number_sum,
  MAX(number) AS number_max,
  MIN(number) AS number_min
FROM `table`

或者等同于上面的查询，您可以LEFT JOIN 对子查询没有ON 子句。只有在您知道子查询将只返回一行的情况下才应该这样做。否则，您最终会得到一个笛卡尔积——连接一侧返回的行数乘以另一侧返回的行数。

如果您需要返回带有一组 WHERE 子句条件的几列和带有一组不同 WHERE 条件的几列，这很方便，但仅 <JOIN 的每一侧 em>一个 行。在这种情况下，JOIN 应该比使用相同的 WHERE 子句执行 两个 子选择更快。

这应该会更快....

SELECT
  /* this one has two aggregates sharing a WHERE condition */
  subq.number_sum_filtered,
  subq.number_max_filtered,
  /* ...and two aggregates on the main table with no WHERE clause filtering */
  MAX(`table`.number) AS number_max,
  MIN(`table`.number) AS number_min
FROM
  `table`
  LEFT JOIN (
    SELECT 
       SUM(number) AS number_sum_filtered,
       MAX(number) AS number_max_filtered
    FROM `table`
    WHERE `somecolumn = `somevalue`
  ) subq /* No ON clause here since there's no common column to join on... */

比这...

SELECT
  /* Two different subselects each over the same filtered set */
  (SELECT SUM(number) FROM `table` WHERE `somecolumn` = `somevalue`) AS number_sum_filtered,
  (SELECT MAX(number) FROM `table` WHERE `somecolumn` = `somevalue`) AS number_max_filtered,
  MAX(`table`.number) AS number_max,
  MIN(`table`.number) AS number_min
FROM
  `table`