count()功能：
1汞斧、統(tǒng)計某個列的值的數(shù)量滔驶，在統(tǒng)計列值時要求列值是非空的（不統(tǒng)計NULL）即：在COUNT()的括號中指定了列或者列表達(dá)式遇革，則統(tǒng)計的就是這個列或者表達(dá)式有值的結(jié)果數(shù)。
2揭糕、統(tǒng)計結(jié)果集的行數(shù)萝快，當(dāng)確認(rèn)括號內(nèi)的表達(dá)式值不可能為空時，實際上就是在統(tǒng)計行數(shù)著角，例如：當(dāng)使用COUNT()的時候揪漩，這種情況下通配符并不像我們猜想的那樣擴(kuò)展成所有的列，實際上會忽略所有列而直接統(tǒng)計所有的行數(shù)
- 如果該表只有一個主鍵索引吏口，沒有任何二級索引的情況下奄容，那么COUNT(*)和COUNT(1)都是通過通過主鍵索引來統(tǒng)計行數(shù)的
- 如果該表有二級索引，則COUNT(1)和COUNT(*)都會通過占用空間最小的字段的二級索引進(jìn)行統(tǒng)計
count(主鍵)：
- innodb會遍歷整張表产徊，把每一行的id都取處理昂勒，返回給server層(8.0這里有所改進(jìn)不是一行一行返回)，server層拿到id后判斷是不可能為null的就按行累加舟铜；
count(1)：
- innodb會遍歷整張表,但不取值戈盈。server層對返回的每一行，放進(jìn)去一個數(shù)字“1”判斷是不可能為null的就按行累加谆刨；
count(字段)：
- 如果這個“字段”定義為 not null 的話塘娶，一行行地從記錄里面讀出這個字段按行累加；
- 如果這個“字段”定義允許為 null痴荐，執(zhí)行的時候判斷到有可能是 null，還要把值取出來再判斷一下官册，不是 null 才累加生兆。
count(*) ：
- 并不會把全部字段取出來，而是專門做了優(yōu)化，不取值鸦难，按行累加
綜上：若要比較四者的不同根吁，可簡單給出如下結(jié)論：
count(字段)<count(主鍵)<count(1)≈count(*)
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count
執(zhí)行計劃
對不同執(zhí)行方法執(zhí)行完成重啟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
-- 錄入數(shù)據(jù)
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 ;
-- 執(zhí)行計劃，只有主鍵索引
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 |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------+
-- 執(zhí)行計劃合蔽，存在二級索引,可以看出一下三種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 |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+-------------+
