count(*)为什么很慢?原因分析

青灯夜游
青灯夜游转载
2023-01-05 21:21:081159浏览

count(*)为什么很慢?下面本篇文章就来给大家分析一下原因,并聊聊count(*)的执行过程,希望对大家有所帮助!

本没想着写这篇文章的,因为我觉得这个东西大多数有经验的开发遇到过,肯定也了解过相关的原因,但最近我看到有几个关注的技术公众号在推送相关的文章。实在令我吃惊!

先上公众号文章的结论:

  • count(*) :它会获取所有行的数据,不做任何处理,行数加1。
  • count(1):它会获取所有行的数据,每行固定值1,也是行数加1。
  • count(id):id代表主键,它需要从所有行的数据中解析出id字段,其中id肯定都不为NULL,行数加1。
  • count(普通索引列):它需要从所有行的数据中解析出普通索引列,然后判断是否为NULL,如果不是NULL,则行数+1。
  • count(未加索引列):它会全表扫描获取所有数据,解析中未加索引列,然后判断是否为NULL,如果不是NULL,则行数+1。

结论:count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)

我也不想卖关子了,以上结论纯属放屁。根本就是个人yy出来的东西,甚至不愿意去验证一下,哪怕看一眼执行计划,也得不出这么离谱的结论。

我不敢相信这是一篇被多个技术公众号转载的文章!

以下所有的内容均是基于,mysql 5.7 + InnoDB引擎, 进行的分析。

拓展:

MyISAM 如果没有查询条件,只是简单的统计表中数据总数,将会返回的超快,因为service层中获取到表信息中的总行数是准确的,而InnoDB只是一个估值。

实例

废话不多说,先看一个例子。

以下是一张表数据量有100w,表中字段相对较短,整体数据量不算大。

CREATE TABLE `hospital_statistics_data` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '外键',
  `hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '医院编码',
  `biz_type` tinyint NOT NULL COMMENT '1服务流程  2管理效果',
  `item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目编码',
  `item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目名称',
  `item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核结果',
  `is_deleted` tinyint DEFAULT NULL COMMENT '是否删除 0否 1是',
  `gmt_created` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT 'gmt_modified',
  `gmt_deleted` datetime(3) DEFAULT '9999-12-31 23:59:59.000' COMMENT '删除时间',
  PRIMARY KEY (`pk_id`)
) DEFAULT CHARSET=utf8mb4  COMMENT='医院统计数据';

此表初始状态只有一个聚簇索引

以下分不同索引情况,看一下COUNT(*)的执行计划。

1)在只有一个聚簇索引的情况下看一下执行计划。

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果:

关于执行计划的各个参数的含义,不在本文的讨论范围内,可自行了解。

这里只关注以下几个属性。

  • type: 这里显示index,说明使用了索引。

  • key:PRIMARY使用了主键索引。

  • key_len: 索引长度8字节。

这里有很关键的一点:count(*)也会走索引,在当前情况下使用了聚簇索引。

好,再往下看。

2)存在一个非聚簇索引(二级索引)

给表添加一个hospital_code索引。

alter table hospital_statistics_data add index idx_hospital_code(hospital_code)

此时表中存在2个索引,主键 hospital_code

同样的,再执行一下:

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果:

同样的,看一下 type、key和key_len三个字段。

是不是觉得有点“神奇”。

为何索引变成刚添加的idx_hospital_code了。

先别急着想结论,再看下面一种情况。

3)存在两个非聚簇索引(二级索引)

在上面的基础上,再添加一个二级索引。

alter table hospital_statistics_data add index idx_biz_type(biz_type)

此时表中存在3个索引,主键 、hospital_code 和 biz_type。

同样的,执行一下:

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果:

是不是更困惑了,索引又..又又...变了.

变成新添加的idx_biz_type。

先不说为何会产生以上的变化,继续往下分析。

在以上3个索引的基础上,分别看一下,count(1)count(id)count(index)count(无索引)

这4种情况,与count(*)的执行计划有何区别。

  • count(1)

  • count(id) 对于样例表来说是,主键是pk_id

image.png

  • count(index)

这里选取biz_type索引字段。

  • count(无索引)

小结:

  • count(index) 会使用当前index指定的索引。

  • count(无索引) 是全表扫描,未走索引。

  • count(1) , count(*), count(id) 一样都会选择idx_biz_type索引

看到这,你还觉得那些千篇一律的公众号文章的结论正确吗?

