Hibernate中IN子句优化查询性能

banq
在本文中，我们探讨 Hibernate 中的参数填充概念以及它如何使用 IN 子句解决 SQL 语句缓存的难题。

我们会了解到，启用hibernate.query.in_clause_parameter_padding属性可以让 Hibernate 将 IN 子句中的参数数量调整为最接近的 2 的幂，从而有效减少缓存语句的数量并重新使用它们以提高性能。

在构建我们的持久层时，优化数据库查询性能是一项重要的需求。

数据库用来提高查询性能的一种技术是SQL语句缓存，它重用以前准备的 SQL 语句，以避免在数据库引擎中重复生成相同执行计划的开销。

然而，在处理IN 子句时，语句缓存会遇到挑战，因为它们通常具有不同数量的参数。

在本教程中，我们将探讨Hibernate 的参数填充功能如何解决此问题并提高带有 IN 子句的查询的语句缓存的有效性。

应用程序设置
在我们探讨 Hibernate 中的参数填充概念之前，让我们先建立一个将在本教程中用到的简单应用程序。

依赖项
让我们首先将Hibernate 依赖项添加到项目的pom.xml文件中：

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>6.5.2.Final</version>
</dependency>

此依赖项为我们提供了核心的 Hibernate ORM功能，包括我们在本教程中讨论的参数填充功能。

定义实体类
现在，让我们定义实体类：

@Entity
class Pokemon {
    @Id
    private UUID id;
    private String name;
    // standard setters and getters
}

Pokemon类是我们教程中的核心实体，我们将在接下来的部分中使用它来学习如何使用参数填充来加速涉及 IN 子句的查询的数据库 SQL 查询执行。

SQL语句缓存
SQL 语句缓存是一种用于优化数据库查询性能的技术。当我们的数据库收到 SQL 查询时，它会准备一个执行计划并执行它以检索结果。这个过程可能很耗时，尤其是对于复杂的查询。

为了避免重复这种开销，数据库引擎会根据准备好的语句缓存查询执行计划，并在后续使用不同参数值的执行中重新使用它们。

让我们考虑一个通过名称属性搜索Pokemon 的示例：

String[] names = { "Pikachu", "Charizard", "Bulbasaur" };
String query = "SELECT p FROM Pokemon p WHERE p.name = :name";
for (String name : names) {
    Pokemon pokemon = entityManager.createQuery(query, Pokemon.class)
      .setParameter("name", name)
      .getSingleResult();
    assertThat(pokemon)
      .isNotNull()
      .hasNoNullFieldsOrProperties();
}

在我们的示例中，SQL 语句SELECT p FROM Pokemon p WHERE p.name = :name仅准备一次，并在循环的每次迭代中重复使用。

命名参数:name在执行期间会被替换为names 数组中存储的实际参数值。此缓存机制可消除为同一 SQL 查询重复准备执行计划的开销。

使用 IN 子句进行 SQL 语句缓存
虽然 SQL 语句缓存在大多数情况下都能很好地发挥作用，但在处理具有不同数量参数的 IN 子句时效率有点低：

String[][] nameGroups = {
    { "Jigglypuff" },
    { "Snorlax", "Squirtle" },
    { "Pikachu", "Charizard", "Bulbasaur" }};
String query = "SELECT p FROM Pokemon p WHERE p.name IN :names";
for (String[] names : nameGroups) {
    List<Pokemon> pokemons = entityManager.createQuery(query, Pokemon.class)
      .setParameter("names", Arrays.asList(names))
      .getResultList();
    assertThat(pokemons)
      .isNotEmpty();
}

在我们的示例中，我们拥有多组Pokemon名称，我们使用 IN 子句检索Pokemon实体。但是，每组的名称数量不同，导致 IN 子句中的参数数量也不同。

在这种情况下，数据库会为每个查询生成一个单独的执行计划，每个查询的参数数量都不同。因此，语句缓存变得无效，因为每个查询都被视为一个新语句。

IN 子句的参数填充
为了解决 IN 子句的 SQL 语句缓存问题，Hibernate 5.2.18 引入了参数填充功能。即使 IN 子句中的参数数量发生变化，参数填充也允许我们重用缓存的语句。

我们可以通过将persistence.xml文件中的hibernate.query.in_clause_parameter_padding属性设置为true来启用此功能：

<property>
    name="hibernate.query.in_clause_parameter_padding"
    value="true"
</property>

使用Spring Data JPA时，我们可以通过在application.yaml文件中添加以下配置来启用参数填充：

spring:
  jpa:
    properties:
      hibernate:
        query:
          in_clause_parameter_padding: true

启用参数填充后，Hibernate 会将 IN 子句中的参数数量调整为最接近的 2 的幂，通过重复最后一个参数值来填充列表。

例如，如果我们的 IN 子句包含 3 个参数，Hibernate 会将其填充为 4 个参数。这确保对于具有 3 个或 4 个参数的查询只准备一个执行计划。

类似地，如果我们的 IN 子句中的参数数量在 5 到 8 之间，则 Hibernate 将在准备好的语句中使用 8 个参数。

为了更好地理解这一点，我们将在应用程序中启用 SQL 日志记录并查看绑定参数：

List<String> names = List.of("Pikachu", "Charizard", "Bulbasaur");
String query = "SELECT p FROM Pokemon p WHERE p.name IN :names";
entityManager.createQuery(query)
  .setParameter("names", names);

当我们运行上述代码时，我们将看到以下日志输出：

org.hibernate.SQL - select p1_0.id,p1_0.name from pokemon p1_0 where p1_0.name in (?,?,?,?)
org.hibernate.orm.jdbc.bind - binding parameter (1:VARCHAR) <- [Pikachu]
org.hibernate.orm.jdbc.bind - binding parameter (2:VARCHAR) <- [Charizard]
org.hibernate.orm.jdbc.bind - binding parameter (3:VARCHAR) <- [Bulbasaur]
org.hibernate.orm.jdbc.bind - binding parameter (4:VARCHAR) <- [Bulbasaur]

尽管我们提供了三个名称，但 Hibernate 已将 IN 子句填充为四个参数。它重复最后一个值Bulbasaur来填充第四个位置。

此功能有助于减少创建的执行计划的数量，从而提高使用 IN 子句时的性能和内存使用率。

当参数填充失败时
虽然参数填充是加快数据库中 SQL 查询执行速度的绝佳功能，但在某些情况下，它可能无法提供预期的好处，甚至会降低性能。

首先，参数填充对于不缓存执行计划的数据库（例如SQLite 、带有BLACKHOLE 存储引擎的MySQL等）毫无用处。在这种情况下，启用参数填充可能会因附加参数而引入不必要的开销。

此外，当 IN 子句中的参数数量非常少或非常多时，启用参数填充可能没有帮助。如果参数数量一直很少，则参数填充的好处可以忽略不计。

另一方面，如果参数数量非常多，参数填充将导致缓存中内存消耗过多，从而可能影响性能。