浅析MySQL进里count(*)、count(1)、count(id)到底谁更快

　　一直以来MySQL查询数量到底谁最快，一直是一个比较热的话题，且很多人搞不懂真相，本文将带你从底层原理去了解，到底count（*）、count（1）、count（id）、count（字段）谁更快。

一、执行计划

+-----------+---------------------------------------------------------------------------+
| Table     | Create Table       
| employees | CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100007 DEFAULT CHARSET=utf8 COMMENT='员工记录表'                |
+-----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

　　查看执行计划

mysql> explain select count(*) from employees;
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | idx_name_age_position | 140     | NULL | 100185 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(1) from employees;
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | idx_name_age_position | 140     | NULL | 100185 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(id) from employees;
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | idx_name_age_position | 140     | NULL | 100185 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

- name 是索引字段
- hire_time 是非索引字段
mysql> explain select count(name) from employees;
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | idx_name_age_position | 140     | NULL | 100185 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(hire_time) from employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100185 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

　　结论一：执行计划相同，代表四条SQL性能几乎相同

　　疑问：四条SQL真的一样吗？平时见到的资料可不是这样说的。我们继续看

mysql> set global query_cache_size=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  set global query_cache_type=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select count(id) from employees;
+-----------+
| count(id) |
+-----------+
|    100003 |
+-----------+
1 row in set (0.01 sec)

mysql> select count(name) from employees;
+-------------+
| count(name) |
+-------------+
|      100003 |
+-------------+
1 row in set (0.01 sec)

mysql> select count(1) from employees;
+----------+
| count(1) |
+----------+
|   100003 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   100003 |
+----------+
1 row in set (0.01 sec)

　　执行SQL后发现耗时是相同的，那是否可以认为四条SQL一致呢？答案是：还不能

二、底层原理

1、count(*)

　　mysql对count(*)做过优化，这里是不会将扫到的行里面的数据取出来的，而是不取值，直接按行累加，性能非常高，这也是MySQL推荐的统计数量的方式，阿里巴巴的规范中明确要求统计数量强制使用这种方式

2、count(1)

　　mysql底层count（1）与count（*）类似，count（1）也不取值，而是将1这个常量作为占位符一样填充，然后统计1的个数，这种方式性能也非常高

3、count（字段）

　　name 是索引字段时，mysql会将索引上的数据取出，然后统计数据的行数，取值是一个过程，会有性能损耗，所以性能不如上述两个。

　　name 不是索引字段时，mysql会全表扫描，取出每一行的数据，然后统计行数，因为每一行数据较大，取值过程性能损耗也大一些，比较慢一点。

4、count（id）
    id 是主键，我们发现执行计划中，同样选择了二级索引，所以这个过程与count（索引字段）基本一致，多于的开销是mysql的查询优化器在选择索引时的开销稍稍多于count（索引字段）

三、总结

　　四条SQL性能非常相近，大多数情况下，可以认为性能一致；

　　如果深究时，还是有区别的：

1、count（*）≈ count（1）> count(索引字段)>count(id)

2、count(*) ≈ count(1) > count(id) > count(无索引字段)

　　这里执行计划相同，性能的区别微乎其微，主要影响性能的点在于，取值时的开销和mysql查询优化器选择索引时的开销。

文章链接：https://blog.csdn.net/shehuinidaye/article/details/108903073