MySQL实战 | 14 为什么count(*)越来越慢?

select count(*) 应该是一个比较常用的语句,用来统计记录行数。

但是,慢慢地你会发现,这个语句越来越慢了,为什么呢?

count(*) 的实现方式

首先,我们来看下它的实现方式。

MySQL 中,不同的存储引擎,count(*) 的实现方式是不同的。

1、MyISAM 引擎,比较简单粗暴,直接将表的总行数存储在磁盘上,因此效率很高;

2、InnoDB 引擎中,执行时,需要一行行的把数据查出来,然后累加;

为啥 MyISAM 就可以这样做呢?因为它不支持事务啊,不用担心数据不一致的问题。

而 InnoDB 就不一样了。

由于 MVCC 的存在,InnoDB 在当前执行环境下,对一共有多少数据行是不确定的,比如:

假设,表 t 中有 1000 条数据,有下面三个用户并行的会话:

1、A 启动事务,查询表的总行数;
2、C 直接插入一条数据,然后查询总行数;
3、B 启动事务,插入一条数据,然后查询总行数;
4、C 查询总行数;

注意,上面启动的事务都没有提交。

A、B、C 查询的结果都不相同。

B 读到的是 1002,是因为可重复读隔离级别的存在,而 C 未开启事务,因此无法看到别的事务的更新;

综上,InnoDB 引擎中,在每一个会话中,都需要逐行读取数据,然后计数返回总行数。

InnoDB 对 count(*) 的优化

InnoDB 中,主键索引存储的是数据,辅助索引存储的只是主键值。

因此,辅助索引比主键索引小得多,轻量得多。

这种情况下,InnoDB 在执行 count(*) 时,就会判断使用哪个索引,会选择最小的树来进行遍历。

在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一。

小结

1、由于 MyISAM 引擎不需要支持事务,因此可以快速返回 count(*)
2、show table status 命令虽然返回很快,但是不准确;
3、InnoDB 执行 count(*) 时会遍历全表,因此性能较差;

count(*)、count(1)、count(主键)、count(字段)的区别

以下,基于 InnoDB。

含义区别

count() 是一个聚合函数,对于返回的结果集,会逐行判断,若返回的不是 NULL,就会加 1,否则不加。

因此,count(*)count(主键 id)count(1) 都表示返回满足条件的结果集的总行数;而 count(字段),则表示返回满足条件的数据行里面,参数“字段”不为 NULL 的总个数。

性能区别

分析性能,考虑以下几个原则:

1、server 层要什么就会返回什么;
2、InnoDB 只返回必要的值;
3、优化器只优化了 count(*)

对于 count(主键id),InnoDB 会遍历全表,取每行的主键 id,返回给 server 层,server 层拿到数据后,进行判断累加。

对于 count(1),InnoDB 仍遍历全表,但是不取值,server 层对返回的每一行数据新增一个 1,然后进行判断累加;

因此,count(1) 要更快些,因为无需取值。从引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。

对于 count(字段)

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

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

结论:按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以我建议你,尽量使用 count(*)

hoxis wechat
提供 CSDN 资料免费下载服务,欢迎来撩~
赞赏一杯咖啡
0%