为什么在SQL的WHERE中对字段使用函数会导致索引失效？-SQL-PHP中文网

因为索引（b+树）里存的是字段原始值，不是函数计算后的结果；where里一调year()、upper()、substr()这类函数，优化器就无法把条件映射到索引结构上，只能逐行取值、计算、比对——等效于全表扫描。

为什么在sql的where中对字段使用函数会导致索引失效？

因为索引（B+树）里存的是字段原始值，不是函数计算后的结果；WHERE里一调YEAR()、UPPER()、SUBSTR()这类函数，优化器就无法把条件映射到索引结构上，只能逐行取值、计算、比对——等效于全表扫描。

WHERE里用`YEAR(create_time)`为什么走不了索引

B+树索引中create_time存的是完整时间戳（如'2025-06-19 14:22:03'），按字节有序排列；而YEAR(create_time) = 2025要求数据库对每一行都执行一次YEAR()，再判断结果。优化器没法跳转到“所有年份为2025”的索引位置——那根本不是索引里存在的值。

✅ 正确写法是把逻辑移向常量侧：

WHERE create_time >= '2025-01-01' AND create_time （左闭右开，精度可控，秒级/微秒级都不漏）
避免用BETWEEN '2025-01-01' AND '2025-12-31 23:59:59'：语义模糊，且可能截断微秒导致漏数据

`UPPER(name) = 'JOHN'`破坏前缀匹配能力

字符串索引依赖完整值的字典序，UPPER()一调，就等于把原字段“重算一遍”，索引里没存大写后的串，也没存任何子串。优化器无法利用name上的普通索引加速这个查询。

✅ 更优解不是硬扛函数索引，而是换思路：

改用前缀匹配：WHERE name LIKE 'john%'（需确保校对规则不区分大小写，如utf8mb4_0900_as_cs）
建函数索引仅当读远大于写，且确认MySQL 8.0+或PostgreSQL支持：CREATE INDEX idx_name_upper ON users (UPPER(name))
隐式转换更危险：比如WHERE name = 123（name是VARCHAR），MySQL会悄悄转成CAST(name AS SIGNED)，效果等同于在列上套函数

聚合函数出现在WHERE里直接让优化器“放弃挣扎”

MAX()、COUNT()这些函数必须先扫数据才能出结果，根本没法作为WHERE的过滤依据——你不能拿一个还没算出来的值去跟索引比。

❌ 这类写法语法可能通不过，或执行时强制全表扫描：

WHERE amount > (SELECT MAX(amount) FROM orders WHERE status = 'paid')：子查询可能走索引，但外层WHERE无索引可用
WHERE amount > MAX(amount) - 100：MySQL直接报错，语法不合法

✅ 实际可行路径只有两条：

拆成两步：先查SELECT MAX(amount) FROM orders WHERE status = 'paid'，拿到值后拼进第二条SQL，确保status和amount上有联合索引（如INDEX idx_status_amount (status, amount)）
MySQL 8.0+可用窗口函数兜底：SELECT * FROM (SELECT *, MAX(amount) OVER (PARTITION BY status) AS max_amt FROM orders) t WHERE status = 'paid' AND amount > max_amt - 100；注意：窗口不影响WHERE能否走索引，仍要靠基础索引支撑

真正容易被忽略的是：函数索引不是银弹。它让某类查询变快，但每次INSERT/UPDATE都要多算一次函数值、多写一次索引页——高并发写入场景下，这个开销可能远超读取收益。