PostgreSQL / 系统课时

使用表达式索引

使用表达式索引

当查询条件对列先做 lower、拼接或其他稳定计算时,普通列索引未必能直接命中。本课用实际 SQL 建立表达式索引,验证查询计划,并掌握函数稳定性、维护成本和查询写法等设计边界。

学习目标

  • 能识别适合使用表达式索引的查询条件,并写出正确的 CREATE INDEX。
  • 能通过 EXPLAIN 验证表达式是否被优化器匹配。
  • 能判断表达式索引的维护成本与使用边界。

先看普通索引为什么可能失效

假设用户表中保存原始邮箱,但业务查询不区分大小写:

sql
CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL
);

CREATE INDEX users_email_idx ON users (email);

INSERT INTO users (email)
VALUES ('Alice@example.com'), ('bob@example.com'), ('CAROL@example.com');

EXPLAIN (COSTS OFF)
SELECT id, email
FROM users
WHERE lower(email) = 'alice@example.com';

这里查询的是 lower(email) 的结果,而索引键保存的是原始 email。仅有 users_email_idx 时,优化器通常不能把这个条件直接等价成对原始索引键的普通等值查找。

建立表达式索引

可以把查询中稳定重复使用的计算结果直接作为索引键:

sql
CREATE INDEX users_lower_email_idx ON users (lower(email));

EXPLAIN (COSTS OFF)
SELECT id, email
FROM users
WHERE lower(email) = 'alice@example.com';

预期结果

text
Index Scan using users_lower_email_idx on users
  Index Cond: (lower(email) = 'alice@example.com'::text)

实际计划可能因数据量、统计信息和成本参数不同而选择其他路径,因此验证重点不是强求某一种节点,而是确认条件能够被表达式索引作为 Index Cond 使用。数据量很小时,顺序扫描仍可能更便宜。

表达式必须与查询语义一致

表达式索引不是“把函数加到索引上就行”。查询中的表达式必须与索引表达式在语义上匹配。例如索引定义为 lower(email),最稳定的使用方式就是查询时也写 lower(email)。把查询改成其他转换、隐式类型转换或不同函数,未必还能得到同样的索引路径。

表达式中使用的函数和操作符还必须满足索引定义要求,其中关键约束是结果应具有 immutable 语义,即相同输入不会因为时间、会话环境或外部数据变化而得到不同结果。因此,不要把依赖当前时间或外部状态的计算随意放进索引表达式。

什么时候值得使用

表达式索引最适合“查询频繁、表达式固定、写入成本可以接受”的场景。它把计算成本从查询阶段部分转移到插入和相关更新阶段:表达式结果会存入索引,因此写入时需要额外计算并维护索引。

例如,下面两类需求通常比较合适:

  • 大量请求都按 lower(email) 做大小写不敏感匹配。
  • 经常按 first_name || ' ' || last_name 这样的派生值定位记录。

不适合为了一个偶发查询建立表达式索引,尤其是表达式复杂、表写入频繁、索引收益又很小的情况。索引本身会增加存储、写放大和后续维护成本。

用唯一表达式索引强化约束

表达式索引还可以承担普通 UNIQUE 无法直接表达的约束。例如业务要求邮箱大小写不同也视为重复,可以建立唯一表达式索引:

sql
CREATE UNIQUE INDEX users_lower_email_uniq ON users (lower(email));

INSERT INTO users (email)
VALUES ('ALICE@example.com');

预期结果

text
ERROR:  duplicate key value violates unique constraint "users_lower_email_uniq"
DETAIL:  Key (lower(email))=(alice@example.com) already exists.

这说明索引约束的是表达式结果,而不是原始字符串的字面值。

设计表达式索引时,最重要的判断不是“函数能不能建索引”,而是“这个表达式是否稳定、是否高频、是否能与真实查询长期保持一致”。先用 EXPLAIN 验证收益,再决定是否承担额外的写入和存储成本。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

使用表达式索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通