SQL数据库函数下推限制_索引利用条件_技术教程

函数下推不保证索引可用，索引生效前提是WHERE中索引列以原始形式参与比较；如UPPER(name)='ALICE'无法走name索引，而name='alice'可以。

SQL数据库中函数下推（Function Pushdown）和索引利用是性能优化的关键环节，但二者存在天然张力：对字段施加函数操作常导致索引失效，即使该字段本身已建索引。理解其限制条件与触发索引的边界，才能写出真正高效的查询。

所谓“函数下推”，是指数据库将计算逻辑（如UPPER()、DATE()、SUBSTR()等）从应用层或执行器下推至存储层处理，以减少数据传输或利用底层加速能力。但这不意味着索引能被使用——索引是否生效，取决于WHERE子句中过滤列是否以“索引列本身”形式参与比较，而非其函数变换结果。

例如：

WHERE name = 'alice' → 若name有B-Tree索引，可走索引查找
WHERE UPPER(name) = 'ALICE' → 即使name有索引，也通常无法直接利用，因索引中存的是原始值，不是大写后值
WHERE created_at > DATE('2025-01-01') → 若created_at是DATETIME类型且有索引，DATE()会截断时间部分，导致范围扫描失效或退化为全表扫描

部分数据库（如MySQL 8.0+、PostgreSQL、TiDB）支持函数索引（Functional Index）或隐式可下推的确定性表达式，但需满足严格条件：

确定性（Deterministic）：相同输入永远返回相同输出，无随机、会话变量、当前时间等依赖
单调性（Monotonic）或可转换性：如WHERE YEAR(create_time) = 2025在MySQL中仍可能用上create_time索引（因优化器可重写为create_time BETWEEN '2025-01-01' AND '2025-12-31 23:59:59'），但MONTH(create_time) = 6一般不行
前缀函数 + 前缀索引匹配：如MySQL中对email建前缀索引INDEX idx_email (email(10))，则WHERE email LIKE 'abc%'可命中；但WHERE SUBSTR(email, 1, 3) = 'abc'通常不可

绕过函数导致的索引失效，核心思路是把函数从索引列移到参数侧，让索引列保持裸露参与比较：

大小写匹配 → 建立大小写不敏感的排序规则（如MySQL的utf8mb4_0900_as_cs）或函数索引：CREATE INDEX idx_name_upper ON t1 (UPPER(name))
日期范围 → 避免WHERE DATE(create_time) = '2025-01-01'，改用：WHERE create_time >= '2025-01-01' AND create_time
字符串前缀 → 用LIKE 'prefix%'而非LEFT(col, N) = 'prefix'；后者几乎总无法走索引
数值范围转换 → WHERE FLOOR(price) = 99应改为：WHERE price >= 99 AND price

不能只看执行计划里有没有“index”字样，要关注实际访问方式：

MySQL中用EXPLAIN FORMAT=JSON查key、rows、filtered及using_index是否为true
PostgreSQL中用EXPLAIN (ANALYZE, BUFFERS)，重点看Index Scan还是Seq Scan，以及Rows Removed by Filter比例
注意“索引覆盖扫描（Index Only Scan）”和“回表（Using index condition）”的区别：前者仅读索引页，后者还需回主键查数据页