日常开发中,获取数据的总数是很常见的业务场景,但是我们发现随着数据的增长count(*)越来越慢,这个是为什么呢,
count(*)的实现方式
我们要明确不同的存储引擎,他的实现方式不一样
当然我们要知道此事的说的是没有带条件的count(*),如果加了where条件的话,MyiSAM返回也不能返回的很快
由于我们现在如果使用mysql,大多使用的存储引擎都是innodb,因此由于他是一行行的累计计数,因此随着数据的越来越多,返回的速度就越慢的原因
为什么innodb不跟MyiSAM一样,也把数据存起来呢
那是因为即使在同一时刻的多个查询,由于多版本控制(MVCC)的原因,innoDB应该返回多少行也是不确定的,这里,我们用count(*)的例子为你解释一下.
假设t表中有10000条记录,我们设计三个用户的并行回话
如上图,你会看到,最后一个时刻,三个会话看到的数据总数不一样,有数据的默认可复用读是他的默认隔离级别,在代码上通过多版本控制,也就是MVCC,每一行记录的要判断自己师傅对这个会话可见,因此对于count(*)请求来说,innoDB只好把数据一行行的读出判断,可见的行才能后用于累加,
当然mysql也是对count(*)是有进行优化的,我们知道我们的索引是一棵树,而主键索引叶子节点是数据,而普通索引叶子节点是主键索引,所以主键索引比普通索引的树大些,因此mysql优化器会拿到索引树小的,进行遍历计算,在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库优化的通用手段之一
此时你可能还依稀记得下面命令可以获取行的数量,但是据官方说明,这个命令返回的行数,是不准确的,只有达到40-50%,所以这个命令也不能直接使用
show table status
总结如下
我们发现好像都不行呀,什么破玩意,我们只能自己实现了,我们自己把行数记录下来
用缓存系统保存计数
对于更细跟频繁的库来说,我们可能就会想到使用缓存系统,比如redis,于是我们试试用redis服务来保存这个表的行数,这个表每插入一条数据,redis就加1,每次删除就减一,试想一下还有什么问题吗
缓存系统可能会发生数据丢失,由于redis不能永久的存储在内存中,因此我们可能会想到持久化存储起来,即使这样,万一redis异常重启了,有可能会发生数据丢失,比如数据插入一行数据,redis记录值加1,此时还没有持久化,此时redis宕机,因此数据库重启,就会发生数据丢失,当然可以把数据从数据库重新拿出来,在放到redis里面,毕竟重启不经常出现的.
但是,就算redis能正常,依然也会发生逻辑上不准确。
比如有个页面要显示近期操作的100条记录和总操作数,这页面的逻辑就是到redis获取总数,再到数据库获取100条记录,如下两种会发生数据不一致的情况
不管上面那种时序去查询数据,最终的结果都会不准确,
使用数据库保存计数
我们可以使用在数据库新建一张表C去记录操作的总行数,由于innodb支持崩溃恢复不丢失数据的,因此可以解决数据丢失的问题,是否能解决不准确的问题呢
当然我们上面我们说过由于事物的支持,会导致不同回话会导致查询数据不一致性,但是我们也可以根据事物的特性,把不准确的问题解决掉,
由于事物可见性的特性,会话A没有提交的操作在会话B中是不可见的,查询计数值总数和查询最近100记录数据上是一致的。
不同count用法
首先,我们要知道count是一个聚合函数,对于返回的结果集,一行行判断,如果count函数的参数不是null,累加值就加1,否则就不加,最后返回累加值.
索引count(*),count(1),count(id),都表示返回满足条件的结果集的总行数,而count(字段)则表示满足条件的数据行里面,参数字段不为null的总个数
count(主键id)
innodb会遍历整张表,把每一行的id值都出来,返回给server层,server层拿到id后,判断是不可能为空的,就按行累加
count(1)
innodb会遍历整张表,但不取值,server层对于返回每一行,放一个数字1进去,判断是不可能为空的,按行累加
count(1)的性能要高于count(id),是由于count(id)返回id会涉及到解析数据行,以及拷贝字段值的操作
count(字段)
count(*)
并不会把所有字段全部取出来,而是专门做的优化,不取值,count(*)肯定不是null,按行累加。
所以我们可以总结,按照效率排序的话,count(字段)<count(主键id)<count(1)=count(*),建议尽量使用count(*)