4. 函数索引实战一：对字符串字段使用 upper/lower 函数实现大小写不敏感查询

4.1 问题场景：大小写敏感带来的查询陷阱

在业务系统中，用户输入往往不区分大小写，但 PostgreSQL 的字符串比较默认是大小写敏感的。例如，用户搜索 “john”、”John” 或 “JOHN” 时，期望返回相同的结果集。若直接对原始字段进行查询，会导致索引无法命中，甚至返回错误的结果。

典型失败案例： 表 users 包含 500 万行数据，字段 username 上建有普通 B-tree 索引。执行 SELECT * FROM users WHERE username = 'John'; 时，若实际存储为 “john”，则返回 0 行；若使用 WHERE LOWER(username) = 'john'，则索引失效，触发全表扫描。

4.2 核心原理：函数索引如何工作

PostgreSQL 允许在索引定义中包裹函数或表达式。当查询的 WHERE 条件使用完全相同的函数表达式时，优化器可以直接走索引扫描，而不是逐行计算函数后再过滤。

对比项 普通索引 函数索引
索引键 原始列值 函数处理后的值
查询写法 WHERE col = 'value' WHERE func(col) = 'value'
大小写不敏感 不支持（除非使用 citext） 支持（upper/lower 包裹）
索引匹配条件 查询条件必须与索引键完全一致 查询中的函数表达式必须与索引定义完全一致
函数索引执行流程：大小写不敏感查询 用户查询 WHERE LOWER(name)=‘john’ 查询解析器 识别函数表达式 优化器 匹配函数索引 函数索引扫描 idx_lower_name 索引条目 ‘john’ → ctid 列表 表堆访问 返回匹配行 关键条件 查询表达式必须与索引定义 完全一致（包括函数名）

4.3 实战步骤：创建函数索引

4.3.1 基础索引创建

-- 创建函数索引：对 username 字段应用 lower() 函数
CREATE INDEX idx_users_lower_username ON users (LOWER(username));

-- 验证索引是否创建成功
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users'
  AND indexname = 'idx_users_lower_username';

4.3.2 查询验证与执行计划

-- 使用函数索引的查询（必须与索引定义一致）
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users
WHERE LOWER(username) = 'john';

-- 输出示例（关键行）：
-- Index Scan using idx_users_lower_username on users
--   Index Cond: (lower((username)::text) = 'john'::text)
--   Buffers: shared hit=3

-- 错误写法（不会使用函数索引）
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users
WHERE LOWER(username) = 'JOHN';  -- 参数大小写不同，但函数索引仍可命中
💡 最佳实践： 建议在查询中统一使用 LOWER() 函数，并将参数也转为小写。这样无论用户输入 “John”、“JOHN” 还是 “john”，都能命中索引。应用层应负责将查询参数标准化。

4.4 性能对比：函数索引 vs 全表扫描

测试场景 执行计划 耗时（500万行） 缓冲区命中
无索引 + LOWER() Seq Scan ~ 3200 ms shared hit=45000
普通索引 + 精确匹配 Index Scan ~ 0.3 ms shared hit=4
函数索引 + LOWER() Index Scan ~ 0.4 ms shared hit=4

4.5 注意事项与避坑指南

  • 函数一致性： 索引定义使用 LOWER()，查询也必须使用 LOWER()，不能混用 UPPER()。建议团队统一规范。
  • 索引维护成本： 函数索引在插入/更新时需要计算函数值，写入性能略低于普通索引。对于写密集场景，需评估吞吐量影响。
  • NULL 值处理： 函数索引不存储 NULL 值（除非使用 COALESCE 包裹），查询 WHERE LOWER(username) IS NULL 不会走索引扫描。
  • 表达式长度限制： 函数索引的表达式不能超过 2KB，复杂的字符串处理可能需要拆解。
  • 替代方案： 如果整个应用都需要大小写不敏感，可考虑 citext 扩展模块，但函数索引更灵活，适用于特定字段。
⚠️ 常见错误： 在查询中使用 WHERE UPPER(username) = 'JOHN'，但索引定义是 LOWER(username)。优化器不会自动转换，必须严格匹配。建议在索引定义和查询中统一使用 LOWER()，并将参数转为小写。

4.6 扩展：多字段函数组合索引

当查询条件涉及多个字段且都需要大小写不敏感时，可以创建组合函数索引：

-- 创建组合函数索引
CREATE INDEX idx_users_lower_name_email
ON users (LOWER(username), LOWER(email));

-- 查询示例
SELECT * FROM users
WHERE LOWER(username) = 'john'
  AND LOWER(email) = 'john@example.com';

-- 也可以只使用前缀字段
SELECT * FROM users
WHERE LOWER(username) = 'john';  -- 仍可走索引

组合函数索引遵循 B-tree 的最左前缀原则。如果查询只使用第二个字段 LOWER(email)，则无法使用该索引，需要单独为 email 创建函数索引。

← 上一章 📖 返回目录 下一章 →