代码之家 › 专栏 › 技术社区 › LSerni

合并来自同一表的统计查询

optimization mysql

LSerni · 技术社区 · 11 年前

我最近在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());

因此,我想我会用上面的例子来格式化一个更一般的问题:如何组合更多的查询,以及如何最好地继续?

1 回复 | 直到 11 年前

LSerni 11 年前

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

要在单个查询中运行所有这些,我们必须将每个结果分配给不同的列,因此 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> 是一个 <条件值> 其中值等于1。否则, 价值 可以是字段名或表达式。

因此,我们的示例查询变成:

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 ; 所以我们需要扫描整个桌子。那么我们也可以不用 哪里 总共

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

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

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

索引优化

由于索引的原因,单个查询可能会实际运行 更慢的 而不是几个不相交的查询。如果条件和值实际针对多个不同的列,导致索引效率降低,则通常会发生这种情况。

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

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

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

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

然而,在实践中,很可能是因为覆盖指数太大而无法被接受,或者如果是这样的话,它是有益的。在这种情况下,我们仍然会合并多个查询,但这次合并为多个查询:

需要全表扫描和/或大量列的查询,特别是如果其他查询不需要相同的查询,将单独进行,并与具有相同特征的所有其他查询合并,以及 未编入索引 (我们不会从索引中获得什么好处。WHERE没有,因为有一个完整的表扫描,而覆盖率没有,因为列太多)。
所有需要类似条件或表达式中类似列集的查询都可以分组在一起,如果条件真的相似,则可能进行索引。每个组可能有自己的不同索引,并针对该组及其表达式进行了优化。