浅谈MySQL 统计行数的 count
共 2537字,需浏览 6分钟
·
2021-11-27 08:34
MySQL count() 函数我们并不陌生,用来统计每张表的行数。但如果你的表越来越大,且是 InnoDB 引擎的话,会发现计算的速度会越来越慢。在这篇文章里,会先介绍 count() 实现的原理及原因,然后是 count 不同用法的性能分析,最后给出需要频繁改变并需要统计表行数的解决方案。
Count() 的实现
![](https://filescdn.proginn.com/cb2f23de707ab5a4b8f4234e4701d6d6/1bdc345858faca0abce13729d6609e43.webp)
![](https://filescdn.proginn.com/aeb4ea128fb871fe8fee925d64363a60/b9ce654c6fbdc6a33e02c68e3e907f61.webp)
InnoDB 和 MyISAM 是 MySQL 常用的数据引擎,由于两者实现的不同,导致 count() 操作计算的效率也不同。
对于 MyISAM 来说,它把每个表的总行数都存在了磁盘上,因此使用 count(*) 计算时,效率很高直接返回结果。但如果加入了 where 条件,依然会进行搜索,所以效率是不高的。
对于 InnoDB 来说,在进行 count(*) 运算时,会把数据从引擎中一行行读出来,然后累计计数,自然表大了之后,效率就变低了。
那么,为什么 InnoDB 不能像 MyISAM 在表中记录呢?原因就在于 InnoDB 比 MyISAM 多了支持事务的特性,同时也需要一定的取舍。由于 MVCC 的控制,使得 MySQL 具有并发的能力,也就是说对于同一时刻,InnoDB 返回的表的行数是不一定的,事务看到的行数与开启后的一致性视图有关,换句话说,每个事务能看到的数据版本是不一样的,只能一行行拿出来进行判断。
像下面的事务,假设表 t 有 10000 条数据:
对于 Session A 来说,Session B 未提交不可见,Session C 提交了,但是在 Session A 启动后提交的,也不可见。所以是 10000.
而对于 Session B 而言,Session C 在启动之前提交,自己又插入了一条,所以结果是 10002.
其实 InnoDB 在进行 count(*) 操作时,还是做了优化的,在进行 count(*) 操作时,由于普通索引会保存主键的 id 值,所以会找到最小的那颗普通索引树进行查找,而不是去遍历主键索引树。
在保证逻辑正确的前提下,减少扫描的数据量,是数据库系统设计的通用法则。
另外在使用 show table status
时,也可以查询出行数,而且速度很快,但需要注意的是,该命令是通过索引统计的值来采样估算的。官方文档说误差可以有 40%-50%.
但如果我们真的需要实时的获取的某个表的行数,应该怎么办呢?
手动保存表的数量
![](https://filescdn.proginn.com/cb2f23de707ab5a4b8f4234e4701d6d6/1bdc345858faca0abce13729d6609e43.webp)
![](https://filescdn.proginn.com/aeb4ea128fb871fe8fee925d64363a60/b9ce654c6fbdc6a33e02c68e3e907f61.webp)
用缓存系统来保存计数
对于进行更新的表,可能会想到用缓存系统来支持。比如 Redis 里来保存某个表总行数。
每次插入数据库时,Redis 计数加一,相反则减一,这样看起来读写操作都很快,但会存在一些问题。
缓存系统会丢失更新:
对于 Redis 在内存中的数据,需要定期的同步到磁盘中,但对于 Redis 异常重启,就没有办法了。比如在 Redis 中插入后,Redis 重启,数据没有持久化到硬盘。这时可以在重启 Redis 后,从数据库执行下 count(*) 操作,然后更新到 Redis 中。一次全表扫描还是可行的。
逻辑不精确:
假设一个页面中,需要显示一张表的行数,以及每一条数据。在实现时,可以先从 Redis 取数量,然后从数据库里取记录。
但可能会出现这样的情况:
数据库查到 100 行结果里有最新插入的记录,而 Redis 计数里少 1.
数据库查到 100 行结果没有最新的记录,但 Redis 计数却多了 1.
对于 Session B 来说,在 T2 时刻,会发现 Redis 的数量比数据库少 1 条。
对于 Session B 来说,在 T2 时刻,会发现 Redis 的数量比数据库多 1 条。
其实产生问题的原因就是因为 Redis 和数据库查记录没有在同一个事务中。
用数据库保存
由于 InnoDB 引擎的支持,MySQL 本身是支持事务的,所以将 Redis 的插入操作换成在数据库的更新操作,就可以利用在RR级别下的事务特性,进而保证数据的精确性。
而且还有一点,由于 redo log 的支持,在 MySQL 发生异常时,是可以保证 crash-safe。
不同 count 用法的执行效率
![](https://filescdn.proginn.com/cb2f23de707ab5a4b8f4234e4701d6d6/1bdc345858faca0abce13729d6609e43.webp)
![](https://filescdn.proginn.com/aeb4ea128fb871fe8fee925d64363a60/b9ce654c6fbdc6a33e02c68e3e907f61.webp)
count() 本身是一个聚合函数,对于返回的结果集,一行行地判断。如果参数不是 NULL 的话,会一直累加,最后返回结果。
所以 count(*), count(id), count(1) 表示都是返回满足条件的结果集总行数。
而 count(字段),则表示满足条件的数据行里,不为 NULL 的字段。
对于 count(id) 来说,InnoDB 会遍历整张表,把每行 id 取出来,给 server 层。Server 判断 id 是否为空,然后累加。
对于 count(1) 来说,InnoDB 会遍历整张表,但不取值。Server 层会自己放入 1,然后累加。
所以对于 count(1) 的执行会比 count(*) 要快,少了解析数据行以及拷贝字段值的操作。
对于 count(字段) 来说,如果字段定义时是 not null, 会一行行读出,并判断不能为 null,然后累加。如果定义时可以为 null,执行时,需要将值去除,判断不是 null 才累加。
count(*) 除外,专门做了优化,不取值,直接按行累加,并且会找到最小的索引树进行计算。
总结
![](https://filescdn.proginn.com/cb2f23de707ab5a4b8f4234e4701d6d6/1bdc345858faca0abce13729d6609e43.webp)
![](https://filescdn.proginn.com/aeb4ea128fb871fe8fee925d64363a60/b9ce654c6fbdc6a33e02c68e3e907f61.webp)
MySQL count() 函数的执行效率和底层的数据引擎有关。MyISAM 不加 where 条件,查询会很快,但不支持事务。InnoDB 支持事务,由于 MVCC 的实现,导致每次查询都需要一行行的扫描,效率不高。
解决方法可以通过设计外部缓存如 Redis,保存记录。但存在异常重启和数据不准确的情况。可以通过在 InnoDB 中新建一张表,保存记录这样的解决方案。
最后,InnoDB 对 count(*) 做了独立的优化,而其他的 count 操作,则需要额外的操作。