自学内容网 自学内容网

count(1),count(*)与 count(‘列名‘) 的区别

参考官方文档:
https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html#function_count

COUNT(expr)

count的基本使用

  1. COUNT(NULL)返回0
  2. COUNT(*) 返回组中的项数。包括 NULL 值和重复项。
  3. COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。其中expression是除 text、image 或 ntext 以外任何类型的表达式。不允许使用聚合函数和子查询。
mysql> SELECT count(*), count(1), count(c3) FROM t;
+----------+----------+-----------+
| count(*) | count(1) | count(c3) |
+----------+----------+-----------+
|        3 |        3 |         2 |
+----------+----------+-----------+
1 row in set (0.00 sec)

count(expr)返回SELECT语句检索到的行中expr的非NULL值的计数。结果是一个BIGINT值
COUNT(*)有点不同,因为它返回检索到的行数计数,无论它们是否包含NULL值。

区别如下:

  1. count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL的数据行;
  2. count(1)忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL的数据行;
  3. count(‘列名’)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是指空字符串或者0,而是表示NULL)的计数,即某个字段值为NULL时,不统计。

最终统计结果count(*)和count(1)是没有区别的,count(‘列名’)因为忽略了值为NULL的数据,所以可能比前两者统计的数量少;

性能对比

测试环境:windows mysql8.0.35,先不创建索引

DROP PROCEDURE IF EXISTS populate_test;
DELIMITER $$
CREATE PROCEDURE populate_test()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO
    IF i % 3 = 0 THEN
    INSERT INTO test VALUES (i, NULL);
        ELSEIF  i % 3 = 1 THEN
        INSERT INTO test VALUES (i, CONCAT(@n, ''));
        ELSEIF i % 3 = 2 THEN
        INSERT INTO test VALUES (i, 'this is text');
        END IF;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL populate_test();

count(*) VS count(1)

从执行计划可以看到:两者完全相同

mysql> EXPLAIN SELECT count(*) FROM test;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100375 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT count(1) FROM test;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100375 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

再对比一下两者EXPLAIN ANALYZE的结果,也是一样的

mysql> EXPLAIN ANALYZE SELECT count(*) FROM test\G;
*************************** 1. row ***************************
EXPLAIN: -> Count rows in test  (actual time=6.26..6.26 rows=1 loops=1)

1 row in set (0.01 sec)

mysql> EXPLAIN ANALYZE SELECT count(1) FROM test\G;
*************************** 1. row ***************************
EXPLAIN: -> Count rows in test  (actual time=6.21..6.21 rows=1 loops=1)

1 row in set (0.01 sec)

记得以前听到别人说在使用count的时候要用count(1)而不要用count(),因为使用count()的时候会对所有的列进行扫描,相比而言count(1)不用扫描所有列,所以count(1)要快一些。其实这个是错误的。
下面是摘自官网的一段话:https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html#function_count
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

SELECT COUNT(*) FROM tbl_name 这条sql的查询性能,对于InnoDB来说,会使用单线程进行查询如果没有WHERE或GROUP BY等额外子句存在,InnoDB表的查询性能将针对单线程工作负载进行优化。

count(*) VS count(列名)

COUNT(*) 不需要任何参数,而且不能与 DISTINCT 一起使用。
COUNT(*) 不需要 expression 参数,因为根据定义,该函数不使用有关任何特定列的信息。
COUNT(*) 返回指定表中行数而不删除副本。它对各行分别计数。包括包含空值的行。
也就是说count()只是返回表中行数,因此在处理count()的时候只需要找到属于表的数据块块头,然后计算一下行数就行了,而不用去读取里面数据列的数据。

而对于count(col)就不一样了,mysql必须读取该列的每一行的值,然后确认下是否为NULL,然后再进行计数。因此count(*)应该是比count(col)快的

从执行计划可看到,count(col)使用了全表扫描

mysql> EXPLAIN SELECT count(b) FROM test;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100375 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN ANALYZE的结果来看,两者的操作不同,count(col)多了一个Aggregate: count(test.b),花费时间不多,主要是全表扫描比较耗时

mysql> EXPLAIN ANALYZE SELECT count(*) FROM test\G;
*************************** 1. row ***************************
EXPLAIN: -> Count rows in test  (actual time=5.71..5.71 rows=1 loops=1)

