Count(1)比Count(*)快么?

count(1)比count(* )快么?

count(* )在PostgreSQL上经常抱怨执行非常慢。而在一段时间中我收到过很多count(1 )比count(* )快的说法,并将这种理解此应用于我的工作中。

为什么count(*)这么慢?

大多数人都会理解以下内容很慢:

SELECT count(*)
FROM /* 复杂查询 */;

这是一个复杂的查询,PostgreSQL必须知道它需要将多少行的数据用于计算。

但如果以下情况很慢,很多人都会感到震惊:

SELECT count(*) FROM /* 大表 */;

由于表中没有存储“行数”(像在MySQL中的MyISAM一样),PostgreSQL计算行的唯一方法是获得全部数据。

因此,count(*)通常会执行表的顺序扫描,这个代价非常昂贵。

是问题出在 count( * )中的 * 么?

select * from 将扩展表的所有列,因此,许多人认为使用count( * )效率低下,应该写count(id)或count(1)代替。但是count( * )中的 * 与select * 中的 * 是完全不同的,count * 中的 * 仅仅代表row并不会展开它,写入count(1)与count( * )是相同的。但是count(id)有些不同,它只计算id是NOT NULL的行数。因此避免count(* )没有任何用处。反而count(* ) 的速度还会更快-参见下文。

源码解读

  • 查看count函数定义
test=# select proname, pronargs, prosrc from pg_proc where proname='count';
 proname | pronargs |     prosrc      
---------+----------+-----------------
 count   |        0 | aggregate_dummy
 count   |        1 | aggregate_dummy
(2 rows)
  • what? 0个参数的count?
test=# select count() from lzzhang;
ERROR:  count(*) must be used to call a parameterless aggregate function

执行报错,说好的0个参数呢?

查看语法,仅保留主要代码。

 * (*)                                  - normal agg with no args
 * (aggr_arg,...)                       - normal agg with args
 * (ORDER BY aggr_arg,...)              - ordered-set agg with no direct args
 * (aggr_arg,... ORDER BY aggr_arg,...) - ordered-set agg with direct args
 *
 * The zero-argument case is spelled with '*' for consistency with COUNT(*).

aggr_args:  '(' '*' ')'
                {
                    $$ = list_make2(NIL, makeInteger(-1));
                }

count(* )的 * 唯一的作用仅仅是作为count函数0个参数的标识使用!!!

  • count查询
    我们来看看几种count的情况
test=# select * from lzzhang;
 id | id1 | id2 
----+-----+-----
  1 |     |    
  1 |   1 |    
  1 |   1 |   1
  2 |     |    
  2 |     |    
  3 |     |    
  3 |     |    
  3 |     |    
(8 rows)

test=# select count(*) from lzzhang;
 count 
-------
     8
(1 row)

test=# select count(1) from lzzhang;
 count 
-------
     8
(1 row)

test=# select count('const_string') from lzzhang;
 count 
-------
     8
(1 row)

test=# select count(id) from lzzhang;
 count 
-------
     8
(1 row)

test=# select count(id1) from lzzhang;
 count 
-------
     2
(1 row)

这里我们简单分成三种类型的count
1. 列名-(id/id1)-只计算非null的数据
2. 无参( * )–计算全部数据
3. 常量(1/const_string)–计算全部数据

count只计算非null的数据。
三种方式在ExecInterpExpr函数中的处理

列名:
    EEO_CASE(EEOP_OUTER_FETCHSOME)
        {
            slot_getsomeattrs(outerslot, op->d.fetch.last_var);

            EEO_NEXT();
        }
    只计算非null的数据

常量:
      EEO_CASE(EEOP_CONST)
        {
            *op->resnull = op->d.constval.isnull;
            *op->resvalue = op->d.constval.value;

            EEO_NEXT();
        }
    常量当然不会是null,所以1/const_string会计算全部数据。

