MySQL COUNT(*) 用主键索引为何效率很低？

Question

文件表五百多万行数据，起初没有索引，执行 SELECT COUNT(*) FROM file; 速度极慢，大概二十几秒。
后来为 filename 和 size 添加了普通索引，SELECT COUNT(*) FROM file; 查询只需要不到1秒，使用 idx_size 索引。
又为 id 添加了唯一索引，查询时间依旧不到1s，索引改用 unq_id

SELECT COUNT(*) FROM file; 使用 PRIMARY 索引效率为啥这么低？那么以后需要 COUNT(*) 的表是否都为主键添加唯一索引会比较好？

---

文件表结构
Field	Type	Null	Key	Extra
id	int(11)	NO	PRI	auto_increment
filename	varchar(255)	NO
size	bigint(20) unsigned	NO	MUL

---

• 没有建立任何索引时，查询时间是23秒，使用PRIMARY索引：

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----------+
| COUNT(*) |
+----------+
|  5416697 |
+----------+
1 row in set (23.39 sec)

mysql> explain SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | pan_file | NULL       | index | NULL          | PRIMARY | 4       | NULL | 4806466 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.07 sec)

---

• 给 filename 和 size 添加了普通索引后，查询时间降为0.95s，使用 idx_size 索引

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----------+
| COUNT(*) |
+----------+
|  5416697 |
+----------+
1 row in set (0.95 sec)

mysql> explain SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----+-------------+----------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | pan_file | NULL       | index | NULL          | idx_size | 8       | NULL | 4806466 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
1 row in set (0.06 sec)

---

• 又为 id 添加了唯一索引，查询速度为 0.90s，索引改用 unq_id

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----------+
| COUNT(*) |
+----------+
|  5416697 |
+----------+
1 row in set (0.90 sec)

mysql> explain SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----+-------------+----------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | pan_file | NULL       | index | NULL          | unq_id | 4       | NULL | 4806466 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+
1 row in set (0.06 sec)

Answer 1

因为主键索引树的叶子节点是数据，二级索引树的叶子节点是主键值，所以二级索引树比主键索引树小很多。对于 count(*) 操作，优化器会找到最小的那棵树来遍历，所以当你有建立二级索引的时候，速度自然就快了