主页 / dba / 问题 / 27558
Accepted
孔夫子
Asked: 2012-10-25 12:56:44 +0800 CST

对于绝对性能，SUM 更快还是 COUNT 更快？

  • 772

这与计算符合特定条件的记录数有关，例如invoice amount > $100。

我倾向于喜欢

COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)

但是，这同样有效

SUM(CASE WHEN invoice_amount > 100 THEN 1 ELSE 0 END)

我会认为 COUNT 更可取，原因有两个：

  1. 传达意图，即COUNT
  2. COUNT 可能在某处涉及一个简单的i += 1操作，而 SUM 不能指望它的表达式是一个简单的整数值。

有没有人有关于特定 RDBMS 差异的具体事实？

sql-server mysql

3 个回答

  1. Best Answer

    你已经自己回答了这个问题。我有几点要补充：

    在PostgreSQL（和其他支持该boolean类型的 RDBMS）中，您可以boolean直接使用测试结果。将其转换为integerand SUM()：

    SUM((amount > 100)::int))

    NULLIF()或者在表达式中使用它and COUNT()：

    COUNT(NULLIF(amount > 100, FALSE))

    或者用一个简单的OR NULL：

    COUNT(amount > 100 OR NULL)

    或其他各种表达方式。性能几乎相同。COUNT()通常比 快得多SUM()。不像SUM()和像保罗已经评论过的，COUNT()永远不会返回NULL，这可能很方便。有关的：

    从Postgres 9.4 开始，还有聚合FILTER子句。看：

    它比上述所有方法快约 5 - 10 %：

    COUNT(*) FILTER (WHERE amount > 100)

    如果查询和你的测试用例一样简单，只有一个计数，没有别的，你可以重写：

    SELECT count(*) FROM tbl WHERE amount > 100;

    ...这是真正的性能之王，即使没有索引。
    使用适用的索引，速度可以提高几个数量级，尤其是使用仅索引扫描时。

    基准

    Postgres 13

    db<>在这里摆弄

    与下面的 Postgres 10 的结果基本相同。（我还在小提琴中添加了一个没有新并行性的测试，以比较苹果和苹果。）

    Postgres 10

    我为 Postgres 10 进行了一系列新的测试，包括聚合FILTER子句和演示索引对大小计数的作用。

    简单设置：

    CREATE TABLE tbl (
   tbl_id int
 , amount int NOT NULL
);

INSERT INTO tbl
SELECT g, (random() * 150)::int
FROM   generate_series (1, 1000000) g;

-- only relevant for the last test
CREATE INDEX ON tbl (amount);

    由于背景噪音和测试台的具体情况，实际时间会有很大差异。从一组更大的测试中显示典型的最佳时间。这两种情况应该抓住本质：

    测试 1计数〜所有行的 1%

    SELECT COUNT(NULLIF(amount > 148, FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 148)::int)                      FROM tbl; -- 136 ms
SELECT SUM(CASE WHEN amount > 148 THEN 1 ELSE 0 END) FROM tbl; -- 133 ms
SELECT COUNT(CASE WHEN amount > 148 THEN 1 END)      FROM tbl; -- 130 ms
SELECT COUNT((amount > 148) OR NULL)                 FROM tbl; -- 130 ms
SELECT COUNT(*) FILTER (WHERE amount > 148)          FROM tbl; -- 118 ms -- !

SELECT count(*) FROM tbl WHERE amount > 148; -- without index  --  75 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 148; -- with index     --   1.4 ms -- !!!

    db<>在这里摆弄

    测试 2计数〜所有行的 33%

    SELECT COUNT(NULLIF(amount > 100, FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 100)::int)                      FROM tbl; -- 138 ms
SELECT SUM(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM tbl; -- 139 ms
SELECT COUNT(CASE WHEN amount > 100 THEN 1 END)      FROM tbl; -- 138 ms
SELECT COUNT(amount > 100 OR NULL)                   FROM tbl; -- 137 ms
SELECT COUNT(*) FILTER (WHERE amount > 100)          FROM tbl; -- 132 ms -- !

SELECT count(*) FROM tbl WHERE amount > 100; -- without index  -- 102 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 100; -- with index     --  55 ms -- !!!

    db<>在这里摆弄

    每组中的最后一个测试使用仅索引扫描，这就是为什么它有助于计算所有行的三分之一。当涉及大约 5% 或更多的行时，普通索引或位图索引扫描无法与顺序扫描竞争。

    Postgres 9.1 的旧测试

    为了验证我EXPLAIN ANALYZE对 PostgreSQL 9.1.6 中的真实表进行了快速测试。

    184568 行中的 74208 行符合条件kat_id > 50。所有查询都返回相同的结果。我依次运行了 10 次以排除缓存效果，并将最佳结果作为注释附加：

    SELECT SUM((kat_id > 50)::int)                      FROM log_kat; -- 438 ms
SELECT COUNT(NULLIF(kat_id > 50, FALSE))            FROM log_kat; -- 437 ms
SELECT COUNT(CASE WHEN kat_id > 50 THEN 1 END)      FROM log_kat; -- 437 ms
SELECT COUNT((kat_id > 50) OR NULL)                 FROM log_kat; -- 436 ms
SELECT SUM(CASE WHEN kat_id > 50 THEN 1 ELSE 0 END) FROM log_kat; -- 432 ms

    性能上几乎没有任何真正的差异。

    • 38

  2. 这是我在 SQL Server 2012 RTM 上的测试。

    if object_id('tempdb..#temp1') is not null drop table #temp1;
if object_id('tempdb..#timer') is not null drop table #timer;
if object_id('tempdb..#bigtimer') is not null drop table #bigtimer;
GO

select a.*
into #temp1
from master..spt_values a
join master..spt_values b on b.type='p' and b.number < 1000;

alter table #temp1 add id int identity(10,20) primary key clustered;

create table #timer (
    id int identity primary key,
    which bit not null,
    started datetime2 not null,
    completed datetime2 not null,
);
create table #bigtimer (
    id int identity primary key,
    which bit not null,
    started datetime2 not null,
    completed datetime2 not null,
);
GO

