列上做运算为什么会失效
WHERE 里对索引列做函数或运算时，B+ 树无法利用列的有序性定位范围，索引就用不上了。解决思路有两条：把运算挪到常量一侧改写条件，或建立表达式索引让索引本身保存计算后的值。
基本信息
| 失效原因 | 索引按列值排序，列被运算后顺序不再对应 |
|---|---|
| 典型场景 | 日期函数、大小写转换、CAST、JSON 取值、字符串截取 |
| 改写原则 | 保持索引列“裸用”，把运算移到常量一侧 |
| MySQL 函数索引 | 8.0.13+ 支持 ((表达式)) 语法的函数索引 |
| PostgreSQL | CREATE INDEX … ((表达式)) 原生支持 |
| 查询一致性 | 查询表达式必须与索引表达式完全一致才能命中 |
条件改写示例
条件改写示例
-- 原写法：列上做 YEAR() 运算，索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- 改写为范围条件，可走 created_at 上的索引
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 大小写不敏感匹配：不要用 UPPER(col)，用不区分大小写的排序规则
SELECT * FROM users WHERE name = 'tom'; -- 列为 _ci 排序规则时天然可用索引表达式索引写法
表达式索引写法
-- MySQL 8.0.13+ 函数索引：表达式要用括号包起来
ALTER TABLE orders ADD INDEX idx_year ((YEAR(created_at)));
SELECT * FROM orders WHERE YEAR(created_at) = 2024; -- 与索引表达式一致才命中
-- PostgreSQL 表达式索引
CREATE INDEX idx_orders_year ON orders ((EXTRACT(YEAR FROM created_at)));
-- 查询里也要用同样的 EXTRACT 写法，换成别的等价形式不会命中失效写法与替代
| 失效写法 | 问题 | 替代写法 |
|---|---|---|
| YEAR(col) = 2024 | 列被函数包裹 | col >= '2024-01-01' AND col < '2025-01-01' |
| DATE(col) = '2024-06-01' | 同上 | col >= '2024-06-01' AND col < '2024-06-02' |
| UPPER(col) = 'ABC' | 列被函数包裹 | 用不区分大小写的排序规则，或建表达式索引 |
| col + 1 = 10 | 列参与算术运算 | col = 9 |
| CAST(col AS CHAR) = '10' | 类型转换包住列 | 把常量转成列的类型 |
| LIKE '%abc' | 前置通配无法定位 | 补一列后缀列，或改用全文检索 |
典型使用场景
- 日期条件改写：把按年、按月的过滤换成范围条件
- 大小写匹配：优先用排序规则而不是把列包进 UPPER
- 类型统一：把常量转成列的类型，而不是反过来
- 表达式索引评估：确属高频查询再建，避免索引膨胀
- 慢查询排查：先看 WHERE 里的列是不是被运算了
- 跨库迁移：检查原来依赖函数索引的语句在新库是否还有对应索引
建了表达式索引却仍写原条件是最常见的白干：索引里存的是表达式的值，查询必须写出一模一样的表达式才会被匹配，上线前先用执行计划确认。
在列上套函数不仅让索引失效，还可能引入隐式类型转换与结果偏差。比较前先确认两侧类型，把转换放在常量一侧，既安全也更快。