直接回答：不，使用索引不一定有效。 很多情况下，即使你在列上创建了索引，MySQL的查询优化器也可能选择不使用它，或者使用了但效果不佳。
这背后的核心原因是：MySQL的查询优化器会评估使用索引的成本。如果它认为全表扫描（Full Table Scan）比“走索引 -> 回表”的成本更低，它就会果断放弃索引。
一、 索引失效或低效的常见场景
以下是一些最常见的导致索引失效或效果不佳的情况，理解它们至关重要：
| 场景 | 错误示例 (可能不走索引) | 正确/优化写法 (会走索引) | 原因说明 |
|---|---|---|---|
| 1. 对索引列使用函数或运算 | WHERE YEAR(create_time) = 2023; | WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; | 函数或运算会破坏索引的B-Tree有序结构，导致无法快速定位。 |
2. 使用 LIKE 且以通配符 % 开头 | WHERE name LIKE '%ming'; | WHERE name LIKE 'ming%'; | B-Tree索引依赖于从左到右的匹配。左模糊查询无法利用索引的有序性。 |
| 3. 隐式类型转换 | WHERE phone = 13800001111; (phone是 VARCHAR类型) | WHERE phone = '13800001111'; | MySQL会将所有行的phone字段转换成数字再比较，相当于对列使用了函数CAST(phone AS SIGNED)，导致索引失效。 |
4. 使用 OR 连接条件 | WHERE user_id = 10 OR email = 'a@b.com'; (只有 user_id有索引) | 1. 给email也加上索引 2. 使用 UNION ALL拆分查询 | 如果OR连接的条件中有一个没有索引，优化器很可能放弃索引进行全表扫描。 |
5. 使用 != 或 <> | WHERE status != 'paid'; | 尽量使用IN或=。例如 WHERE status IN ('pending', 'failed'); | 负向查询通常返回大量数据，优化器认为全表扫描更划算。 |
| 6. 索引列区分度太低 | WHERE gender = 'M'; ( gender列只有'M'和'F'两种值) | 这种列不适合建索引。 | 索引的意义在于快速筛选。如果一个值能筛选掉大部分数据，索引才有效。否则，回表的成本可能高于全表扫描。 |
| 7. 优化器认为全表扫描更快 | SELECT * FROM users WHERE age > 20; (age>20的数据占全表的80%) | 这是正常情况，无需优化。 | 当查询需要返回的数据量非常大时，顺序读取全表（顺序I/O）可能比通过索引进行大量随机I/O（索引定位+回表）要快。 |
| 8. 联合索引未遵循最左前缀原则 | INDEX idx_name_age (name, age) WHERE age = 25; | WHERE name = 'Alice'; WHERE name = 'Alice' AND age = 25; | 对于 (a, b) 联合索引，必须先使用a，才能用到b。直接查b是用不上这个索引的。 |
二、 如何排查索引效果？—— EXPLAIN 命令详解
EXPLAIN 是MySQL提供的查询分析工具，它是排查索引问题的最核心、最权威的手段。它可以告诉你MySQL是如何执行一条SQL语句的。
用法：
sql
EXPLAIN SELECT * FROM users WHERE age = 25;
执行后会返回一张表，以下是需要重点关注的几个字段：
1. type (访问类型)
这是最重要的字段，显示了MySQL查找数据所用的方法。性能从好到差依次为：
system>const: 几乎不用管，表示查询已优化为常量，速度最快。eq_ref: 优秀。通常出现在多表连接中，使用了主键或唯一索引。ref: 良好。使用了非唯一性索引进行等值查询。range: 还行。对索引进行了范围查询，如BETWEEN,>,<。index: 较差。扫描了整个索引树。虽然比全表扫描快（因为索引通常比表数据小），但仍然是低效的。ALL: 最差！警报！ 表示正在进行全表扫描 (Full Table Scan)。这是优化的首要目标，必须尽可能避免。
排查指南：如果 type 是 ALL 或 index，你的索引很可能没起作用或效果很差。
2. possible_keys 和 key
possible_keys: 显示MySQL认为可能会用到的索引。key: 显示MySQL实际决定使用的索引。
排查指南：如果 key 字段为 NULL，而 possible_keys 里有你期望的索引，说明优化器因为某种原因（如上文列举的场景）放弃了它。
3. key_len (索引长度)
表示实际用到的索引的字节数。这个值可以帮你判断联合索引是否被充分利用。
排查指南：对于联合索引 (a, b, c)，如果 key_len 只等于 a 列的长度，说明只用到了索引的第一个字段。如果等于 a 和 b 的长度之和，说明用到了前两个字段。
4. rows (预估扫描行数)
优化器估计要找到结果需要读取的行数。这个值越小越好。
排查指南：如果这个数字非常大，接近表总行数，即使 key 不为 NULL，也说明索引的选择性不高，查询效率不会太好。
5. Extra (额外信息)
这是一个信息宝库，包含了很多重要提示：
Using index: 最佳状态。表示查询使用了覆盖索引，数据直接从索引中获取，无需回表。性能极高。Using where: 表示在存储引擎层返回数据后，MySQL服务器层还需要进行一次过滤。Using index condition: (索引下推) 这是一个很好的优化，表示在索引层面就进行了部分WHERE条件的过滤，减少了回表次数。Using filesort: 坏信号。表示MySQL无法利用索引完成排序，必须在内存或磁盘上进行额外的排序操作，非常耗性能。通常需要通过添加合适的索引来解决。Using temporary: 坏信号。表示MySQL需要创建一个临时表来处理查询，常见于GROUP BY或UNION操作，性能很差。
三、排查步骤总结
- 找出慢查询：通过慢查询日志（Slow Query Log）或性能监控工具定位有问题的SQL。
- 执行
EXPLAIN：对慢查询SQL执行EXPLAIN。 - 分析
EXPLAIN结果：- 检查
type是否为ALL。 - 检查
key是否为NULL。 - 检查
rows是否过大。 - 检查
Extra是否有Using filesort或Using temporary。
- 检查
- 定位问题：根据分析结果，对照上面“索引失效的场景”，判断问题所在（是函数？是左模糊？还是索引建得不对？）。
- 优化和验证：
- 修改SQL语句（如把函数调用改为范围查询）。
- 或修改/添加索引（如创建更合适的联合索引）。
- 再次
EXPLAIN：对优化后的SQL再次执行EXPLAIN，确认type、key、Extra等指标是否得到改善。 - 测试性能：实际测试查询时间，确认优化有效。