性能调优基础篇之select count(*) 与select count(1)

最新推荐文章于 2025-01-13 23:23:02 发布

minna_D

最新推荐文章于 2025-01-13 23:23:02 发布

阅读量715

点赞数

CC 4.0 BY-SA版权

分类专栏：性能调优基础篇

本文链接：https://blog.youkuaiyun.com/minna_D/article/details/38514967

性能调优基础篇专栏收录该内容

7 篇文章

订阅专栏

本文通过实验对比了在不同数量级的数据集上使用count(*)和count(1)查询性能的区别，发现两者在大部分情况下性能相当，但在千万数量级数据集时，count(1)查询性能有所下降。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

很多时候count(*) 与count(1)容易造成人误解

实验环境：　pg数据库 , 　

10万数量级

=> explain analyze select count(*) from city;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Aggregate (cost=5488.88..5488.89 rows=1 width=0) (actual time=13.690..13.690 rows=1 loops=1)
   -> Seq Scan on city (cost=0.00..5307.90 rows=72390 width=0) (actual time=0.004..8.382 rows=72390 loops=1)
Total runtime: 13.726 ms
(3 rows)

Time: 23.646 ms
=> explain analyze select count(1) from city;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Aggregate (cost=5488.88..5488.89 rows=1 width=0) (actual time=16.912..16.912 rows=1 loops=1)
   -> Seq Scan on city (cost=0.00..5307.90 rows=72390 width=0) (actual time=0.005..9.382 rows=72390 loops=1)
Total runtime: 16.952 ms
(3 rows)

Time: 20.035 ms

千万数量级

explain analyze select count(1) from xx;

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2144923.85..2144923.86 rows=1 width=0) (actual time=48684.707..48684.707 rows=1 loops=1)
   -> Seq Scan on xx (cost=0.00..2102132.08 rows=17116708 width=0) (actual time=0.026..47239.822 rows=17154212 loops=1)
Total runtime: 48691.623 ms
(3 rows)

Time: 48705.884 ms
hotel=>
explain analyze select count(*) from xx;
^[[A                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2144923.85..2144923.86 rows=1 width=0) (actual time=65436.442..65436.442 rows=1 loops=1)
   -> Seq Scan on xx (cost=0.00..2102132.08 rows=17116708 width=0) (actual time=0.006..64207.656 rows=17154225 loops=1)
Total runtime: 65445.016 ms
(3 rows)

Time: 65446.541 ms