mysql中 SELECT COUT(PRIMARY)、count(1)、count(*) 的区别

有时候我们要查询一个数据表记录总数的时候，会用到标题中所列的的SQL语句，，当然有很多种方式，下面我们就详细分析这3种方式在mysql中的执行过程。


有这样一个表结构：

mysql> desc person;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(60)             | NO   | MUL | NULL    |                |
| descs | varchar(20)          | NO   | MUL | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)



mysql> explain select count(1) from person;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)


mysql> explain select count(*) from person;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)


mysql> explain select count(id) from person;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)


通过上面3种mysql的执行过程，我们可以看出，这3种方法的执行过程是一模一样的，不存在性能和效率上的差异。


注意：
1，对于MyISAM或者HEAP引擎，该查询返回的行数是准确值。
2，对于InnoDB，该查询返回的行数是一个近似值（事实上也是准确值，我测试几次也都是准确的）。