针对于这个问题，我们来实际操作得出结论：
建表：
CREATE TABLE test_a(
id int,
name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
生成7000003 条数据，其中7000000万条是null值，3条是有数据的
1 name1
2 name2
3 name3
\N name1
......
\N name7000000
用 count(*) 查询
hive (panniu)> select count(*) from test_a;
-- 过程省略
OK
7000003
-- 测了三次分别是
Time taken: 12.339 seconds, Fetched: 1 row(s)
Time taken: 11.018 seconds, Fetched: 1 row(s)
Time taken: 11.393 seconds, Fetched: 1 row(s)
-- 查看执行计划
hive (panniu)> explain select count(*) from test_a;
OK
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: test_a
Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator
aggregations: count() -- 这有区别
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
sort order:
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
value expressions: _col0 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
用 count(1) 查询
hive (panniu)> select count(*) from test_a;
-- 过程省略
OK
7000003
-- 测了三次分别是
Time taken: 10.992 seconds, Fetched: 1 row(s)
Time taken: 12.217 seconds, Fetched: 1 row(s)
Time taken: 11.268 seconds, Fetched: 1 row(s)
-- 查看执行计划
hive (panniu)> explain select count(1) from test_a;
OK
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: test_a
Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator
aggregations: count(1) -- 这有区别
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
sort order:
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
value expressions: _col0 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
用count(id) 查询
hive (panniu)> select count(id) from test_a;
-- 过程省略
OK
3
-- 测了三次分别是
Time taken: 13.405 seconds, Fetched: 1 row(s)
Time taken: 12.248 seconds, Fetched: 1 row(s)
Time taken: 13.341 seconds, Fetched: 1 row(s)
-- 查看执行计划
hive (panniu)> explain select count(id) from test_a;
OK
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: test_a
Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: id (type: int)
outputColumnNames: id -- 查询id字段
Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count(id) -- 这有区别
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
sort order:
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
value expressions: _col0 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
结论：
1）count(*) 和 count(1) 统计行数包含 null值；count(id) 统计行数不包含 null 值。
2）通过执行计划看不出三个有什么根本区别，只能在测试速度上做评判，评判如下：count(1) 比 count(*) 稍快， count(id) 比前两个稍慢。
海汼部落原创文章，原文链接：http://www.hainiubl.com/topics/75585
