函数操作索引列会破坏B+树有序性导致全表扫描;应将函数移至等号右侧或转为范围查询,复合索引需高区分度字段前置,并用EXPLAIN ANALYZE验证命中。

函数操作索引列会直接破坏B+树的有序结构,优化器无法用索引值做快速定位,只能退化为全表扫描。
WHERE中对索引列调用函数(如UPPER、DATE、SUBSTR)
数据库索引存储的是字段原始值,不是函数计算后的结果。一旦写成 WHERE UPPER(name) = 'JOHN' 或 WHERE DATE(create_time) = '2024-01-01',优化器就无法把索引页里的 name 值直接和 'JOHN' 比对——它得先把每行的 name 拿出来算一遍 UPPER(),再比较,等价于全表扫。
- 改写方向是把函数“挪到右边”或“转成范围”:用
WHERE name = 'john'(配合大小写不敏感 collation)替代UPPER(name) = 'JOHN' - 日期类优先用闭区间:
WHERE create_time >= '2024-01-01' AND create_time ,比 <code>DATE(create_time) = ...稳定走索引 - 若业务强依赖函数逻辑(比如总要查年份),可建函数索引:
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM order_date))(PostgreSQL/MySQL 8.0+ 支持),但会增加写开销
隐式类型转换等效于在索引列上套CAST()
字段是 INT,却传字符串参数:WHERE user_id = '123',MySQL 会自动转成 CAST(user_id AS CHAR) = '123',本质还是对索引列做了函数操作。PostgreSQL 更严格,可能直接报错或拒绝隐式转换。
- 应用层绑定参数时必须类型一致:Java 用
setLong()绑INT列,别用setString() - SQL 中显式转换右侧值:
WHERE device_id = '12345678'(device_id是VARCHAR)或WHERE user_id = 123::INTEGER(PostgreSQL) - 检查字符集是否统一:
character_set_client和字段 collation 不一致也会触发隐式转换
复合索引未满足最左前缀,或字段顺序不合理
索引 (status, created_at) 只对 WHERE status = ? 或 WHERE status = ? AND created_at > ? 有效;若只写 WHERE created_at > ?,索引完全失效。更隐蔽的是低选择性字段放前面,比如 (status, user_id),status 只有 0/1 两个值,索引项大量重复,优化器大概率跳过。
- 按高频查询条件重建索引:如果常查
WHERE user_id = ? ORDER BY created_at,就建(user_id, created_at) - 高区分度字段前置:把
user_id放复合索引左边,status放右边 - 验证是否命中:用
EXPLAIN ANALYZE看Index Cond是否出现对应字段,而不是只看key非空
真正卡住性能的往往不是“用了函数”,而是函数掩盖了查询语义失真——比如本该查精确时间点,却写成 DATE() 截断后匹配,导致索引失效的同时还扩大了结果集。改写时先想清楚业务意图,再选技术路径。

















