1.网上一些文章的总结

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

由此，最后count的性能从高到低是：

count(*) count(1) > count(id) > count(普通索引列) > count(未加索引列)

以上结论错误至极，根本没有得到验证。

下面我将基于MySQL 5.7 + InnoDB引擎进行总结分析。

2.分析

下面是一张数据量为100万的表，表中字段比较短，整体数据量不大。

CREATE TABLE `hospital_statistics_data` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
  `hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
  `biz_type` tinyint NOT NULL,
  `item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `is_deleted` tinyint DEFAULT NULL,
  PRIMARY KEY (`pk_id`)
) DEFAULT CHARSET=utf8mb4;

下面我会通过不同的索引情况来看 count(*) 的执行计划。

2.1 只有一个聚簇索引

EXPLAIN select COUNT(*) from hospital_statistics_data;

type: index
key: PRIMARY
key_len: 8

count(*) 会遍历索引，并使用聚集索引。

2.2 存在非聚集索引（二级索引）

hospital_code向表中添加索引。

alter table hospital_statistics_data add index idx_hospital_code(hospital_code)

此时表中有2个索引 primary key，hospital_code。

EXPLAIN select COUNT(*) from hospital_statistics_data;

type: index
key: idx_hospital_code
key_len: 146

索引变为刚刚添加的 idx_hospital_code 。

2.3 有两个非聚集索引（二级索引）

再添加一个二级索引。

alter table hospital_statistics_data add index idx_biz_type(biz_type);

此时表中有3个索引 primary key、hospital_code、biz_type。

EXPLAIN select COUNT(*) from hospital_statistics_data;

type: index
key: idx_biz_type
key_len: 1

现在索引变成是 biz_type，是不是很神奇。

2.4 基于以上三个索引，分别来看 count(1), count(id), count(index), count(no index)

这四个的执行计划和 count(*) 有什么区别？

count(1)

EXPLAIN select COUNT(1) from hospital_statistics_data;

type: index
key: idx_biz_type
key_len: 1

count(pk_id)

EXPLAIN select COUNT(pk_id) from hospital_statistics_data;

type: index
key: idx_biz_type
key_len: 1

count(index)

EXPLAIN select COUNT(biz_type) from hospital_statistics_data;

type: index
key: idx_biz_type
key_len: 1

count (no index)

EXPLAIN select COUNT(item_code) from hospital_statistics_data;

type: ALL
key: null
key_len: null

2.5 总结

• count(index) 将使用当前索引指定的索引。
• count(no index) 是没有索引的全表扫描。
• count(1), count(*), count(id)也会选择 idx_biz_type 索引。

三、知识点

MySQL分为服务层和引擎层。

所有的SQL在执行前都会经过服务层的优化。优化有很多种，可以简单分为成本和规则优化。

执行计划反映了SQL优化后服务层可能的执行过程。在大多数情况下，执行计划是可信的（不绝对，以防有人说我只看执行计划过于片面）。

索引类型分为聚集索引和非聚集索引（二级索引）。其中，数据挂在聚簇索引上，非聚簇索引只是记录的主键id。

抛开数据内存不谈，只谈数据量是扯淡。什么500万是极限，什么超过2个表需要优化join，什么是null就不会去索引等等，都是错误的。

原因分析

原因很简单。如上所述，服务层将根据成本进行优化。而且，一般情况下，非聚簇索引占用的内存要比聚簇索引小很多。

问题讨论

3.1 如果你是 MySQL 开发者，你会在执行 count(*) 查询时使用哪个索引？

我相信普通人使用非聚集索引。

3.2 如果有2个或多个非聚集索引如何选择？

那么一定要选择占用内存最小的。同样是非聚集索引，idx_hospital_code 长度为146字节，而 idx_biz_type 长度仅为1。

3.3 那为什么count(*)取了index之后还是很慢？

这里要明确一点，索引只是提高效率的一种方式，并不能完全解决效率问题。count(*)有一个明显的缺陷，就是需要计算总数，也就是遍历所有符合条件的数据，相当于一个计数器。当数据量足够大时，即使使用非聚集索引，也不能优化太多。

官方文档：

InnoDB 以相同的方式处理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作。没有性能差异。

简单的说，InnoDB下的 count(*) 相当于 count(1)。

3.4 既然会自动取索引，那上面所谓的快速排序还觉得对吗？

count(*) 的性能与数据量有很大关系。此外，二级索引的字段长度越短越好。

另外，网上提到的索引故障大多是片面的，这里只说一点。量变可以导致质变。索引失效取决于你划定数据的范围。

如果筛选的数据量占整体数据量的比例过高，就会放弃使用索引，否则，就会优先使用索引。但是这个规则并不完美，有时候可能和你预想的不一样。也可以使用一些技巧强制使用索引，但这种方法很少用到。

例如：

通过上表hospital_statistics_data，做如下查询：

select * from hospital_statistics_data where hospital_code is  not  null ;

这个SQL hospital_code 这个时候会用到索引吗？

如果 hospital_code只有一小部分数据是null，那么不会使用索引，否则会使用索引。

就像买橘子一样。如果只买2斤，挑篮子里好的就可以了。但是如果你要买一筐，相信老板不会让你一个一个挑，而是一次给你一整筐。当然，大家也不傻，谁都知道篮子里肯定有几个坏果子。但这样对老板来说效率最高，成本也最低。