8.2.1.18 Function Call Optimization — MySQL 5.7

8.2.1.18 Оптимизация вызовов функций

Функции MySQL помечаются внутренне как детерминированные или недетерминированные. Функция является недетерминированной, если, при фиксированных значениях её аргументов, она может возвращать разные результаты при разных вызовах. Примеры недетерминированных функций: RAND(), UUID().

Если функция помечена как недетерминированная, ссылка на неё в WHERE-оператор вычисляется для каждой строки (при выборке из одной таблицы) или комбинации строк (при выборке из соединения нескольких таблиц).

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

Недетерминированные функции могут повлиять на производительность запроса. Например, некоторые оптимизации могут быть недоступны, или может потребоваться больше блокировок. В последующем обсуждении используется RAND(), но это применимо и к другим недетерминированным функциям.

Предположим, что таблица t имеет такое определение:

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));

Рассмотрим два запроса:

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

Оба запроса, по-видимому, используют поиск по первичному ключу из-за сравнения по равенству с первичным ключом, но это верно только для первого из них:

Первый запрос всегда генерирует не более одной строки, потому что POW() с константными аргументами является константным значением и используется для поиска по индексу.
Второй запрос содержит выражение, использующее недетерминированную функцию RAND(), которая не является константой в запросе, но на самом деле имеет новое значение для каждой строки таблицы t. Следовательно, запрос считывает каждую строку таблицы, оценивает предикат для каждой строки и выводит все строки, для которых первичный ключ совпадает со случайным значением. Это может быть ноль, одна или несколько строк, в зависимости от значений столбца id и значений в последовательности RAND().

Последствия недетерминизма не ограничиваются операторами SELECT. Данный оператор UPDATE использует недетерминированную функцию для выбора строк, которые должны быть изменены:

UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

Предполагается, что цель состоит в том, чтобы обновить не более одной строки, для которой первичный ключ соответствует выражению. Однако может быть обновлено ноль, одна или несколько строк, в зависимости от значений столбца id и значений в последовательности RAND().

Описанное поведение имеет последствия для производительности и репликации:

Поскольку недетерминированная функция не производит константное значение, оптимизатор не может использовать стратегии, которые могли бы быть применимы в противном случае, такие как поиск по индексу. Результатом может быть сканирование таблицы.
InnoDB может перейти к блокировке диапазона ключей, а не к блокировке одной строки для одной соответствующей строки.
Обновления, которые не выполняются детерминированно, небезопасны для репликации.

Сложности возникают из-за того, что функция RAND() оценивается один раз для каждой строки таблицы. Чтобы избежать многократного вычисления функции, воспользуйтесь одним из этих методов:

Переместите выражение, содержащее недетерминированную функцию, в отдельный оператор, сохранив значение в переменной. В исходном операторе замените выражение ссылкой на переменную, которую оптимизатор может рассматривать как константное значение:
```
SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;
```
Присвойте случайное значение переменной в таблице с производными данными. Этот метод приводит к присвоению значения переменной один раз до её использования в сравнении в WHERE-оператор:
```
SET optimizer_switch = 'derived_merge=off';
UPDATE t, (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt
SET col_a = some_expr WHERE id = @keyval;
```

Как упоминалось ранее, недетерминированное выражение в WHERE-оператор может препятствовать оптимизациям и привести к сканированию таблицы. Однако частичная оптимизация WHERE-оператора может быть возможна, если другие выражения детерминированы. Например:

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

Если оптимизатор может использовать partial_key для уменьшения набора выбранных строк, RAND() выполняется меньше раз, что уменьшает влияние недетерминизма на оптимизацию.