Mysql性能优化：为什么count(*)这么慢？

 

导读

• 在开发中一定会用到统计一张表的行数，比如一个交易系统，老板会让你每天生成一个报表，这些统计信息少不了sql中的count函数。

• 但是随着记录越来越多，查询的速度会越来越慢，为什么会这样呢？Mysql内部到底是怎么处理的？

• 今天这篇文章将从Mysql内部对于count函数是怎样处理的来展开详细的讲述。

count的实现方式

• 在Mysql中的不同的存储引擎对count函数有不同的实现方式。

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高（没有where查询条件）。

• InnoDB引擎并没有直接将总数存在磁盘上，在执行count(*)函数的时候需要一行一行的将数据读出来，然后累计总数。

为什么InnoDB不将总数存起来？

• 说到InnoDB相信读者总会想到其支持事务的特性，事务具有隔离性，如果将总数存起来，怎么保证各个事务之间的总数的一致性呢？不明白的看下图：

• 事务A和事务B中的count(*)的执行结果是不同的，因此InnoDB引擎在每个事务中返回多少行是不确定的，只能一行一行的读出来用来判断总数。

如何提升count效率

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

show table status

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

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

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

缓存系统存储总数

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

• 缓存系统和Mysql是两个系统，比如redis和Mysql这两个是典型的比较。两个系统最难的就是在高并发下无法保证数据的一致性。通过以下两图我们来理解一下：

• 通过上面两张图，无论是redis计数+1还是insert into user先执行，最终都会导致数据在逻辑上的不一致。第一张图会出现redis计数少了，第二张图虽然计数正确了但是并没有查询出插入的那一行数据。

• 在并发系统里面，我们是无法精确控制不同线程的执行时刻的，因为存在图中的这种操作序列，所以，我们说即使Redis正常工作，这个计数值还是逻辑上不精确的。

在数据库保存计数

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

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

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

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

不同count的用法

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

• count的用法有多种，分别是count(*)、count(字段)、count(1)、count(主键id)。那么多种用法，到底有什么差别呢？当然，「前提是没有where条件语句」。

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

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

• count(字段)：

  • 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；

  • 如果这个字段定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

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

• 所以结论很简单：「按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以建议读者，尽量使用count(*)。」

• 「注意」：这里肯定有人会问，count(id)不是走的索引吗，为什么查询效率和其他的差不多呢？陈某在这里解释一下，虽然走的索引，但是还是要一行一行的扫描才能统计出来总数。

总结

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

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

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

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

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