前言:此博客为本人学习课程之后反思复盘之用,如有错误之处,还请您指正, 文章若有侵权之处,也请您联系我删除。
14.count(*)这么慢,我该怎么办?
相信大家在开发中,经常会遇到需要查询表记录总数的场景。刚开始,表的记录比较小,随着业务量的增长,表的记录总数会越来越大,我们会发现,count(*)会越来越慢。
不同引擎count(*)实现方式
MYSQL原生的引擎MYISAM,因为不支持事务和不支持mvcc的原因,所以可以将每张表的记录总数存在磁盘里,因此MYISAM执行count(*)非常快,只需到表里拿相应的值就可以。
这里需要注意,如果count(*)后有where条件,那么它查询是不会那么快的。
innodb因为事务和MVCC的关系,所以某一时刻,数据表的记录总数是不确定的:
如图所示,在可重复读隔离级别下,三个会话查询记录总数,分别返回了不同的值。
所以innodb执行count(*)只能扫描索引树,一行一行把数据取出来,判断记录是否对自己可见,按行累加。
当然innodb对于count(*)有优化,会找到最小的那棵索引数进行扫描。
使用show table status也可以拿到表记录总数,但是这个值是不精确的,误差可以达到40%-50%,因为这个值也是通过采样估算得来的。
四种count用法比较
至于分析性能差别的时候,你可以记住这么几个原则:
server 层要什么就给什么;
InnoDB 只给必要的值;
现在的优化器只优化了 count(*) 的语义为“取行数”,其他“显而易见”的优化并没有做。
性能:count(字段)<count(主键id)<count(1)约等于count(*)
四种count运行过程:
count(字段):一行行从表里拿到这个字段返回给server层,server层判断:这个字段不可能为空,按行累加;可能为空,判断值不为空之后,按行累加
count(主键id):innodb引擎遍历整张表,把每一行的主键id取出来返回给server层,server判断不会为空,按行累加。(主键不可能为空,这里其实不用判断为空,可以进行优化,但是代 码就是这样)
count(1):innodb引擎遍历整张表,但不取值,给每行放一个1,判读不为空之后,按行累加。
count(*):对count(*)专门做了优化,优化器认为count(*)语义是取行树,不需要取所有字段,count(*)肯定不为空,按行累加。
那么既然count(*)是最快的操作,我们有没有什么办法可以解决count(*)慢呢?
一般遇到性能问题,我们会想到读写分离,利用redis等非关系型数据库来做缓存等等,这里我们可以尝试将记录总数保存在缓存里。
在缓存中保存表记录总数
将记录保存在缓存中,插入一条记录,记录总数加1,删除一条记录,记录总数减1。但是这样会有什么问题呢?
缓存可能会丢失更新。
缓存无法永久保存数据,可能会丢失更新,导致数据不准确。对于这种情况,我们可以将缓存的数据持久化保存到磁盘里。但即使是这样,还是有问题,假如redis在持久化数据的时候,异常重启了,那么这次更新操作就丢失了。这种情况,我们也可以解决,对表执行count(*)操作,将值写回redis。
但是即使是这样,用缓存来保存表记录总数,在逻辑上也是不正确的。
如图所示,表数据插入一行,但是redis计数还未增加,这时会话B拿到的的redis计数和最近100条记录在逻辑上不一致,记录有最新一次的插入记录,但是计数却未增加。
所以,用缓存来保存表记录总数存在逻辑不一致的问题,那么还有没有其他的办法呢?
我们可以将表记录总数保存到一张表中。
使用一张表来存储记录总数
我们可以用MYSQL的事务,来解决上述redis存储的逻辑不一致的问题。
在可重复读隔离级别下,MYSQL可以拿到一致性视图,逻辑上是一致的。
如图所示,会话B因为可重复读隔离级别的关系,读到的“计数值”和“最近100条记录”在逻辑上是一致的。
这里刚开始有点混淆,因为这里执行count(*)也需要判断记录是否对自己可见,而innodb也是因为需要判断记录是否对自己可见,所以无法将记录总数保存在磁盘里面,他们的不同点是:
这里获取表记录总数,只需select这一行数据即可,判断记录是否对自己可见,也只判断这一行;而innodb想要将记录总数保存在磁盘里,需要执行count(*)操作,需要判断所有行记录是否对自己可见。
小结:
本文介绍了innodb为什么不能像MYISAM那样将记录总数保存在引擎中的原因,我们提出了解决方法:将记录总数保存在缓存中,对于缓存中存在的逻辑不一致的问题,我们最后提出了将记录总数保存在某一张表中的解决方案。
文中还介绍了四种count用法的性能、执行过程,四种count用法里,count(*)就是最快的,所以,其他的count千万不要再用!