mysql函数查询为什么不走索引_mysql函数索引失效原因

索引通常会失效，因为MySQL无法用索引树中存储的原始值匹配函数处理后的结果，如UPPER(name)需逐行计算，导致全表扫描；MySQL 8.0.13+支持函数索引，或可通过冗余字段+普通索引优化。

MySQL 中对字段使用函数（如 UPPER()、DATE()、SUBSTRING() 等）进行查询时，**索引通常会失效**，根本原因是：MySQL 无法直接用索引树中存储的原始值去匹配函数处理后的结果。

函数导致索引列无法被直接比较

索引是基于字段原始值构建的 B+ 树结构。当你写 WHERE UPPER(name) = 'ABC'，MySQL 必须先把每行的 name 取出来、调用 UPPER() 计算，再比对——这个过程绕过了索引查找，只能全表扫描。

• 索引只存 name 的原始值（如 'abc'），不存 UPPER(name) 的结果（'ABC'）
• 优化器发现无法用索引做“等值定位”，就放弃使用该索引
• 即使 name 字段上有索引，UPPER(name) 也会让索引失效

常见导致索引失效的函数写法

以下写法基本都会让对应字段的索引失效：

• WHERE YEAR(create_time) = 2025 → 应改用范围查询：create_time >= '2025-01-01' AND create_time
• WHERE SUBSTRING(phone, 1, 3) = '138' → 若前缀固定，可考虑生成冗余字段 phone_prefix 并建索引
• WHERE CONCAT(first_name, ' ', last_name) = 'Zhang San' → 应避免在 WHERE 中拼接，改用联合查询或冗余字段
• WHERE status + 0 = 1（隐式类型转换）→ 本质也是函数操作，同样失效

MySQL 8.0+ 支持函数索引（Functional Key）

MySQL 8.0.13 起支持「函数索引」，允许你为表达式创建索引，从而让带函数的查询走索引：

• 建索引示例：CREATE INDEX idx_upper_name ON users ((UPPER(name)));
• 查询就能命中：SELECT * FROM users WHERE UPPER(name) = 'LISA';
• 注意括号语法：双括号 ((UPPER(name))) 是必须的，表示函数索引
• 仅适用于 MySQL 8.0.13+，且函数必须是 deterministic（确定性）的

替代方案：冗余字段 + 普通索引

兼容老版本或更可控的方式是增加计算好的冗余字段：

• 加字段：ALTER TABLE users ADD COLUMN name_upper VARCHAR(100) STORED;
• 填值：UPDATE users SET name_upper = UPPER(name);
• 建索引：CREATE INDEX idx_name_upper ON users(name_upper);
• 查的时候用：WHERE name_upper = 'LISA' → 索引正常生效
• 配合触发器或应用层保证冗余字段同步更新

函数索引失效不是 bug，而是索引机制的自然限制。理解原理后，要么升级到 8.0+ 用函数索引，要么用冗余字段+普通索引，避免在 WHERE 中对索引字段直接套函数。