关于COUNT()的效率是一个很好的问题,下面是我结合一些文章和自己理解对于此问题的探讨！如有错解，欢迎大家在评论区指正~

存储引擎

在MySQL中，日常开发中比较常用的有MyISAM和InnoDB两种存储引擎。两者之间的其中一个区别是使用count(*)函数计算表的具体行数。

存储引擎

COUNT()

备注

MyISAM

保存表的具体行数

无事务之类操作的最优方案

InnoDB

先从读取表中的数据到内存缓冲区，然后扫描全表获得行记录数

需要扫描一遍整个表来计算有多少行

备注：count函数中加上where条件时，在两个存储引擎中的效果是一样的，都会扫描全表计算某字段有值项的次数

场景分析

  1. MyISAM保存表的总行数,因此count(*)并且无where子句,很快会返回表的总行数
  2. MyISAM保存表的总行数,利用count(column)并且无where子句,并且此column不为null,很快会返回表的总行数
  3. MyISAM保存表的总行数,利用count(column)并且无where子句,并且此column可以为null,mysql会对表进行全表或全索引扫描来确定行数
  4. InnoDB查询count(*),count(column(not null)),count(column(may be null))并且无where子句,mysql会对表进行全表或全索引扫描来确定行数
  5. MyISAM和InnoDB 查询count(*),count(column(not null)),count(column(may be null))并且存在where子句,mysql会对表进行索引扫描(如果列上有索引),速度也比较快

COUNT() in InnoDB & MyISAM

COUNT(*)和COUNT(1)本质上没有区别，执行的复杂度都是O(N)，也就是采用全表扫描，进行循环+计数的方式进行统计。

如果是MySQL MyISAM存储引擎，统计数据表的行数只需要O(1)复杂度，这是因为每张MyISAM的数据表都有一个meta信息有存储了row_count值。而一致性由表级锁来保证。

而InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，只维护一个row_count变量。因此就需要采用扫描全表，进行循环+计数的方式来完成统计。

在InnoDB引擎中，如果是采用COUNT(*)和COUNT(1)来统计数据行数，要尽量采用二级索引。
因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。
对于查找具体的行来说，采用主键索引效率更高。

而对于COUNT(*)和COUNT(1)这种，不需要查找具体的行，只是统计行数来说，系统会自动采用占用空间更小的二级索引来进行统计。

如果有多个二级索引的时候，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

官网：

Mysql 实现Row Number mysqli num rows_主键


Mysql 实现Row Number mysqli num rows_主键_02


部分翻译：

  • COUNT(*)有点不同，它返回检索的行数的计数，无论它们是否包含 NULL值。
  • 对于事务性存储引擎，例如 InnoDB，存储精确的行数是有问题的。多个交易可能同时发生，每个交易都可能影响计数。
  • InnoDB不保留表中的内部行数，因为并发事务可能同时 “ 看到 ”不同数量的行。因此，SELECT COUNT(*)语句只计算当前事务可见的行。

在MySQL 5.7.18之前，通过扫描聚簇索引来InnoDB处理 SELECT COUNT()语句。从MySQL 5.7.18开始， 通过遍历最小的可用二级索引来InnoDB处理SELECT COUNT()语句，除非索引或优化器提示指示优化器使用不同的索引。如果不存在辅助索引，则扫描聚簇索引。

聚簇索引(clustered index)和辅助索引(secondary index)

聚簇索引(clustered index)
每一个InnoDB存储引擎下的表都有一个特殊的索引用来保存每一行的数据，称为聚簇索引。通常情况下，聚簇索引是主键的同义词。在InnoDB中，mysql是这样选择聚簇索引的：

如果表中定义了PRIMARY KEY，那么InnoDB就会使用它作为聚簇索引；

否则，如果没有定义PRIMARY KEY，InnoDB会选择第一个有NOT NULL约束的唯一索引作为PRIMARY KEY，然后InnoDB会使用它作为聚簇索引；

如果表中没有定义PRIMARY KEY或者合适的唯一索引。InnoDB会在一个合成的列中自动生成一个包含行ID的隐含的聚簇索引。这些行使用InnoDB赋予这些表的ID进行排序。行ID是6个字节的字段，且作为新行单一地自增。因此，根据行ID排序的行数据在物理上是根据插入的顺序进行排序。

聚簇索引如何加速查询
因为所有的行数据都跟聚簇索引存放在同一个地方，因此，通过聚簇索引访问数据行会更快。如果表十分大，跟使用不同地方保存数据和索引的存储组织来说，聚簇索引的结构会节省很多的I/O操作。（比如说，MyISAM使用了一个文件来保存数据以及另一个文件保存索引记录）。

辅助索引(secondary index)
除了聚簇索引之外的所有索引都被称为辅助索引。在InnoDB里，辅助索引的每一行记录都包含每一行的主键列，辅助索引指向主键。InnoDB使用这个主键来查找在聚簇索引中的行。如果主键很长，辅助索引会使用更多的空间，因此辅助索引有利于存储引擎拥有长度更短的主键。

测试

创建一个表：

CREATE TABLE `inno_log_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `u_id` (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
表的当前索引及explain结果：

Mysql 实现Row Number mysqli num rows_主键_03


剩下测试，请按照上面添加一个普通索引自行完成！！！

优化总结：

  • 一般情况下：COUNT() = COUNT(1) > COUNT(字段)
    所以尽量使用COUNT(    )，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。
  • 如果要统计COUNT()，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT
    COUNT(    )的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。

//相关参考链接
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html