基于JpaSpecificationExecutor优化MySQL混合文本JSON列模糊搜索方案咨询

MySQL 5.7.22数据库中，表t1的c1列类型为varchar(400)，同时存储纯文本（如"abcd"、"12345.6"）和JSON格式数据（如{"key1":"value1"}、{"key1":"value1","key2":"value2"}）。需要实现模糊搜索逻辑，支持用户输入纯文本片段（如"34"、"cd"）、完整JSON、JSON片段（如{"key1":"value1"}、"value1"}）进行匹配。

当前使用Spring Data JPA 2.3.3的JpaSpecificationExecutor实现多列搜索，针对c1列采用criteriaBuilder.like(c1, "%" + value + "%")，但%value%的模糊匹配会触发全表扫描，性能极差。

1. 利用MySQL全文索引（优先推荐） #

MySQL 5.7及以上支持对varchar和JSON字段创建全文索引，能大幅提升模糊搜索性能，避免全表扫描。

步骤1：创建全文索引 #

针对c1列创建全文索引：

ALTER TABLE t1 ADD FULLTEXT INDEX ft_idx_c1 (c1);

如果需要支持短词搜索（如用户输入的"34"、"cd"这类2字符的内容），需要修改MySQL配置：

• 修改my.cnf（或my.ini）：

  [mysqld]
  ft_min_word_len=2  # 设置最小索引词长度为2
  ft_stopword_file=''  # 禁用停用词（可选，根据需求调整）
  

• 重启MySQL后，重建全文索引：

  ALTER TABLE t1 DROP INDEX ft_idx_c1;
  ALTER TABLE t1 ADD FULLTEXT INDEX ft_idx_c1 (c1);
  

如果是中文搜索，还需要安装ngram分词器并指定索引使用该分词器：

ALTER TABLE t1 ADD FULLTEXT INDEX ft_idx_c1 (c1) WITH PARSER ngram;

步骤2：Spring Data JPA中实现查询 #

方式1：使用@Query

在Repository接口中定义查询方法：

@Repository
public interface T1Repository extends JpaRepository<T1, Long>, JpaSpecificationExecutor<T1> {
    @Query(value = "SELECT * FROM t1 WHERE MATCH(c1) AGAINST(?1 IN BOOLEAN MODE)", nativeQuery = true)
    List<T1> fuzzySearchByC1(String searchValue);
}

说明：IN BOOLEAN MODE支持更灵活的匹配规则，比如+value1表示必须包含，value1*表示前缀匹配等，适合模糊搜索场景。

方式2：在Specification中集成

public class T1Specifications {
    public static Specification<T1> fuzzySearchC1(String searchValue) {
        return (root, query, criteriaBuilder) -> {
            // 绑定参数避免SQL注入
            return criteriaBuilder.sqlRestriction("MATCH(c1) AGAINST(?1 IN BOOLEAN MODE)", searchValue, String.class);
        };
    }
}

2. 针对JSON字段创建虚拟列索引 #

如果用户经常搜索JSON中的特定键值对（如key1的值），可以创建虚拟生成列并添加索引，单独优化JSON部分的搜索性能。

步骤1：创建虚拟列和索引 #

比如针对JSON中的key1字段创建虚拟列：

ALTER TABLE t1 ADD COLUMN c1_key1 VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(c1, '$.key1'))) VIRTUAL;
CREATE INDEX idx_c1_key1 ON t1(c1_key1);

如果需要模糊匹配key1的值，也可以给虚拟列创建全文索引：

ALTER TABLE t1 ADD FULLTEXT INDEX ft_idx_c1_key1 (c1_key1);

步骤2：Spring Data JPA中查询 #

在Specification中针对虚拟列做查询：

public static Specification<T1> searchJsonKey1(String value) {
    return (root, query, criteriaBuilder) -> {
        // 前缀匹配可走索引，全模糊仍需配合全文索引
        return criteriaBuilder.like(root.get("c1Key1"), "%" + value + "%");
    };
}

3. 分场景处理搜索请求 #

根据用户输入的内容类型（纯文本/JSON片段），分支执行不同的查询逻辑，进一步优化性能：

1. 判断输入类型：检查输入是否包含{和}，判断是否为JSON片段；
2. 纯文本场景：使用全文索引的MATCH AGAINST查询；
3. JSON片段场景：解析出键值对，使用虚拟列索引或JSON函数查询。

示例代码：

public static Specification<T1> dynamicFuzzySearch(String searchValue) {
    return (root, query, criteriaBuilder) -> {
        if (searchValue.contains("{") && searchValue.contains("}")) {
            // 解析JSON片段提取键值（这里以key1为例，实际可通过Jackson/Gson解析任意键）
            String targetValue = extractJsonValue(searchValue, "key1");
            return criteriaBuilder.like(root.get("c1Key1"), "%" + targetValue + "%");
        } else {
            // 纯文本用全文索引
            return criteriaBuilder.sqlRestriction("MATCH(c1) AGAINST(?1 IN BOOLEAN MODE)", searchValue, String.class);
        }
    };
}

// 辅助方法：从JSON片段中提取指定键的值
private static String extractJsonValue(String jsonStr, String key) {
    ObjectMapper mapper = new ObjectMapper();
    try {
        JsonNode node = mapper.readTree(jsonStr);
        return node.has(key) ? node.get(key).asText() : jsonStr;
    } catch (JsonProcessingException e) {
        // 解析失败按纯文本处理
        return jsonStr;
    }
}

4. 大数量级场景：引入Elasticsearch #

如果数据库数据量较大（百万级以上），或者搜索需求复杂（如多字段联合模糊搜索、JSON深度查询），建议将数据同步到Elasticsearch：

1. 数据同步：通过MySQL binlog监听（如Canal）、定时任务或Spring Data事件监听，将t1表数据同步到ES；
2. Spring Data Elasticsearch集成：定义ES的Repository接口，使用@Query或原生DSL实现模糊搜索；
3. 优势：ES的搜索性能远优于MySQL的模糊匹配，支持复杂的JSON嵌套查询、分词搜索等。

• 全文索引不适合%value%这种前后都通配的场景，但MATCH AGAINST的前缀匹配（如value*）性能远高于LIKE；
• 虚拟列只支持MySQL 5.7及以上版本，VIRTUAL类型不占存储空间，查询时计算；STORED类型会存储数据，占用空间但查询更快；
• 所有查询要注意SQL注入风险，必须使用Spring Data JPA的参数绑定方式，避免直接拼接SQL。

内容的提问来源于stack exchange，提问作者rahulP