count(*)很慢的原因是什么

共计 4023 个字符，预计需要花费 11 分钟才能阅读完成。

这篇文章主要介绍“count(*) 很慢的原因是什么”的相关知识，丸趣 TV 小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“count(*) 很慢的原因是什么”文章能帮助大家解决问题。

以下所有的内容均是基于，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

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_code 的 len 是 146 字节；而 idx_biz_type 的 len 只有 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 个字：回表。

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

执行过程

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 变量的值发送到客户端。

关于“count(*) 很慢的原因是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注丸趣 TV 行业资讯频道，丸趣 TV 小编每天都会为大家更新不同的知识点。