下次再有人跟你说“用 SELECT * 索引就废了”，你可以把这篇文章甩给他

SELECT * 是否就等于 索引失效？

在日常开发与面试中，经常会听到一句话：不要用 SELECT *，会导致索引失效。这句话流传极广，但也极度片面，甚至误导了很多开发者。

今天我们就以 SELECT * 是否必然导致索引失效 为切入点，系统梳理 MySQL 索引失效的常见场景、底层原因、优化方案，帮你真正从原理层面理解索引，而不是死记八股文。

一、先破误区：SELECT * 本身不会直接让索引失效

很多人一看到 SELECT * 就默认索引废了，这是典型的只知表象，不知原理。

核心结论

1. SELECT * ≠ 索引失效只要 WHERE 条件字段命中索引，MySQL 优化器依然会优先考虑走索引。
2. 真正让优化器放弃索引的本质原因是：回表成本过高。
3. 优化器的决策逻辑：
   • 索引扫描 + 大量回表（随机 IO）
   • 直接全表扫描（顺序 IO）
   • SELECT * 属于非覆盖索引查询，查到索引后必须回聚簇索引取整行数据。
   • 优化器会对比两种成本：
   • 当查询结果集占总数据量 20%~30% 左右时，优化器会直接选择全表扫描，因为顺序 IO 远快于大量随机 IO。

生产建议

虽然 SELECT * 不直接失效，但生产环境严禁无脑使用。 正确做法：

• 只查询业务需要的字段
• 尽量使用覆盖索引，让 EXPLAIN 的 Extra 出现：Using index 彻底避免回表，性能提升一个量级。

二、MySQL 索引失效 7 大高频场景

理解了 SELECT * 的本质，我们再来看真正会导致索引失效的场景，这也是面试与开发中最常踩的坑。

1. 违背最左前缀原则（联合索引第一大坑）

底层原理联合索引是按照字段从左到右依次排序的，跳过最左列，或在中间使用范围查询（> < between），会直接导致后面的列无法使用索引。

注意：MySQL 8.0.13 支持索引跳跃扫描（ISS），可以在缺失最左列时尝试走索引，但不应该依赖它，且 8.0.31 存在 Bug 可能丢数据。

失效示例索引：(sname, s_code, address)

-- 跳过最左列 sname
SELECT * FROM students WHERE s_code = 1;

-- 范围查询后，address 不再使用索引
WHERE sname='A' AND s_code>1 AND address='Shanghai';

优化方案

• 调整联合索引顺序
• 补齐最左前缀条件
• 不要用 ISS 兜底烂索引设计

2. 索引列上做计算、函数、类型转换

底层原理索引 B+Tree 存储的是字段原始值，是有序的。 一旦对索引列做计算、函数处理，值的有序性被破坏，MySQL 无法使用二分查找，只能全表扫描。

失效示例

-- 索引列上运算
WHERE height + 1 = 170;

-- 索引列使用函数
WHERE DATE(create_time) = '2022-01-01';

优化方案把计算挪到等号右边，用范围查询代替函数：

WHERE create_time 
BETWEEN '2022-01-01 00:00:00' 
AND '2022-01-01 23:59:59';

3. LIKE 以通配符开头（%xxx 或 %xxx%）

底层原理B+Tree 按字符从左到右排序，以 % 开头，MySQL 不知道从哪里开始匹配，只能全表扫描。

失效示例

WHERE sname LIKE '%Guide';
WHERE sname LIKE '%Guide%';

优化方案

• 能右模糊就右模糊：LIKE 'Guide%'
• 只查覆盖索引字段，降级为 index 扫描
• 大规模模糊搜索：上 ElasticSearch

4. OR 连接条件使用不当

底层原理

• OR 两边任意一个字段没有索引，直接全表扫描
• 即使都有索引，过滤数据量太大，索引合并成本太高，优化器依然会放弃

失效示例

WHERE sname = '学生1' OR address = '上海';

优化方案拆分为 UNION ALL / UNION，让每一段独立走索引。

5. IN / NOT IN 使用不当

两个典型问题：

1. IN 列表过长默认超过 200 个值，优化器从精确统计切换为估算，可能放弃索引。
2. NOT IN 常见失效需要遍历整棵 B+Tree 证明“不存在”，通常全表扫描。

优化建议

• 长列表：改用 JOIN 临时表
• 尽量用 NOT EXISTS 或 LEFT JOIN + IS NULL 替代 NOT IN

6. 隐式类型转换（最隐蔽的坑）

黄金规则

转换发生在索引列上 → 索引失效 转换发生在常量上 → 索引正常

字符串与数字比较时，MySQL 会把字符串转成数字。

开发中 80% 的“莫名其妙索引失效”，都来自这里。

7. ORDER BY 导致 Using filesort（不算失效但极慢）

严格说不是索引失效，但会产生极慢的文件排序。

出现 Using filesort 常见原因：

• 排序字段不在索引里
• WHERE 与 ORDER BY 用不同索引
• 排序需要回表

优化方案让同一个联合索引同时满足：

• WHERE 过滤
• ORDER BY 排序 实现覆盖索引 + 无额外排序。

三、必须掌握的面试要点

1. 基础知识点

• 主流关系型数据库：MySQL、Oracle、PostgreSQL、SQL Server、SQLite
• 共同核心：支持 ACID 事务
• SQL 用途：增删改查、视图、存储过程、数据分析
• 优化神器：EXPLAIN 执行计划 重点看：
  • type：索引访问效率
  • Extra：是否 Using index、Using filesort、Using temporary

2. 面试 & 开发建议

1. 不要死记结论，要理解原理B+Tree 有序性、回表成本、优化器成本模型，才是索引的灵魂。
2. 索引设计三原则
   • 遵循最左前缀
   • 尽量使用覆盖索引
   • 禁止在索引列上做计算、函数、隐式转换
3. 不要依赖新特性兜底索引跳跃扫描、索引合并等，只能锦上添花，不能弥补烂设计。

四、回到最初的问题

SELECT * 会不会导致索引失效？

• 不会直接失效。
• 真正危险的是：回表成本过高，让优化器主动放弃索引，选择全表扫描。
• 最佳实践：少用 SELECT *，多用覆盖索引。

真正决定索引是否失效的，从来不是 SELECT *，而是：你是否理解 B+Tree、是否遵守索引规则、是否控制了回表成本。

下次再有人跟你说“用 SELECT * 索引就废了”，你可以把这篇文章发给他。