mysql中count(id) count(1) count(*)的用法区别
发布:smiling 来源: PHP粉丝网 添加日期:2014-09-28 15:56:59 浏览: 评论:0
在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 |
- +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ //phpfensi.com
- | 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;
Tags: count(id) count(1) count(*)
- 上一篇:开启MYSQL远程连接权限方法总结
- 下一篇:Liunx如何修改MySQL字符集
相关文章
- ·mysql中count(1)与count(*)比较(2014-09-29)
- ·mysql SELECT FOUND_ROWS()与COUNT(*)用法区别(2014-10-04)
- ·mysql下count(*)与count(column)区别性能对比(2014-10-09)
- ·mysql 优化之COUNT(*)效率分析(2014-10-15)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)