为何MySQL中SELECT COUNT(1)用索引，SELECT *却不用？

我有如下查询语句：

SELECT COUNT(1)
FROM nhd
WHERE gnis_id IN (01372336);

表nhd上存在INDEX gnis_id (gnis_id)索引，这个查询确实会使用该索引。但当我把COUNT(1)替换为*后，通过EXPLAIN发现索引未被使用。

为什么会出现这种情况？为什么返回列的变化会影响MySQL对WHERE子句所用索引的选择？

使用SELECT *时的EXPLAIN输出： #

id: 1
select_type: SIMPLE
table: nhd
partitions: NULL
type: ALL
possible_keys: gnis_id
key: NULL
key_len: NULL
ref: NULL
rows: 2752840
filtered: 10.00
Extra: Using where

关键分析 #

1. COUNT(1)用索引的原因

当执行SELECT COUNT(1)时，MySQL只需要统计符合条件的行数，不需要返回任何实际列数据。此时gnis_id索引属于覆盖索引——仅通过索引就能完成统计，完全不需要访问主表的聚簇索引数据，成本极低，所以优化器会优先选择这个索引。

2. SELECT *不用索引的原因

当查询SELECT *时，需要返回表中所有列的数据。但gnis_id索引只包含gnis_id这一个字段，MySQL如果用这个索引找到符合条件的行后，必须再去主表（聚簇索引）中读取其他列的数据，这个过程叫回表。

从EXPLAIN的结果来看，预估符合条件的行数超过27万（rows: 2752840 × filtered: 10.00%），这么多行的回表操作会产生大量IO开销，成本远高于直接全表扫描。优化器经过成本计算后，选择放弃使用gnis_id索引，转而进行全表扫描来避免高昂的回表成本。

额外建议 #

• 如果想强制在SELECT *时使用gnis_id索引，可以用FORCE INDEX(gnis_id)语句，但实际执行效率可能不如全表扫描，建议测试后再决定。
• 如果这类查询很频繁，可以考虑创建包含所有需要返回列的联合索引（即覆盖索引），但会占用更多存储空间，需要根据业务场景权衡。

内容的提问来源于stack exchange，提问作者neubert