统计大表的count()太慢，怎么办？

柔光的暖阳◎ 2023-05-29 03:21 0阅读 0赞

## 前言 ##

首先我们知道，MyISAM引擎直接把一个表的总行数存在了磁盘上，因此只需count(\* )的时候直接从磁盘上取值返回即可。  
但是InnoDB引擎由于支持事务，引出了隔离级别以及MVCC等概念，因此即使是同一个时刻的多个查询，在不同的事务隔离级别以及同一种隔离级别但是不同事务下读取到的count(\* )值也不同。

举例：  
一个表的最开始的总行数是10000  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]  
会话A开始一个事务查询表的总行数，得到的肯定是10000  
会话C使用自动提交机制向表中插入一条数据  
会话B在这时开始事务，向表中插入一条数据

此时，在会话A和会话B的事务中分别执行count(\* )，前者得到10000，后者得到10002（多出的一条是它自己的事务中插入的，另一条是会话B中插入的）,在会话C中执行count(\* )得到10001，因为会话A和会话B的事务还未提交。

## 优化器的作用 ##

在执行count(\* )的时候，MySQL优化器也会对该语句的执行进行优化：我们知道，InnoDB的主键索引的叶子节点都是数据，而辅助索引的叶子节点存放的是主键，因此辅助索引比普通索引要小很多，而且，无论使用主键索引还是辅助索引，得到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树遍历。

## 如何统计大表的count(\* ) ##

#### 1 利用缓存 ####

可以用Redis的键值来保存目标表的行数， 这个表每被插入一行 Redis 计数就加1，每被删除一行 Redis 计数就减 1。这种方式下，读和更新操作都很快，但是也存在很严重的缺陷。

**缺陷1**：丢失更新

Redis是基于内存的数据库，所有的数据都是先放在内存中再定期持久化到磁盘上。假如内存中的数据还未持久化，Redis服务器宕机了，则那部分的数据便会丢失。  
当然，这个缺陷是由办法解决的：当Redis重启之后，到数据库里执行count(\* )取出值后更新到Redis中。

**缺陷2**：不精确

如果会话A插入一条数据，此时会话B取了Redis中该表的函数，然后Redis中的计数加1，这两个会话的执行时间肯定都是一瞬间完成的，这就导致了统计的精度问题。

#### 2 保存在数据库中 ####

单独建立一张表C保存保存其他表的计数，这样能保证服务器宕机不丢失数据，因为InnoDB引擎可以保证崩溃恢复不丢失数据。其次，利用事务的原子性和隔离性可以保证统计逻辑上的精确性，比如会话A开启事务，将表C的计数加1，此时会话B（利用事务的自动提交机制）从表C中取出计数做统计，然后会话A提交事务，随着这里看起来和第一种方案中的缺陷2得到的结果类似，但是至少它的逻辑是正确的，因为在会话A未提交之前对表的操作，在默认隔离级别（可重复读）下，对其他事务是不可见的。  
但是，一切脱离逻辑业务的设计都是耍流氓，虽然对于开发人员来说，业务逻辑没问题，但是展示给用户的统计技术还是有失“精度”的。所以接下来我们再来讨论下count()这个聚合函数的基本原理，实际业务中还是可以考虑使用count来保证展现给用户的“精度”。

## 3 count(字段)的选择 ##

首先需要知道以下几个原则：  
1

> server层要什么就给什么  
> InnoDB只给必要的值

> count(\* )和count(1)：计算表一共有多少行，包含字段为NULL的行。  
> count(字段)：统计该字段在表中值为非NULL的行数。

> count()原理简述：count()是MySQL提供的聚合函数，执行时，数据库引擎层会把需要的数据返回给server层，server层拿到数据之后符合条件的数据就在结果总数中累加一个值。

#### count(主键id) ####

InnoDB引擎会遍历整张表，把每一行的id取出来，返回个server层。server层拿到id后，判断是不可能为空的，就按行累加。

#### count(1) ####

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

#### count(字段） ####

*  如果这个“字段”是定义为not null的话，InnoDB引擎遍历整个表，把“字段”取出来，server层收到数据，按行累加。
 *  如果这个“字段”定义允许为null，InnoDB引擎遍历整个表，把“字段”取出来，server层收到数据后判断不是null才累加。

执行效率：count（字段）<count（主键id）< count（1）≈ count（\*）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191126203320271.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw,size_16,color_FFFFFF,t_70