mysql - 组合来自同一个表的统计查询

Question

我最近在 SO 上看到一个请求，将同一 history 表中的三个查询合并为一个以提高性能。

这三个查询是

SELECT COUNT(*) as number, SUM(order_total) as sum FROM history;
SELECT COUNT(*) as number, SUM(order_total) as sum FROM history 
    WHERE date <= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -30 DAY));
SELECT COUNT(*) as number, SUM(order_total) as sum FROM history
    WHERE date <= UNIX_TIMESTAMP(CURDATE());

所以我想我应该格式化一个更一般的问题，以上面的示例为例:如何组合更多查询，以及如何最好地进行？

Answer 1

所有查询都访问相同的变量，仅用于运行总和和总计的条件有所不同。

要在单个查询中运行这一切，我们必须将每个结果分配给不同的列，因此而不是 number和sum我们将有number1 , number2 , ... sum3 ，以便访问结果。

基本替换

一般来说，COUNT() , SUM()等是aggregate functions ，因此我们将用包含条件的新表达式替换每个实例。

例如:COUNT(*) WHERE some_condition与

相同

add 1 for each record among the records where <some_condition>

可以重写(尽管慢一点)为

add 1 if <some_condition>, else 0, for each record among ALL the records

这是

SUM(IF(<some_condition>, 1, 0))

这同样适用于 SUM(value) WHERE <some_condition> :变成SUM(IF(<some_condition>, value, 0)) .

当考虑MIN()时, MAX()和AVG() ，我们看到默认值 0 可能会出现问题。通过使用 NULL 而不是 0 可以解决此问题。

我们的第一次迭代允许简单的替换:

Single query                 Combined query
COUNT(*)                     SUM(<conditionalOne>)
SUM(value)                   SUM(<conditionalValue>)
AVG(value)                   AVG(<conditionalValue>)
MIN(value)                   MIN(<conditionalValue>)
...

哪里<conditionalValue>是，如果 <condition>存在，

IF(<condition>, value, NULL)

或者简单地value 。 <conditionalOne>是 <conditionalValue>其中值等于 1。否则，value可以是字段名称或表达式。

因此我们的示例查询变为:

SELECT
    SUM(1) AS number1, SUM(order_total) AS sum1,
    SUM(IF(date <= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -30 DAY)), 1, NULL)) AS number2,
    SUM(IF(date <= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -30 DAY)), order_total, NULL)) AS sum2,
    SUM(IF(date <= UNIX_TIMESTAMP(CURDATE()), 1, NULL)) AS number3,
    SUM(IF(date <= UNIX_TIMESTAMP(CURDATE()), order_total, NULL)) AS sum3
FROM history;

合并 WHERE

在这种情况下，至少有一个条件对整个表有效，即一个查询没有 WHERE ;所以我们需要扫描整个表。那么我们也可以不使用 WHERE总共。

否则，我们将合并这三个条件并使用其中最大或最宽松的条件(因此，如果我们选择去年、上个月和上周，我们实际上只会添加去年的选择)。

我们可以自动执行此操作，并希望 MySQL 优化器能够解决问题:

WHERE (<condition1>) OR (<condition2>) OR (<condition3>);

索引优化

由于索引的原因，单个查询实际上运行的速度很可能比多个不连贯的查询慢。如果条件和值实际上针对多个不同的列，则经常会发生这种情况，从而降低索引效率。

如果根本没有索引，那么合并查询应该总是比单独运行它们更方便。

理论上我们希望有一个 covering index包含 WHERE 中出现的所有列子句，从基数最小的到基数最大的，后跟表达式中出现的所有列。这样，MySQL 选择器将快速将所需行归零，并且还将找到内存中已存在的所需值。

在此示例中，条件基于 date查询要求 order_total ，因此我们将仅使用两列创建索引。

 CREATE INDEX history_stat_ndx ON history(`date`, order_total);

但在实践中，覆盖索引很可能太大而无法被接受，或者如果是的话，也没有什么好处。在这种情况下，我们仍然会合并多个查询，但这次合并为多个查询:

• 需要全表扫描和/或大量列的查询，特别是如果其他查询不需要相同的查询，它将自行进行，并将与具有相同特征的所有其他查询合并，并且不被索引(我们从索引中获得的 yield 很少。对于 WHERE 来说没有好处，因为存在全表扫描，对于覆盖范围来说则没有好处，因为列太多)。

• 表达式中需要相似条件或相似列集的所有查询都可以分组在一起，如果条件确实相似，则可能会建立索引。每个组可能有自己不同的索引，并针对该组及其表达式进行了优化。