MySQL执行count()计数为什么会这么慢

2,158 阅读5分钟

「这是我参与11月更文挑战的第21天,活动详情查看:2021最后一次更文挑战

在开发中,我们经常会做一些界面查询的功能,基本上基础数据的查询都会采用分页模式,会在每次查询的时候都查询出总的条数,根据查询的总条数,然后在计算出需要展示的页面总数,渲染到浏览器让用户能够分页查询数据。查询的语句一般都如下:

SELECT count(*) FROM table_name;

这样的查询语句,在系统最开始上线的时候数据量不是很多,这样查询的性能比较快,当数据里达到几百万条的数据,查询的时间可能会话费几秒剩下更久,这样的话一个查询页面就会出现数据很久才会出来影响用户体验。接下来彻底的了解下MySQL为什么查询总数会这么慢,有助于以后我们功能优化。

计数的实现方式

MySQL比较常用的引擎是MyISAM、InnoDB两种引擎,这两种引擎对于count(*)统计总数都有不同的实现方式:

  • MyISAM引擎:在数据增加或删除的时候,每次都会将表的总行数存在磁盘上,在每次执行count(*)查询总数的时候,会直接将总数返回,这样的话,很快就会把总数返回;
  • InnoDB引擎:在查询总数的时候,会去一行行的数条数进行累加,这样就导致每次查询都会去数一下总条数,导致查询总数耗费极多的时间;

为什么InnoDB不能够学MyISAM引擎一样,直接记录一个总数,每次查询总数的时候就直接拿出来返回就可以呢?

在InnoDB内部是记录了一个总条数,但是这个数据是个大概,不准确的,在需要知道准确数据的时候,InnoDB不会将这个数据返回给客户端,而是去现场数出总数,这样做的原因:

  • InnoDB支持事务,为了保证数据的隔离性,采用了MVCC多版本并发控制,在不同的会话中,根据rodo log日志,及每个会话的事务隔离级别需要判断当前需要能够可见那些数据,这样的话就需要将当前会话的可见记录统计计数,不同的会话级别查询出来的总数可能不一致。

InnoDB在计算总数的时候,是基于索引树是查找总数的,在构建的索引中,有写索引树比较小,只是单单的计算总数,InnoDB会选取一颗比较小的索引数来遍历,这样可减少扫描的数据量,提高查询的效率,又能够得到准确的总数值。

count(*)、count(1)、count(主键)、count(普通字段)效率

上面的几种求总数的写法,想必大家都用过,其中用的最多的是前面三种:count(*)、count(1)、 count(主键) 那么上面的四种写法到底执行效率怎么样呢?、

count()属于一个聚合函数,所以在计算总数的时候,会根据返回的结果集,一行行的判断,如果count的参数不是NULL,就会累加1,如果为NULL,则不累计。

  • count(字段) 与count(主键):实现的逻辑是一样的,都会遍历整张表,把每一行的主键或字段取出来,然后给server层,server拿到数据后,判断是否为空:不为空,就累加1,如果字段被允许定义为NULL,就需要把值取出来再判断一下,不是NULL才累加。因为字段可能存在NULL,就需要多步判断,由此可以得出查询效率:count(主键) > count(字段)

  • count(1):跟上面的遍历逻辑一样,不会取字段值,直接返回1数字,server对于返回的每一行都为1,判断是不可能为空,直接按行累加,相比count(id), 在查询的时候没有取具体的字段值,这会减少一部分时间,所以count(1)的查询效率会高于count(主键)

  • count():这种场景对于MySQL是特殊处理的场景,对于count()这种语法,在server层的时候,不会去判断是否为空,直接累加计数,这样的话查询速度就会略高于count(1)

经过上面的分析,可以得出这样的查询效果:count(*) ≈ count(1) > count(主键) > count(字段)

如何避免count计数

在我的工作项目中,我们整个项目组对于页面的查询都没有计算总数,而是给用户查询的时候,默认只查询第一页,不会告知用户当前条件总数,如果查询返回的条数小于当前页码个数,则认为是最后一页,用户不可以点击下一次,这样的话,用户只能够一页一页的点击下一页,而且点击的下一页最多只能够查询10页,如果用户点击的页数越多,分页越生,查询效率也会很低下;

在做这个方案之前,我们对用户的查询页码数有过分页点击调查统计,用户点击到后面10页的可能性极低,如果用户想看更多数据就走异步导出功能,这样的话就会加快用户界面查询的速度,也会减少服务器的查询压力,提高数据库的性能。