在MySQL中，若WHERE子句对索引列使用YEAR()、SUBSTR()等函数，索引就会失效。因为索引存储的是原始值，函数的使用会破坏其有序性。应将其改写为范围查询（如created_at >= '2024-01-01'）或LIKE前缀匹配（如name LIKE 'abc%'），以保证索引列的裸露。

WHERE里用YEAR()、SUBSTR()这类函数会失效索引

MySQL无法对索引列执行函数后再走B+树查找，因为索引里存的是原始值，不是函数结果。比如created_at上有索引，但写成WHERE YEAR(created_at) = 2024，优化器只能放弃索引，退化为全表扫描。

本质是：索引的有序性在函数处理后被破坏，数据库没法用“跳查”方式定位数据。

• 常见失效函数：YEAR()、MONTH()、DATE()、SUBSTR()、UPPER()、TRIM()
• 隐式类型转换也算函数行为：比如WHERE phone = 13800138000（phone是VARCHAR），MySQL自动转成CAST(phone AS SIGNED)，同样包裹索引列
• 哪怕函数只作用于常量侧（如WHERE id = CAST('123' AS UNSIGNED)）也不影响索引，问题只出在索引列被函数包裹

怎么改写才能让索引继续生效

核心思路是把函数操作从字段移到条件值上，保持索引列“裸露”参与比较。

以时间为例，别用YEAR(created_at)，改用范围边界：

WHERE created_at >= '2024-01-01' AND created_at < '2024-01-01'

字符串前缀匹配也一样：WHERE SUBSTR(name, 1, 3) = 'abc' 应改成 WHERE name LIKE 'abc%'——后者能命中name上的B-Tree索引。

• 日期类优先用范围，而非提取年/月函数
• 字符串左匹配用LIKE 'xxx%'，避免SUBSTR或LEFT()
• 大小写问题可建函数索引（MySQL 8.0+）：CREATE INDEX idx_name_lower ON users ((LOWER(name)))，但要注意兼容性和维护成本

EXPLAIN里一眼识别是否中招

执行EXPLAIN后重点看三列：

• key为NULL：说明压根没走索引
• type是ALL：确认是全表扫描
• rows数值极大（远超实际返回行数）：大概率索引失效导致扫了不该扫的行

如果possible_keys里有候选索引，但key却是NULL，基本可以断定是函数/计算/隐式转换惹的祸。

复合索引上函数更隐蔽，也更危险

比如说，联合索引是(user_id, created_at)，你写WHERE user_id = 123 AND YEAR(created_at) = 2024。乍一看好像用上了user_id，但实际上，created_at上的函数会使整个索引的范围扫描能力直接丧失。这意味着什么呢？就是说，只能先靠user_id进行过滤，然后在得到的结果集中，再逐行去计算YEAR()的值。

这种情况下，rows可能比单列索引还高，因为优化器误判了过滤效果。

• 最左前缀原则依然成立，但函数一加，右边字段就“失能”
• 即便只对联合索引第二列用函数，第一列的等值过滤也无法触发高效范围扫描
• 测试时别只看返回结果对不对，一定要EXPLAIN看执行路径

真正卡住性能的，往往不是没建索引，而是建了却因一行函数调用彻底作废。每次写WHERE条件前，多问一句：这个字段名，是不是还干干净净地站在等号左边？