MySQL查询优化器：SELECT id用索引，SELECT *为何不用？

我执行了以下SQL查询：

select * from `tracked_employments`
where `tracked_employments`.`file_id` = 10006000
and `tracked_employments`.`user_id` = 1003230
and `tracked_employments`.`can_be_sent` = 1
and `tracked_employments`.`type` = 'jobchange'
and `tracked_employments`.`file_type` = 'file'
order by `tracked_employments`.`id` asc
limit 1000
offset 2000;

同时表上存在一个联合索引 idx_file_user_can_type_file，包含列：file_id, user_id, can_be_sent, type, file_type。

通过EXPLAIN分析发现，该查询未使用上述索引；但将SELECT *替换为SELECT id时，查询会使用该索引。请问为何查询选择的列会影响索引的使用？

核心原因：覆盖索引与回表成本的权衡 #

1. 先明确索引的特性 #

你提到的联合索引属于InnoDB的二级索引，这类索引的叶子节点会自动存储主键id的值，但不会包含表中其他列的数据。

2. SELECT id使用索引的原因 #

当你仅查询id时，这个联合索引已经能提供所需的全部数据：

• 你的WHERE条件完全匹配索引的所有列，优化器可以通过索引快速定位到符合条件的行；
• 二级索引叶子节点自带的主键id就能满足查询需求，不需要再去聚簇索引（主键索引）中查询其他数据——这就是覆盖索引扫描，执行成本极低，所以优化器会优先选择使用该索引。

3. SELECT *不使用索引的原因 #

当你查询*时，需要获取表中所有列的数据，这会触发回表操作：

• 如果使用该二级索引，优化器需要先找到符合条件的行，拿到主键id后，再去聚簇索引中查询其他列的数据；
• 你的查询带有offset 2000 limit 1000，意味着需要先定位到3000条符合条件的行，再跳过前2000条取后1000条。回表3000次的IO开销，加上额外的数据处理成本，会远高于直接扫描聚簇索引：
  聚簇索引本身包含表的所有列，扫描时可以直接过滤WHERE条件，且数据天生按id有序排列，不需要额外执行排序操作，整体成本比“二级索引+回表”更低，因此优化器选择放弃使用该二级索引。

额外建议 #

如果想让SELECT *也使用该索引，可以考虑将其修改为覆盖索引（比如通过INCLUDE子句把需要的列加入索引），但这种方式会大幅增加索引体积，不推荐。更合理的做法是避免使用SELECT *，只查询实际需要的列，既减少IO开销，也更容易触发覆盖索引扫描。

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