MySQL: count()的坑

count(*), count(1), count(someColumn) 倒底有没有分别? 使用count(someColumn)只取一列,会不会性能好点? 如果是这样的话,那count(1)是不是性能最佳?

答案:

1. count(*)和count(1)完全是一码事:都是用来数“行数”的。MySQL遇到"*", 并不会去展开它。在性能方面,这类查询会走covering index. 

2. count(someColumn)跟其他完全不同,它数的是someColumn列中非NULL值的个数。在性能方面,系统会根据someColumn有没有索引,来决定走不走covering index.

例:

drop table if exists play_count;  
create table play_count(
	id bigint auto_increment not null, 
	indexed_column varchar(50),      --有索引列
	non_indexed_column varchar(50),  --无索引列
	primary key(id),
	key idx(indexed_column) 
);

--插入三行数据,存在空值
insert into play_count(indexed_column,non_indexed_column) value('a',null);
insert into play_count(indexed_column,non_indexed_column) value(null,'b');
insert into play_count(indexed_column,non_indexed_column) value('c','c');


--count查询
select count(*) from play_count;     --返回3
select count(1) from play_count;     --返回3   
select count(id) from play_count;     --返回3 
select count(indexed_column) from play_count;     --返回2
select count(non_indexed_column) from play_count;     --返回2 



explain select count(*) from play_count; --Extra: Using Index
explain select count(1) from play_count; --Extra: Using Index
explain select count(id) from play_count; --Extra: Using Index
explain select count(indexed_column) from play_count; --Extra: Using Index
explain select count(non_indexed_column) from play_count; --Type: All

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.