---19.1 从语义看,明显不同:
COUNT是计算行数的函数,COUNT(*)可以计算出行数,COUNT(1)也可以计算出行数,1在这里代表count函数的参数时一个常量值为1,这样,表中有多少行,理论上就计算多少次COUNT(1)。
---19.2 从结果看,有相似之处。似乎同样的条件,得到同样的数字结果。
---19.3 从查询执行计划看,也相似。这表明,处理过程是类似的。
---19.4 从具体的数据库系统看,实现可能存在不同。我们以MySQL为例,看他们在内部处理的不同之处:
mysql> explain select count(1) from t3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select count(1) AS `count(1)` from `test`.`t3` |
+-------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from t3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t3` |
+-------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
看到他们之间的差别了吗?
第一查询执行计划:count(1)就是count(1),这表明是求值。
第二个查询执行计划:count(*)变成了count(0),这表明是计算行数的。
--19.5 另外,MySQL文档上说,列在这里,对大家有帮助:
COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.