Select count(*) 的优化_sql

概述首先说明： select count(*) 和 select count(1)的效率相差无几。这里开始引用自“德哥@Digoal”的博客，原文链接：http://blog.163.com/digoal@126/blog/static/163877040201331252945440/ –引用部分开始– 引用块内容 PostgreSQL 的count确实是一大软肋, 特别是全表的count. 在9

首先说明：
select count(*) 和 select count(1)的效率相差无几。

这里开始引用自“德哥@Digoal”的博客，原文链接：http://blog.163.com/digoal@126/blog/static/163877040201331252945440/

–引用部分开始–

引用块内容
Postgresql 的count确实是一大软肋,特别是全表的count.
在9.2以前全表的count只能通过扫描全表来得到,即使有pk也必须扫描全表.
9.2版本增加了index only scan的功能,count(*)可以通过仅仅扫描pk就可以得到.
但是如果是一个比较大的表,pk也是很大的,扫描pk也是个不小的开销.
那么有没有办法来优化count全表的 *** 作呢,如果你的场景真的有必要频繁的count全表,那么可以尝试一下使用以下方法来优化你的场景.
其实非常简单,就是给表建立几个触发器,每次插入,删除,truncate表时触发,将表的记录数更新到一个记录表中.
但是要知道,这样会带来一个问题,并发的插入和删除 *** 作,如果仅仅使用1条记录来存储表的count(*)值的话,会有严重的锁冲突的问题.
例如两个session,同时插入1条记录,在触发触发器时,由于都要更新count表的同一条记录,那么会发生行锁等待.
因此,可以使用多条记录来缓解行锁冲突的问题,如下 :
创建测试表,a,假设要经常count(*) from a.

create table a(ID serial4 primary key,info text,crt_time timestamp(0) default Now());

创建记录a表记录数的表

create table cnt_a(ID int primary key,cnt int);

为了缓解行锁冲突,这里使用了1001条记录来存储count(*) from a的值.

insert into cnt_a select generate_serIEs(0,1000),0;

在计算count(*) a时,使用sum(cnt) from cnt_a就可以了. 因此只需要扫描1001行.
后面会看到当a表的记录数越多,性能提升约明显.
创建插入/删除/truncate触发器

CREATE OR REPLACE FUNCTION public.tg_insert_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_ID int;  rm numeric;begin select max(ID),random() into m_ID,rm from cnt_a;  update cnt_a set cnt=cnt+1 where ID=(rm*m_ID)::int;  return null;end;$function$;CREATE OR REPLACE FUNCTION public.tg_delete_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_ID int;  rm numeric;begin select max(ID),rm from cnt_a;  update cnt_a set cnt=cnt-1 where ID=(rm*m_ID)::int;  return null;end;$function$;CREATE OR REPLACE FUNCTION public.tg_truncate_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare begin update cnt_a set cnt=0 where not cnt=0;  return null;end;$function$;create trigger tg1 after insert on a for each row execute procedure tg_insert_a();create trigger tg2 after delete on a for each row execute procedure tg_delete_a();create trigger tg3 after truncate on a for each statement execute procedure tg_truncate_a();

插入以下记录后，测试完后通过count(*) 和sum(cnt)比对数据是否一致

postgres=# select count(*) from a;  count  --------- 1755964(1 row)Time: 285.491 mspostgres=# select sum(cnt) from cnt_a ;   sum   --------- 1755964(1 row)Time: 0.689 ms

当记录数到达千万级别后,性能以及提升几千倍了.

–引用部分开始–

“德哥@Digoal”又做了一些后续的优化，详情可点击链接自行移步查看：http://blog.163.com/digoal@126/blog/static/163877040201331252945440/

我在实际应用中也发现了其中存在死锁现象，认为是触发器函数中select的“Shared Lock”和update的“Exclusive Lock”竞争产生的。详见我的博客：《Transaction中的SQL死锁》http://www.jb51.cc/article/p-ugjukbzh-zp.html

于是我也做了一些改进，即：假设表cnt_a中的记录数是固定的，所以在insert触发器和delete触发器中，不再从cnt_a中去取max(ID)，从而避免了死锁。
改进的触发器函数：

CREATE OR REPLACE FUNCTION tg_insert_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare rm numeric;begin select random() into rm;  update trigger_cnt set cnt=cnt+1 where ID=(rm*1000)::int;  return null;end;$function$;CREATE OR REPLACE FUNCTION tg_delete_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare rm numeric;begin select random() into rm;  update trigger_cnt set cnt=cnt-1 where ID=(rm*1000)::int;  return null;end;$function$;