SQL 中表达式索引是否有效？

PostgreSQL表达式索引仅在查询中表达式与索引定义字面完全一致时生效，要求IMMUTABLE函数；MySQL需通过STORED虚拟列间接实现；SQL Server依赖PERSISTED计算列；跨库迁移时极易失效，须用EXPLAIN验证。

PostgreSQL 的表达式索引确实有效，但仅限于查询条件与索引定义完全匹配的场景

PostgreSQL 支持在 CREATE INDEX 中使用表达式（如函数调用、运算、类型转换），这类索引能加速特定模式的查询。但它不会自动“覆盖”语义等价的其他写法——优化器只在 WHERE 或 ORDER BY 子句中出现**字面一致**的表达式时才会考虑使用它。

常见错误现象：SELECT * FROM users WHERE lower(name) = 'alice' 能走 lower(name) 索引；但 WHERE name::text ILIKE 'alice' 或 WHERE name ~* '^alice$' 就不能，哪怕语义相同。

• 必须确保查询中使用的表达式与索引定义逐字符一致（包括空格、括号、函数名大小写）
• 支持的函数需标记为 IMMUTABLE，否则建索引会报错：ERROR: index expression must be immutable
• 表达式索引不支持 INCLUDE 列，也不能用于唯一约束（除非表达式本身保证唯一性且声明为 UNIQUE）

MySQL 不支持标准表达式索引，8.0+ 的函数索引是有限替代方案

MySQL 8.0 引入了“函数索引”，但本质是基于虚拟列（GENERATED COLUMN）的间接实现。你不能直接写 INDEX (UPPER(email))，而必须先添加虚拟列，再对其建索引：

ALTER TABLE users ADD COLUMN email_upper VARCHAR(255) 
  GENERATED ALWAYS AS (UPPER(email)) STORED;
CREATE INDEX idx_email_upper ON users(email_upper);

这意味着：查询必须显式引用该虚拟列才能命中索引，例如 WHERE email_upper = 'JOE@EXAMPLE.COM'；直接写 WHERE UPPER(email) = 'JOE@EXAMPLE.COM' 通常无法利用该索引（除非优化器做特殊重写，实际中极少触发）。

• 虚拟列必须声明为 STORED 才能被索引（VIRTUAL 列不可索引）
• UPPER()、TRIM()、DATE() 等常见函数可用，但自定义函数不行
• 索引字段长度受虚拟列定义限制，比如 VARCHAR(100) 虚拟列无法安全承载超长原始值的 UPPER() 结果

SQL Server

的计算列索引需要 PERSISTED 才可靠

SQL Server 通过计算列 + 索引模拟表达式索引。关键点在于：计算列默认是 VIRTUAL（运行时计算），只有标记为 PERSISTED 后，SQL Server 才允许在其上创建索引，并保证查询中使用相同表达式时能命中。

示例：

ALTER TABLE orders ADD total_amount AS quantity * unit_price PERSISTED;
CREATE INDEX ix_orders_total_amount ON orders(total_amount);

此时 WHERE quantity * unit_price > 1000 可能走索引，但前提是优化器识别出该表达式与计算列定义完全一致。实际中更稳妥的做法是直接查计算列：WHERE total_amount > 1000。

• 未加 PERSISTED 的计算列无法建索引
• 计算列表达式必须是 DETERMINISTIC（如不能含 GETDATE()、NEWID()）
• 索引统计信息基于存储值，若底层字段更新频繁，需注意统计信息陈旧导致计划退化

跨数据库迁移时，表达式索引是最容易失效的优化项之一

不同数据库对“表达式等价性”的判断逻辑差异极大：PostgreSQL 最严格（字面匹配），MySQL 最受限（依赖虚拟列命名），SQL Server 居中但依赖 PERSISTED 和确定性。同一段带函数的查询，在一个库跑得飞快，在另一个库可能全表扫描。

真正容易被忽略的是：即使语法能建成功，也未必能在业务查询中生效。上线前务必用 EXPLAIN（或对应平台的执行计划工具）确认索引是否真实被选中，而不是仅看 CREATE INDEX 是否成功。