mysql count(*) 和 count(1) 以及 count(column)，有什么区别吗？

Question

如题
我想问，用哪一个更好？

Answer 1

count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。

数据支持懒得重新做了，记录数太少看不出来，引用一个前人的数据吧。
count(*),count(1)和count(主键)的区别，6楼。

SQL> select count(*) from ysgl_compile_reqsub;

  COUNT(*)
----------
   5288265

已用时间:  00: 00: 07.51
SQL> select count(1) from ysgl_compile_reqsub;

  COUNT(1)
----------
   5288265

已用时间:  00: 00: 00.68
SQL> select count(id) from ysgl_compile_reqsub;

 COUNT(ID)
----------
   5288265

已用时间:  00: 00: 00.68
SQL> select count(rowid) from ysgl_compile_reqsub;

COUNT(ROWID)
------------
     5288265

已用时间:  00: 00: 01.01

Answer 2

count(*) 和 count(1), count(0) 一样, 计算结果集的行数.

mysql> explain extended select count(*) c from yanse;
...(略)
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1003 | select count(0) AS `c` from `test1`.`yanse` |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

count(column) 和上面的区别在与 不计算column为null的情况.

mysql> select * from ab;
+----+------+
| id | name |
+----+------+
|  1 | 2    |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql> select count(*) from ab;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(name) from ab;
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(0) from ab;
+----------+
| count(0) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)