Spring JPA和JDBC模板——用IN子句执行非常慢的选择查询

Question

我正在尝试从我的Java项目中执行以下查询。我正在使用MySQL和数据存储，并已将Hikari CP配置为数据源。

SELECT iv.* FROM identifier_definition id 
INNER JOIN identifier_list_values iv on id.definition_id = iv.definition_id
where 
     id.status IN (:statuses)
 AND id.type = :listType
 AND iv.identifier_value IN (:valuesToAdd)

MySQL连接字符串：

jdbc:mysql://hostname:3306/DBNAME?useSSL=true&allowPublicKeyRetrieval=true&useServerPrepStmts=true&generateSimpleParameterMetadata=true

当我从MySQL workbench执行同样的查询时，它在0.5秒内返回结果。

然而，当我在JPA存储库或Spring JDBC模板中执行相同的操作时，几乎需要50秒的时间。

此查询有2个IN子句，其中状态集合只有3个项目，而标识值集合有10000个项目。

当我使用JDBC模板在没有命名参数的情况下执行原始SQL查询时，它在2秒内得到结果。然而，这种方法对SQL注入很敏感。

JPA和JDBC模板都使用了Java PreparedStatement。我的直觉是，在添加大型参数集时，底层PreparedStatement会导致性能问题。

如何提高查询性能？

下面是我正在使用的JDBC模板代码：

@Component
public class ListValuesDAO {

    private static final Logger LOGGER = LoggerFactory.getLogger(ListValuesDAO.class);

    private final NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    public ListValuesDAO(DataSource dataSource) {
        jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public void validateListOverlap(List<String> valuesToAdd, ListType listType) {

    String query = "SELECT iv.* FROM identifier_definition id  " +
            "INNER JOIN identifier_list_values iv on id.definition_id = iv.definition_id where " +
            "id.status IN (:statuses) AND id.type = :listType AND iv.identifier_value IN (:valuesToAdd)";

    List<String> statuses = Arrays.stream(ListStatus.values())
            .map(ListStatus::getValue)
            .collect(Collectors.toList());

    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("statuses", statuses);
    parameters.addValue("listType", listType.toString());
    parameters.addValue("valuesToAdd", valuesToAdd);

    List<String> duplicateValues = jdbcTemplate.query(query, parameters, new DuplicateListValueMapper());

    if (isNotEmpty(duplicateValues)) {
        LOGGER.info("Fetched duplicate list value entities");
    } else {
        LOGGER.info("Could not find duplicate list value entities");
    }
}

编辑- 1

我看到这篇文章，其他人在MSSQL服务器上使用准备语句运行选择查询时遇到了类似的问题。MySQL中是否有像“sendStringParametersAsUnicode”这样的属性？

编辑-2

已尝试启用一些与 MySQL 性能相关的属性。结果还是一样的。

jdbc:mysql://localhost:3306/DBNAME?useSSL=true&allowPublicKeyRetrieval=true&useServerPrepStmts=true&generateSimpleParameterMetadata=true&rewriteBatchedStatements=true&cacheResultSetMetadata=true&cachePrepStmts=true&cacheCallableStmts=true

Answer 1

相反，将列表传递给IN子句，将列表作为逗号分隔的字符串传递，并在查询中使用

从string_split（：valuesToAdd，'，'）中选择值

因此，您的查询将如下所示

SELECT iv.* FROM identifier_definition id  
     INNER JOIN identifier_list_values iv on id.definition_id = iv.definition_id 
where id.status IN (:statuses) AND id.type = :listType AND iv.identifier_value 
    IN (select value from string_split(:valuesToAdd, ','))

string_split是SQL服务器中的一个函数，MySQL可能有类似的函数

Answer 2

要添加到表中的复合索引：

id:  INDEX(type, status, definition_id)
id:  INDEX(definition_id, type, status)
iv:  INDEX(identifier_value, definition_id)
iv:  INDEX(definition_id, identifier_value)

对于 jdbc，连接参数应包含如下内容

?useUnicode=yes&characterEncoding=UTF-8

如需进一步讨论，请为每个表提供SHOW CREATE TABLE，并为任何有问题的查询提供EXPLAIN SELECT…。

Answer 3

我认为应该在JPA中启用“show_sql”为true，然后尝试，我认为它运行多个查询是因为延迟加载，因此它可能需要时间。