必要知识点

  • mysql 分为service层引擎层

  • 所有的sql在执行前会经过service层的优化,优化分为很多类型,简单的来说可分为成本规则

  • 执行计划所反映的是service层经过sql优化后,可能的执行过程。并非绝对(免得有些人说我只看执行计划过于片面)。绝大多数情况执行计划是可信的

  • 索引类型分为聚簇索引非聚簇索引(二级索引)。其中数据都是挂在聚簇索引上的,非聚簇索引上只是记录的主键id。

  • 抛开数据内存,只谈数据量,都是扯淡。什么500w就是极限,什么2个表以上的join都需要优化了,什么is null不会走索引等,纯纯的放屁。

  • 相信一点,编写mysql代码的人比,看此文章的大部分人都要优秀。他们会尽可能在执行前,对我这样菜逼写的乱七八糟的sql进行优化。

原因分析

其实原因非常非常简单,上面也说了,service层会基于成本进行优化

并且,正常情况下,非聚簇索引所占有的内存要远远小于聚簇索引。所以问题来了,如果你是mysql的开发人员,你在执行count(*)查询的时候会使用那个索引?

我相信正常人都会使用非聚簇索引

那如果存在2个甚至多个非聚簇索引又该如何选择呢?

那肯定选择最短的,占用内存最小的一个呀,在回头看看上面的实例,还迷惑吗。

同样都是非聚簇索引。idx_hospital_codelen146字节;而idx_biz_typelen只有1。那还要选吗?

那为何count(*)走了索引,却还是很慢呢?

这里要明确一点,索引只是提升效率的一种方式,但不能完全的解决效率问题。count(*)有一个明显的缺陷,就是它要计算总数,那就意味着要遍历所有符合条件的数据,相当于一个计数器,在数据量足够大的情况下,即使使用非聚簇索引也无法优化太多。

官方文档:

InnoDBhandlesSELECT COUNT(*)andSELECT COUNT(1)operations in the same way. There is no performance difference.

简单的来说就是,InnoDB下 count(*) 等价于 count(1)

既然会自动走索引,那么上面那个所谓的速度排序还觉得对吗? count(*)的性能跟数据量有很大的关系,此外最好有一个字段长度较短的二级索引。

拓展:

另外,多说一下,关于网上说的那些索引失效的情况,大多都是片面的,我这里只说一点。量变才能引起质变,索引的失效取决于你圈定数据的范围,若你圈定的数据量占整体数据量的比例过高,则会放弃使用索引,反之则会优先使用索引。但是此规则并不是完美的,有时候可能与你预期的不同,也可以通过一些技巧强制使用索引,但这种方式少用。

举个栗子:

通过上面这个表hospital_statistics_data,我进行了如下查询:

select * from hospital_statistics_data where hospital_code is not null;

此时这个sql会使用到hospital_code的索引吗?

这里也不卖关子了,若hospital_code只有很少一部分数据是null值,那么将不会走索引,反之则走索引。

原因就2个字:回表

好比去买砂糖橘,如果你只买几斤,那么你随便挑筐里面好的就行。但是如果你要买一筐,我相信老板不会让你在里面一个个挑,而是一次给你一整筐,当然大家都不傻,都知道筐里里面肯定有那么几个坏果子。但是这样效率最高,而且对老板来说损失更小。

执行过程

摘抄自《从根上理解mysql》。我强烈推荐没有系统学过mysql的,看看这本书。

1.首先在server层维护一个count变量

2.server层向InnoDB引擎要第一条记录

3.InnoDB找到第一条二级索引记录,并返回给server层(注意:由于此时只是统计记录数量,所以并不需要回表)

4.由于COUNT函数的参数是*,MySQL会将*当作常数0处理。由于0并不是NULL,server层给count变量加1。

5.server层向InnoDB要下一条记录。

6.InnoDB通过二级索引记录的next_record属性找到下一条二级索引记录,并返回给server层。

7.server层继续给count变量加1。

8.重复上述过程,直到InnoDB向server层返回没记录可查的消息。

9.server层将最终的count变量的值发送到客户端。

总结

写完后还是心中挺郁闷的,现在能从公众号获取到的好文章越来越少了,现在已经是知识付费的时代了。

挺怀念刚工作的时候,那时候每天上午都花点时间看看公众号文章,现在全都是广告。哎!

不过也正常,谁也不能一直为爱发电。

学习还是建议多看看书籍,一般能成书的都不会太差。现在晚上能搜到的都是千篇一律的文章,对错不知。网上

【相关推荐:mysql视频教程

以上就是count(*)为什么很慢?原因分析的详细内容,更多请关注php中文网其它相关文章!

声明:本文转载于:掘金社区,如有侵犯,请联系admin@php.cn删除
PHP中文网
程序员·梦开始的地方
下载APP