【259期】揭秘 MySQL 中 count 是怎样执行的？

Java精选 2022-08-09

收录于合集

#面试题系列专栏 349 个

#面试官问 405 个

#MySQL 84 个

点击上方“Java精选”，选择“设为星标”

别问别人为什么，多问自己凭什么！

下方有惊喜，留言必回，有问必答！

每一天进步一点点，是成功的开始...

文章目录

1. 前言

相信在此之前，很多人都只是记忆，没去理解，只知道count(*)、count(1)包括了所有行，在统计结果的时候，不会忽略列值为NULL，count(列名)只统计列名那一列，在统计结果的时候，会忽略列值为NULL的记录。

下面就从原理上给大家分析一下。

2. 建表

和前面一样，用的同一个表，表中有将近10W条数据

CREATE TABLE demo_info(
    id INT NOT NULL auto_increment,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY uk_key2 (key2),
    KEY  idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
)ENGINE = INNODB CHARSET=utf8mb4;

3. count是怎么样执行的？

经常会看到这样的例子：

当你需要统计表中有多少数据的时候，会经常使用如下语句

SELECT COUNT(*) FROM demo_info;

由于聚集索引和非聚集索引中的记录是一一对应的，而非聚集索引记录中包含的列（索引列+主键id）是少于聚集索引（所有列）记录的，所以同样数量的非聚集索引记录比聚集索引记录占用更少的存储空间。

如果我们使用非聚集索引执行上述查询，即统计一下非聚集索引uk_key2中共有多少条记录，是比直接统计聚集索引中的记录数节省很多I/O成本。所以优化器会决定使用非聚集索引uk_key2执行上述查询。

注意：这里已经验证过了，uk_key2比其他索引成本更低。

分析一下执行计划

在执行上述查询时，server层会维护一个名叫count的变量，然后：

server层向InnoDB要第一条记录。
InnoDB找到uk_key2的第一条二级索引记录，并返回给server层（注意：由于此时只是统计记录数量，所以并不需要回表）。
由于count函数的参数是*，MySQL会将*当作常数0处理。由于0并不是NULL，server层给count变量加1。
server层向InnoDB要下一条记录。
InnoDB通过二级索引记录的next_record属性找到下一条二级索引记录，并返回给server层。
server层继续给count变量加1。
重复上述过程，直到InnoDB向server层返回没记录可查的消息。
server层将最终的count变量的值发送到客户端。

4. count(1)，count(id)，count(非索引列)，count(二级索引列)的分析

来看看count(1)

SELECT COUNT(1) FROM demo_info;

执行计划和count(*)一样

对于count(*)、count(1)或者任意的count(常数)来说，读取哪个索引的记录其实并不重要，因为server层只关心存储引擎是否读到了记录，而并不需要从记录中提取指定的字段来判断是否为NULL。所以优化器会使用占用存储空间最小的那个索引来执行查询。

再看一下count(id)：

explain SELECT COUNT(id) FROM demo_info;

对于count(id)来说，由于id是主键，不论是聚集索引记录，还是任意一个二级索引记录中都会包含主键字段，所以其实读取任意一个索引中的记录都可以获取到id字段，此时优化器也会选择占用存储空间最小的那个索引来执行查询。

再看一下count(非索引列)

explain select count(common_field) from demo_info

对于count(非索引列)来说，优化器选择全表扫描，说明只能在聚集索引的叶子结点顺序扫描。

请确认你理解了全表扫描，它是顺序扫描聚集索引的所有叶子结点并判断。另外，更多关于mysql面试题资料，公众号Java精选，回复java面试，获取最新面试题资料，支持在线随时随地刷题。

而对于其他二级索引列，count(二级索引列)，优化器只能选择包含我们指定的列的索引去执行查询，只能去指定非聚集索引的B+树扫描，可能导致优化器选择的索引扫描代价并不是最小。

综上所述

对于count(*)、count(常数)、count(主键)形式的count函数来说，优化器可以选择扫描成本最小的索引执行查询，从而提升效率，它们的执行过程是一样的，只不过在判断表达式是否为NULL时选择不同的判断方式，这个判断为NULL的过程的代价可以忽略不计，所以我们可以认为count(*)、count(常数)、count(主键)所需要的代价是相同的。

而对于count(非索引列)来说，优化器选择全表扫描，说明只能在聚集索引的叶子结点顺序扫描。

count(二级索引列)只能选择包含我们指定的列的索引去执行查询，可能导致优化器选择的索引执行的代价并不是最小。

其实上述这些区别就是因为非聚集索引记录比聚集索引记录占用更少的存储空间，减少更多I/O成本，所以优化器才有了不同索引的选择，仅此而已。