函数索引为何不被查询优化器选用？

Question

为何在WHERE条件中使用函数时，函数索引未被查询优化器选用？例如，对upper(name)建立函数索引后，执行查询WHERE upper(name) = 'JOHN'却未走索引。可能原因包括：统计信息不准确、选择性差、或优化器估算成本高于全表扫描。此外，部分数据库版本对函数索引的支持存在限制，执行计划需结合实际环境分析。

Answer 1

为何在WHERE条件中使用函数时，函数索引未被查询优化器选用？

1. 函数索引的基本概念与工作原理

函数索引（Function-Based Index, FBI）是数据库系统中一种特殊的索引类型，允许在表达式或函数结果上创建索引。例如，在Oracle中可以执行：

CREATE INDEX idx_upper_name ON employees(UPPER(name));

该索引能加速如 WHERE UPPER(name) = 'JOHN' 这类查询。其核心机制是将函数计算后的值存储在索引结构中，从而避免对每行数据实时计算。

然而，即使建立了函数索引，查询优化器仍可能选择不使用它，这涉及多个层面的技术因素。

2. 常见原因分析：从表层到深层

• 统计信息不准确：优化器依赖统计信息评估执行成本。若表的行数、数据分布或索引选择性未更新，可能导致错误的成本估算。
• 选择性差：如果 UPPER(name) 的值高度重复（如大量用户名为 "John"），索引的选择性降低，全表扫描反而更高效。
• 优化器成本估算高于全表扫描：即使索引可用，若预估需访问大量索引条目并回表，优化器可能判定全表扫描成本更低。
• 数据库版本限制：某些旧版数据库（如早期MySQL）不支持函数索引，或仅部分支持（如PostgreSQL需表达式完全匹配）。
• 查询写法不匹配索引定义：例如索引基于 UPPER(name)，但查询使用了 upper(trim(name))，表达式不一致导致无法命中。

3. 深入剖析：优化器决策流程

现代数据库优化器采用基于成本的模型（CBO），其决策过程如下图所示：

graph TD
    A[解析SQL语句] --> B{是否存在匹配索引?}
    B -->|是| C[获取统计信息]
    B -->|否| D[选择全表扫描]
    C --> E[估算索引扫描成本]
    E --> F[估算回表成本]
    F --> G[总成本 vs 全表扫描成本]
    G -->|索引成本低| H[选用函数索引]
    G -->|全表成本低| I[放弃索引，全表扫描]

此流程揭示了为何即使存在函数索引，也可能被跳过——关键在于“成本”权衡。

4. 实际案例与验证方法

假设我们有以下场景：

字段	类型	索引类型	样本数据分布
name	VARCHAR(50)	无	10万行，'john'出现8万次
UPPER(name)	函数索引	B-Tree	高重复率

执行计划可能显示：

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE UPPER(name) = 'JOHN';

-- 输出：
-- Id | Operation         | Name           | Cost
-- 0  | SELECT STATEMENT  |                | 1200
-- 1  |  TABLE ACCESS FULL| EMPLOYEES      | 1200

尽管索引存在，但因选择性极低，优化器判断全表扫描更优。

5. 解决方案与最佳实践

1. 更新统计信息：定期执行 ANALYZE TABLE 或 DBMS_STATS.GATHER_TABLE_STATS。
2. 提升选择性：结合其他高选择性字段构建复合函数索引，如 (UPPER(name), dept_id)。
3. 强制使用索引（谨慎）：使用 Hint 如 Oracle 中的 /*+ INDEX(employees idx_upper_name) */。
4. 重构查询逻辑：在应用层统一处理大小写，存储标准化数据，避免运行时函数调用。
5. 检查数据库版本兼容性：确认当前版本是否完整支持所用函数索引语法。
6. 使用虚拟列索引（如MySQL 5.7+）：创建持久化虚拟列并建索引，提高稳定性。

此外，可通过启用跟踪功能（如Oracle的10053 trace）深入分析优化器决策依据。