MySQL 中的 count 你用对了吗？性能对比一览

朱雀 2023-10-13 21:36 0阅读 0赞

在MySQL中，count()是一个强大的统计函数，但你知道吗？它在不同的引擎中有着不同的实现方式！不仅如此，本文还会带你了解不同count用法的性能差异，告诉你哪种用法最高效。此外，我们还将探讨使用缓存系统和数据库来保存计数的方案差异，揭示它们之间的对比。

首先来看一下这张思维导图，对本文内容有个简单的认识。

![format_png][]

#### **count(\*) 的实现方式** ####

在不同的 MySQL 引擎中，count(*) 有不同的实现方式，这里讨论的是没有过滤条件的 count(*)。

*  MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(`*`) 的时候会直接返回这个数，效率很高；如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。
 *  而 InnoDB 引擎就麻烦了，它执行 count(`*`) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

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

这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

如下案例所示，最后在同一时刻三个会话查询表t的总行数结果不同。

![img][]

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

虽然在 InnoDB 引擎中执行 count(`*`) 执行需要逐行读取，但是内部还是做了查询优化。 InnoDB 是索引组织表，主键索引树的叶子节点是数据，而二级索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(\*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。**在保证逻辑正确的前提下，尽量减少扫描的数据量**，是数据库系统设计的通用法则之一。

除了执行 count(`*`) 命令得到数据行数，我们还使用过 `show table status` 命令，该命令用于显示表中当前有多少行，但是需要注意的是，该命令得到的结果是通过采样来估算的，官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用。

**总结**

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

#### **不同的 count 用法** ####

分析 count(\*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能，有哪些差别？

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

所以，count(\*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

**对于 count(主键 id) 来说**，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

**count(主键 id)** 不会走主键索引，因为普通索引树比主键索引树小很多。假设表中有多个普通索引树，则由优化器来决定走哪个索引。

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

单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

**对于 count(字段) 来说：**

1.  如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
2.  如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(字段) 需要查询出该字段值，只能通过聚簇索引树，所以效率最差。

但是 **count(`\*`) 是例外**，并不会把全部字段取出来，而是专门做了优化，不取值。count(\*) 肯定不是 null，直接按行累加。

主键 ID肯定非空，为什么优化器不能像优化 count(*)那样优化count(主键ID) 呢？答案是没必要，不做重复优化，推荐使用 count(*)。

根据上述分析，按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(`*`)，所以我建议你，尽量使用 count(`*)`。

有些文章说 count(*) 性能差，用词不恰当，难道其他几种计数方式就不差了，注意是计数性能差，而不是count(*)差。关于计数性能差，可以增加缓存，比如说 redis缓存或者本地缓存，但是不能保证完全实时一致。

#### **用缓存系统保存计数** ####

对于更新很频繁的库来说，你可能会第一时间想到，用缓存系统来支持。

你可以用一个 Redis 服务来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1，每被删除一行 Redis 计数就减 1。这种方式下，读和更新操作都很快，但你再想一下这种方式存在什么问题吗？

没错，缓存系统可能会丢失更新。

Redis 的数据不能永久地留在内存里，所以你会找一个地方把这个值定期地持久化存储起来。但即使这样，仍然可能丢失更新。试想如果刚刚在数据表中插入了一行，Redis 中保存的值也加了 1，然后 Redis 异常重启了，重启后你要从存储 redis 数据的地方把这个值读回来，而刚刚加 1 的这个计数操作却丢失了。

当然了，这还是有解的。比如，Redis 异常重启以后，到数据库里面单独执行一次 count(`*`) 获取真实的行数，再把这个值写回到 Redis 里就可以了。异常重启毕竟不是经常出现的情况，这一次全表扫描的成本，还是可以接受的。

但实际上，**将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使 Redis 正常工作，这个值还是逻辑上不精确的**。

Redis 和 MySQL 是两个独立的数据源，我们需要解决并发环境下数据不一致的问题，一般我们都会**先更新数据库，再删缓存。**

我们查询如下两个时序图：

![img][img 1]

会话A在 T2时刻执行了插入操作，在 T3时刻会话B读取缓存中的计数，那么此时读取到的计数和会话A事务结束后读取到的计数就会发生不一致。

如果在会话A中调整更新计数操作和插入操作的顺序，那么是否会有所好转呢？

![img][img 2]

答案还是不行。虽然在 T3 时刻会话B 可以查询到最新的计数，但是无法获取到待插入的数据R。

因为 Redis 和 MySQL 是不同的存储构成的系统，不支持分布式事务，所以没法保证计数的精确性。

#### **在数据库保存计数** ####

根据上面的分析，用缓存系统保存计数有丢失数据和计数不精确的问题。那么，如果我们把这个计数直接放到数据库里单独的一张计数表 C 中，又会怎么样呢？

首先，这解决了崩溃丢失的问题，InnoDB 是支持崩溃恢复不丢数据的。

利用事务来解决时序2 图中的问题，如下所示：

![img][img 3]

因为MySQL 事务机制和 MVCC，在 T3时刻会话B进行的操作不受会话A 的影响，因为会话A在 T4才提交事务，T2做的修改对会话B不可见。

#### 总结 ####

在不同的存储引擎中，`count(*)`函数的实现方式不同。我们之前讨论过使用缓存系统来存储计数值存在的问题。现在，我来简洁地解释一下为什么将计数值存储在Redis中不能保证与MySQL表中的数据精确一致。

Redis和MySQL是不同的存储系统，它们不支持分布式事务，因此无法提供精确一致的视图。这就是为什么将计数值存储在Redis中无法确保与MySQL表中数据的一致性。相比之下，将计数值存储在MySQL中可以解决一致性视图的问题。

[format_png]: https://img-blog.csdnimg.cn/img_convert/4a675ed2a1c884e58a7de341ec057bee.webp?x-oss-process=image/format,png
[img]: https://img-blog.csdnimg.cn/img_convert/5f3894b637f1969b6dcd6f9900515411.webp?x-oss-process=image/format,png
[img 1]: https://img-blog.csdnimg.cn/img_convert/d6b075832cff0930fec8f25b99873aa7.webp?x-oss-process=image/format,png
[img 2]: https://img-blog.csdnimg.cn/img_convert/0cc1fe806f2997288db3b908d3ce85f7.webp?x-oss-process=image/format,png
[img 3]: https://img-blog.csdnimg.cn/img_convert/02199bb4eb799ed065ddb8d1bb90b91f.webp?x-oss-process=image/format,png