前言
一直以来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)
执行计划
count（*）
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)
count(1)
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)
count(id)
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)
count(字段)
- 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一致呢？答案是:还不能
底层原理
count（*）
- mysql对count(*)做过优化，这里是不会将扫到的行里面的数据取出来的，而是不取值，直接按行累加，性能非常高，这也是MySQL推荐的统计数量的方式，阿里巴巴的规范中明确要求统计数量强制使用这种方式
count（1）
- mysql底层count（1）与count（*）类似，count（1）也不取值，而是将1这个常量作为占位符一样填充，然后统计1的个数，这种方式性能也非常高
count（字段）
- name 是索引字段时，mysql会将所有上的数据取出，然后统计数据的行数，取值是一个过程，会有性能损耗，所以性能不如上述两个
- name 不是索引字段时，mysql会全表扫描，取出每一行的数据，然后统计行数，因为每一行数据较大，取值过程性能损耗也大一些，比较慢一点
count（id）
- id是主键，我们发现执行计划中，同样选择了二级索引，所以这个过程与count（索引字段）基本一致，多于的开销是mysql的查询优化器在选择索引时的开销稍稍多于count（索引字段）
总结
- 四条SQL性能非常相近，大多数情况下，可以认为性能一致
- 如果深究时，还是有区别的
- count（*）≈ count（1）> count(索引字段)>count(id)
- count(*) ≈ count(1) > count(id) > count(无索引字段)
- 这里执行计划相同，性能的区别微乎其微，主要影响性能的点在于，取值时的开销和mysql查询优化器选择索引时的开销
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/83660.html
