今天看到同事转载的一篇《select count(*)和select count(1)的区别》的博文,兴致所致,就想对文中提出的结论进行验证一下:
从内容来看,主要是有主键和没有主键的影响,那么
第一步:创建test1(有主键),test2(无主键)两张表
CREATE TABLE `test1` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`value` int(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=400000 DEFAULT CHARSET=utf8;
CREATE TABLE `test2` (
`id` int(12) NOT NULL,
`value` int(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
第二步:插入40万条数据
DROP PROCEDURE inserttest;
CREATE PROCEDURE inserttest()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 400000 DO
-- test1表为test1,test2表为test2
INSERT INTO test1 VALUES(i, i);
SET i = i+1;
END WHILE;
END
CALL inserttest;
第三步:论证第一个观点“表没有主键的情况下,count(1)比count(*)快”
我们测试10遍(test2表没有主键,限于篇幅只列出10个)
SELECT SQL_NO_CACHE COUNT(1) as ooo FROM test2;
SELECT SQL_NO_CACHE COUNT(*) as aaa FROM test2;
结果数据
次数 |
count(1) |
count(*) |
1 |
时间: 0.107s |
时间: 0.105s |
2 |
时间: 0.106s |
时间: 0.105s |
3 |
时间: 0.105s |
时间: 0.106s |
4 |
时间: 0.106s |
时间: 0.106s |
5 |
时间: 0.106s |
时间: 0.108s |
6 |
时间: 0.106s |
时间: 0.103s |
7 |
时间: 0.107s |
时间: 0.106s |
8 |
时间: 0.104s |
时间: 0.103s |
9 |
时间: 0.105s |
时间: 0.105s |
10 |
时间: 0.105s |
时间: 0.110s |
结论:该观点不值得相信,从结果数据来看,count(1)比count(*)平均时间可能稍短一点,但是并不总是这样。
第四步:论证第二个观点“表在有主键的情况下,主键作为count条件时最快”
我们测试10遍(test1表有主键,限于篇幅只列出10个)
SELECT SQL_NO_CACHE COUNT(1) as ooo FROM test1;
SELECT SQL_NO_CACHE COUNT(*) as aaa FROM test1;
SELECT SQL_NO_CACHE COUNT(id) as aaa FROM test1;
结果数据(忍不住多列了3个)
次数 |
count(1) |
count(*) |
count(id) |
1 |
时间: 0.052s |
时间: 0.053s |
时间: 0.059s |
2 |
时间: 0.053s |
时间: 0.052s |
时间: 0.060s |
3 |
时间: 0.052s |
时间: 0.052s |
时间: 0.059s |
4 |
时间: 0.052s |
时间: 0.053s |
时间: 0.060s |
5 |
时间: 0.054s |
时间: 0.052s |
时间: 0.059s |
6 |
时间: 0.052s |
时间: 0.054s |
时间: 0.059s |
7 |
时间: 0.051s |
时间: 0.053s |
时间: 0.060s |
8 |
时间: 0.052s |
时间: 0.053s |
时间: 0.061s |
9 |
时间: 0.054s |
时间: 0.053s |
时间: 0.059s |
10 |
时间: 0.071s |
时间: 0.052s |
时间: 0.062s |
11 |
时间: 0.051s |
时间: 0.052s |
时间: 0.059s |
12 |
时间: 0.051s |
时间: 0.052s |
时间: 0.059s |
13 |
时间: 0.052s |
时间: 0.053s |
时间: 0.059s |
结论:count在表有主键的时候比没有主键的时候快,但是主键作为count条件时最慢,count(1)在多数情况下性能更优。
第五步:本来想论证“表在只有一个字段的时候count(*)最快”,但是很少情况下表只有一个字段,我就不论证了。
第六步:论证“count(*)和count(1)时统计包括null的,而count(列名)不包含null值”
我们先将test1表的value列修改为可以为空
ALTER TABLE `test1`
MODIFY COLUMN `value` int(12) NULL AFTER `id`;
再将其中13行数据修改为null
UPDATE `test1` SET `value`=NULL WHERE (`id`='13')
通过以下语句
SELECT SQL_NO_CACHE COUNT(1) as ooo FROM test1;
SELECT SQL_NO_CACHE COUNT(*) as aaa FROM test1;
SELECT SQL_NO_CACHE COUNT(id) as aaa FROM test1;
SELECT SQL_NO_CACHE COUNT(value) as aaa FROM test1;
数据证据
count(1) |
count(*) |
count(id) |
count(value) |
399999 |
399999 |
399999 |
399998 |
结论:count(*)和count(1)时统计包括null的,而只有count(有空值的列)不包含null值。
第七步:对于select sum(1)的使用介绍,主要是说sum方法的计算结果是参数值1或者其他和满足条件行数的乘积
当然也就是说sum(1)也可以用来统计行数,然后我测试了一下性能(测试有null值并且有主键的表test1),数据如下:
次数 |
count(1) |
count(*) |
count(id) |
count(value) |
SUM(1) |
1 |
时间: 0.052s |
时间: 0.052s |
时间: 0.060s |
时间: 0.091s |
时间: 0.104s |
2 |
时间: 0.054s |
时间: 0.051s |
时间: 0.060s |
时间: 0.090s |
时间: 0.105s |
3 |
时间: 0.053s |
时间: 0.051s |
时间: 0.059s |
时间: 0.093s |
时间: 0.100s |
4 |
时间: 0.052s |
时间: 0.053s |
时间: 0.060s |
时间: 0.094s |
时间: 0.102s |
5 |
时间: 0.051s |
时间: 0.052s |
时间: 0.059s |
时间: 0.091s |
时间: 0.102s |
结论:sum(1)作为统计行数时,性能最差。
tips:可能我的测试结果和原作者有不符的地方,如果有理解有误的地方请指正,我使用的是mysql。