添加REPLACE后的HQL查询性能变慢，如何优化？

我们通过定时任务（cronjob）同步产品到数据库，需要实现搜索时忽略名称字段的空格（比如输入XDF51241L能匹配到名称为XDF 51241 L的产品），且不能改动sku和ean字段。但在HQL中添加REPLACE处理名称字段后，查询性能暴跌。

原查询（未加REPLACE） #

where.append(" and (p.sku like :search or pd.name like :search or p.ean like :search ");
where.append(" or EXISTS(select 1 from ProductFeature pfe where pfe.product=p and (pfe.name like :search or pfe.value like :search))) ");

q.setParameter("search", "%"+productCriteria.getSearch()+"%");

添加REPLACE后的查询 #

where.append(" and (p.sku like :search or pd.name like :search or p.ean like :search ");
where.append(" or REPLACE(pd.name, ' ', '') like :search ");
where.append(" or EXISTS(select 1 from ProductFeature pfe where pfe.product=p and (pfe.name like :search or pfe.value like :search))) ");

q.setParameter("search", "%" + productCriteria.getSearch().replaceAll("\\s+", "") + "%");

优化方案 #

1. 新增冗余字段存储无空格名称

• 在产品详情表中新增name_without_spaces字段，类型与原name字段一致。
• 利用现有cronjob同步数据时，提前把原名称的所有空格移除，存入这个冗余字段；产品新增/更新时也同步维护该字段的值。
• 修改HQL查询，直接用这个字段匹配去空格后的搜索词，彻底去掉REPLACE操作：

  where.append(" and (p.sku like :search or pd.name like :search or p.ean like :search ");
  where.append(" or pd.name_without_spaces like :searchNoSpace ");
  where.append(" or EXISTS(select 1 from ProductFeature pfe where pfe.product=p and (pfe.name like :search or pfe.value like :search))) ");
  
  q.setParameter("search", "%"+productCriteria.getSearch()+"%");
  q.setParameter("searchNoSpace", "%" + productCriteria.getSearch().replaceAll("\\s+", "") + "%");
  

• 给name_without_spaces字段添加普通索引，进一步加速查询。

2. 使用数据库函数索引（部分数据库支持）

• 不想新增字段的话，可以直接给REPLACE(pd.name, ' ', '')创建函数索引，不同数据库语法如下：
  • MySQL：CREATE INDEX idx_product_name_nospace ON product_detail (REPLACE(name, ' ', ''));
  • PostgreSQL：CREATE INDEX idx_product_name_nospace ON product_detail USING btree (REPLACE(name, ' ', ''));
  • Oracle：CREATE INDEX idx_product_name_nospace ON product_detail (REPLACE(name, ' ', ''));
• 注意：函数索引会增加数据写入时的开销（新增/更新产品要维护索引），但能让原查询的REPLACE条件用上索引，大幅提升性能。

3. 优化搜索词匹配逻辑

• 先对用户输入的搜索词做拆分：保留原词模式，同时生成去空格模式。优先用原词执行带索引的查询，若结果不足，再补充去空格的匹配查询，合并结果。
• 这种方式适合大部分搜索是带空格的场景，能减少不必要的全表扫描。

4. 引入全文检索引擎（复杂场景首选）

• 如果还有其他搜索需求（比如多字段模糊匹配、同义词检索等），可以引入Elasticsearch这类全文检索工具。
• 同步产品数据到数据库时，同时把数据同步到Elasticsearch，在ES中给名称字段配置分析器，自动去掉空格建立索引。查询时直接调用ES接口，性能远优于数据库模糊查询。

内容的提问来源于stack exchange，提问作者Muhammed Hakan ÇELİK