mysql中count(id) count(1) count(*)的用法区别

在mysql中很多朋友在写统计count时每次可能都不一样如,count(id) count(1) count(*)这三个统计出来的结果是一样的,但它们之间的性能有比较过吗？下面我来给大家举例说明一下.

表结构如下,代码如下:

1. mysql>showcreatetableuserG;
2. ***************************1.row***************************
3. Table:user
4. CreateTable:CREATETABLE`user`(
5. `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
6. `name`varchar(50)NOTNULL,
7. `pwd`varchar(50)NOTNULL,
8. `email`varchar(100)NOTNULL,
9. `phone`varchar(30)NOTNULL,
10. `sex`enum('F','M','N')NOTNULLDEFAULT'N',
11. `addres`varchar(100)NOTNULL,
12. `tag`varchar(100)NOTNULL,
13. PRIMARYKEY(`id`),
14. KEY`name`(`name`)
15. )ENGINE=InnoDBAUTO_INCREMENT=5000003DEFAULTCHARSET=utf8COMMENT='用户表'
16. 1rowinset(0.00sec)

下面做一下explain,1、count(id),代码如下:

1. mysql>selectcount(id)fromuser;
2. +-----------+
3. |count(id)|
4. +-----------+
5. |5000002|
6. +-----------+
7. 1rowinset(1.93sec)mysql>explainselectcount(id)fromuser;
8. +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
9. |1|SIMPLE|user|index|NULL|name|152|NULL|4998401|Usingindex|+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+1rowinset(0.05sec)2、count(1)
10. mysql>selectcount(1)fromuser;
11. +----------+
12. |count(1)|
13. +----------+
14. |5000002|
15. +----------+
16. 1rowinset(0.90sec)mysql>explainselectcount(1)fromuser;
17. +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
18. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
19. +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
20. |1|SIMPLE|user|index|NULL|name|152|NULL|4998401|Usingindex|
21. +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
22. 1rowinset(0.00sec)3、count(*)
23. mysql>selectcount(*)fromuser;
24. +----------+
25. |count(*)|
26. +----------+
27. |5000002|
28. +----------+
29. 1rowinset(0.87sec)mysql>explainselectcount(*)fromuser;
30. +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
31. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
32. +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
33. |1|SIMPLE|user|index|NULL|name|152|NULL|4998401|Usingindex|
34. +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
35. 1rowinset(0.00sec)

比较三个查询,explain的结果一模一样,这说明这三个的效率是一样的吗？再看看下面三个操作,带上where条件 sex='F',以下三个操作中间均会重启mysql服务,代码如下:

1. 1、count(id)
2. mysql>selectcount(id)fromuserwheresex='F';
3. +-----------+
4. |count(id)|
5. +-----------+
6. |1681259|
7. +-----------+
8. 1rowinset(18.87sec)
9. mysql>explainselectcount(id)fromuserwheresex='F';
10. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
11. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
12. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
13. |1|SIMPLE|user|ALL|NULL|NULL|NULL|NULL|4998401|Usingwhere|
14. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
15. 1rowinset(0.00sec)2、count(1)
16. mysql>selectcount(1)fromuserwheresex='F';
17. +----------+
18. |count(1)|
19. +----------+
20. |1681259|
21. +----------+
22. 1rowinset(4.81sec)
23. mysql>explainselectcount(1)fromuserwheresex='F';
24. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
25. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
26. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
27. |1|SIMPLE|user|ALL|NULL|NULL|NULL|NULL|4998401|Usingwhere|
28. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
29. 1rowinset(0.00sec)3、count(*)
30. mysql>selectcount(*)fromuserwheresex='F';
31. +----------+
32. |count(*)|
33. +----------+
34. |1681259|
35. +----------+
36. 1rowinset(4.69sec)
37. mysql>explainselectcount(*)fromuserwheresex='F';
38. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
39. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
40. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+//phpfensi.com
41. |1|SIMPLE|user|ALL|NULL|NULL|NULL|NULL|4998401|Usingwhere|
42. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
43. 1rowinset(0.00sec)

以上三种查询有一些差别,其中count(id)用时最长,count(*)比count(1)速度要稍微快一点.

两组查询,带条件的都没有使用到索引,扫描了全表,而没有条件的则使用了索引name,所以在应用中尽量不使用count(*)和count(1),杜绝使用count(primary_key).

网上有很多资料说:

没有主键，count(1)比count(*)快；

有主键的话，count(primary_key)最快,但是在上面的测试中发现,count(primary_key)是最慢的,难道是测试不准确？这个有待验证。

如果表只有一个字段,则count(*)是最快的.

说明:

count(1)中的1并不是指第一个column；

count(*)和count(1)一样,包括对值为NULL的统计；

count(column)不包括对值为NULL的统计,这里的column指的不是primary_key；