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

文件表五百多万行数据，起初没有索引，执行 SELECT COUNT(*) FROM file; 速度极慢，大概二十几秒。

后来为 filename 和 size 添加了普通索引，SELECT COUNT(*) FROM file; 查询只需要不到1秒，使用 idx_size 索引。

又为 id 添加了唯一索引，查询时间依旧不到1s，索引改用 unq_id

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

idint(11)NOPRIauto_increment

filenamevarchar(255)NO

sizebigint(20) unsignedNOMUL没有建立任何索引时，查询时间是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_idmysql> 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)