MySQL之count探究

count()功能：

1、统计某个列的值的数量，在统计列值时要求列值是非空的（不统计NULL）即：在COUNT()的括号中指定了列或者列表达式，则统计的就是这个列或者表达式有值的结果数。

2、统计结果集的行数，当确认括号内的表达式值不可能为空时，实际上就是在统计行数，例如：当使用COUNT()的时候，这种情况下通配符并不像我们猜想的那样扩展成所有的列，实际上会忽略所有列而直接统计所有的行数

• 如果该表只有一个主键索引，没有任何二级索引的情况下，那么COUNT(*)和COUNT(1)都是通过通过主键索引来统计行数的
• 如果该表有二级索引，则COUNT(1)和COUNT(*)都会通过占用空间最小的字段的二级索引进行统计

count(主键)：

• innodb会遍历整张表，把每一行的id都取处理，返回给server层(8.0这里有所改进不是一行一行返回)，server层拿到id后判断是不可能为null的就按行累加；

count(1)：

• innodb会遍历整张表,但不取值。server层对返回的每一行，放进去一个数字“1”判断是不可能为null的就按行累加；

count(字段)：

• 如果这个“字段”定义为 not null 的话，一行行地从记录里面读出这个字段按行累加；
• 如果这个“字段”定义允许为 null，执行的时候判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(*) ：

• 并不会把全部字段取出来，而是专门做了优化，不取值，按行累加

综上：若要比较四者的不同，可简单给出如下结论：

count(字段)<count(主键)<count(1)≈count(*)

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count

执行计划

对不同执行方法执行完成重启mysqld- server排除缓存干扰

测试过程

-- 测试表
CREATE TABLE `test_count` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` char(50) NOT NULL,
  `col2` varchar(50) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
 
-- 录入数据
DELIMITER $$
DROP PROCEDURE IF EXISTS `bucket_insert`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `bucket_insert`()
BEGIN
  DECLARE i INT;
  SET i =0;
  SET AUTOCOMMIT=0; 
  WHILE i < 10000000 DO
  IF i MOD 2 = 0 THEN
  INSERT INTO test_count (col1,col2,col3) VALUES (MD5(RAND()*1000),NULL,RAND()*500);
  ELSE
    INSERT INTO test_count (col1,col2,col3) VALUES (MD5(RAND()*1000),MD5(RAND()*3000),RAND()*500);
  END IF;
  SET i = i + 1;
  END WHILE;
  COMMIT;
  END$$
DELIMITER ;
 
-- 执行计划，只有主键索引
 
root@[test]> explain select count(*) from test_count;
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_count | index | NULL          | PRIMARY | 4       | NULL | 9694550 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.02 sec)
 
root@[test]> explain select count(1) from test_count;
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_count | index | NULL          | PRIMARY | 4       | NULL | 9694550 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
 
root@[test]> explain select count(id) from test_count; 
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_count | index | NULL          | PRIMARY | 4       | NULL | 9694550 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
 
root@[test]> explain select count(col1) from test_count;   
+----+-------------+------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | test_count | ALL  | NULL          | NULL | NULL    | NULL | 9694550 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------+
 
-- 执行计划，存在二级索引,可以看出一下三种count都会选择索引length最短的索引扫描
root@[test]> explain select count(id) from test_count;    
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_count | index | NULL          | idx_col1 | 200     | NULL | 9694550 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
1 row in set (0.00 sec)
 
root@[test]> explain select count(*) from test_count; 
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_count | index | NULL          | idx_col1 | 200     | NULL | 9694550 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
1 row in set (0.00 sec)
 
root@[test]> explain select count(1) from test_count;
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_count | index | NULL          | idx_col1 | 200     | NULL | 9694550 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+