mysql count(*),count(1),count(字段)的区别

最新推荐文章于 2024-07-01 11:30:48 发布

qianweifeng123

最新推荐文章于 2024-07-01 11:30:48 发布

阅读量1w

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qianweifeng123/article/details/51078144

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql中count(*),count(1),count(字段)的区别

日常开发中，我们写sql语句时可能会使用到count()聚合函数，但是常用的count(*),count(1),count(字段)之间有何区别呢，对应用的影响如何？

测试用例
分析
总结

测试用例

测试用的表结构：

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(128) | NO   |     | NULL    |                |
| price    | double       | NO   |     | NULL    |                |
| sellerId | bigint(20)   | NO   |     | NULL    |                |
| menu_url | varchar(128) | NO   |     |         |                |
| detail   | varchar(128) | NO   |     |         |                |
+----------+--------------+------+-----+---------+----------------+

执行的语句：

mysql> explain select count(*) from t_menu where price = 2011;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_menu | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from t_menu;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_menu | index | NULL          | PRIMARY | 4       | NULL |    2 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(id) from t_menu;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_menu | index | NULL          | PRIMARY | 4       | NULL |    2 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(id) from t_menu where price = 2011;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_menu | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(1) from t_menu;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_menu | index | NULL          | PRIMARY | 4       | NULL |    2 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(1) from t_menu where price = 2011;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_menu | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(price) from t_menu where price = 2011;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_menu | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(price) from t_menu;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_menu | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

分析

在sql语句前加“explain”，可以显示执行计划。
可以发现，使用count()聚合函数后，若有where条件，且where条件的字段未建立索引，则查询不会走索引，直接扫描了全表。
由于表内数据较少，无法得出比较准确的效率比较。
根据资料所查：
有主见或联合主键的情况下，count(*)略比count(1)快一些。
没有主键的情况下count(1)比count(*)快一些。
如果表只有一个字段，则count(*)是最快的。
当在给表添加几个索引后，count(*)有时候会走到其他索引，可以说明，mysql系统会自动优化到一个合适的索引项。

其次，为何说count(主键) 不一定比count(其余索引) 快？
索引是一种B+树的结构，以块为单位进行存储。假设块大小是1K，主键索引大小为4B，有一个字段A的索引大小为2B。
同样一个块，能存放256个主键索引，但是能存放512个字段A的索引。
假设总数据是2K条，意味着主键索引占用了8个块，而A字段索引占用了4个块，统计时用主键索引需要经历的块多，IO次数多。效率也比A字段索引慢。

总结

1.如果在开发中确实需要用到count()聚合，那么优先考虑count(*)，因为mysql数据库本身对于count(*)做了特别的优化处理。
2.使用count()聚合函数后，最好不要跟where age = 1；这样的条件，会导致不走索引，降低查询效率。除非该字段已经建立了索引。
3.count(字段),非主键字段，这样的使用方式最好不要出现。因为它不会走索引。