sql count(1) count(*)区别_PostgreSQL的count(1)真的比count(*)快么？
作者簡介
張連壯，多年P(guān)ostgreSQL數(shù)據(jù)庫內(nèi)核研發(fā)經(jīng)驗，高可用/數(shù)據(jù)復(fù)制方面經(jīng)驗較為豐富，目前主要從事分布式數(shù)據(jù)庫Citus相關(guān)工作，CitusDB中國【站主】專注于Citus技術(shù)分享的全信息平臺，歡迎大家參與到Citus知識傳播和Citus使用推廣的活動中！！！
背景
count(* )在PostgreSQL上經(jīng)常被抱怨執(zhí)行非常慢。而在一段時間中我收到過很多count(1 )比count(* )快的說法，并將這種理解此應(yīng)用于我的工作中。
為什么count(*)這么慢？
l?大多數(shù)人都會接受以下SQL很慢：
SELECT count(*)FROM /* 復(fù)雜查詢 */;
這是一個復(fù)雜的查詢，PostgreSQL必須知道它需要將多少行的數(shù)據(jù)用于計算。
l?但如果以下情況很慢，很多人都會感到震驚：
SELECT count(*) FROM /* 大表 */;
由于表中沒有存儲“行數(shù)”(像在MySQL中的MyISAM一樣)，PostgreSQL計算行的唯一方法是獲得全部數(shù)據(jù)。
因此，count(*)通常會執(zhí)行表的順序掃描，這個代價非常昂貴。
問題出在?count(?*?)中的?*?么？
select * from 中的 * 將擴(kuò)展表的所有列，因此，許多人認(rèn)為使用count( * )效率低下，應(yīng)該寫count(id)或count(1)代替。但是count( * )中的 * 與select * 中的 * 是完全不同的，count * 中的 * 僅僅代表row并不會展開它，寫入count(1)與count( * )是相同的效果。但是count(id)有些不同，它只計算id是NOT NULL的行數(shù)。因此避免count(?* )沒有任何用處，反而count(?* ) 的速度還會更快。
源碼解讀
l?查看count函數(shù)定義
test=# select proname, pronargs, prosrc from pg_proc where proname='count';proname | pronargs | prosrc ---------+----------+-----------------count | 0 | aggregate_dummycount | 1 | aggregate_dummy(2 rows)
l?什么??0個參數(shù)的count?
test=# select count() from lzzhang;ERROR:??count(*)?must?be?used?to?call?a?parameterless?aggregate?function
執(zhí)行報錯，說好的0個參數(shù)呢？
查看語法，僅保留主要代碼。
* (*) - 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函數(shù)0個參數(shù)的標(biāo)識使用！！！
l?count查詢
我們來看看幾種count的情況
test=# select * from lzzhang;id | id1 | id2 ----+-----+-----1 | | 1 | 1 | 1 | 1 | 12 | |
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的數(shù)據(jù)
2. 無參(* )–計算全部數(shù)據(jù)
3. 常量(1/const_string)–計算全部數(shù)據(jù)
count只計算非null的數(shù)據(jù)。
三種方式在ExecInterpExpr函數(shù)中的處理
列名： EEO_CASE(EEOP_OUTER_FETCHSOME) { slot_getsomeattrs(outerslot, op->d.fetch.last_var); EEO_NEXT(); } 只計算非null的數(shù)據(jù)常量： EEO_CASE(EEOP_CONST) { *op->resnull = op->d.constval.isnull;
*op->resvalue = op->d.constval.value; EEO_NEXT(); }????常量當(dāng)然不會是null，所以1/const_string會計算全部數(shù)據(jù)。無參： 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的計算，速度更快！現(xiàn)在cpu的計算速度很快了，我的單核每秒可以計算6.5億次，所以 * 和 1的時間幾乎是一樣的。
l?計算cpu單核運(yùn)算力的小程序
[lzzhang@lzzhang-pc ~]$ cat test.c #include #include #include #include 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是多版本并發(fā)控制，在PostgreSQL中并不是這么簡單的可以完成。每個行版本(“tuple”)包含可見的數(shù)據(jù)快照的信息。但是這些信息不會存儲在索引中。因此，計算索引中的條目通常是不夠的，因為PostgreSQL必須訪問表元組以確保索引條目可見。
為了緩解這個問題，PostgreSQL引入了visibility map，這是一種數(shù)據(jù)結(jié)構(gòu)，用于存儲表塊中的所有元組是否對所有人可見。
如果大多數(shù)表塊都是可見的，則索引掃描不需要訪問表元組來確定可見性。這種索引掃描稱為“index?only?scan”，因此掃描索引以計算行數(shù)通常更快。
vacuum會更新visibility map，因此如果要使用索引來加速count * 的計算，請確保autovacuum在表上運(yùn)行得足夠頻繁。
真的需要嗎count(*?)么？
有時最好的解決方案是尋找替代方案。
如果您不需要精確計數(shù)可以使用近似值。在這種情況下，您可以使用PostgreSQL用于查詢計劃的估計值：
SELECT?reltuples::bigintFROM pg_catalog.pg_classWHERE relname = 'mytable';
這個值由autovacuum和autoanalyze更新，所以它永遠(yuǎn)不會超過10％的誤差。您可以減少autovacuum_analyze_scale_factor以便autoanalyze更頻繁地運(yùn)行。
使用輔助表
我們創(chuàng)建mytable_count用于記錄表的行數(shù)，并在mytable修改數(shù)據(jù)時使用觸發(fā)器更新mytable_count的值。
START TRANSACTION;CREATE TABLE mytable_count(c bigint);
CREATE FUNCTION mytable_count() RETURNS triggerLANGUAGE plpgsql AS$$BEGINIF TG_OP = 'INSERT' THENUPDATE 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 mytableFOR EACH ROW EXECUTE PROCEDURE mytable_count();-- TRUNCATE triggers must be FOR EACH STATEMENTCREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytableFOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();-- initialize the counter tableINSERT INTO mytable_countSELECT count(*) FROM mytable;COMMIT;
我們在單個事務(wù)中執(zhí)行所有操作，以便不會“丟失”任何數(shù)據(jù)，因為CREATE TRIGGER是SHARE ROW EXCLUSIVE的鎖，這可以防止所有并發(fā)修改，當(dāng)然，缺點(diǎn)是所有并發(fā)數(shù)據(jù)修改必須等到SELECT count(* )完成。
這為我們提供了一個非常快速替代count(* )的方案，但代價是減慢了對表的數(shù)據(jù)修改性能。即使這個計數(shù)表不停的更新，也沒有“表膨脹”的危險，因為這些都是HOT更新。
PostgreSQL中文社區(qū)歡迎廣大技術(shù)人員投稿
投稿郵箱：press@postgres.cn
總結(jié)
以上是生活随笔為你收集整理的sql count(1) count(*)区别_PostgreSQL的count(1)真的比count(*)快么？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 支付宝转到支付宝收费吗
- 下一篇： python如何爬取网站所有目录_[py