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

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(字段),非主键字段,这样的使用方式最好不要出现。因为它不会走索引。


展开阅读全文
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值