使用场景:
看到标题是不是有人觉得,哎,这mysql中的查询,count(*)肯定效率是不如count(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;
可以看到,表中只有一个主键索引,很简单,然后我们通过一些方式来进行数据的添加。
MySQL插入数据的基本语法为:
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
我们先进行简单的数据添加
INSERT INTO user(username, address, password) VALUES ('test_1', '100000000000000001', '1'),('test_2', '100000000000000002', '2');
刷新一下可以看到,数据库中有简单的两条数据
接下来我们使用查询插入语句,将查询后的语句插入为新的数据
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...) SELECT 字段1, 字段2, ... FROM <表名>
先查询再插入
INSERT INTO user(username, address, password) SELECT username,address,password FROM student
实际上,执行查询插入语句后,数据记录条数会呈指数增长。那么多执行几次查询插入语句,就会得到想要的百万数据了。
我们来用两种方式统计一下表中的记录数,如下:
可以看到,两条SQL的执行效率并没有相差,都是0.8秒。
再来看其他两个查询比较
id是主键,username和address是普通字段。可以明显的看出来使用id查询是占一定优势的,四百多万条数据差别就达到了两秒多。想一下如果是千万级别的数据,那差别是不是更大了。
场景实现:
对于原因的话,我们可以先使用explain关键字来查看一下查询语句是否使用索引或者是使用了什么索引。
然后对于explain中的不同项,我们可以来解释一下:
- type:前两个的type是index,表示为全索引扫描,就是把整个索引全部过一遍(注意:索引不是全部表的字段);后两个的 type 值为 all,表示全表扫描,即不会使用索引。
- key :这个表示 MySQL 决定采用哪个索引来优化对该表的访问,PRIMARY 表示利用主键索引,NULL 表示不用索引。
- key_len:这个表示 MySQL 使用的键长度,因为我们的主键类型是 INT 且非空,所以值为 4。
- Extra:这个中的 Using index 表示优化器只需要通过访问索引就可以获取到需要的数据(不需要回表)。
仅仅通过这些是不够的,我么们还需要解释一下这个其中的执行过程。
原因分析:
我们在学习mysql的时候,学习到count函数是归在聚合函数的那一类,就是和sum,max等是同一类的,这就说明他也是一个聚合函数。
聚合函数要对返回的结果继续宁一行行的判断。
对于 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() 就知道你是想统计总记录数,就会去找到一个最小的索引树去遍历,然后统计记录数。
因为主键索引(聚集索引)的叶子节点是数据,而普通索引的叶子节点则是主键值,所以普通索引的索引树要小一些。然而在上文的案例中,我们只有主键索引,所以最终使用的就是主键索引。