专栏首页洁癖是一只狗Mysql获取数据的总行数count(*)很慢

Mysql获取数据的总行数count(*)很慢

日常开发中,获取数据的总数是很常见的业务场景,但是我们发现随着数据的增长count(*)越来越慢,这个是为什么呢,

count(*)的实现方式

我们要明确不同的存储引擎,他的实现方式不一样

  • MyiSAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候直接返回个数,效率很高
  • 而innoDB引擎就麻烦了,他的执行count(*)的时候,是一行行的累加计数

当然我们要知道此事的说的是没有带条件的count(*),如果加了where条件的话,MyiSAM返回也不能返回的很快

由于我们现在如果使用mysql,大多使用的存储引擎都是innodb,因此由于他是一行行的累计计数,因此随着数据的越来越多,返回的速度就越慢的原因

为什么innodb不跟MyiSAM一样,也把数据存起来呢

那是因为即使在同一时刻的多个查询,由于多版本控制(MVCC)的原因,innoDB应该返回多少行也是不确定的,这里,我们用count(*)的例子为你解释一下.

假设t表中有10000条记录,我们设计三个用户的并行回话

  • 会话A启动事务并查询一次表的总数
  • 会话B启动事务,插入一条记录后,查询表的总数
  • 会话C启动事务,单独插入一下数据后,查询表的总数

如上图,你会看到,最后一个时刻,三个会话看到的数据总数不一样,有数据的默认可复用读是他的默认隔离级别,在代码上通过多版本控制,也就是MVCC,每一行记录的要判断自己师傅对这个会话可见,因此对于count(*)请求来说,innoDB只好把数据一行行的读出判断,可见的行才能后用于累加,

当然mysql也是对count(*)是有进行优化的,我们知道我们的索引是一棵树,而主键索引叶子节点是数据,而普通索引叶子节点是主键索引,所以主键索引比普通索引的树大些,因此mysql优化器会拿到索引树小的,进行遍历计算,在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库优化的通用手段之一

此时你可能还依稀记得下面命令可以获取行的数量,但是据官方说明,这个命令返回的行数,是不准确的,只有达到40-50%,所以这个命令也不能直接使用

show table status

总结如下

  1. MyiSAM表虽然count(*)很快,但是不支持事物
  2. show table status命令虽然很快但是不准确
  3. innoDB直接count(*)扫描全表,性能不佳

我们发现好像都不行呀,什么破玩意,我们只能自己实现了,我们自己把行数记录下来

用缓存系统保存计数

对于更细跟频繁的库来说,我们可能就会想到使用缓存系统,比如redis,于是我们试试用redis服务来保存这个表的行数,这个表每插入一条数据,redis就加1,每次删除就减一,试想一下还有什么问题吗

缓存系统可能会发生数据丢失,由于redis不能永久的存储在内存中,因此我们可能会想到持久化存储起来,即使这样,万一redis异常重启了,有可能会发生数据丢失,比如数据插入一行数据,redis记录值加1,此时还没有持久化,此时redis宕机,因此数据库重启,就会发生数据丢失,当然可以把数据从数据库重新拿出来,在放到redis里面,毕竟重启不经常出现的.

但是,就算redis能正常,依然也会发生逻辑上不准确。

比如有个页面要显示近期操作的100条记录和总操作数,这页面的逻辑就是到redis获取总数,再到数据库获取100条记录,如下两种会发生数据不一致的情况

  1. 查询到100结果里面有最新插入的数据,而redis计数还没有加1
  2. 查询到100行结果里面没有最新插入的数据,而redis计数已经加1

不管上面那种时序去查询数据,最终的结果都会不准确,

使用数据库保存计数

我们可以使用在数据库新建一张表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(字段)

  1. 如果这个字段是定义为not null的话,一行行的从记录里面读取这个字段,判断不能为null,按行累加
  2. 如果这个子弹定义允许为null,那么执行的时候,判断到有可能是null,还要把字段取出来判断一下,不是null才累加

count(*)

并不会把所有字段全部取出来,而是专门做的优化,不取值,count(*)肯定不是null,按行累加。

所以我们可以总结,按照效率排序的话,count(字段)<count(主键id)<count(1)=count(*),建议尽量使用count(*)

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-10-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql 基础篇

    存储引擎是mysql的特性之一,使用者可以根据自己的业务场景选择自己适合的存储引擎,是不是要支持事物,如何选择存储,如何选择索引数据,当然你也可以定制自己的存储...

    小土豆Yuki
  • 面试Spring IOC

    所谓的IoC容器就是指的Spring中Bean工厂里面的Map存储结构(存储了Bean的实例)

    小土豆Yuki
  • 并发编程问题为什么都很诡异

    并发编程对于很多人说都是比较难的,总是出现一些莫名其妙的bug,让我们很是苦恼,那么他到底是难在哪里呢,今天就带大家看看引起并发bug的根源

    小土豆Yuki
  • count(1)、count(*) 与 count (列名) 的执行区别

    当表的数据量大些时,对表作分析之后,使用count(1)还要比使用count(*)用时多了!

    程序员小强
  • 你还在认为 count(1) 比 count(*) 效率高?

    来源| blog.csdn.net/iFuMI/article/details/77920767

    Python进击者
  • 你是一直认为 count(1) 比 count(*) 效率高么?

    有 Where 条件的 count,会根据扫码结果count 一下所有的行数,其性能更依赖于你的 Where 条件,所以文章我们仅针对没有 Where 的情况进...

    Fayson
  • IRscope代码拆解一

    readLines()函数读入文本文件,结果好像是一个向量,文件中的每行是向量中的一个元素。

    用户7010445
  • 你是一直认为 count(1) 比 count(*) 效率高么?

    MySQL count(1) 真的比 count(*) 快么? 反正同事们都是这么说的,我也姑且觉得对吧,那么没有自己研究一下究竟?如果我告诉你他们一样,你信么...

    java思维导图
  • 你是一直认为 count(1) 比 count(*) 效率高么?

    MySQL count(1) 真的比 count(*) 快么? 反正同事们都是这么说的,我也姑且觉得对吧,那么没有自己研究一下究竟?如果我告诉你他们一样,你信么...

    谭庆波
  • count(1) 比 count(*) 效率高么?

    MySQL count(1) 真的比 count(*) 快么? 反正同事们都是这么说的,我也姑且觉得对吧,那么没有自己研究一下究竟?如果我告诉你他们一样,你信么...

    JAVA葵花宝典

扫码关注云+社区

领取腾讯云代金券