mysql中count(id) count(1) count(*)的用法区别
发布时间：01/15 来源： 浏览：
关键词：
在mysql中很多朋友在写统计count时每次可能都不一样如，count(id) count(1) count(*)这三个统计出来的结果是一样的，但它们之间的性能有比较过吗？下面我来给大家举例说明一下。
表结构如下：
| 代码如下 | |
| mysql> show create table userG; *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `pwd` varchar(50) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(30) NOT NULL, `sex` enum('F','M','N') NOT NULL DEFAULT 'N', `addres` varchar(100) NOT NULL, `tag` varchar(100) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=5000003 DEFAULT CHARSET=utf8 COMMENT='用户表' 1 row in set (0.00 sec) |
|
下面做一下explain： 1、count(id)
| 代码如下 | |
| mysql> select count(id) from user; +-----------+ | count(id) | +-----------+ | 5000002 | +-----------+ 1 row in set (1.93 sec)mysql> explain select count(id) from user; +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | index | NULL | name | 152 | NULL | 4998401 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+1 row in set (0.05 sec)2、count(1) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 5000002 | +----------+ 1 row in set (0.90 sec)mysql> explain select count(1) from user; +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | index | NULL | name | 152 | NULL | 4998401 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)3、count(*) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 5000002 | +----------+ 1 row in set (0.87 sec)mysql> explain select count(*) from user; +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | index | NULL | name | 152 | NULL | 4998401 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) |
|
比较三个查询，explain的结果一模一样，这说明这三个的效率是一样的吗？
再看看下面三个操作，带上where条件 sex='F'，以下三个操作中间均会重启mysql服务。
| 代码如下 | |
| 1、count(id) mysql> select count(id) from user where sex='F'; +-----------+ | count(id) | +-----------+ | 1681259 | +-----------+ 1 row in set (18.87 sec) mysql> explain select count(id) from user where sex='F'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4998401 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)2、count(1) mysql> select count(1) from user where sex='F'; +----------+ | count(1) | +----------+ | 1681259 | +----------+ 1 row in set (4.81 sec) mysql> explain select count(1) from user where sex='F'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4998401 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)3、count(*) mysql> select count(*) from user where sex='F'; +----------+ | count(*) | +----------+ | 1681259 | +----------+ 1 row in set (4.69 sec) mysql> explain select count(*) from user where sex='F'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4998401 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) |
|
以上三种查询有一些差别，其中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；
