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

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《Mysql性能优化：为什么你的count(*)这么慢？》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

导读

• 文章首发于作者微信公众号【码猿技术专栏】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条件)，虽然结果准确，但会导致性能问题。
• 缓存系统的存储计数虽然简单效率高，但是无法保证数据的一致性。
• 数据库保存计数很简单，也能保证数据的一致性，建议使用。
• 思考题，读者留言区讨论：在系统高并发的情况下，使用数据库保存计数，是先

  更新计数+1

  ,还是先

  插入数据

  。即是先

  update total+=1

  还是先

  insert into

  。

以上就是《Mysql性能优化：为什么你的count(*)这么慢？》的详细内容，更多关于mysql的资料请关注golang学习网公众号！