3. 函数索引概念：什么是函数索引？与普通索引的区别，创建语法与使用限制

3.1 函数索引的定义

函数索引（Functional Index），又称表达式索引（Expression Index），是 PostgreSQL 中一种特殊的索引类型。它不直接索引表的原始列值，而是索引对一列或多列应用某个函数或表达式后的计算结果。

当查询的 WHERE 条件、ORDER BY 或 JOIN 条件中包含对列的函数运算时，普通索引无法被使用，而函数索引可以精确匹配该表达式，实现索引扫描。

3.2 与普通索引的核心区别

3.3 创建语法

函数索引的创建语法与普通索引几乎相同，只需将列名替换为括号包裹的表达式：

-- 基本语法
CREATE INDEX idx_name ON table_name (expression);

-- 示例 1：对用户名字段创建大小写不敏感的索引
CREATE INDEX idx_users_lower_name ON users (LOWER(name));

-- 示例 2：对订单创建时间按日期截断的索引
CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));

-- 示例 3：对 JSONB 字段中的某个键创建索引
CREATE INDEX idx_users_email ON users ((data->>'email'));

-- 示例 4：多列混合表达式索引
CREATE INDEX idx_full_name ON employees (LOWER(last_name) || ' ' || LOWER(first_name));

3.4 使用限制与注意事项

• 表达式必须稳定（IMMUTABLE）：索引使用的函数必须是 IMMUTABLE 类型，即对于相同的输入始终返回相同的结果。例如 LOWER()、DATE()、ABS() 是稳定的；而 RANDOM()、CURRENT_TIMESTAMP 不允许用于函数索引。
• 查询必须精确匹配表达式：优化器只会在查询的 WHERE 条件与索引定义中的表达式完全一致时使用该索引。例如索引定义为 LOWER(name)，查询写 WHERE LOWER(name) = 'alice' 会命中索引，但 WHERE LOWER(TRIM(name)) = 'alice' 不会。
• 索引维护开销：每次 INSERT、UPDATE、DELETE 时，数据库都需要重新计算表达式并更新索引，写入性能会受影响。
• 索引大小与类型：函数索引支持 B-tree、Hash、GiST、GIN 等多种索引类型，但默认使用 B-tree。表达式结果的数据类型决定了索引的适用操作（如 B-tree 支持排序和范围查询，Hash 仅支持等值查询）。
• NULL 处理：如果表达式结果为 NULL，该行不会被包含在索引中（B-tree 默认行为），除非使用 NULLS NOT DISTINCT 选项（PostgreSQL 15+）。

3.5 函数索引工作原理示意图

3.6 实战验证：函数索引是否生效

使用 EXPLAIN 命令可以验证函数索引是否被查询使用：

-- 创建测试表
CREATE TABLE test_users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 插入测试数据
INSERT INTO test_users (name)
SELECT 'User_' || generate_series(1, 100000);

-- 创建函数索引
CREATE INDEX idx_lower_name ON test_users (LOWER(name));

-- 验证查询计划
EXPLAIN ANALYZE
SELECT * FROM test_users WHERE LOWER(name) = 'user_50000';

-- 预期输出（索引扫描）：
-- Index Scan using idx_lower_name on test_users  (cost=0.29..8.31 rows=1 width=36)
--   Index Cond: (lower(name) = 'user_50000'::text)