索引是 MySQL 性能优化的第一道防线，但很多开发者都遇到过这样的困惑：字段明明加了索引，EXPLAIN 一看却是全表扫描。索引失效往往不是数据库"不听话"，而是 SQL 的写法触发了优化器的绕过逻辑。下面这 8 种场景，覆盖了日常开发中 90% 的索引失效问题。

一、违反最左前缀原则

联合索引 (a, b, c) 只有在查询条件从最左列开始连续匹配时才会生效。

-- 索引：(name, age, city)  

SELECT * FROM users WHERE age = 25;              -- 失效，跳过 name  

SELECT * FROM users WHERE name = 'Tom' AND city = '北京';  -- city 部分失效  

SELECT * FROM users WHERE name = 'Tom' AND age = 25;       -- 生效

排查：EXPLAIN 的 key 字段为 NULL 或 key_len 明显偏小，说明只用到了部分索引列。

二、在索引列上使用函数

只要对索引列做了函数运算，B+ 树的有序性就被破坏，优化器只能放弃索引。

SELECT * FROM orders WHERE YEAR(created_at) = 2026;        -- 失效  

SELECT * FROM orders WHERE created_at >= '2026-01-01'  

  AND created_at < '2027-01-01';                          -- 生效

原则：把函数计算移到等号右边的常量侧，让索引列"裸露"在比较符左边。

三、隐式类型转换

字段是字符串类型，却用数字去查，MySQL 会隐式转换字段本身，导致索引失效。

-- phone 为 varchar(11)  

SELECT * FROM users WHERE phone = 13800138000;             -- 失效  

SELECT * FROM users WHERE phone = '13800138000';           -- 生效

排查：EXPLAIN 出现 type: ALL，且 rows 接近全表行数时，优先检查字段类型与参数类型是否一致。

四、使用前导通配符的 LIKE

SELECT * FROM article WHERE title LIKE '%MySQL%';          -- 失效  

SELECT * FROM article WHERE title LIKE 'MySQL%';           -- 生效

% 在左侧时无法利用索引的有序性。如果业务确实需要模糊匹配，考虑引入全文索引（FULLTEXT）或搜索引擎。

五、OR 连接的条件中有一侧无索引

SELECT * FROM users WHERE name = 'Tom' OR age = 25;  

-- 若 age 无索引，整体退化为全表扫描

优化：给两侧字段都建索引，或改用 UNION ALL 拆开查询。

六、范围查询后的列失效

联合索引 (a, b, c) 中，如果 a 走了范围查询，后面的 b、c 就无法继续用于精确定位。

SELECT * FROM t WHERE a > 10 AND b = 5;   -- b 无法走索引定位

注意：范围条件应尽量放在联合索引的最后一列。

七、使用 NOT、!=、<> 等否定条件

SELECT * FROM users WHERE status != 1;     -- 大概率全表扫描

否定条件意味着要扫描"除某值以外"的绝大部分数据，优化器判断走索引还不如直接全表。可考虑改写成 IN 正向枚举。

八、数据区分度太低

当一个字段的取值高度重复（如性别、状态标记），优化器认为走索引的代价高于全表扫描，会主动放弃索引。

SELECT * FROM users WHERE gender = 1;      -- 区分度极低

排查：用 SELECT COUNT(DISTINCT col) / COUNT(*) FROM t; 计算区分度，低于 0.1 的字段单独建索引意义不大。

统一排查流程

1. 用 EXPLAIN 查看执行计划，重点关注 type、key、key_len、rows、Extra

1. type: ALL 代表全表扫描；key: NULL 代表未走索引

1. Extra 出现 Using filesort 或 Using temporary 说明排序、分组也未命中索引

1. 用 SHOW INDEX FROM 表名 核对索引列顺序

1. 对慢查询开启 slow_query_log，结合 mysqldumpslow 或 pt-query-digest 做聚合分析

SET GLOBAL slow_query_log = ON;  

SET GLOBAL long_query_time = 1;

小结

索引失效的本质，是 SQL 写法让优化器无法利用 B+ 树的有序性。记住三条铁律：索引列不参与运算、类型必须一致、遵循最左前缀。配合 EXPLAIN 逐条验证，绝大多数慢查询都能定位到具体原因。