MySQL中的COUNT(*)和COUNT(col)

​另一篇：difference between count(1) and count(*)

 

看看人们是如何使用COUNT(*)和COUNT(col)的，看起来大多数人都认为它们是同义词，只是使用他们喜欢的，而在性能甚至查询结果上都有实质性的差异。此外，我们还发现InnoDB和MyISAM引擎在执行上存在差异。

注意:所有测试都应用于MySQL 8.0.30版本，在后台，我运行每个查询三到五次，以确保所有查询都完全缓存在缓冲池中(对于InnoDB)或文件系统中(对于MyISAM)。

InnoDB引擎下的count()函数

来看看一个InnoDB的例子：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE TABLE count_innodb (   id int(10) unsigned NOT NULL AUTO_INCREMENT,   val_with_nulls int(11) default NULL,   val_no_null int(10) unsigned NOT NULL,   PRIMARY KEY idx (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;   (mysql) > select count(*) from count_innodb; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.38 sec)   (mysql) > select count(val_no_null) from count_innodb; +--------------------+ | count(val_no_null) | +--------------------+ |           10000000 | +--------------------+ 1 row in set (0.38 sec)

在innodb引擎中，count(*)和count(val_no_null)耗费了相同的时间。我们继续往下看，相同的条件下，MyISAM执行COUNT(*)比innodb快多了。

但是，为什么我们不能直接缓存表的行记录数呢？innodb不会记录表的记录数，因为同一时间，并发事务看到的行数可能不一样。因此，select count(*)语句只能计算当前事务可以看到的记录数。我们可以借助information schema来获取表的大致数量：

?

1 2 3 4 5 6 7

(mysql) >  select table_rows from information_schema.tables where table_name='count_innodb'; +------------+ | TABLE_ROWS | +------------+ |    9980586 | +------------+ 1 row in set (0.00 sec)

可以看到，这个记录数是不准确的。不过，有些场景只要一个粗略的统计就可以了。

 

再来看看COUNT(val_with_nulls)

?

1 2 3 4 5 6 7

(mysql) >  select count(val_with_nulls) from count_innodb; +-----------------------+ | count(val_with_nulls) | +-----------------------+ |               9990001 | +-----------------------+ 1 row in set (2.14 sec)

可以看到返回的值和count(*)是不同的。

这是为什么呢？因为val_with_nulls列没有定义成not null，可能会有null值，mysql必须通过扫描表来发现。这也是为什么查询的结果不同。

所以，COUNT(*)和COUNT(col)不仅有性能上的差异，还有功能上的差异。

我们再来看看另外的查询，研究一下innodb在相同的where条件下，是如何管理COUNT(*), COUNT(val_no_null), COUNT(val_with_nulls)的。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

(mysql) >  select count(*) from count_innodb where id<1000000; +----------+ | count(*) | +----------+ |   980000 | +----------+ 1 row in set (0.30 sec)   (mysql) > explain select count(*) from count_innodb where id<1000000\G *************************** 1. row ***************************   select_type: SIMPLE         table: count_innodb          type: range possible_keys: PRIMARY           key: PRIMARY          rows: 1955802      filtered: 100.00         Extra: Using where; Using index   (mysql) >  select count(val_no_null) from count_innodb where id<1000000; +--------------------+ | count(val_no_null) | +--------------------+ |             980000 | +--------------------+ 1 row in set (0.33 sec)   (mysql) >  explain select count(val_no_null) from count_innodb where id<1000000\G *************************** 1. row ***************************   select_type: SIMPLE         table: count_innodb          type: range possible_keys: PRIMARY           key: PRIMARY          rows: 2013804      filtered: 100.00         Extra: Using where

我们可以看到，在这两种情况下，查询的性能是相等的，只有10%的差异，如果你更仔细地关注COUNT(*)查询的EXPLAIN，将注意到Using index。这意味着MySQL可以只使用索引，而不涉及其他表数据，这可能足以获得大型表的行数。

你也可能希望使用已经有索引的列来加快大型表的查询速度。

COUNT(val_with_nulls)会有什么惊喜吗?让我们来看看:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

(mysql) > select count(val_with_nulls) from count_innodb where id<1000000; +-----------------------+ | count(val_with_nulls) | +-----------------------+ |                970001 | +-----------------------+ 1 row in set (0.33 sec)   (mysql) > explain select count(val_with_nulls) from count_innodb where id<1000000\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: count_innodb    partitions: NULL          type: range possible_keys: PRIMARY           key: PRIMARY       key_len: 4           ref: NULL          rows: 1955802      filtered: 100.00         Extra: Using where 1 row in set, 1 warning (0.00 sec)

我们可以看到，在所有COUNT(*)， COUNT(val_with_nulls)， COUNT(val_with_nulls)中，查询的性能相当均匀。

 

MyISAM引擎下的count()函数

来看看MyISAM引擎下的count函数：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE TABLE count_myisam (   id int(10) unsigned NOT NULL,   val_with_nulls int(11) default NULL,   val_no_null int(10) unsigned NOT NULL,   KEY idx (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;   (mysql) > select count(*) from count_myisam; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.00 sec)   (mysql) > select count(val_no_null) from count_myisam; +--------------------+ | count(val_no_null) | +--------------------+ |           10000000 | +--------------------+ 1 row in set (0.00 sec)

速度很快！

由于这是一个MyISAM表，我们已经缓存了表中的行数，这就是MyISAM引擎的工作方式。这就是为什么它可以立即回答COUNT(*)和COUNT(val_no_null)查询。

请注意引擎之间的区别:InnoDB是事务引擎，而MyISAM是非事务存储引擎。

?

1 2 3 4 5 6 7

(mysql) > select count(val_with_nulls) from count_myisam; +-----------------------+ | count(val_with_nulls) | +-----------------------+ |               9990001 | +-----------------------+ 1 row in set (14.18 sec)

但是当涉及到MyISAM表的COUNT(val_with_nulls)时，我们可以看到比InnoDB慢了40倍;差别太大了。

同样，我们可以看到COUNT(val_with_nulls)的行为，因为NULL值显然不会被考虑。MySQL Optimizer在这种情况下做得很好，只在需要时进行全表扫描，因为列可以为NULL。

现在让我们用WHERE子句对MyISAM表进行更多的查询:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

(mysql) >  select count(*) from count_myisam where id<1000000; +----------+ | count(*) | +----------+ |  1001237 | +----------+ 1 row in set (0.41 sec)   (mysql) >  explain select count(*) from count_myisam where id<1000000 \G *************************** 1. row ***************************   select_type: SIMPLE         table: count_myisam          type: range possible_keys: idx           key: idx          rows: 1041561      filtered: 100.00         Extra: Using where; Using index   (mysql) > select count(val_no_null) from count_myisam where id<1000000; +--------------------+ | count(val_no_null) | +--------------------+ |            1001237 | +--------------------+ 1 row in set (2.55 sec)   (mysql) >  explain select count(val_no_null) from count_myisam where id<1000000\G *************************** 1. row ***************************   select_type: SIMPLE         table: count_myisam          type: range possible_keys: idx           key: idx          rows: 1041561      filtered: 100.00         Extra: Using index condition; Using MRR (mysql) >  select count(val_with_nulls) from count_myisam where id<1000000; +-----------------------+ | count(val_with_nulls) | +-----------------------+ |               1000281 | +-----------------------+ 1 row in set (2.55 sec)   (mysql) >  explain select count(val_with_nulls) from count_myisam where id<1000000\G *************************** 1. row ***************************   select_type: SIMPLE         table: count_myisam          type: range possible_keys: idx           key: idx          rows: 1041561      filtered: 100.00         Extra: Using index condition; Using MRR

可以看到，即使使用WHERE子句，COUNT(*)和COUNT(col)的性能也会有显著不同。

事实上，这个示例显示了5倍的性能差异，因为所有数据都位于内存(请注意，由于它是MyISAM引擎，数据缓存发生在文件系统缓存级别)。对于io绑定的工作负载，在这种情况下，经常可以看到甚至是100倍的性能差异。

COUNT(*)查询可以使用索引覆盖，而COUNT(col)则不能。当然，可以将索引扩展为(id,val_with_nulls)，并使查询再次被索引覆盖，但我只在无法更改查询(即，它是第三方应用程序)或由于某种原因在查询中出现列名，并且需要非null值的计数时才会使用这种解决方法。

值得注意的是，在这种情况下，MySQL Optimizer并没有很好地优化查询。可能会注意到(val_no_null)列是not null，因此COUNT(val_no_null)与COUNT(*)相同，查询可以作为索引覆盖的查询运行。但是mysql没有，两个查询都执行行读取。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

(mysql) >  alter table count_myisam drop key idx, add key idx (id,val_with_nulls); Query OK, 10000000 rows affected (1 min 38.71 sec) Records: 10000000  Duplicates: 0  Warnings: 0   (mysql) >  select count(val_with_nulls) from count_myisam where id<1000000; +-----------------------+ | count(val_with_nulls) | +-----------------------+ |               1000281 | +-----------------------+ 1 row in set (0.42 sec)   (mysql) >  select count(*) from count_myisam where id<1000000; +----------+ | count(*) | +----------+ |  1000762 | +----------+ 1 row in set (0.56 sec)

正如所看到的，与没有索引的COUNT(val_with_nulls)查询相比，扩展索引有助于提高对空值的COUNT(val_with_nulls)查询大约7倍。但同时，你可以看到COUNT(*)变慢了0.6倍，可能是因为在这种情况下索引变长了两倍。

最后，我想消除一些关于COUNT(0)和COUNT(1)的错觉。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

(mysql) >  select count(1) from count_innodb where id<1000000; +----------+ | count(1) | +----------+ |   980000 | +----------+ 1 row in set (0.30 sec)   (mysql) >  select count(0) from count_innodb where id<1000000; +----------+ | count(0) | +----------+ |   980000 | +----------+ 1 row in set (0.30 sec)   (mysql) > explain select count(1) from count_innodb where id<1000000\G *************************** 1. row ***************************   select_type: SIMPLE         table: count_innodb          type: range possible_keys: PRIMARY           key: PRIMARY          rows: 1955802      filtered: 100.00         Extra: Using where; Using index   (mysql) >  explain select count(0) from count_innodb where id<1000000\G *************************** 1. row ***************************   select_type: SIMPLE         table: count_innodb          type: range possible_keys: PRIMARY           key: PRIMARY          rows: 1955802      filtered: 100.00         Extra: Using where; Using index

如你所见，查询的性能和解释是相同的，在COUNT()函数的括号内放入什么数字并不重要。它可以是你想要的任何数字，根据性能和此查询的实际输出，它将完全等于COUNT(*)。