mysql> EXPLAIN ANALYZE SELECT count(b) FROM test\G;
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(test.b)  (cost=20131 rows=1) (actual time=93.8..93.8 rows=1 loops=1)
    -> Table scan on test  (cost=10094 rows=100375) (actual time=0.0303..81.9 rows=100000 loops=1)

count(*)会走索引吗

下面是摘自官网的一段话:https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html#function_count
InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.

还是以上面的test表为例,100000条数据,没有任何索引,所以是没有走索引的
在这里插入图片描述
添加主键索引,ALTER TABLE test ADD PRIMARY KEY (a);,可以看到使用了主键索引
在这里插入图片描述
现在再在列b上添加索引,ALTER TABLE test ADD INDEX (b);,可以看到没有使用主键索引,而是使用b辅助索引
在这里插入图片描述
这里这个最小可用的二级索引怎么理解?
我们可以看到b在100000条数据中只有2种值,所以对b列加索引,B+树只需要一个根节点即可,因此索引树是最小的。主键索引因为所有key都不同,所以索引树是最大的。当前实际中这种列区分度低,一般不会对其创建索引

mysql> SELECT b FROM test GROUP BY b;
+--------------+
| b            |
+--------------+
| NULL         |
| this is text |
+--------------+
2 rows in set (0.00 sec)

也可以强制走主键索引:SELECT count(*) FROM test FORCE INDEX(PRIMARY);
在这里插入图片描述

MyISAM count优化

对于MyISAM表,COUNT(*)经过优化,如果SELECT从一个表中查询没有查询其他列,并且没有WHERE子句,则可以非常快速地返回。例如:SELECT COUNT(*) FROM student;。因为此存储引擎存储了精确的行数,并且可以非常快速地访问。此优化仅适用于MyISAM表,只有当第一列定义为NOT NULL时,COUNT(1)才会进行相同的优化。

CREATE TABLE `t_myisam` (
  `c1` int NOT NULL AUTO_INCREMENT,
  `c2` char(5) DEFAULT NULL,
  `c3` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t_myisam VALUES
(NULL, 'a', NULL), (NULL, 'b', '1'), (NULL, 'c', '2');

通过执行计划可以看到,table列是NULL,表明根本没有查表的数据

mysql> EXPLAIN SELECT count(*) FROM t_myisam;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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)

通过show warnings;可以看到实际上执行的是select count(0) AS count(*) from mysql_learn.t_myisam

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------+
| Level | Code | Message                                                                    |
+-------+------+----------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `mysql_learn`.`t_myisam` |
+-------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果加上where条件就需要扫描表的数据才能得到count结果了

mysql> EXPLAIN SELECT count(*) FROM t_myisam WHERE c1 < 100;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_myisam | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

InnoDB如何处理count(*)

对于InnoDB等事务存储引擎,存储精确的行数是有问题的,因为多个事务可能同时发生,每个交易都可能影响计数。InnoDB不会在表中保留行的内部计数,因为并发事务可能会同时看到不同数量的行。因此,SELECT COUNT(*)语句仅对当前事务可见的行进行计数。

InnoDB处理 SELECT COUNT(*)语句时,如果索引记录没有完全在Buffer Pool中,会花费一些时间。如果想要更快地计数,可以在自己的应用层维护一个计数表,让您的应用程序根据插入和删除操作来更新它。然而,在数千个并发事务正在启动对同一计数器表的更新的情况下,这种方法可能无法很好地扩展。如果近似行数足够,请使用SHOW TABLE STATUS。

总结

这里把上面实验的结果总结一下:

  1. count(*)和count(1)执行的效率是完全一样的。

  2. count(*)的执行效率比count(col)高,因此可以用count(*)的时候就不要去用count(col)。

  3. count(col)的执行效率比count(distinct col)高,不过这个结论的意义不大,这两种方法也是看需要去用。

  4. 如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。
    如果经常count(*)的话则可以找一个最小的col建立非聚集索引以避免全表扫描而影响整体性能。
    在不加WHERE限制条件的情况下,COUNT(*)与COUNT(COL)基本可以认为是等价的;
    但是在有WHERE限制条件的情况下,COUNT(*)会比COUNT(COL)快非常多;


原文地址:https://blog.csdn.net/qq_40926260/article/details/142557424

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!