MySQL 中 count(*) 和 count(1)

一张有 100W 条数据的表

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

 

 

====>

 

 

explain 分析

 

 

• type：前三个的 type 值为 index，表示全索引扫描，就是把整个索引过一遍就行（注意是索引不是整个表）；后两个的 type 值为 all，表示全表扫描，即不会使用索引。
• key：这个表示 MySQL 决定采用哪个索引来优化对该表的访问，PRIMARY 表示利用主键索引，NULL 表示不用索引。
• key_len：这个表示 MySQL 使用的键长度，因为我们的主键类型是 INT 且非空，所以值为 4。
• Extra：这个中的 Using index 表示优化器只需要通过访问索引就可以获取到需要的数据（不需要回表）。

 

 原理分析:

主键索引和普通索引的存储又有所不同:

在主键索引中，叶子结点保存了每一行的数据。而在普通索引中，叶子结点保存的是主键值，当我们使用普通索引去搜索数据的时候，先在叶子结点中找到主键，再拿着主键去主键索引中查找数据，相当于做了两次查找，这也就是我们平常所说的回表操作。

             对于 select count(1) from user; 这个查询来说，InnoDB 引擎会去找到一个最小的索引树去遍历（不一定是主键索引），但是不会读取数据，而是读到一个叶子节点，就返回 1，最后将结果累加。

            对于 select count(id) from user;  这个查询来说，InnoDB 引擎会遍历整个主键索引，然后读取 id 并返回，不过因为 id 是主键，就在 B+ 树的叶子节点上，所以这个过程不会涉及到随机 IO（并不需要回表等操作去数据页拿数据），性能也是 OK 的。

           对于 select count(username) from user;  这个查询来说，InnoDB 引擎会遍历整张表做全表扫描，读取每一行的 username 字段并返回，如果 username 在定义时候设置了 not null，那么直接统计 username 的个数；如果 username 在定义的时候没有设置 not null，那么就先判断一下 username 是否为空，然后再统计。

      select count(*) from user; ，这个 SQL 的特殊之处在于它被 MySQL 优化过，当 MySQL 看到 count(*) 就知道你是想统计总记录数，就会去找到一个最小的索引树去遍历，然后统计记录数。

 

=========>为主键索引（聚集索引）的叶子节点是数据，而普通索引的叶子节点则是主键值，所以普通索引的索引树要小一些。然而在上文的案例中，我们只有主键索引，所以最终使用的就是主键索引。

 

结论：

第一个查询性能最高，第二个次之（因为需要读取 id 并返回），第三个最差（因为需要全表扫描），第四个的查询性能则接近第一个。

 

《=========================================================================================》

聚簇索引和非聚簇索引

聚簇索引：找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。

非聚簇索引：索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

 

主键一定是聚簇索引，MySQL的InnoDB中一定有主键，即便研发人员不手动设置，则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的id来当作主键索引,其它普通索引需要区分SQL场景，当SQL查询的列就是索引本身时，我们称这种场景下该普通索引也可以叫做聚簇索引。

 

1. 聚簇索引（Clustered Index）

• 数据物理存储顺序：聚簇索引将数据按照主键顺序存储，数据行和索引存储在一起，物理上按顺序排列。

• 主键为默认聚簇索引：在InnoDB存储引擎中，主键默认就是聚簇索引。如果没有主键，则选择一个唯一的非空列；如果没有唯一的列，则MySQL会自动生成一个隐藏的行ID作为聚簇索引。

• 快速数据检索：聚簇索引在使用主键查询时非常高效，因为数据和索引在同一位置，不需要额外的查找步骤。

• 数据页分裂影响：由于数据按主键顺序存储，插入新数据时可能会触发数据页分裂，导致性能下降，尤其是在对主键频繁插入或更新的情况下。

• 每个表只能有一个聚簇索引：因为数据行只能按一种顺序存储，因此一个表只能有一个聚簇索引。

2. 非聚簇索引（Non-Clustered Index）

• 索引和数据分离：非聚簇索引将索引和数据存储在不同的物理位置。索引指向的是数据行的物理地址或主键值，而不是直接存储数据本身。

• 索引项包含指针：非聚簇索引的叶子节点存储的是指向数据行的指针（或主键），因此在使用非聚簇索引查询时，通常需要一次额外的回表操作来检索完整数据。

• 可以有多个非聚簇索引：一个表可以有多个非聚簇索引，这些索引用于非主键列上的查询。

• 适合频繁更新的列：因为数据和索引分离，非聚簇索引在更新时不会像聚簇索引那样频繁导致数据页分裂，适合用于非主键列的高频查询和更新。