PG之LIKE查询索引问题

Hort@IT 字数: 4214 阅读耗时: 10 分钟 2025/06/19 博客独享热度: 6 评论: 0

为什么直接写 LIKE '%xxx%' 可以走索引（比如 trgm 索引），但用 LIKE CONCAT('%', #{xxx}, '%') 或等价表达式就不走索引了？

在 PostgreSQL 中，使用 LIKE CONCAT('%', #{xxx}::text, '%') 这种动态拼接的模糊查询 不走索引 的原因，与 PostgreSQL 的查询优化器逻辑、索引类型以及 SQL 表达式的解析方式密切相关。以下是详细解释：

1. 优化器无法解析动态表达式

PostgreSQL 的查询优化器在生成执行计划时，需要对查询条件进行 静态分析，以确定是否可以利用索引。

直接写 LIKE '%xxx%'：优化器可以明确识别这是一个 固定模式 的模糊匹配，如果存在 pg_trgm 索引（如 GIN 或 GiST 索引），可以直接使用索引加速查询。
使用 CONCAT('%', #{xxx}::text, '%')：这是一个 动态表达式，优化器无法在编译时确定其最终结果（例如是否为 %xxx%），因此无法判断是否适合使用 pg_trgm 索引。即使你创建了相关索引，优化器也会认为这是一个普通表达式，不会触发索引扫描。

2. `pg_trgm` 索引的限制

pg_trgm 是 PostgreSQL 的扩展模块，专门用于支持 模糊匹配（如 LIKE '%xxx%'）。

需要显式创建索引：你需要先安装 pg_trgm 模块，并创建 GIN 或 GiST 索引。例如：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_user_name_gin ON users USING GIN (user_name gin_trgm_ops);

只有固定模式能触发索引
- ✅ WHERE user_name LIKE '%xxx%' → 走索引（因为优化器能识别模式）。
- ❌ WHERE user_name LIKE CONCAT('%', 'xxx', '%') → 不走索引（因为优化器无法解析动态表达式）。

3. 如何验证是否使用了索引？

可以通过 EXPLAIN ANALYZE 查看执行计划：

-- 直接写模式（可能走索引）
EXPLAIN ANALYZE
SELECT * FROM users WHERE user_name LIKE '%xxx%';

-- 使用 CONCAT（可能不走索引）
EXPLAIN ANALYZE
SELECT * FROM users WHERE user_name LIKE CONCAT('%', 'xxx', '%');

如果输出中出现 Index Scan using idx_user_name_gin，则表示使用了索引。
如果输出是 Seq Scan（顺序扫描），则表示未使用索引。

4. 解决方案

✅ 方案 1：在应用层拼接完整模式

将模糊匹配的完整模式（如 %xxx%）直接作为参数传入 SQL，避免使用 CONCAT：

-- 应用层拼接模式（推荐）
SELECT * FROM users WHERE user_name LIKE #{pattern}; -- pattern = "%xxx%"

这样优化器可以识别为固定模式，并正确使用 pg_trgm 索引。

✅ 方案 2：使用 `pg_trgm` 的函数索引

如果必须使用动态拼接，可以尝试创建 函数索引（但需要确保模式格式一致）：

-- 创建函数索引（不推荐，灵活性差）
CREATE INDEX idx_user_name_trgm ON users 
  USING GIN (user_name gin_trgm_ops);

但此方法仍需优化器识别模式，无法完全解决动态表达式的问题。

✅ 方案 3：改用全文搜索（tsvector）

如果查询模式复杂或性能要求极高，可以改用 PostgreSQL 的 全文搜索功能（tsvector），结合 to_tsvector 和 to_tsquery 实现更高效的模糊匹配。

5. 为什么直接写 `LIKE '%xxx%'` 走索引？

固定模式：'%xxx%' 是一个静态字符串，优化器可以明确识别其为模糊匹配模式。
索引适配：pg_trgm 索引专为这种模式设计，能够通过 三元组（trigram） 快速定位匹配数据。
统计信息：PostgreSQL 的统计信息（如 ANALYZE）会记录字段的 trigram 分布，进一步优化索引扫描。

6. 总结对比

查询方式	是否走索引	原因
`LIKE '%xxx%'`	✅ 是	优化器识别为固定模式，可使用 `pg_trgm` 索引。
`LIKE CONCAT('%', 'xxx', '%')`	❌ 否	优化器无法解析动态表达式，无法触发索引。
应用层拼接 `LIKE '%xxx%'`	✅ 是	等效于直接写固定模式，可使用索引。

7. 补充建议

定期更新统计信息：运行 ANALYZE table_name;，确保优化器有最新的字段分布数据。
避免过度索引：过多索引会增加写操作开销，需权衡查询与写入需求。
监控执行计划：通过 EXPLAIN ANALYZE 持续验证索引是否生效。