count(*) 与count(1)的效率-优快云博客

本文通过实际测试对比了SQL中count(*)与count(1)的执行效率，结果显示count(1)通常比count(*)更快，尤其是在大型数据集上。文章提供了具体的测试案例和性能数据，对于理解SQL查询优化具有一定的参考价值。

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

使用count(*) 与count(1)的效率，大部分是count(1)效率高，count(*)效率低，具体原因待查

db2inst2@gd1:~> time db2 "select count(*) from OT_JNL_VERIFY"

1          
-----------
  225919230

  1 record(s) selected.


real    8m7.084s
user    0m0.016s
sys     0m0.012s
db2inst2@gd1:~> time db2 "select count(1) from OT_JNL_VERIFY"

1          
-----------
  225914539

  1 record(s) selected.


real    6m46.074s
user    0m0.016s
sys     0m0.012s



db2inst2@gd1:~> time db2 "select count(*) from  OT_JNL_QUERY"

1          
-----------
   40924380

  1 record(s) selected.


real    1m45.073s
user    0m0.008s
sys     0m0.020s
db2inst2@gd1:~> time db2 "select count(1) from  OT_JNL_QUERY"

1          
-----------
   40924338

  1 record(s) selected.


real    1m25.203s
user    0m0.008s
sys     0m0.020s

db2inst2@gd1:~> time db2 "select count(*) from  FT_USER_MER_CTRL"

1          
-----------
    4421292

  1 record(s) selected.


real    0m7.255s
user    0m0.012s
sys     0m0.020s
db2inst2@gd1:~> time db2 "select count(1) from  FT_USER_MER_Ctrl"

1          
-----------
    4421305

  1 record(s) selected.


real    0m5.626s
user    0m0.012s
sys     0m0.012s

转载于:https://www.cnblogs.com/atwanli/articles/4962380.html