mysql索引什么时候会失效_mysql常见失效场景

mysql索引可能因函数/表达式、隐式类型转换、LIKE前缀通配、NOT/!=/OR等操作而失效，需通过EXPLaiN验证并遵循裸列查询、统一类型、避免前导%等原则。

MySQL 索引不是建了就一定生效的——优化器会根据成本（cost）决定是否走索引，而很多看似合理的写法，实际会让索引完全失效。下面这几种情况，在日常 SQL 编写和线上慢查排查中高频出现，务必警惕。

WHERE 中对索引列用了函数或表达式

比如 date(create_time) = '2024-12-31'、SUBSTR(phone, 1, 3) = '138'、price * 1.1 > 100，这些都会让索引失效。

• 原因：B+ 树索引里存的是原始值，不是计算后的结果；MySQL 无法用索引直接匹配“算完之后的值”
• ✅ 正确做法是把计算移到右边，让左边保持裸列：

  SELECT * FROM orders WHERE create_time >= '2024-12-31 00:00:00' AND create_time < '2025-01-01 00:00:00';

• ⚠️ 衍生方案：MySQL 5.7+ 可建生成列 + 索引，例如：ADD column create_date DATE AS (DATE(create_time)) STOred，再给 create_date 加索引

联合索引没按最左前缀使用

联合索引 KEY idx_name_age_city (name, age, city)，以下查询会失效：

• WHERE age = 25（跳过 name，直接查中间列）
• WHERE city = '北京'（只用最右列）
• WHERE name = '张三' AND city = '上海'（跳过 age，中断连续性）

✅ 能走索引的写法必须从左开始连续使用： WHERE name = '张三' ✔️ WHERE name = '张三' AND age > 20 ✔️ WHERE name = '张三' AND age = 25 AND city LIKE '上%' ✔️（注意：LIKE 后缀带 % 仍可用索引）

隐式类型转换导致索引“悄悄失效”

字段定义为 VARCHAR，但查询时传了数字：WHERE user_id = 12345 —— 看似没问题，实则触发隐式转换，等价于 WHERE CAST(user_id AS SIGNED) = 12345，索引失效。

• ✅ 统一类型：字符串字段务必用引号：WHERE user_id = '12345'
• ⚠️ 反向转换（int 字段传字符串）有时能走索引（如 age = '25'），但这不可靠，取决于数据分布和 MySQL 版本，**一律应避免混用类型**
• 检查方法：用 EXPLAIN 看 type 是否为 ALL 或 key 是否为 NULL

LIKE 以通配符 % 开头 or 用了 NOT/!=/OR 等操作符

WHERE name LIKE '%三'、WHERE status != 1、WHERE name = '张三' OR age = 25，这些都极大概率触发全表扫描。

• LIKE '%xxx'：无法利用 B+ 树的有序性，只能扫全表；若必须模糊前缀，考虑全文索引或 elasticsearch
• != 或 NOT IN：优化器通常认为“排除少数”不如“扫描多数再过滤”，尤其当结果集占比高时
• OR：只要任意一个分支字段无索引，整个 WHERE 就可能放弃索引（即使另一侧有）；拆成 union ALL 是常见绕过方式

真正容易被忽略的一点：索引是否生效，不只看语法，更要看数据分布和优化器估算。比如一张只有 100 行的表，哪怕写了 WHERE id = ?，MySQL 也可能直接全表扫描——因为比走索引还快。所以别只信“我加了索引”，要信 EXPLAIN 的输出和真实执行时间。