count(*),count(1),count(filed)区别
- 表结构
tb_user2 CREATE TABLE `tb_user2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`created_on` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户测试2'
- 记录数据
+----+------+------+------------+
| id | name | age | created_on |
+----+------+------+------------+
| 1 | a | 10 | NULL |
| 2 | b | NULL | NULL |
| 3 | c | NULL | NULL |
+----+------+------+------------+
count(*)与count(n)的区别
select count(*),count(1),count(2) from tb_user2;
+----------+----------+----------+
| count(*) | count(1) | count(2) |
+----------+----------+----------+
| 3 | 3 | 3 |
+----------+----------+----------+
explain select count(*),count(1),count(2) from tb_user2;
+------------+-------+---------------+---------+---------+-
| table | partitions | type | possible_keys | key |
+----+-------------+----------+------------+-------+-------
| tb_user2 | NULL | index | NULL | PRIMARY |
+----+-------------+----------+------------+-------+------
- 结论
表中只有一个主键,则采用主键来统计行数,count(*)和count(n)没有什么区别
给tb_user2添加二级索引在次统计
alter table tb_user2 add index idx_age (age);
explain select count(*),count(1),count(2) from tb_user2;
+------------+-------+---------------+---------+---------+-
| table | partitions | type | possible_keys | key |
+----+-------------+----------+------------+-------+-------
| tb_user2 | NULL | index | NULL | idx_age |
+----+-------------+----------+------------+-------+------
- 结论
如果表中有二级索引,则采用占用空间最小的二级索引, 因为二级索引比主键索引(聚簇索引))占用的空间要小。
count(field)
select count(age) from tb_user2;
+------------+
| count(age) |
+------------+
| 1 |
+------------+
- 结论
count(field)则统计非空字段的数据记录数
结论
- count(*)和count(1),count(n)
1、都是统计数据的行数
2、如果表中只有一个主键索引,没有二级索引,那么则通过主键索引来统计行数,如果表中有二级索引,则采用占用空间最小的二级索引,来统计行数
- count(filed)
统计非空字段的数据行数