300万条数据的Spring Boot原生查询耗时4分钟，如何优化性能？

你当前查询性能低下的核心原因是前导通配符的LIKE查询无法命中普通B+树索引，且跨表OR过滤+未建合适的联合索引导致全表扫描，300万数据下全表扫描自然耗时极长，可按以下优先级优化：

1. SQL结构重构 #

原有SQL先关联两张表再做OR过滤，很容易触发全表关联扫描，建议将OR条件拆分为两个独立子查询，用UNION ALL/UNION合并结果集，避免全表关联：

@Query(value = "SELECT p.* FROM products p WHERE p.id IN (" +
            "SELECT p1.id FROM products p1 " +
            "WHERE p1.product_name LIKE CONCAT('%',?1,'%') " +
            "AND p1.parent_product_id IS NULL " +
            "AND p1.is_active = true " +
            "AND (p1.is_laboratory IS NULL OR p1.is_laboratory = false) " +
            "UNION " +
            "SELECT p2.id FROM products p2 " +
            "JOIN product_generic_name pg ON pg.id = p2.product_generic_name_id " +
            "WHERE pg.product_generic_name LIKE CONCAT('%',?1,'%') " +
            "AND pg.is_active = true) "
        ,nativeQuery = true)
Page<Products> findByProductNameLikeAndGenericNameLike(String searchText, Pageable pageable);

如果确认两个子查询的结果不会重复，可以把UNION换成UNION ALL，跳过去重逻辑性能提升30%以上。

2. 建立适配查询逻辑的联合索引 #

单值product_name索引完全适配不了你的查询逻辑，需要创建覆盖所有过滤、关联、查询字段的联合索引，避免回表查询：

• products表创建联合索引：

CREATE INDEX idx_products_search ON products(is_active, parent_product_id, is_laboratory, product_generic_name_id, product_name, id);

• product_generic_name表创建联合索引：

CREATE INDEX idx_pg_search ON product_generic_name(is_active, product_generic_name, id);

3. 模糊查询逻辑优化 #

%xxx%格式的全模糊查询无法命中普通B+树索引，针对这个场景的优化方案：

• 若使用MySQL 5.7+版本，可给products.product_name和product_generic_name.product_generic_name字段建立FULLTEXT全文索引，替换LIKE为MATCH() AGAINST()语法做全文检索，查询耗时可从分钟级降到毫秒级
• 若业务允许，可调整检索规则为仅后缀模糊匹配（即xxx%），即可直接命中普通B+树索引，无需额外改造。

4. 分页逻辑优化 #

Spring Data JPA的Page默认会先执行COUNT查询统计总条数，300万表的COUNT查询本身就会占用大量耗时，如果业务不需要展示总页数、总条数，可以将返回值从Page<Products>改为Slice<Products>，跳过COUNT查询，分页性能可提升一倍以上。

5. 长期大数据量方案 #

如果后续数据量持续增长到千万级以上，可引入Elasticsearch做专门的全文检索引擎，将商品名称、通用名称等需要检索的字段同步到ES，先从ES检索到符合条件的商品ID，再到数据库查询完整数据，适配更复杂的检索场景。

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