MySQL 笔记整理(14) --count(*)这么慢,我该怎么办?

发布时间:2019-04-01 14:00:19发布者:Mr.Zhang阅读(209)

笔记记录自林晓斌(丁奇)老师的《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的记录还是有可能是不精确的。我们来举两个例子:

  1. 如果我们先插入记录,再更新Redis时可能会存在这一情况。T1时刻表t插入数据R。T2时刻读取redis记录数,并且从DB中查询最近的100条记录。T3时刻,更新redis记录,计数加1。这种情况下,T2时刻读取最近100条记录是包括数据R的,而同时Redis的记录数是不包含R的。
  2. 反过来,如果我们先更新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有这么几个原则:

  1. server层要什么就给什么
  2. InnoDB只给必要的值
  3. 现在优化器只优化了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(*)。

上期问题:

  如果有一个情况是这样的:想要收缩表空间,结果适得其反,看上去是:

  1. 一个表t文件大小为1TB;
  2. 对这个表执行alter table t engine = InnoDB;
  3. 执行完成后,空间不仅没有变小,还稍微大了一点,变为了1.01TB

  请问这是什么原因导致的呢?

  答:在DDL期间,如果刚好有外部的DML在执行,这期间可能会引入一些新的空洞。另外,还有一个更深层的机制在文章中没有提到。在重建表的时候,InnoDB不会把整张表占满,每个页会留下1/16的空间给后续的更新用。也就是说,其实重建后的表不是“最”紧凑的。所以导致上述情况的可能是这么一个流程:

  1. 将表t重建一次。
  2. 插入一部分数据,但是插入的这些数据,用掉了一部分预留空间。
  3. 再重建一次表t,就可能会出现上面的情况了。

问题:

  在上面关于count计数的讨论中,我们用了事务来确保数据的精确性。由于事务可以保证中间结果不被别的事务读到,因此修改计数值和插入新的记录的顺序是不影响逻辑结果的。但是,从并发系统性能的角度考虑,你觉得在这个事务序列里,应该先插入操作记录,还是先更新计数表呢?





本文转自博客园,原文地址:https://www.cnblogs.com/dogtwo0214/p/10621434.html