MySQL多个子查询与整个查询 [英] MySQL Multiple Subqueries vs. whole queries

问题描述

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

I'm wondering which way to get data from a MySQL database has better performance characteristics.

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

Using subqueries within one main query:

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语句检索相同的数据.

Or, 3 distinct SELECT statements retrieving the same data.

提前谢谢！

推荐答案

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

Since these three aggregates come from the same table with the same WHERE conditions, you have no need for subselects. All three of the aggregates are operating on the same row grouping (no GROUP BY specified, so one row for the whole table), so they can all exist in the SELECT list directly.

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

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

If any of the aggregates needs to be based on different conditions you would filter in a WHERE clause, then you will need to either use a subselect for the differing condition, or do a cartesian join. This subselect and the following LEFT JOIN method should be equivalent, performance-wise for aggregates returning only one row:

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`

或与上述查询等效，您可以针对没有ON子句的子查询 LEFT JOIN.仅当您知道子查询将仅返回一行时，才应执行此操作.否则，您将得到笛卡尔乘积-连接的一侧返回的行数与另一侧返回的行数乘以.

Or equivalent to the query above, you can LEFT JOIN against a subquery with no ON clause. This should only be done in situations when you know the subquery will return only one row. Otherwise, you will end up with a cartesian product -- as many rows as returned by one side of the join multiplied by the number of rows returned by the other side.

如果您需要返回几组带有一组WHERE子句条件的列，而几列具有一组不同的WHERE条件，但是每行仅返回 行，这将很方便JOIN的侧面.在这种情况下，与具有相同WHERE子句的两个子选择相比，JOIN的速度应该更快.

This is handy if you need to return a few columns with one set of WHERE clause conditions and a few columns with a different set of WHERE conditions, but only one row from each side of the JOIN. In this case, it should be faster to JOIN than to do two subselects with the same WHERE clause.

这应该更快....

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`

这篇关于MySQL多个子查询与整个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！