PostgreSQL / 系统课时
创建表达式索引
创建表达式索引
本课解决“查询对列做了函数处理,普通索引为什么失效”的问题。你将为表达式结果建立索引,并让查询条件与索引表达式保持一致。
学习目标
- 理解普通列索引与表达式索引的区别
- 为常用函数结果创建表达式索引
- 验证查询表达式是否精确匹配索引定义
为什么普通索引可能无法使用
假设已经为 email 创建普通索引,但查询写成 lower(email) = 'alice@example.com'。数据库需要比较的是函数计算结果,而普通索引保存的是原始邮箱值,两者不是同一个索引键。
表达式索引会在写入或更新时计算表达式,并把结果保存到索引中。查询再次使用相同表达式时,就能直接搜索这些预先计算的键值。
为 lower(email) 建立索引
-- 重建演示表,保证脚本可以重复运行
DROP TABLE IF EXISTS demo_users;
CREATE TABLE demo_users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email varchar(200) NOT NULL,
display_name varchar(100) NOT NULL
);
-- 邮箱故意混合大小写,模拟规范化查询
INSERT INTO demo_users (email, display_name)
SELECT
'User' || g || '@Example.com',
'user-' || g
FROM generate_series(1, 10000) AS g;
-- 索引键保存 lower(email) 的计算结果
CREATE INDEX idx_demo_users_lower_email
ON demo_users (lower(email));
ANALYZE demo_users;
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id, email, display_name
FROM demo_users
WHERE lower(email) = 'user5000@example.com';预期结果
执行计划的核心节点包含:
Index Scan using idx_demo_users_lower_email on demo_users
Index Cond: lower(email) = 'user5000@example.com'查询中的 lower(email) 与索引定义一致,因此能够使用表达式索引。
查询写法必须匹配表达式
表达式索引不是“模糊匹配”。如果索引定义为 lower(email),查询改成 upper(email)、trim(lower(email)) 或其他不同表达式,通常无法使用该索引。
常量一侧可以正常变化,例如不同的小写邮箱值仍能复用同一个索引。关键是被索引列上的表达式结构要一致。
表达式也可以组合
可以把表达式与普通列放进同一个联合索引,例如限制同一租户内邮箱忽略大小写后唯一。
例如,可使用片段 CREATE UNIQUE INDEX uq_users_tenant_lower_email ON users (tenant_id, lower(email)); 限制同一租户内的邮箱忽略大小写后唯一。该片段依赖现有 users 表以及 tenant_id、email 两列。生效后,同一租户内的 Alice@example.com 与 alice@example.com 会被视为重复值。
成本与适用边界
表达式索引能减少查询时的重复计算,但会增加写入成本,因为每次插入或相关列更新时都要重新计算表达式并维护索引。
应只为稳定、高频、可重复使用的表达式建立索引。对于偶发查询或高频变化字段,先通过执行计划确认收益。