无参:
    EEO_CASE(EEOP_AGG_STRICT_TRANS_CHECK)
        {
            AggState   *aggstate;
            AggStatePerGroup pergroup;

            aggstate = op->d.agg_strict_trans_check.aggstate;
            pergroup = &aggstate->all_pergroups
                [op->d.agg_strict_trans_check.setoff]
                [op->d.agg_strict_trans_check.transno];

            if (unlikely(pergroup->transValueIsNull))
                EEO_JUMP(op->d.agg_strict_trans_check.jumpnull);

            EEO_NEXT();
        }
        检查之后就直接计算。

所以* 并不会比1快, 反而* 比1 会减少的cpu的运算,速度更快!
现在cpu的计算速度很快了,我的单核每秒可以计算6.5亿次,所以 * 和 1的时间几乎是一样的。

计算cpu单核运算力的小程序

[lzzhang@lzzhang-pc ~]$ cat test.c 
#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <signal.h>

unsigned long long counter=1;

void signal_handler(int sig)
{
    printf("counter %llu \n", counter);
    fflush(stdout);
    exit(0);
}

int main(int argc, char *argv[])
{
    signal(SIGALRM, signal_handler);
    alarm(1);
    while(counter++);
    return 0;
}
[lzzhang@lzzhang-pc ~]$ gcc test.c
[lzzhang@lzzhang-pc ~]$ ./a.out 
counter 657214986 

提升PostgreSQL Count 性能

仅使用索引扫描

扫描一个小索引比全表扫描会减少很多代价,但是,由于PostgreSQL是多版本并发控制的策略,在PostgreSQL中并不是这么简单的可以完成。每个行版本(“tuple”)包含可见的数据快照的信息。但是这些信息不会存储在索引中。因此,计算索引中的条目通常是不够的,因为PostgreSQL必须访问表元组以确保索引条目可见。

为了缓解这个问题,PostgreSQL引入了visibility map,这是一种数据结构,用于存储表块中的所有元组是否对所有人可见。
如果大多数表块都是可见的,则索引扫描不需要经常访问表元组来确定可见性。这种索引扫描称为“index only scan”,因此扫描索引以计算行数通常更快。

vacuum会更新visibility map,因此如果要使用索引来加速count * 的计算,请确保autovacuum在表上运行得足够频繁。

你真的需要吗count(* )么?

有时最好的解决方案是寻找替代方案。

如果您不需要精确计数可以使用近似值。在这种情况下,您可以使用PostgreSQL用于查询计划的估计值:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

这个值由autovacuum和autoanalyze更新,所以它永远不会超过10%的误差。您可以减少autovacuum_analyze_scale_factor以便autoanalyze更频繁地运行。

使用辅助表

我们创建mytable_count用于记录表的行数,并在mytable修改数据时使用触发器更新mytable_count的值。

START TRANSACTION;

CREATE TABLE mytable_count(c bigint);

CREATE FUNCTION mytable_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE mytable_count SET c = c + 1;

      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE mytable_count SET c = c - 1;

      RETURN OLD;
   ELSE
      UPDATE mytable_count SET c = 0;

      RETURN NULL;
   END IF;
END;$$;

CREATE TRIGGER mytable_count_mod AFTER INSERT OR DELETE ON mytable
   FOR EACH ROW EXECUTE PROCEDURE mytable_count();

-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();

-- initialize the counter table
INSERT INTO mytable_count
   SELECT count(*) FROM mytable;

COMMIT;

我们在单个事务中执行所有操作,以便不会“丢失”任何数据,因为CREATE TRIGGER是SHARE ROW EXCLUSIVE的锁,这可以防止所有并发修改,当然,缺点是所有并发数据修改必须等到SELECT count(* )完成。
这为我们提供了一个非常快速替代count(* )的方案,但代价是减慢了对表的数据修改性能。
即使这个计数表不停的更新,也没有“表膨胀”的危险,因为这些都是HOT更新。

在原文基础上增加了更具说服力的源码解读点击原文地址

文章浏览总量 760 次

要发表评论,您必须先登录