索引失效是开发者最常踩的坑之一。很多时候你明明建了索引，EXPLAIN 一看，type 却是 ALL（全表扫描），查询慢得离谱。理解哪些写法会导致索引失效，比死记硬背优化规则重要得多。
下面这些场景都是真实的生产事故高频来源，每一个都值得你刻进开发习惯里。
场景一：对索引列使用函数或表达式
这是最经典的索引失效场景。一旦你在 WHERE 子句中对索引列做了任何函数运算或表达式计算，优化器就无法直接使用索引上的值来定位数据，只能退化为全表扫描。
错误示例：
SELECT * FROM orders WHERE DATE(create_time) = '2025-01-01';
-- 或
SELECT * FROM orders WHERE amount + 10 = 100;create_time 列上明明有索引，但被 DATE() 函数包裹后，索引中存储的是完整的 datetime 值，而 DATE() 的结果需要逐行计算才能比对，索引自然就废了。
EXPLAIN 表现： type=ALL，key=NULL。
正确写法：
SELECT * FROM orders
WHERE create_time >= '2025-01-01 00:00:00'
AND create_time < '2025-01-02 00:00:00';改用范围查询，索引就能正常工作。这个场景在日期处理、字符串截取、数学运算中极其常见。
场景二：隐式类型转换
MySQL 在比较不同类型的列和值时，会悄悄做类型转换。当索引列是字符串类型，而你传入的是数字，MySQL 会将字符串列的值逐个转换为数字再比较——这一转换发生在索引列上，导致索引失效。
典型错误：
-- phone 列是 VARCHAR(20) 类型
SELECT * FROM users WHERE phone = 13800138000;表面看没什么问题，实际 MySQL 内部做的是 CAST(phone AS UNSIGNED) = 13800138000，把索引列强制转换了，索引随即失效。
EXPLAIN 表现： type=ALL，Extra 中可能出现 Using where。
正确写法：
SELECT * FROM users WHERE phone = '13800138000';值加上引号，类型匹配，索引生效。反过来，如果列是数字类型，传入字符串通常不会导致失效（MySQL 会将字符串转为数字，对列不做转换），但为了规范，始终建议类型严格匹配。
场景三：LIKE 查询以 % 开头
LIKE 的索引使用取决于通配符的位置：
col LIKE 'abc%'—— 前缀匹配，索引可以正常使用。col LIKE '%abc'—— 后缀匹配，索引失效。col LIKE '%abc%'—— 全模糊匹配，索引失效。
原因： B+ 树索引按列值从左到右有序排列。'abc%' 相当于找以 "abc" 开头的所有值，索引可以直接定位到 "abc" 节点然后顺序扫描。而 '%abc' 或 '%abc%' 没有任何固定前缀，索引无法直接定位，只能全表扫描。
EXPLAIN 表现： type=ALL。
解决方案： 如果确实需要后缀或全文模糊搜索，考虑使用全文索引（FULLTEXT），或者通过 Elasticsearch 等外部搜索引擎来补足。
场景四：联合索引不满足最左前缀原则
联合索引 (a, b, c) 相当于建立了 (a)、(a, b)、(a, b, c) 三个索引。如果查询条件跳过了最左边的列，索引就会失效。
错误示例：
-- 索引：idx_name_age_status (name, age, status)
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE status = 1 AND name = 'Tom'; -- 这个不会失效，优化器会调整顺序
SELECT * FROM users WHERE age = 25 AND status = 1; -- 这个会失效，因为跳过了 name第二句因为 name 出现在条件中，即使顺序不对，优化器也会自动调整为最左匹配，索引可用。第三句完全缺失 name，索引只能失灵。
EXPLAIN 表现： key 可能显示索引名，但 key_len 很短，说明只用到了索引的一部分或者根本没起效，实际扫描行数巨大。
正确做法： 设计联合索引时，把查询中出现频率最高、区分度最大的列放在最左侧。
场景五：范围查询导致右侧列索引失效
联合索引中，如果对某一列使用了范围条件（>、<、BETWEEN、LIKE 前缀但非精确），那么该列右侧的索引列会失效。
示例：
-- 索引：idx_a_b_c (a, b, c)
SELECT * FROM orders WHERE a = 1 AND b > 10 AND c = 3;这里 a 可以精确匹配，b 用到了范围，但 c 的条件无法通过索引进一步过滤。因为索引在 b 的范围内，c 的值并不是全局有序的，无法直接定位。
EXPLAIN 表现： key_len 只覆盖到 a 和 b，Extra 中可能有 Using index condition（索引下推），但 c 的过滤是在回表或索引扫描中完成的。
优化建议： 如果 c 的过滤性很强，可以考虑调整索引列顺序，把范围列尽量往后放，或者将 c 的过滤条件单独处理。
场景六：OR 连接的条件中存在非索引列
OR 两边只要有一个条件没有索引，整个查询就可能全表扫描。
错误示例：
-- name 有索引，nickname 没有索引
SELECT * FROM users WHERE name = 'Tom' OR nickname = 'Tom';即使 name 有索引，因为 OR 需要合并两个结果集，优化器判断走索引 + 全表扫描再合并，不如直接全表扫描划算，于是选择了全表扫描。
EXPLAIN 表现： type=ALL。
解决方案： 用 UNION 改写，将两个查询分别用索引拼接：
SELECT * FROM users WHERE name = 'Tom'
UNION
SELECT * FROM users WHERE nickname = 'Tom';或者确保 nickname 也加上索引。
场景七：使用不等于、NOT IN、NOT EXISTS
!=、<>、NOT IN、NOT EXISTS 这类否定条件，通常会导致索引失效。因为索引查找依赖于有序结构快速定位等值或范围，而“不等于”意味着要排除某几个值，剩下大量数据仍需扫描。
示例：
SELECT * FROM products WHERE status != 'deleted';如果 status 只有少数几种值且分布不均，优化器可能仍然选择索引，但大多情况下会退化为全表扫描。
EXPLAIN 表现： type=range 甚至 ALL。
优化建议： 如果这类查询非常频繁，可以考虑通过设计一种状态枚举让查询变为等值查询，或者使用覆盖索引减少回表开销。
场景八：列参与运算或被函数包裹
这一点是场景一的细化，但值得单独强调，因为太常见了。
-- 错误
SELECT * FROM products WHERE stock - 1 = 0;
-- 正确
SELECT * FROM products WHERE stock = 1;
-- 错误
SELECT * FROM users WHERE LEFT(name, 3) = 'Tom';
-- 正确
SELECT * FROM users WHERE name LIKE 'Tom%';记住一句话：索引列要“赤裸裸”地出现在比较符的一侧，不要让它在表达式里。
场景九：IS NULL 或 IS NOT NULL（视情况而定）
索引列是否存 NULL，以及数据库对 NULL 的选择率评估，会导致优化器决策差异。
- 在 MySQL 中，
IS NULL通常可以使用索引，因为索引结构中包含 NULL 值。 IS NOT NULL如果匹配大量行，优化器可能认为全表扫描更快，从而不走索引。
EXPLAIN 表现： 可能看到 type=ref 或 ALL，取决于数据分布。
最佳实践： 建表时为列设置 NOT NULL DEFAULT，尽量避免 NULL 值带来的额外判断和索引效率下降。
场景十：数据量太小或区分度极低，优化器主动放弃索引
这不是“失效”，而是优化器认为全表扫描成本更低。比如：
- 整张表只有几百条数据，一次全表扫描可能只需 1-2 个数据页，比回表查询更快。
- 某个索引列的区分度极低（比如
gender只有男/女），过滤后仍要扫描大量行，优化器会直接全表扫描。
EXPLAIN 表现： type=ALL，但 rows 很小。
应对： 对于小表，不必强行建索引。对于低区分度列，考虑与其他高区分度列创建联合索引，或者干脆不建。
预防索引失效的日常习惯
- 写 SQL 时多看一眼索引定义，确保条件列匹配上最左前缀，不包裹函数。
- 使用
EXPLAIN检查，尤其上线前的修改。重点关注type、key、rows。 - 开启
slow_query_log，定期分析慢查询，对出现ALL的语句逐一排查。 - 避免在 WHERE 中对列做运算，业务逻辑尽量前置到应用层处理好再传入恒定值。
- 字符列查询必须加引号，彻底堵死隐式转换。
- 合理设计联合索引，将等值查询列放在前面，范围查询列放在最后。
索引失效并不可怕，可怕的是你上线了还不知道。把上述场景记在心里，你就能躲过至少八成索引相关的性能坑。