函数使索引失效是因为B+树索引存储原始值，而函数运算后无法匹配有序结构；应将函数移至常量侧，如DATE(create_time)='2024-01-01'改为create_time>='2024-01-01' AND create_time

WHERE 子句中对索引列使用函数（如 DATE()、SUBSTR()、YEAR()）会直接导致索引失效——这不是数据库“不够聪明”，而是 B+ 树索引根本无法匹配函数计算后的结果。

---

为什么函数会让索引失效

索引里存的是原始值，比如 create_time 列的值是 '2024-01-01 14:23:05'，索引就按这个完整时间戳排序存储。一旦写成 WHERE DATE(create_time) = '2024-01-01'，数据库就得对每一行的 create_time 先调用 DATE() 函数，再比对结果——这等于绕过索引，逐行计算。

本质上，优化器看到的是「对索引列施加了不可下推的运算」，它无法把查询条件映射到索引的有序结构上，只能退回到全表扫描。

---

DATE()、YEAR() 等日期函数怎么改写

核心原则：让索引列以“裸值”形式出现在 WHERE 右侧比较中，把函数/计算挪到常量侧。

• WHERE DATE(create_time) = '2024-01-01' → 改为 WHERE create_time >= '2024-01-01' AND create_time
• WHERE YEAR(create_time) = 2024 → 改为 WHERE create_time >= '2024-01-01' AND create_time
• WHERE create_time::date = '2024-01-01'（PostgreSQL）→ 同样要避免，改用范围写法

注意：BETWEEN 容易漏掉边界精度（比如 BETWEEN '2024-01-01' AND '2024-01-01 23:59:59'），推荐用左闭右开区间（>= + ）更安全、可读性更强。

---

字符串函数如 SUBSTR()、UPPER() 怎么处理

这类函数同样破坏索引前缀匹配能力。例如 WHERE SUBSTR(name, 1, 3) = 'adm' 无法利用 name 上的普通索引。

• 优先考虑是否能转为前缀匹配：WHERE name LIKE 'adm%'（可走索引）
• 若必须做大小写不敏感匹配，且字段是 VARCHAR，建索引时指定校对规则（如 MySQL 的 utf8mb4_0900_as_cs 不区分大小写）比在查询里套 UPPER() 更可靠
• 真要依赖函数结果查，且查询频繁，可考虑函数索引（PostgreSQL/Oracle 支持 CREATE INDEX idx ON t(UPPER(name))），但写入性能会下降，别滥用

---

容易被忽略的隐含陷阱

有些“看似没函数”的写法，其实暗藏转换：

• WHERE id + 0 = 100 —— 算术表达式，照样失效
• WHERE username || '' = 'alice'（PostgreSQL 字符串拼接）—— 表达式操作，索引不生效
• WHERE create_time = CURDATE() —— CURDATE() 是函数，等价于 DATE(NOW())，同样失效；应改用 WHERE create_time >= CURDATE() AND create_time

最稳妥的自查方式：执行 EXPLAIN，看 key 是否非 NULL，type 是否落到 ref 或更好级别；别依赖“看起来差不多”的写法。