--set ansi_warnings on;
set nocount on;
dbcc dropcleanbuffers with NO_INFOMSGS;
dbcc freeproccache with NO_INFOMSGS;
declare @bigstart datetime2;
declare @start datetime2, @dump bigint, @counter int;

set @bigstart = sysdatetime();
set @counter = 1;
while @counter <= 100
begin
    set @start = sysdatetime();
    select @dump = count(case when number < 100 then 1 end) from #temp1;
    insert #timer values (0, @start, sysdatetime());
    set @counter += 1;
end;
insert #bigtimer values (0, @bigstart, sysdatetime());
set nocount off;
GO

set nocount on;
dbcc dropcleanbuffers with NO_INFOMSGS;
dbcc freeproccache with NO_INFOMSGS;
declare @bigstart datetime2;
declare @start datetime2, @dump bigint, @counter int;

set @bigstart = sysdatetime();
set @counter = 1;
while @counter <= 100
begin
    set @start = sysdatetime();
    select @dump = SUM(case when number < 100 then 1 else 0 end) from #temp1;
    insert #timer values (1, @start, sysdatetime());
    set @counter += 1;
end;
insert #bigtimer values (1, @bigstart, sysdatetime());
set nocount off;
GO

    分别查看单个运行和批次

    select which, min(datediff(mcs, started, completed)), max(datediff(mcs, started, completed)),
            avg(datediff(mcs, started, completed))
from #timer group by which
select which, min(datediff(mcs, started, completed)), max(datediff(mcs, started, completed)),
            avg(datediff(mcs, started, completed))
from #bigtimer group by which

    运行 5 次（并重复）后的结果非常不确定。

    which                                       ** Individual
----- ----------- ----------- -----------
0     93600       187201      103927
1     93600       187201      103864

which                                       ** Batch
----- ----------- ----------- -----------
0     10108817    10545619    10398978
1     10327219    10498818    10386498

    它表明，当使用 SQL Server 计时器的粒度进行测量时，运行条件​​的可变性远大于实现之间的差异。任何一个版本都可以排在首位，我得到的最大方差是 2.5%。

    但是，采取不同的方法：

    set showplan_text on;
GO
select SUM(case when number < 100 then 1 else 0 end) from #temp1;
select count(case when number < 100 then 1 end) from #temp1;

    文本文本 (SUM)

      |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END))
       |--Stream Aggregate(DEFINE:([Expr1011]=Count(*), [Expr1012]=SUM([Expr1004])))
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [tempdb].[dbo].[#temp1].[number]<(100) THEN (1) ELSE (0) END))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#temp1]))

    文本 (COUNT)

      |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1008],0)))
       |--Stream Aggregate(DEFINE:([Expr1008]=COUNT([Expr1004])))
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [tempdb].[dbo].[#temp1].[number]<(100) THEN (1) ELSE NULL END))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#temp1]))

    从我的阅读来看，SUM 版本似乎做得更多。除了SUM之外，它还执行 COUNT 。话虽如此，但COUNT(*)它是不同的，应该比COUNT([Expr1004])（跳过 NULL，更多逻辑）更快。一个合理的优化器会意识到[Expr1004]在SUM([Expr1004])SUM 版本中是一个“int”类型，因此使用一个整数寄存器。

    无论如何，虽然我仍然相信该COUNT版本在大多数 RDBMS 中会更快，但我从测试中得出的结论是，我将SUM(.. 1.. 0..)在未来继续使用，至少对于 SQL Server 而言，除了使用时引发的 ANSI WARNINGS 之外没有其他原因COUNT.

    • 12

  3. 根据我的跟踪经验，对于大约 10,000,000 的查询中的这两种方法，我注意到 Count(*) 使用大约两倍的 CPU 并且运行速度更快。但我的查询没有过滤器。

    数数（*） 

    CPU...........: 1828   
Execution time:  470 ms

    总和(1) 

    CPU...........: 3859  
Execution time:  681 ms
    • 0

相关问题