MySQL 笔记整理（14） --count(*)这么慢，我该怎么办？
笔记记录自林晓斌（丁奇）老师的《mysql实战45讲》
（本篇内图片均来自丁奇老师的讲解，如有侵权，请联系我删除）
14） --count(*)这么慢，我该怎么办？
有时你会发现，随着系统中记录数越来越多，select count(*) from t执行得也越来越慢。那么今天，我们就来聊聊count(*)语句到底是怎样实现的，以及mysql为什么会这么实现。
count(*)的实现方式:
前面我们提到过，mysql的引擎是插件式的，这里要明确一点，在不同的mysql引擎中，count(*)有不同的实现方式。
- myisam引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
- innodb引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数。
这里要说明的是，我们这里的count(*)，是没有加任何where的过滤条件的，如果添加了过滤条件，myisam引擎也是不能返回这么快的。之前的文章我们分享了为什么要使用innodb引擎，因为不论是在事务支持，并发能力还是数据安全方面，innodb都优于myisam。那么，为什么innodb不跟myisam一样，也把数字存下来呢？
这是因为即使是在同一个时刻的多个查询，优于mvcc（多版本并发控制）的原因，innodb表“应该返回多少行”也是不确定的。这里，我用一个算count（*）的例子来解释：假设表t中现在有10000条记录，我们设计了三个用户并行的会话。
图1 会话a,b,c的执行流程
- 会话a先启动事务并查询一次表的总行数；
- 会话b启动事务，插入一行记录后，查询表的总行数；
- 会话c先启动一个单独的语句，插入一行记录后，查询表的总行数。
我们假设从上到下是按照时间顺序执行的，同一行语句是在同一时刻执行的。你会看到，三个会话a,b,c会同时查询表t的总行数，但拿到的结果却不同。这和innodb的事务设计有关系，可重复读是它默认的隔离级别，在代码上是通过mvcc，也就是多版本并发控制来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于count(*)请求来说，innodb只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。
当然，mysql在执行count(*)过程时还是做了优化的。innodb是索引组织表，主键索引的叶子节点是数据，而普通索引的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，mysql优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
另外，如果你用过show table status命令的话，就会发现这个命令的输出结果里面也有一个table_rows用于显示这个表当前有多少行，这个命令执行挺快的，那这个字段能代替count(*)吗?实际上是不能的，我们之前提到过，索引统计的值是通过采样来估算的。实际上，这个字段正是通过采样估算二来的，因此它也很不准。官方文档说这个字段的误差可能达到40%~50%.
现在我们回到文章开头的问题，如果你现在有一个页面要经常显示交易操作记录的总数，到底应该怎么办呢？答案是，我们只能自己计数。基本思路是我们自己找一个地方，把操作记录表的行数存起来。
用缓存系统保存计数：
你可能会很自然的想到使用redis这样的服务来保存这个表的总行数。这个表每次插入一行redis记录就加1.每次删除一行，redis记录就减1。但你再想一下，这会有什么问题嘛？没错，缓存系统有可能会丢失更新。如redis异常重启，这时redis中保存的记录都不会有了。当然，这个问题还是有解决办法的，比如每次redis重启之后我们都去db中单独执行一次count(*)来求得记录，毕竟redis并不会常常重启，这个成本也不会很高。但实际上，即使你使用这种方式，redis的记录还是有可能是不精确的。我们来举两个例子：
- 如果我们先插入记录，再更新redis时可能会存在这一情况。t1时刻表t插入数据r。t2时刻读取redis记录数，并且从db中查询最近的100条记录。t3时刻，更新redis记录，计数加1。这种情况下，t2时刻读取最近100条记录是包括数据r的，而同时redis的记录数是不包含r的。
- 反过来，如果我们先更新redis记录，再插入数据呢？t1时刻我们更新redis计数加1。t2时刻，读取redis记录，从db中查询最近的100条记录。t3时刻，向表t插入数据r。这种情况下，t2时刻读取最近100条记录是不包括r的，而redis的记录中却是包含r这个记录的。
在数据库保存计数：
使用缓存可能会有记录不精确的问题，如果我们把这个计数直接放到数据库里单独的一张计数表c中，又会怎么样呢？首先是异常重启的问题，数据库崩溃了，异常重启。mysql是支持崩溃恢复的，所以这其实不是个问题。我们再来看看刚刚在缓存系统中出现的记录不精确的问题：
相信你已经发行，记录不精确的问题的关键在于，读取记录和记录总数时数据还没有完整的更新完。即redis与db中记录并不完全一致。换句话说，比如上面两个例子，如果我们在t4时刻再去执行t2中的操作就不会出现这类问题了。
图2，会话a,b的执行时序图
如图2所示，由于mysql默认的隔离级别是可重复读，所以在会话b中，t3时刻并不会读到t2时刻改变的数据。因为t3时刻，会话a的改动还未提交，它对会话b来说是不可见的。这样，就保证了数据的精确结果。
不同count的用法：
你可能看到过各种写法来求总行数，如count(*), count(1)，count(主键id)等等。这里我们首先要弄清楚count()语义。count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数是null，累计值就加1，否则就不加。最后返回累计值。
所以count(*), count(1)，count(主键id)返回的就是满足条件的结果集的总行数；而count(字段)，返回的就是满足条件的数据里，参数“字段”不为null的总个数。mysql有这么几个原则：
- server层要什么就给什么
- innodb只给必要的值
- 现在优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。
因此，对于count(主键id),innodb先是会遍历整张表，把每一行的id都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累计。对于count(1)，innodb先是会遍历整张表，server层对返回的每一行，放一个“1”进去，判断不为空，按行累计。由于少了返回id，解析数据行等操作，count（1）会更快一些。对于count(字段)，不仅要判断数据行，还要判断是否为null。而对于count(*)，由于做了优化，直接按行累计。所以结论是，按效率排序的话 count(字段)<count(主键id)<count(1)约等于count(*)。
上期问题：
如果有一个情况是这样的：想要收缩表空间，结果适得其反，看上去是：
- 一个表t文件大小为1tb；
- 对这个表执行alter table t engine = innodb；
- 执行完成后，空间不仅没有变小，还稍微大了一点，变为了1.01tb
请问这是什么原因导致的呢？
答：在ddl期间，如果刚好有外部的dml在执行，这期间可能会引入一些新的空洞。另外，还有一个更深层的机制在文章中没有提到。在重建表的时候，innodb不会把整张表占满，每个页会留下1/16的空间给后续的更新用。也就是说，其实重建后的表不是“最”紧凑的。所以导致上述情况的可能是这么一个流程：
- 将表t重建一次。
- 插入一部分数据，但是插入的这些数据，用掉了一部分预留空间。
- 再重建一次表t，就可能会出现上面的情况了。
问题：
在上面关于count计数的讨论中，我们用了事务来确保数据的精确性。由于事务可以保证中间结果不被别的事务读到，因此修改计数值和插入新的记录的顺序是不影响逻辑结果的。但是，从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是先更新计数表呢？