count函数性能比较与优化

count(*)实现方式

count(*) 在不同的搜索引擎中，实现不同。MyISAM将一个表的总行数存在了磁盘可以直接返回，InnoDB需要一行一行从引擎读取累计计数。

为什么innodb不存下表的总行数?

对于Innodb来说，即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，同样的查询返回的结果也可能不同，比如InnoDB 表“应该返回多少行”也是不确定的。如下图

会话A	会话B	会话C
begin;
select count(*) from t;
		insert into t(插入一行);
	begin;
	insert into t(插入一行);
select count(*) from t;(返回10000)	select count(*) from t;(返回10002)	select count(*) from t;(返回10001)

这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

count(*)优化策略

• 遍历最小的索引树

  InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

• 不会把全部字段取出来，不取值，按行累加。

如果经常查询记录总数，应该如何设计优化？

首先，需要自己计数。 方案一：使用redis缓存存储。 缺陷：存在逻辑上不精确，数据不一致问题。

假设我们有页面，展示最新100条数据与总行数。我们通过查询redis计数与查询最新100条记录实现该功能。单独使用是没问题的。如果同时有另一个会话进行插入，就会因为时序导致数据不一致问题。

假设先插入数据，后redis+1。在两者执行中间访问页面。如下表。此时插入数据生效，redis未变化，导致最新100条记录正确，总行数错误。

时刻	会话A	会话B
T1	插入一行数据
T2		读redis计数，查询最新100条记录
T3	redis计数加1

如果新增操作时序反过来，先redis+1后新增，就会存在总行数正确，最新100条未更新的情况。同样数据不一致。

方案二、在数据库保存计数 把这个计数直接放到数据库里单独的一张计数表 C 中。 首先，这解决了崩溃丢失的问题，InnoDB 是支持崩溃恢复不丢数据的。 其次，对于方案一的数据不一致问题，我们可以利用“事务”特性解决

img

如图。会话 B 的读操作仍然是在 T3 执行的，但是因为这时候更新事务还没有提交，所以计数值加 1 这个操作对会话 B 还不可见。因此，会话 B 看到的结果里， 查计数值和“最近 100 条记录”看到的结果，逻辑上就是一致的。

count(字段 ) < count(主键id ) < count(1) ≈ count(*)

首先要清楚 count() 的语义。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

先说结论，效率从低到高：count(字段 ) < count(主键id ) < count(1) ≈ count(*)

对count(字段)来说：

1. 字段定义not null，一行行地从记录里面读出这个字段，判断为不能是 null，按行累加

2. 字段允许null，判断为可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(字段)效率最低，因为它表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。而其它三个表示返回满足条件的结果集的总行数。同时取值操作，拷贝字段操作耗时。

对于 count(主键 id) 来说： InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。 性能不高，因为需要从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(1) 来说： InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

对于count(*) 来说： 不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

参考： 《mysql实战45讲》丁奇