MySQL(七) 统计记录数

一.统计记录数的小例子

1.1 创建数据表

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;
1
2
3
4
5
6
7

1.2 存入几条数据

1.3 执行下面几条语句

SELECT COUNT(*) FROM USER;
SELECT COUNT(id) FROM USER;
SELECT COUNT(1) FROM USER;
SELECT COUNT(username) FROM USER;
SELECT COUNT(address) FROM USER;
1
2
3
4
5

执行上述五条语句，结果都是一样的，那我们来分析一下这四条语句的效率吧

1.4 使用explain 来研究SQL的执行效率

执行第一行语句
EXPLAIN SELECT COUNT(*) FROM USER;
查看这行sql的执行计划

继续执行完接下来的几个sql的执行计划,总的如下：

前三个统计方式的执行计划是一样的，后两个是一样的。

那为什么会有不同呢

type : 前三个的type值为index， 表示全索引扫描，就是把整个索引过一遍就行，注意的是过一遍指的只是索引并不是整个表；后两个的type值为all，表示全表扫描，即不会使用索引。
key ： 这个表示MySQL决定采用哪个索引来优化对该表的访问，PRIMARY表示利用主键索引，NULL表示不用索引。
Extra ：这个中的Using index 表示优化器只需要通过访问索引就可以过的到需要的数据。（并不需要回表）。
通过explain我们其实也能大概看出来前三种统计方式的执行效率要高，因为前三种用了索引去寻找数据，并不需要全表扫描。

1.5 原理分析

1.5.1 主键索引与普通索引与索引的关系。

回表：由于在主键索引中，叶子节点保存了每一行的数据。
而在普通索引中，叶子节点保存的是主键值，当我们使用普通索引去搜索数据的时候，先在叶子节点中找到主键，再拿着主键去主键索引中查找数据，相当于做了两次查找。

1.5.2 分析

count聚合函数：count函数是聚合函数（avg，sum等）， 那么我们就需要对返回的结果集进行一行行的判断。

SELECT COUNT(*) FROM USER;
SELECT COUNT(id) FROM USER;
SELECT COUNT(1) FROM USER;
SELECT COUNT(username) FROM USER;
SELECT COUNT(address) FROM USER;
1
2
3
4
5

1.5.2.1 SELECT COUNT(1) FROM USER;

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

1.5.2.2 select count(id) from user;

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

1.5.2.3 select count(username) from user;

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

1.5.2.4 select count(*) from user;

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

1.5.2.5主键索引和普通索引树的大小比较

因为主键索引（聚集索引）的叶子节点是数据，而
普通索引的叶子节点则是主键值，索引普通索引的索引树要小一些。

1.5.2.6 给username添加索引

现在，如果我修改上面的表，为 username 字段也添加索引，然后我们再来看 explain select count(*) from user; 的执行计划：

可以看到，此时使用的索引就是 username 索引了，和我们前面的分析结果是一致的。从上面的描述中我们就可以看出，第一个查询性能最高，第二个次之（因为需要读取 id 并返回），第三个最差（因为需要全表扫描），第四个的查询性能则接近第一个。