测试mysql版本为8.0.23
Innodb
测试表结构和数据如下
mysql> desc innodb_test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from innodb_test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | NULL |
+----+------+
4 rows in set (0.00 sec)
COUNT(*) SQL执行计划如下(COUNT(1)和COUNT(id)情况一样 )
mysql> EXPLAIN SELECT COUNT(*) FROM innodb_test;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | innodb_test | NULL | index | NULL | PRIMARY | 4 | NULL | 4 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
但是COUNT(name) 执行计划如下，因为name没有索引，所以也不会用到索引
mysql> EXPLAIN SELECT COUNT(name) FROM innodb_test;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | innodb_test | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.00 sec)
并且COUNT(name) ,因为id=4的name为null，所以只会查出来三条数据。
mysql> SELECT COUNT(name) FROM innodb_test;
+-------------+
| COUNT(name) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
Myisam
测试表结构和数据如下(无任何索引)
mysql> desc myisam_test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from myisam_test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | NULL |
+----+------+
4 rows in set (0.00 sec)
COUNT(*) SQL执行计划如下(COUNT(1)和COUNT(id)情况一样 )，可以看到这个关键词 Select tables optimized away，因为myisam存储了表的行数
mysql> EXPLAIN SELECT COUNT(*) FROM myisam_test;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set (0.00 sec)
COUNT(name) 执行计划如下，因为没有索引，所以查询了全表，同理也只能查到三条数据
mysql> EXPLAIN SELECT COUNT(name) FROM myisam_test;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | myisam_test | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.00 sec)
