mysql函数在where条件中如何使用_mysql索引失效说明

WHERE 中对索引列使用函数必然导致索引失效，因索引基于原始值有序存储，函数改变数据形式使优化器无法匹配；隐式转换同理，应改写条件将函数移至参数侧并用 EXPLAIN 验证。

WHERE 中用函数会导致索引失效

MySQL 在 WHERE 子句中对索引列使用函数（如 UPPER()、DATE()、SUBSTRING()、YEAR() 等），会直接导致该列上的索引无法被用于范围扫描或等值查找——不是“可能失效”，而是“基本必然失效”。

根本原因：索引是按列原始值的有序结构存储的，而函数改变了数据的表达形式，优化器无法将函数结果与索引 B+ 树中的原始值做快速比对。

• 例如：WHERE UPPER(name) = 'JOHN' → 即使 name 有索引，也走全表扫描
• 再如：WHERE YEAR(create_time) = 2024 → create_time 的 B+ 树索引完全失效
• 即使是看似无害的 WHERE id + 1 = 100，也会让 id 索引失效（因为要对每行计算）

哪些写法看似“没用函数”但实际触发 隐式转换？

隐式类型转换 和字符集转换，效果等同于加了函数，同样破坏索引。

• WHERE user_id = '123'（user_id 是 INT）→ MySQL 自动转成 CAST('123' AS SIGNED)，索引仍可用；但若字段是 VARCHAR 而传入数字，且存在字符集 / 排序规则不一致，就可能失效
• WHERE mobile = 13800138000（mobile 是 VARCHAR）→ 触发全字段隐式转数字比较，无法用索引
• WHERE name = 'john' COLLATE utf8mb4_0900_as_cs（而索引用的是默认 collation）→ 排序规则不匹配，索引跳过

替代方案：重写 WHERE 条件以保留索引能力

核心思路是「把函数从列上挪开，移到参数侧」或「改用可命中索引的等价表达」。

• 日期提取类：不用 YEAR(create_time) = 2024，改写为

  WHERE create_time >= '2024-01-01' AND create_time <'2025-01-01'

• 大小写比较类：避免 UPPER(name) = 'JOHN'，改为建函数索引（MySQL 8.0+）：

  CREATE INDEX idx_name_upper ON users ((UPPER(name)));

  或更稳妥地统一存大写 + 普通索引

• 前缀匹配类：不用 SUBSTRING(phone, 1, 3) = '138'，改用 phone LIKE '138%'（前提是索引未被其他操作干扰）
• 数值计算类：不用 price * 1.1 > 100，改写为 price > 100 / 1.1

如何快速验证某条 WHERE 是否用了索引？

别猜，用 EXPLAIN 看 key 和 rows 字段，重点关注：

• key 为 NULL → 确认没走索引
• type 是 ALL → 全表扫描，基本可判定失效
• possible_keys 有值但 key 为 NULL → 有索引但没用上，大概率是函数 / 类型转换 /OR 多条件等原因
• 执行前加 SET optimizer_trace="enabled=on"; 可查详细决策路径（适合复杂 case）

真正容易被忽略的点是：哪怕只在一个 OR 分支里对索引列用了函数，整个 WHERE 就可能放弃该索引；复合索引中只要左侧字段被函数处理，右侧字段索引能力也一并归零。