B+树索引无法处理表达式,因为其叶子节点仅按字段原始值严格排序,而对索引列使用函数或运算(如age+5、DATE(create_time))会破坏原始有序结构,使优化器无法通过二分查找定位起止位置,只能全表扫描。

因为索引是基于字段原始值构建的有序结构(如B+树),而运算会破坏这个结构的可定位性——数据库无法在索引中直接查找 age + 5 = 30 的结果,只能逐行计算再比对。
为什么B+树索引无法处理表达式
B+树的每个叶子节点按索引列的原始值严格排序,比如 age 列索引里存的是 18、25、30、35……这些真实值。当你写 WHERE age + 5 = 30,MySQL 实际要找的是 age = 25,但它没法从“加5后等于30”这个表达式反推出原始值在树中的位置——这相当于拿着加工后的结果去查原始目录,目录里根本没有这项。
同理,YEAR(create_time) = 2024 会让优化器放弃 create_time 索引,因为索引里存的是 '2024-01-15 14:22:03' 这样的完整时间,不是单独的年份整数。
哪些写法属于“对字段做运算”
以下操作都会导致索引失效,只要出现在 WHERE 子句左侧的索引列上:
-
WHERE age * 2 > 50(数学运算) -
WHERE UPPER(name) = 'TOM'(字符串函数) -
WHERE DATE(create_time) = '2024-01-01'(日期函数) -
WHERE id + 0 = 123(看似无意义的强制转换)
如何验证是否真的失效了
别猜,用 EXPLAIN 看执行计划:
执行 EXPLAIN SELECT * FROM user WHERE age + 5 = 30;,如果 key 字段为 NULL、type 是 ALL,就确认失效;而改写成 WHERE age = 25 后,key 会显示实际索引名,type 变为 ref 或 const。
注意:MySQL 8.0+ 支持函数索引,但需显式创建,比如 ADD INDEX idx_age_plus5 ((age + 5)),这不是默认行为,也不解决通用性问题。
最容易被忽略的隐性运算
有些“运算”藏得深,容易被当成安全写法:
— WHERE create_time >= NOW() - INTERVAL 7 DAY:虽然用了函数,但 NOW() 是运行时计算,右侧是常量表达式,create_time 本身没被函数包裹,通常仍可走索引(前提是 create_time 有索引);
— WHERE CAST(age AS CHAR) LIKE '2%':明显对索引列做了类型转换,必然失效;
— WHERE age BETWEEN ? AND ? 是安全的,因为不改变字段本身,只限定范围边界。
真正危险的,永远是把索引列放在函数/运算符的**左侧**——哪怕只多一个括号、一个加号、一个 TRIM(),它就不再是那个能被B+树快速定位的原始值了。










