count(*) 怎么优化？

 前言

哈喽，我是狗哥。小伙伴都知道我最近换工作了，薪资、工作内容什么的都是我比较满意的。五月底也面试了有 6、7 家公司，应该拿了有 5 个 offer。这段时间也被问了很多面试题，我打算写一个专题分享出来，希望对你们有所帮助～

这期面试官提的问题是：

count (1) 和 count (*) 有啥区别？你更推荐用哪个？数据量很大的情况下怎么优化？

国际惯例先上思维导图：

1.1 往期精彩

MySQL 查询语句是怎么执行的？

MySQL 索引

MySQL 日志

MySQL 事务与 MVCC

MySQL 的锁机制

MySQL 字符串怎么设计索引？

面试官：数据库自增 ID 用完了会咋样？

面试官：order by 怎么优化？

02 四种 count 的区别

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

既然都说到这里了，干脆就把 4 种 count 的区别都对比下：

• count (字段）：遍历整张表，需要取值，判断 字段！= null，按行累加；
• count (主键) ：遍历整张表，需要取 ID，判断 id !=null，按行累加；
• count (1) ：遍历整张表，不取值，返回的每一行放一个数字 1，按行累加；
• count (*)：不会把全部字段取出，专门做了优化，不取值。count ( * ) 肯定不是 null，按行累加。

count (主键) 可能会选择最小的索引来遍历，而 count (字段) 的话，如果字段上没有索引，就只能选主键索引，所以性能上 count (字段) < count (主键)

因为 count (*) 和 count (1) 不取字段值，减少往 server 层的数据返回，所以比其他 count (字段) 要返回值的性能较好；

所以结论是：** 按照效率排序的话，count (字段)<count (主键 id)<count (1)≈count ()，建议尽量使用 count ()。

2.1 MySQL 对 count (*) 做的优化

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。因此，普通索引树比主键索引树小很多。

对于 count (*) 来说，遍历哪个索引树得到的结果逻辑上都是一样的。MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

03 count (*) 的实现方式

count (*) 在不同引擎中的实现方式是不一样的：

• MyISAM：不支持事务，把一个表的总行数存在了磁盘上，因此执行 count (*) 的时候会直接返回这个数，效率很高；
• InnoDB：支持事务，它执行 count (*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

当然这里讨论的是没有 where 条件下的 count，如果有 where 条件，那么即使是 MyISAM 也必须累积计数的。

至于有 where 条件怎么执行，建议看看海神的这篇文章：

SELECT COUNT (*) 会造成全表扫描吗？

当你的记录数越来越多的时候，计算一个表的总行数会越来越慢。你可能会问：

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？

其实是因为 InnDB 支持事务的 MVCC 的原因，当前时刻的 SQL 应该返回的记录数是多少，它也需要扫描才知道。不知道 MVCC 的，可以看看之前的旧文：

MySQL 事务与 MVCC

看完还不懂？举个例子：假设表 t 中现在有 10000 条记录，有三个用户并行的会话。

• 会话 A 先启动事务并查询一次表的总行数；
• 会话 B 启动事务，插入一行后记录后，查询表的总行数；
• 会话 C 先启动一个单独的语句，插入一行记录后，查询表的总行数。

它的执行流程以及结果是这样的：

A、B、C

你也发现了，因为 MVCC 机制，事务之间是存在可见性的。所以，并发环境下每个会话得到的数据是不一样的。

分析：

• 会话 A 在 C 之前启动，C 可见 A 且会话 C 自己插入一行，再 count (*)，对它自己来说肯定是可见的、所以结果 +1。
• 会话 A、C 在 B 之前启动，B 可以看见 A、C，自己插入一条数据 +1、C 插入一条数据 +1、所以 B 结果 + 2

04 TABLE_ROWS 能代替 count (*) 吗？

如果你看过官方文档的话，你会知道 show table status 命令，它的结果有个 ROWS 字段就是估算该表的数据量，如下所示：

真实数据：

图一是估算数据、图二是真实数据。实际上你会发现两种数据不一致，因为 show table status 命令对数量的统计是估算的，并不准确。

到这里我们小结一下：

• MyISAM 表虽然 count (*) 很快，但是不支持事务；
• show table status 命令虽然返回很快，但是不准确；
• InnoDB 表直接 count (*) 会遍历全表，虽然结果准确，但会导致性能问题。

那么问题来了：假设我现在有个订单页面，更新很频繁，并且需求是要显示实时的操作记录总数、并且展现最新的 100 条记录信息。应该用哪种方式呀？

很明显只能自己计数呀，那么如何设计呢？

05 基于 count (*) 的计数方案

基本思路就是：你需要自己找一个地方，把操作记录表的行数存起来。

5.1 结果放在 Redis

更新频繁，我第一时间肯定是想到 Redis 这神器呀。表插入一行 Redis 计数加一，删除一行计数减一。Redis 性能贼好，听起来这方案似乎完美。

仔细一想，还是有 ** 丢失更新的问题：MySQL 插入一行，Redis 宕机咋办？** 你可能会说，恢复之后再执行一次 count (*)，再次缓存不就得了？

好，丢失更新的问题确实解决了，但是 MySQL 和 Redis 的数据怎么保证一致性呢？

假设我现在要去最新的 100 条数据，并在前端展现。时序图如下：

很明显，会话 A 插入数据，但是还没来得及更新 Redis；会话 B 查询 Redis 计数，并向 MySQL 查询最新的 100 条记录。

此时数据就不精确：查到的 100 行结果里面有最新插入记录，而 Redis 的计数里还没加 1，总数不精确。

有人可能说，你 SessionA 换个顺序不就好了。先更新 Redis 计数、再插入 MySQL 表记录。像下面这样

其实在 T3 时刻还是会出现不一致的情况：查到的 100 行结果里面没有最新插入记录，而 Redis 的计数里加了 1，最新记录不精确

所以说，用 Redis 保存计数有丢失数据和计数不精确的问题。

5.2 结果放在 MySQL

上面出现数据丢失或计算不精确的原因在于：MySQL 和 Redis 的事务不是同一体系的，我们并不能保证两者事务的原子性，而把 Redis 也换成 MySQL 这就迎刃而解了。

那我们换个思路，不能新建一张 MySQL 表 C 专门用来存放订单表的总数吗？

看到这里，你可能会说这不跟开头冲突了么？由于 InnoDB 要支持事务，从而导致 InnoDB 表不能把 count (*) 直接存起来，然后查询的时候直接返回计算好的。你现在说又能存，这不扯了么？

其实我们可以利用事务原子性和隔离特性解决这一问题：表 C 计数器的修改和订单数据的写表在一个事务中。读取计数器和查询最近订单数据也在一个事务中。看到这里，有没有清晰一点？

我来画个时序图：

会话 A 进行写操作，T3 时刻，A 的更新事务还没有提交；所以计数值加 1 这个操作对会话 B 还不可见。也就是说会话 B 看到的结果在逻辑上就是一致的。

看到这里是不是有点，成也事务败也事务的感觉？

06 总结

首先，在 4 中 count 的对比中，我们应该选 count (*)，因为 MySQL 对它作做了优化；

第二，count (*) 在两种搜索引擎中的实现是不一样的，MyIsam 直接把总数存在硬盘、而 InnDB 则是老式计数；

第三，分析了 Redis 存储计数会出现的问题，把计数值也放在 MySQL 中，利用事务的原子性和隔离性，就可以解决一致性的问题。

最后，数据量不大，我们尽量用 count (*) 实现计数；数据量很大的情况考虑新建 MySQL 表存储计数，用事务的原子性和隔离性解决。