Как повысить эффективность кэширования операторов с помощью заполнения параметров в предложении

Вступление

Недавно я наткнулся на следующую тему в Твиттере:

Менее известная функция jOOQ, о которой разработчики не знают, но базы данных будут рады! Автоматическое заполнение списка (для предотвращения конфликтов в кэшах плана выполнения/слишком большого количества сложных анализов) Автоматическое заполнение списка (для предотвращения конфликтов в кэшах плана выполнения/слишком большого количества сложных анализов)

Эта функция jOOQ действительно очень полезна, так как она уменьшает количество инструкций SQL, которые необходимо создавать при динамическом изменении параметров предложения IN.

Начиная с Hibernate ORM 5.2.18, теперь можно использовать заполнение параметров в предложении, чтобы повысить эффективность кэширования инструкций SQL.

В этой статье я собираюсь объяснить, как работает этот новый механизм и почему вы обязательно должны учитывать его при использовании системы реляционных баз данных, которая поддерживает кэширование плана выполнения.

Поведение по умолчанию

Сейчас , учитывая, что у нас есть следующая сущность JPA:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Integer id;

    private String title;

    //Getters and setters omitted for brevity
}

И, допустим, мы хотим загрузить несколько Разместите сущности по их идентификаторам, используя следующий запрос сущности JPA:

List getPostByIds(
        EntityManager entityManager, 
        Integer... ids) {
    return entityManager.createQuery(
        "select p " +
        "from Post p " +
        "where p.id in :ids", Post.class)
    .setParameter("ids", Arrays.asList(ids))
    .getResultList();
}

При выполнении следующего тестового случая:

assertEquals(
    3, 
    getPostByIds(entityManager, 1, 2, 3).size()
);

assertEquals(
    4, 
    getPostByIds(entityManager, 1, 2, 3, 4).size()
);

assertEquals(
    5, 
    getPostByIds(entityManager, 1, 2, 3, 4, 5).size()
);

assertEquals(
    6, 
    getPostByIds(entityManager, 1, 2, 3, 4, 5, 6).size()
);

Hibernate выполнит следующие инструкции SQL:

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (? , ? , ?)
"], 
Params:[
    1, 2, 3
]

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (?, ?, ?, ?)
"], 
Params:[
    1, 2, 3, 4
]

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (? , ? , ? , ? , ?)
"], 
Params:[
    1, 2, 3, 4, 5
]

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (? , ? , ? , ? , ? , ?)
"], 
Params:[
    1, 2, 3, 4, 5, 6
]

Каждый вызов генерирует новую инструкцию SQL, поскольку предложение IN query требует другого количества параметров привязки.

Однако, если базовая реляционная база данных предоставляет кэш планов выполнения, эти 4 SQL – запроса создадут 4 разных плана выполнения.

Поэтому, чтобы повторно использовать уже сгенерированный план выполнения, нам нужно использовать ту же инструкцию SQL Строка значение для нескольких комбинаций параметров привязки в предложении.

Заполнение параметров в предложении

Если вы включите режим гибернации.запрос.in_clause_parameter_padding Спящий режим


    name="hibernate.query.in_clause_parameter_padding"
    value="true"

И повторите предыдущий тестовый случай, Hibernate создаст следующие SQL-запросы:

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (?, ?, ?, ?)
"], 
Params:[
    1, 2, 3, 3
]

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (?, ?, ?, ?)
"], 
Params:[
    1, 2, 3, 4
]

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (? , ? , ? , ? , ? , ? , ? , ?)
"], 
Params:[
    1, 2, 3, 4, 5, 5, 5, 5
]

Query:["
    SELECT  p.id AS id1_0_, p.title AS title2_0_
    FROM    post p
    WHERE   p.id IN (? , ? , ? , ? , ? , ? , ? , ?)
"], 
Params:[
    1, 2, 3, 4, 5, 6, 6, 6
]

Поэтому на этот раз требуется только 2 плана выполнения, так как и первые два запроса, и последние два имеют одинаковое количество значений параметров привязки.

Это возможно, потому что Hibernate теперь заполняет параметры до следующей степени 2 числа. Таким образом, для 3 и 4 параметров используются 4 параметра привязки. Для 5 и 6 параметров используется 8 параметров привязки.

Круто, правда?

Вывод

Если вы используете Oracle или SQL Server, то вы можете воспользоваться преимуществами кэширования плана выполнения. Функция заполнения параметров в предложении увеличивает вероятность повторного использования уже сгенерированного плана выполнения, особенно при использовании большого количества параметров в предложении.