mysql--count(*)为什么慢

迈不过友情╰ 2023-02-14 10:12 8阅读 0赞

在开发系统的时候，可能经常需要计算一个表的行数，比如一个交易系统的所有变更记录总数。这时候可能会想，一条 select count(\*) from t 语句不就解决了吗？

但是，你会发现随着系统中记录数越来越多，这条语句执行得也会越来越慢。然后你可能就想了，MySQL 怎么这么笨啊，记个总数，每次要查的时候直接读出来，不就好了吗。那么今天，我们就来聊聊 count(\*) 语句到底是怎样实现的，以及 MySQL 为什么会这么实现。

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

你首先要明确的是，在不同的 MySQL 引擎中，count(\*) 有不同的实现方式。

1、MyISAM 引擎，比较简单粗暴，直接将表的总行数存储在磁盘上，因此效率很高；
    
    2、InnoDB 引擎中，执行时，需要一行行的把数据查出来，然后累加；

为啥 MyISAM 就可以这样做呢？因为它不支持事务啊，不用担心数据不一致的问题。

而 InnoDB 就不一样了。

由于 MVCC 的存在，InnoDB 在当前执行环境下，对一共有多少数据行是不确定的

这里需要注意的是，我们在这里讨论的是没有过滤条件的 count(\*)，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。

不论是在事务支持、并发能力还是在数据安全方面，InnoDB引擎都优于 MyISAM。MySQL从5.6开始默认的存储引擎也是InnoDB。这就是当记录数越来越多的时候，计算一个表的总行数会越来越慢的原因。

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB表对于“应该返回多少行”也是不确定的。这里，举个例子。

假设，表 t 中有 1000 条数据，有下面三个用户并行的会话：

1、A 启动事务，查询表的总行数；  
2、C 直接插入一条数据，然后查询总行数；  
3、B 启动事务，插入一条数据，然后查询总行数；  
4、C 查询总行数；

注意，上面启动的事务都没有提交。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FoYW90YXRh_size_16_color_FFFFFF_t_70]  
A、B、C 查询的结果都不相同。

B 读到的是 1002，是因为可重复读隔离级别的存在，而 C 未开启事务，因此无法看到别的事务的更新；

InnoDB 引擎中，在每一个会话中，都需要逐行读取数据，然后计数返回总行数。

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

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

**如何提升count效率**  
在InnoDB对于如何提升count(\*)的查询效率，网上有多种解决办法，这里主要介绍三种，并分析可行性。

1，show table status  
show table status这个命令能够很快的查询出数据库中每个表的行数，但是真的能够替代count(\*)吗？

答案是不能。原因很简单，这个命令统计出来的值是一个「估值」，因此是不准确的，官方文档说误差大概在40%-50%。

因此这种方法直接pass，不准确还用它干嘛。

2，缓存系统存储总数  
这种方法也是最容易想到的，增加一行就+1，删除一行就-1，并且缓存系统读取也是很快，既简单又方便的为什么不用？

缓存系统和Mysql是两个系统，比如redis和Mysql这两个是典型的比较。两个系统最难的就是在高并发下无法保证数据的一致性。

在并发系统里面，我们是无法精确控制不同线程的执行时刻的，所以，即使Redis正常工作，计数值逻辑上还是可能不精确的。

3，在数据库保存计数  
通过缓存系统保存的分析得知了使用缓存无法保证数据在逻辑上的一致性，因此我们想到了直接使用数据库来保存，有了「事务」的支持，也就保证了数据的一致性了。

如何使用呢？很简单，直接将计数保存在一张表中（table\_name,total）。

至于执行的逻辑只需要将缓存系统中redis计数+1改成total字段+1即可，

在同一个事务中，保证了数据在逻辑上的一致性。

**总结**：

MyISAM表虽然count(\*)很快，但是不支持事务；

show table status命令虽然返回很快，但是不准确；

InnoDB直接count(\*)会遍历全表(没有where条件)，虽然结果准确，但会导致性能问题。

缓存系统的存储计数虽然简单效率高，但是无法保证数据的一致性。

数据库保存计数很简单，也能保证数据的一致性，建议使用。

最后

在系统高并发的情况下，使用数据库保存计数，是先更新计数+1,还是先插入数据。即是先update total+=1还是先insert into。

先insert再update，因为先Update会有加锁的操作，后续的insert操作会被阻塞。
    但是，update操作的表只是记录每个表的数量，基本上不会执行insert操作，影响不大。
    而且公司高并发环境下，insert操作时非常频繁的，如果每个insert操作都需要执行update的话，
    对性能的影响还是极大，最好应该用这个方法和redis缓存相结合，每过一段时间执行一次update操作
    
    大量update请求同时请求操作一个表,可能会造成锁表,结合定时任务,结合redis,缓解数据库压力;

**不同的 count 用法**  
在 select count(?) from t 这样的查询语句里面， count(\*) 、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能，有哪些差别。

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

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

至于分析性能差别的时候，可以记住这么几个原则：

server 层要什么就给什么；
    InnoDB 只给必要的值；
    现在的优化器只优化了 count(*) 的语义为“取行数”，其他的优化并没有做。

对于 count(主键id)，InnoDB 会遍历全表，取每行的主键 id，返回给 server 层，server 层拿到数据后，进行判断累加。

对于 count(1)，InnoDB 仍遍历全表，但是不取值，server 层对返回的每一行数据新增一个 1，然后进行判断累加；

因此，count(1) 要更快些，因为无需取值。从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段)：

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

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

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

注释：部分参考自其他博客

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