MySQL中count是怎样执行的？———count(1)，count(id)，count(非索引列)，count(二级索引列)的分析

2023-05-06 20:37:11 浏览数 (1)

1. 前言

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

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

2. 建表

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

代码语言：javascript复制
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是怎么样执行的？

经常会看到这样的例子： 当你需要统计表中有多少数据的时候，会经常使用如下语句

代码语言：javascript复制
SELECT COUNT(*) FROM demo_info;

  由于聚集索引和非聚集索引中的记录是一一对应的，而非聚集索引记录中包含的列（索引列 主键id）是少于聚集索引（所有列）记录的，所以同样数量的非聚集索引记录比聚集索引记录占用更少的存储空间。如果我们使用非聚集索引执行上述查询，即统计一下非聚集索引uk_key2中共有多少条记录，是比直接统计聚集索引中的记录数节省很多I/O成本。所以优化器会决定使用非聚集索引uk_key2执行上述查询。

注意：这里已经验证过了，uk_key2比其他索引成本更低。 详情可见MySQL查询为什么选择使用这个索引？——基于MySQL 8.0.22索引成本计算

分析一下执行计划

在执行上述查询时，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)

代码语言：javascript复制
SELECT COUNT(1) FROM demo_info;

执行计划和count(*)一样

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

再看一下count(id)：

代码语言：javascript复制
explain SELECT COUNT(id) FROM demo_info;

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

再看一下count(非索引列)

代码语言：javascript复制
explain select count(common_field) from demo_info

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

请确认你理解了全表扫描，它是顺序扫描聚集索引的所有叶子结点并判断。

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

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

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

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

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

count mysql 索引 统计 优化

0 人点赞